Apply conditional formatting

apply conditional formatting

Conditional formatting is formatting that appears different depending on what the cell contains. One example of this is a cell that displays negative numbers in red and positive ones in black, but the feature is very rich and can apply all types of formatting, not just colors.

Suppose you have a worksheet on which you enter monthly sales totals. If any salesperson's three-month average falls below $10,000 at any point, you want that cell to appear shaded with yellow, indicating that the salesperson is on probation. Figure 4-2 shows an example.

Figure 4-2: This worksheet uses conditional formatting to check for trouble with low productivity.

You could even go a step further with this example and set it up to show yellow if the value was between $9,000 and $9,999, and red if it were less than $9,000.

To create a conditional format:

1. Select the cell(s).
2. Choose Format > Conditional Formatting. The Conditional Formatting dialog box opens.
3. Set up criteria for the first condition. The most common way is to set it up by cell value, as shown in Figure 4-3. Make your selections from the drop-down lists. Then either enter a fixed value to compare to, or enter a cell address.

Figure 4-3: Create a conditional format.

4. Click the Format button. The Format Cells dialog box opens.
5. Use the Format Cells dialog box to set up the formatting for the condition, as shown in Figure 4-4. You can choose character formatting (Font tab), borders around the cell (Border tab), and background shading for the cell (Patterns tab).
6. Click OK.

Figure 4-4: Specify the formatting for the condition.

7. If you want to set up another condition, click the Add button and repeat the process. Otherwise click OK.
8. Test your formatting by changing the values to simulate the condition being triggered.