Colour coding a bar chart

zgall111

New Member
Joined
Dec 9, 2015
Messages
6
I have the data set shown below and I have use it to produce the following bar chart - https://www.evernote.com/l/AdyXDHxZg29OR6p8DIRRtveKyxIVErog394 - that I would like to colour code using this colour scheme (based on the category) - https://www.evernote.com/l/AdzKudLCwm5FJKIxFLz88LT7YwGThI7xpqo.

Can anyone explain how I do this?

Street NameTotal CountCategory
Second2022Numbers
First1914Numbers
Third1652Numbers
Main1607Other
Fourth1492Numbers
Maple1138Nature
Fifth1127Numbers
Park1104Nature
Church993Other
Sixth967Numbers
Pine904Nature
Birch846Nature
Railway801Other
Cedar783Nature
Seventh780Numbers
River670Nature
Spruce665Nature
Mill640Other
Poplar619Nature
Willow616Nature
Eighth591Numbers
Elm530Nature
Tenth527Numbers
Ninth521Numbers
Victoria501Royalty
Oak498Nature
King479Royalty
Sunset475Nature
Lake460Nature
Aspen428Nature
Centre395Other
Mountain395Nature
Lakeview379Nature
Queen371Royalty
Eleventh341Numbers
James334People
Smith333People
George327People
Martin311People
Albert308People
Campbell296People
William295People
North293Other
Woodland293Nature
Wilson288People
Elizabeth283Royalty
Riverside282Nature

<!--StartFragment--> <colgroup><col width="65" span="3" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I actually read that post previously but I can't figure out how to get it work for my specific use case, which is slightly different. He is using the data points themselves as the categories whereas I am using a data point plus a separate list of categories. I hope that makes sense.
 
Upvote 0
Put the category's at the top, if you want I can email you the chart I made, other than having the larger values at the bottom it looks just like yours but with the bars are different colors based on category.
 
Upvote 0
No, it's pretty much the same. As Michael says, put the category labels across the top row, as in my screen shot. The formula in D2, which is copied and pasted into D2:H48, is

=IF(D$1=$C2,$B2,NA())

ConditionalData.png


Select A1:A48, then hold Ctrl while selecting D1:H48, so both ranges are highlighted, and insert a bar chart. Double click the vertical axis, and check Plot Categories in Reverse Order and select Axis Crosses at Maximum Category, and format the tickmark spacing so every 1 tickmark is displayed.

ConditionalChart.png
 
Upvote 0
Is it possible to use this method and have axis labels on the outside of the bars? It appears that when stacked bar is selected, that option disappears.

No, it's pretty much the same. As Michael says, put the category labels across the top row, as in my screen shot. The formula in D2, which is copied and pasted into D2:H48, is

=IF(D$1=$C2,$B2,NA())

ConditionalData.png


Select A1:A48, then hold Ctrl while selecting D1:H48, so both ranges are highlighted, and insert a bar chart. Double click the vertical axis, and check Plot Categories in Reverse Order and select Axis Crosses at Maximum Category, and format the tickmark spacing so every 1 tickmark is displayed.

ConditionalChart.png
 
Upvote 0
Use clustered bars, plot columns A:B, D:H (adding B from before). Columns D:H plot over column B, but column B is in the chart. Select column B and add labels to that column, option Outside End.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top