How to color chart bars based on their values

(Chart data is made up)

This article demonstrates two ways to color chart bars and chart columns based on their values.

Excel has a built-in feature that allows you to color negative bars differently than positive values. You can even pick colors.

You need to use a workaround if you want to color chart bars differently based on a condition.

1. How to color chart bars differently if the chart value is negative?

All columns in this chart are blue.

1. Right-click on a column on the chart.
2. Click on "Format Data Series...".
3. Click "Fill" button.
   
4. Click on "Solid fill".
5. Click "Invert if negative", see the image above.
6. Pick a color for positive values.
7. Pick a color for negative values.

2. How to color chart columns based on a condition?

What if you want to color bars by comparing?

This chart example shows bars colored differently depending on the preceding value. If a value is larger than the previous one, it will be colored blue. Smaller than the previous value and the bar will be red.

2.1 How to build

The trick here is to split data into two different chart series, you can do that by placing them in two columns using formulas.

Formula in cell B2: =IF(A3>A2,A3,"")

Formula in cell C2: =IF(A3<A2,A3,"")

Copy these cells and paste them on cells below, as far as needed.

2.2 Insert chart

Now it is time to build the column chart, shown above.

1. Select values in in column A
2. Go to tab "Insert" on the ribbon
3. Click on "Insert column chart" button

2.3 Add data series

1. Right click on columns and click "Select Data..."
   
2. Click "Edit" button below "Legend Entries (Series)"
   
3. Click "Series values" button and select cell range B2:B33
4. Click OK
5. Click "Add" button
6. Select cell range C2:C33
7. Click OK

The chart changes to this:

2.4 Change chart gap width and series overlap

You can see that there are gaps between series.

1. Right click on a column
2. Click "Format Data Series..."
   
3. Change "Series Overlap" to 100%
   

This is what the chart looks like:

3. How to color chart bars/columns based on multiple conditions?

The image above demonstrates a chart that has bars/columns colored based on multiple conditions. It shows colored columns based on quarter, the color corresponds to the quarter number.

3.1 Prepare data

The image above shows the data, it is divided into four different columns. Each column corresponds to a quarter and is its own chart series.

You can create a formula that populates columns F to I accordingly based on the Month name in column D if you don't want to copy the values manually and paste them to their destinations cells.

Columns B and C are only there two create categories based on year and quarter for the months on the chart. The chart shows this on the x-axis (horizontal axis), the year and quarter are displayed below the months.

Explaining formula in cell F3

Step 1 - Calculate the relative position of the given item in the array

The MATCH function returns a number representing the relative position of an item in a cell range or array.

MATCH($D3, {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0)

becomes

MATCH("Jan", {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0)

and returns 1.

Step 2 - Calculate the quotient

The QUOTIENT function returns the integer portion of a division.

QUOTIENT(MATCH($D3, {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0), 4)+1

becomes

QUOTIENT(1, 4)+1

becomes

0+1

and returns 1.

Step 3 - Compare with column

The equal sign compares the values and returns TRUE if they match and FALSE if not. The COLUMNS function returns a number representing the number of columns in a cell range.

(QUOTIENT(MATCH($D3, {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0), 4)+1)=COLUMNS($F$2:F2)

becomes

1=COLUMNS($F$2:F2)

becomes

1=1

and returns TRUE.

Step 4 - Show value in cell if condition is met

The IF function returns one argument if the logical expression evaluates to TRUE and another if FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF((QUOTIENT(MATCH($D3, {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0), 4)+1)=COLUMNS($F$2:F2), $E3, "")

becomes

IF(TRUE, $E3, "")

The IF function returns the value in cell E3 if TRUE and nothing if FALSE.

IF(TRUE, $E3, "")

becomes

IF(TRUE, 0.1, "")

and returns 0.1.

3.2 Insert column chart

1. Select cell range F2:I26.
2. Go to tab "Insert" on the ribbon.
3. Click on "Column Chart" and a popup menu appears.
4. Click on "Clustered Column", a chart appears on the screen see image above.

3.3 Add values to the x-axis

1. Right-click on the chart.
2. Click on "Select Data...", a dialog box appears.
   
3. Click the "Edit" button. Another dialog box shows up on the screen.
   
4. Select cell range B3:D26.
5. Press Enter, click on "OK" button. You are now back to the first dialog box.
6. Click "OK" button to dismiss the dialog box.

3.4 Change gap width and series overlap

1. Right-click on any bar or column on the chart. A menu appears on the screen.
2. Click on "Format Data Series...". A pane shows up on the right side of the screen, see image above.
3. Click on "Series Options" button to access Series Overlap settings.
4. Change "Series Overlap" to 65%.
5. Change "Gap width" to 0%.
6. Close pane.

3.5 Change column colors

1. Right-click on any bar or column on the chart. A menu appears on the screen.
2. Click on "Format Data Series...". A pane shows up on the right side of the screen, see the image above.
3. Click the "Fill & Line" button.
4. Click on the black triangle next to "Fill" to expand settings.
5. Click radio button "Solid fill".
6. Click on Color, see image above.
7. Pick a color.
8. Click on another series on chart
9. Repeat step steps 5 to 8 until all series have been changed.
10. Close settings pane.

Download Excel file

Color-bars-by-valuev3.xlsx

---

Weekly Blog EMAIL

Email

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---