Conditional Formatting in Excel

If you need to highlight data quickly, then applying Conditional Formatting is a great way to view data in an instance.

In this example, you will create a series of conditional formats to change the appearance of data in worksheet cells displaying the package volume and delivery exception rates of a regional distribution centre.

1. Select cells C4:C12.
2. On the Home tab, in the Styles group, click Conditional Formatting. On the menu that appears, point to Colour Scales, and then in the top row of the palette that appears, click the second pattern from the left.

Excel formats the selected range.

3. Select cells F4:F12.
4. On the Home tab, in the Styles group, click Conditional Formatting. From the menu that appears, point to Data Bars, and then click the light blue data bar format.

Excel formats the selected range.

5. Select cells I4:I12.
6. On the Home tab, in the Styles group, click Conditional Formatting. On the menu that appears, point to Icon Sets, and then in the left-hand column of the list of formats that appears, click the three traffic lights.

Excel formats the selected cells.

7. With the range I4:I12 still selected, on the Home tab, in the Styles group, click Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager opens.

8. Click the icon set rule, and then click Edit Rule.

The Edit Formatting Rule dialog box opens.

9. Select the Reverse Icon Order check box.

Excel reconfigures the rules so the red light icon is at the top and the green light icon is at the bottom.

10. In the red light icon’s row, in the Type list, click Percent.
11. In the red light icon’s Value field, type 80.
12. In the yellow light icon’s row, in the Type list, click Percent.
13. In the yellow light icon Value field, type 67.
14. Click OK twice to clear the Edit Formatting Rule dialog box and the Conditional Formatting Rules Manager.

Excel 2007 formats the selected cell range.

15. Click cell C15.
16. On the Home tab, in the Styles group, click Conditional Formatting. On the menu that appears, point to Highlight Cells Rules, and then click Less Than.

The Less Than dialog box opens.

17. In the left field, type 96%.
18. In the With list, click Red text.
19. Click OK.

The Less Than dialog box closes, and Excel displays the text in cell C15 in red.