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>
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

zgall111

New Member
Joined
Dec 9, 2015
Messages
6
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.
 

Michael_E

Board Regular
Joined
Nov 20, 2015
Messages
61
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.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,851
Office Version
365
Platform
Windows, MacOS
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())



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.

 

zgall111

New Member
Joined
Dec 9, 2015
Messages
6
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())



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.

 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,851
Office Version
365
Platform
Windows, MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,091
Messages
5,466,579
Members
406,490
Latest member
Ankusharma

This Week's Hot Topics

Top