Conditional formatting applies one or more rules to any cells you want. An example of a rule might be If the value is greater than 5000, color the cell yellow. By applying this rule to the cells in a worksheet, you'll be able to see at a glance which cells are more than 5000. There are also rules that can mark the top 10 items, all cells that are below the average, cells that are within a certain date range, and many more.
Optional: You can download this example for extra practice.
To create a conditional formatting rule:
- Select the cells you want to add formatting to.
- In the Home tab, click the Conditional Formatting command. A drop-down menu will appear.
- Select Highlight Cells Rules or Top/Bottom Rules. We'll choose Highlight Cells Rules for this example. A menu will appear with several rules.
- Select the desired rule (Greater Than, for example).
Selecting the Greater Than rule
- From the dialog box, enter a value in the space provided, if applicable. In this example, we want to format cells that are greater than $5000, so we'll enter 5000 as our value. If you want, you can enter a cell reference instead of a number.
- Select a formatting style from the drop-down menu.
Entering a value and formatting style
- The formatting will be applied to the selected cells.
The formatted cells
If you want, you can apply more than one rule to your cells.