Create A Bar Chart With Separate Positive And Negative Colors
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.
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.