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.
