• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Chart Colors

You are here: Home / Excel Charts / Excel Chart Colors
Excel Chart Colors
August 3, 2016 by Mynda Treacy

I was reading a health magazine last week and the chart below caught my eye.

click image to enlarge (sorry about the quality, it's a photo of the magazine and photography is not my 'thing' 😉 )

An Example of Bad Chart Colors

×

There’s something fundamentally wrong with this chart. Can you see what it is?

Overall the layout is good. The columns are sorted from largest to smallest allowing us to quickly see the reasons for hospitalisation in order for each age band. The scale for each chart starts and finishes at the same point so the columns can be fairly compared.

But what do the column colors represent? My initial thought was that they would color code the different reasons, allowing me to quickly find the same reason in each age band.

Color can also be used to draw attention, but the largest and smallest columns both use strong bold colors, so that doesn’t appear to be the motivation here either.

After some time I realised that the colors simply match the column order. The gratuitous use of color has distracted me from the message, wasting my time for no good reason. The columns are already sorted from largest to smallest, I don’t also need different colors for each column.

Note: you may have noticed that the age bands are different sizes. Generally the bin sizes should be equal, but since the values are percentages of a population, as opposed to absolute values, it is ok to have different size bins.

The Power of Color

“A good design will use color to save the viewer from thinking too much. With several hundred million years of evolution behind us, color perception is deeply wired into our fabric. As a result, color perception is fast, accurate, automatic, and effortless.

On the other hand, thinking is a relatively recent evolutionary advance, and we are not yet very good at it. By thinking, I mean reading text, attaching meaning to an icon, searching memory, etc. These activities are relatively slow, error-prone, require mental resources and effort and take learning.

How many times have you misread a word? Probably often. How many times have you mistakenly seen blue as orange? Probably never, even if you are a dichromate.

In sum, a designer should strive to capitalize on our hard-wired perceptual capabilities whenever possible.”

Source:  http://www.visualexpert.com/FAQ/Part5/cfaqPart5.html#p5.6

Better Excel Chart Colors

Click image to enlarge

Better Excel Chart Colors

×

By using color to create relationships in the data I’m able to quickly glean the following insights:

  • Abdominal & Digestive System is an issue throughout life
  • Pregnancy & Childbirth and Mental Illness are prominent from 15 to 44 years old
  • Kidney & Urinary start causing issues from 25 to 44 and progressively become more common
  • Bones & Joints are problems when you’re young and later in life. No doubt for different reasons.
  • Issues with Ear, nose & throat seem to improve by 25

Trying to glean the above insights from the first chart is tediously slow and error prone because I’m forced to read each label and memorize their position. In contrast the second chart (above) allows me to easily single out a colour e.g. Abdominal & Digestive System, and focus on it as it moves through the age bands.

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Techniques used to build this chart:

  • There are actually 5 separate charts.
  • I used a Ghost Series to ensure the axis scale is the same on each chart.
  • I duplicated the series in each chart so I could have 2 sets of labels: one for the percentage set to 'outside end', and a separate label for the category label set to 'inside base'.
  • I set the color of each column manually by clicking on the column once to select the series and then again to select a single column. I then changed the fill color.

Reasons to Use Color in Charts

Color is perhaps the most powerful reporting tool we have available because it’s so quickly and clearly conveyed. We can use it to:

  1. Draw attention to a key metric or data point. Bold colours have more emphasis than lighter shades.
  2. Form distinct areas in your report. Use white space to segregate. An example of this is the wider space between the columns separating each age band.
  3. Link data points and areas together using the same colors to show they have something in common. Equally, don’t use the same colors for different types of data as this can be misleading.
  4. Less is more. While the colors in this chart are complimentary, if I’m honest there are probably too many colors for my liking. I’d be inclined to pick out 3 key reasons and color them, with the rest a paler shade of grey to detract attention away from them. This would also make it easier for people with color vision deficiency to read the chart. More on that in a moment.
  5. A good rule of thumb is to use no more than 3 different colors in your reports. You can use shades of a color to differentiate.

Common Color Mistakes

  1. Christmas Tree Effect: Too many bright and varied colors.
  2. Misleading color relationships: The same colors used to code different types of data.
  3. Gratuitous color: gradient fills and dark backgrounds in charts.

Formatting for Color-blind / Color Vision Deficiency

While we’re on the subject of color it’s a great time to share some resources for choosing colors suitable for readers with color vision deficiency:

  • Color Brewer: For choosing sequential, diverging and qualitative colors that are color blind safe, print friendly or photocopy safe.
  • Coblis Color Blindness Simulator: A helpful simulator to see how readers who have color vision deficiencies see the colors you used.
Excel Chart Colors

More Excel Charts Posts

excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.
stacked bar waffle chart

Stacked Bar Excel Waffle Charts

Stacked Bar Excel Waffle Charts are an alternate to using conditional formatting to build waffle charts, and some say they're easier.




Category: Excel Charts
Previous Post:Moving VBA Code and Forms Into PERSONAL.XLSBMoving VBA Code and Forms Into PERSONAL.XLSB
Next Post:Slicer Controlled Interactive Excel ChartsSlicer Controlled Interactive Excel Charts

Reader Interactions

Comments

  1. cristina

    February 18, 2022 at 5:16 am

    i love your posts !!

    Reply
    • Mynda Treacy

      February 18, 2022 at 9:16 am

      Thanks so much, Cristina!

      Reply
  2. Michael Pierce

    August 11, 2016 at 3:13 am

    I have two suggestions: (1) vary the colors a bit more and (2) only color the bars that show up in multiple categories.

    Although it looks nice, having most of the colors be some form of blue/green makes it difficult for me to immediately know which categories are related and I end up having to read the column labels to confirm. Might just be my eye, but it’s still a little tough for me to immediately “see” the connections.

    I’d also suggest dropping all of the one-off categories to a neutral grey color. For example, I was scanning through the various categories a few times looking for the match to “Eyes” in the 64+ category only to figure out there is no match. Likewise with “Respiratory System” in kids. If color is the methodology for connecting things, I expect everything with a color to have a match somewhere.

    Just some random thoughts…hope they are helpful.

    Reply
    • Mynda Treacy

      August 11, 2016 at 10:08 am

      Hi Michael,

      Thanks for your thoughts. I agree, as I mentioned in point 4 of my ‘Reasons to Use Color in Charts’.

      Cheers,

      Mynda

      Reply
  3. Judy A

    August 5, 2016 at 9:14 am

    I tried to identify what was wrong and lacked chart-savvy. I knew some things irritated me, but could not articulate them. I came up with
    1. “the percentages don’t add up to 100%”
    however, if you put in a bar for “other”, that would add no value and probably confuse since it would be a greater percentage than some of the other top 5 reasons.

    2. “bars for the same reason appear in different orders in the different groups”.
    Your approach to color-code the reasons solves this – you can order the bars by percentage AND make it easy to see what reasons occur to different ages.

    Reply
    • Mynda Treacy

      August 5, 2016 at 12:48 pm

      Hi Judy,

      Glad you had a go at figuring out what was wrong with this chart. You’re right, the bars don’t add up to 100%, but if we had an ‘other’ category this would detract from the top 5.

      Mynda

      Reply
  4. RK

    August 5, 2016 at 8:51 am

    I don’t quite follow how you duplicated the series in each chart so you could have 2 labels. I have tried to recreate the charts, but was not successful in doing so. Could you elaborate a little more on how that is accomplished? Thanks

    Reply
    • Mynda Treacy

      August 5, 2016 at 12:45 pm

      Hi RK,

      Each chart has 3 series. You can see them if you right-click the chart > Select Data. In the dialog box there are 3 legend entries (series).

      The Ghost series column fill is set to none so you can’t actually see it in the chart. The other 2 series are set to 100% overlap (right-click > format data series > series options), so they appear to be just one series.

      You can then set the labels on these two series differently so one label is the value and is outside end, and the other series is the category label set to inside base.

      If you download the sample file you can see the series and settings I’ve applied. If you’re still stuck please post your question on the Excel forum and upload your file and we’ll be happy to help.

      Mynda

      Reply
  5. Jon W.

    August 5, 2016 at 12:49 am

    The chart also leaves out 14-year olds. 🙂

    Your points about use of color are very good.

    Reply
    • Mynda Treacy

      August 5, 2016 at 9:11 am

      Cheers, Jon.

      14 year olds are at the height of puberty, including them might have skewed the results 😉

      Reply
  6. jomili

    August 4, 2016 at 11:19 pm

    Mindy,
    I can’t get the link to Lighthouse International to resolve. Can you post the URL of the website?

    Reply
    • Mynda Treacy

      August 5, 2016 at 9:15 am

      Hi Jomili,

      If you right-click the link you can choose ‘copy’ and that will give you the URL. Here it is without the anchor text: http://li129-107.members.linode.com/accessibility/design/accessible-print-design/effective-color-contrast/

      Mynda

      Reply
  7. Glenn Case

    August 4, 2016 at 10:35 pm

    One more suggestion to make the chart easier to read: Use horizontal bars so that the text label for each can be read without having to turn your head sideways. (In a magazine, this is probably not a big deal, since you can easily turn the mag, but with a PC monitor, it’s a pain.)

    Reply
    • Mynda Treacy

      August 5, 2016 at 9:12 am

      Good point, Glenn. I had the same thoughts and if you download the Excel file you’ll see I included a portrait version of the chart too.

      Mynda

      Reply
  8. Peter Buyze

    August 3, 2016 at 9:12 pm

    Good subject Mynda. I am always very particular about the quality of data presentation. You can either “score” well with your audience because you get the message across clearly, or you can ruin it completely because you get sidetracked by questions & discussions about details because of a crappy presentation. And colour is very often 1 of the most important aspects of a presentation.

    Having said all this, I am a weeny bit surprised by the 1st version of the Australian women graph at the top of the page. It is meant to show something, but because of the grey overlay it is not very clear. Even enlarging it does not improve it much. I don’t understand why you put that overlay in. The subsequent versions are fine.

    Last but not least: I have published a G+ post about your article – see https://plus.google.com/+PeterBuyze/posts/LaLaj9ebREu.

    Reply
    • Mynda Treacy

      August 3, 2016 at 9:15 pm

      Cheers, Peter.

      Sorry about the quality of the first image. There’s no grey overlay, it’s just a photo of the magazine. Unfortunately I couldn’t find a copy of it published online, I only had the hard copy.

      Thanks for your feedback. I’ll make mention of this so others don’t think the same.

      Mynda

      Reply
      • Adelaide Dupont

        August 4, 2016 at 7:19 am

        Are you able to make the image brighter once you’re in an editing programme/Preview?

        And also take out the noise?

        Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.