Related Tutorials: Excel 2013 / 2010 / 2007 Conditional Formatting Tutorials
Adding conditional formatting to a cell in Excel allows you to apply different formatting options, such as color, to a cell or cells based on the data in the cell(s).
To implement conditional formatting:
- Create a set of conditions that control formatting changes in the target cells.
- Enter your data.
If the conditions you set are met by the data, then the formatting is applied.
Up to three conditions can be set for a cell, so it is possible to vary the formatting as the contents of a cell changes.
Conditional Formatting Example
Set conditional formatting so that the background color of a cell:
- turns red if the data in the cell is less than 0.
- turns blue if the data in the cell is between 0, and 100.
- turns green if the data in the cell is greater than 100.
Adding Conditional Formatting to a Cell
- Select a cell or range of cells to be formatted. For this example, select cell C1.
- Choose Format > Conditional Formatting from the menus.
- In the first drop-down box on the left, choose the Cell Value Is option if it is not already selected.
- In the second drop-down box from the left, choose an operator. For this example, choose less than.
- In the text box on the right, type a number or a cell reference. For this example, type a zero ( 0 ).
- Click the Format button.
- Click on the Patterns tab.
- Select a color for the conditional formatting, choose red, for this example.
- Click OK
To add the other conditional formats, click the Add button. Repeat steps 3 to 8, using the numbers and colors outlined above for the second and third conditional formats.
Click OK, to return to your spreadsheet.
Enter various numbers, such as -10, 45, and 125, into cell C1 to test that the background color of the cell changes as the
number in the cell changes.