How to Change Bar Chart Color Based on Value

To change the bar chart color based on value, you need to group first. For example, to make a bar chart with the data below in 5 different groups (1 – 10, 10 – 20, 20 – 30, 30 – 40, 40 – 50).

Step 1: Re-arrange data similar to the table below, use formula =IF(AND(C$1<$B4,$B4<=C$2),$B4,"") to re-group data based on the range.

Step 2: Select data X and Y, and click the Insert Tab from the ribbon;

Step 3: Click the Column Chart in the Charts area;

Step 4: Click the chart to select X and Y columns (see above), then move mouse cursor to select the data range B3:G14, and extend the selection to whole data range (G14 in this example).

Step 5: You will get the chart as below.

Step 6: Remove the selection of Y value.

Step 7: Now the Bar chart is colored based on value.

Download Example

Leave a Reply