Our goal as analysts is to tell a story with our data and it is a well-known fact that data can be consumed much faster through visualization than through text. That is why it is vital that we put serious thought into how we present our data to the leaders we support.
Using different colors in our charts to call out facts about our data is a very good way to instantly tell a story. Your audiences' eyes can instantaneously split the data can focus on the results you want to direct them to first. All this can be done without saying a single word!
Creating Multi-Colored "Single-Series" Bar Charts
Take a look at the two bar charts below. Right away you can tell some of the bars are not like the others and this is good, because you audience is going to be immediately drawn to the unique-looking bars (ie the gray and checkered bars in the example).
Unfortunately, it's not so straightforward to build a chart that looks like the examples shown above. Yes, you could manually change the colors of individual bars each time, but when you do things manually there is a loss of time and a greater chance for errors. So how can we automate this task, allowing our bars to change their format based on the current month's data?
The Short Answer: By using two chart series and making them look like one!
Setting Up Your Chart Data
Instead of using a single line of data for your bar chart, you are going to need to set up 4 rows. The first two rows will house your data and the last two will store your Data Label values. To make your "new" chart data table, you are going to want to use similar formulas to those shown below.
The key to your formulas, is you are going to only want a single value per row within the two sections. For the first two data rows, you are going to want one value in each column to house the actual data point, while the other will have the value of the #NA error. That way in our chart only displays one bar between the two chart series.
You'll need to use the same idea for the data labels, with one row having the data point value and the other having a "" (blank) value.
Creating Your Bar Chart
Now that your data is set up, we can begin creating your chart. Simply highlight the cell range containing your two rows of data, go to the INSERT tab and select a 2-D Column chart (not stacked).
Below are the steps visually (click to enlarge):
Making Two Chart Series Appear As One
In order to create the effect of having only one chart series in your graph, you will simply need to overlap your two series by 100%. This can be done by right-clicking on one of chart bars and selecting the option to Format Data Series. Then select the Series Options icon and find where you can modify the Series Overlap setting (shown below).
Some Quick Chart Clean Up
This part is just my personal preference, but I will provide a few steps on how I like to clean up my charts to make them easier to look at.
Here's what I did:
- Give your chart a relevant title and also make it have a larger font size than the rest of the text within the chart
- Remove the Y-Axis Labels. We are going to be adding Data Labels later on, so there is no use having both (it's a bit redundant!)
- To save space, I like to have my chart legends overlay the chart area (typically near the upper right or left-hand corners)
- Move the X-Axis Labels below all the data (Format Axis > Axis Options > Label Position > Low)
- Delete the Gridlines and remove the chart border
Setting Up The Data Labels
This is the part I absolutely hate, but I'm able to get through it by telling myself, "Chris, you only have to do this once, you only have to do this once."
So let's dive into it! First you are going to need to add Data Labels to both Chart Series. You can do this by right-clicking on a bar within each series and selecting Add Data Labels.
Once you have added both sets of data labels, your chart should look similar to the bar chart shown below. You'll notice that we have all these #N/A errors showing up in the labels. This is why we created the two label rows in your Chart Data section, to rid our chart of these errors.
Linking The Data Labels With A Formula
In order to connect our data labels with a cell value, we will need to do the following steps
- Select a single Data Label (by double-clicking the desired label)
- Click into the Formula Bar
- Select the Cell you wish to pull from and hit the Enter key on your keyboard
You will need to repeat this step for all of your data labels until they are all connected to their respective data point.
The Polished Result!
Guess what? You now have a chart that can tell a much more effective story!
Learn More With This Example Workbook
I have created a sample workbook with 4 different variations of multi-colored bar charts. The workbook is completely unlocked so you can dig in and discover how the magic works. As always, in order to download this example file you will need to be a subscriber of my free newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.
How Do I Modify This To Fit My Specific Needs?
Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it!
I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).
I wish you the best of luck and I hope this tutorial gets you heading in the right direction!
Chris "Macro" Newman :)