What is Conditional Formatting?
▪ Conditional formatting allows you to select one or more cells, and create rules for when and how those cells are formatted.
▪ You can control the cell font, fill color and border setting, based on the selected cell’s contents, or based on the contents of another cell.
▪ If the rules (conditions) that you specified are met, then the formatting is applied.
▪ For example, you can set conditional formatting so that a cell turns blue or green, based on its value.
Apply Conditional Formatting to a cell
In this example, you’ll set conditional formats so that a cell:
- turns blue if it contains a value higher than 75 and
- turns green if it contains a value lower than 50.
Follow these steps to apply conditional formatting to cells:
1. Select the cells to be formatted. In this example, cells B2:B4 are selected.
2. On the menu bar, choose Format>Conditional Formatting…
3. In the Conditional Formatting dialog box, leave the first drop-down box set to Cell
4. In the second drop-down box, choose one of the operators. In this example, choose
5. In the text box, type a number or a cell reference. In this example, type the value you want to check — 75.
6. Click the Format button
7. On the Patterns tab, select a colour for the conditional formatting. In this example, a medium blue fill colour is selected.
You can also choose a Font format or a cell Border.
8. Click OK, to close the Format Cells dialog box.
9. If you want to add another conditional format, click the Add button.
10. Repeat steps 3 to 8, using the values and colors that you want for the second conditional format.
11. Click OK, to return to the worksheet.
In the example shown at the right, cell B2 contains a value of 76, so it is formatted with a blue fill color, because it’s greater than 75.
Cell B4 is green, because it’s value is less than 50.