How to create a chart with conditional formatting in Excel?
For example, you have a score table of a class, and you want to create a chart to color scores in different ranges, such as greater than 90, less than 60, and between 60 and 90 as below screenshot shown, how could you handle it? This article will introduce a way to create chart with conditional formatting to solve it.
Recommended Excel Productivity Tools
To distinguish scores in different ranges in a chart, you can create the chart with conditional formatting in Excel.
1. Add three columns right to the source data as below screenshot shown:
(1) Name the first column as >90, type the formula =IF(B2>90,B2,0) in the first blank cell of this column, and then drag the AutoFill Handle to the whole column;
(2) Name the second column as <60, type the formula =IF(B2<60,B2,0), and drag the AutoFill Handle to the whole column;
(3) Name the third column as 60~90, type the formula =IF(AND(B2>=60,B2<=90),B2,0), and drag the AutoFill Handle to the whole column.
Now you will get a new source data as below screenshot shown:
2. Please select the Name column and the new three columns with holding the Ctrl key, and then click Insert > Insert Column or Bar Chart (or Column) > Clustered Column. See screenshot:
Now a chart with conditional formatting is created. You will see the scores greater than 90 are blue, the scores less than 60 are orange, while the scores between 60 and 90 are gray. See screenshot: