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. Press with right mouse button on on a column on the chart.
2. Press with mouse on "Format Data Series...".
3. Press with left mouse button on "Fill" button.
   
4. Press with mouse on "Solid fill".
5. Press with left mouse button on "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. Press with mouse on "Insert column chart" button

2.3 Add data series

1. Press with right mouse button on on columns and press with left mouse button on "Select Data..."
   
2. Press with left mouse button on "Edit" button below "Legend Entries (Series)"
   
3. Press with left mouse button on "Series values" button and select cell range B2:B33
4. Press with left mouse button on OK
5. Press with left mouse button on "Add" button
6. Select cell range C2:C33
7. Press with left mouse button on 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. Press with right mouse button on on a column
2. Press with left mouse button on "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. Press with mouse on "Column Chart" and a popup menu appears.
4. Press with mouse on "Clustered Column", a chart appears on the screen see image above.

3.3 Add values to the x-axis

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

3.4 Change gap width and series overlap

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

Get the Excel file

Color-bars-by-valuev3.xlsx

Charts category

How to create a dynamic chart
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]

How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]

Color chart columns based on cell color
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]

Excel categories

Comments (2)