Create A Bar Chart With Separate Positive And Negative Colors

By Chris Newman •  Updated: 09/10/15 •  6 min read
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).

Vary the colors of same-series data markers in a chart

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

Vary the colors of same-series data markers in a chart

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.

Color Coded Bar Charts with Microsoft Excel

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):

Color Coded Bar Charts with Microsoft Excel

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).

Excel Chart Series Overlap Bars Columns

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.

How to Make Bars Different Colors in an Excel Chart

Here's what I did:

  1. Give your chart a relevant title and also make it have a larger font size than the rest of the text within the chart
  2. 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!)
  3. To save space, I like to have my chart legends overlay the chart area (typically near the upper right or left-hand corners)
  4. Move the X-Axis Labels below all the data (Format Axis > Axis Options > Label Position > Low)
  5. 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.

How to Make Bars Different Colors in an Excel Chart

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

  1. Select a single Data Label (by double-clicking the desired label)
  2. Click into the Formula Bar
  3. 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.

Excel Different Colored Column Chart Same Series Appearance

The Polished Result!

Guess what? You now have a chart that can tell a much more effective story!

Spreadsheet Chart Bar Callout Specific Bar in Graph

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.

Examples of Multiple colored bar charts Excel Workbook File

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.

Keep Learning

Chris Newman

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.