Author: Oscar Cronquist Article last updated on May 11, 2021

color chart bars by value

(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?

column chart containing negative values

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.
    format data series - invert if negative
  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.

color bars - chart1

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.

color bars - data

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

color column chart by comparing values4

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..."
    color bars by value - select data source
  2. Click "Edit" button below "Legend Entries (Series)"
    color bars by value - edit 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:

color bars - chart

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..."
    color bars - format data series
  3. Change "Series Overlap" to 100%
    color bars - format data series1

This is what the chart looks like:

color bars - chart1

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

How to color chart bars columns based on multiple conditions 1

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

How to color chart bars columns based on multiple conditions 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.

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

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

How to color chart bars columns based on multiple conditions insert 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

How to color chart bars columns based on multiple conditions horizontal axis

  1. Right-click on the chart.
  2. Click on "Select Data...", a dialog box appears.
    How to color chart bars columns based on multiple conditions add x axis values
  3. Click the "Edit" button. Another dialog box shows up on the screen.
    How to color chart bars columns based on multiple conditions add x axis labels
  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.

How to color chart bars columns based on multiple conditions horizontal axis2

3.4 Change gap width and series overlap

How to color chart bars columns based on multiple conditions gap width

  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

How to color chart bars columns based on multiple conditions chart column color 1

  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