Results 1 to 9 of 9

Colour coding a bar chart

This is a discussion on Colour coding a bar chart within the Excel Questions forums, part of the Question Forums category; I have the data set shown below and I have use it to produce the following bar chart - https://www.evernote.com/l/AdyXDHxZg...veKyxIVErog394 ...

  1. #1
    New Member
    Join Date
    Dec 2015
    Posts
    6

    Default Colour coding a bar chart

    I have the data set shown below and I have use it to produce the following bar chart - https://www.evernote.com/l/AdyXDHxZg...veKyxIVErog394 - that I would like to colour code using this colour scheme (based on the category) - https://www.evernote.com/l/AdzKudLCw...LT7YwGThI7xpqo.

    Can anyone explain how I do this?

    Street Name Total Count Category
    Second 2022 Numbers
    First 1914 Numbers
    Third 1652 Numbers
    Main 1607 Other
    Fourth 1492 Numbers
    Maple 1138 Nature
    Fifth 1127 Numbers
    Park 1104 Nature
    Church 993 Other
    Sixth 967 Numbers
    Pine 904 Nature
    Birch 846 Nature
    Railway 801 Other
    Cedar 783 Nature
    Seventh 780 Numbers
    River 670 Nature
    Spruce 665 Nature
    Mill 640 Other
    Poplar 619 Nature
    Willow 616 Nature
    Eighth 591 Numbers
    Elm 530 Nature
    Tenth 527 Numbers
    Ninth 521 Numbers
    Victoria 501 Royalty
    Oak 498 Nature
    King 479 Royalty
    Sunset 475 Nature
    Lake 460 Nature
    Aspen 428 Nature
    Centre 395 Other
    Mountain 395 Nature
    Lakeview 379 Nature
    Queen 371 Royalty
    Eleventh 341 Numbers
    James 334 People
    Smith 333 People
    George 327 People
    Martin 311 People
    Albert 308 People
    Campbell 296 People
    William 295 People
    North 293 Other
    Woodland 293 Nature
    Wilson 288 People
    Elizabeth 283 Royalty
    Riverside 282 Nature

  2. #2
    New Member
    Join Date
    Dec 2015
    Posts
    6

    Default Re: Colour coding a bar chart

    Anyone? Could really use some help with this.

  3. #3
    Board Regular
    Join Date
    Nov 2015
    Posts
    61

    Default Re: Colour coding a bar chart

    Set each category as it's own data set, this blog goes a bit more in depth but the concept is the same.

    Conditional Formatting of Excel Charts - Peltier Tech Blog

  4. #4
    New Member
    Join Date
    Dec 2015
    Posts
    6

    Default Re: Colour coding a bar chart

    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.

  5. #5
    Board Regular
    Join Date
    Nov 2015
    Posts
    61

    Default Re: Colour coding a bar chart

    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.

  6. #6
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,365

    Default Re: Colour coding a bar chart

    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
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  7. #7
    New Member
    Join Date
    Dec 2015
    Posts
    6

    Default Re: Colour coding a bar chart

    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.

    Quote Originally Posted by Jon Peltier View Post
    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.


  8. #8
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,365

    Default Re: Colour coding a bar chart

    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.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  9. #9
    New Member
    Join Date
    Dec 2015
    Posts
    6

    Default Re: Colour coding a bar chart

    Thanks! That worked

    Quote Originally Posted by Jon Peltier View Post
    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com