Microsoft Excel: Conditional Formatting

For Microsoft Office Excel 2003 and earlier versions

If you have ever manually formatted Excel cells to highlight important data, then you need conditional formatting. If you want an automatic way to easily display key values and results, then you need conditional formatting.

Conditional formatting in Microsoft Excel allows you to set rules for automatic cell formatting. If the rules (conditions) are met, then the formatting is applied. You can have up to 3 criteria in a cell.

• Sample-Excel: Conditional Formatting (zip file)

How to Apply Conditional Formatting

1. Select the cells to be formatted.
2. Choose Format > Conditional Formatting...
3. Leave the first drop-down box set to Cell Value Is (the other option is Formula Is which requires formula criteria.
4. In the second drop-down box, choose one of the operators such as 'greater than or equal to' or 'between'.
5. In the text box, type a number or a cell reference. For instance, you may want to format all cells with a value greater than the value in cell B5.
6. Click the Format button.
7. Choose any combination of Font, Border and/or Patterns.
8. Click OK.
9. To add another conditional format, click the Add button.
10. Repeat steps 3 to 8, choosing the criteria and format for the second condition. A third condition is also allowed.
11. Click OK to return to the worksheet and apply the formatting.

Change or Remove Conditional Formatting

1. Select the cells with the format that you want to change or delete.
2. Choose Format > Conditional Formatting...
3. To change, select Format for the condition you want to modify. To start from scratch, pick the Clear option and then pick your formatting choices.
4. To remove conditional formatting, choose Delete and check which conditions you want to delete.
5. Choose OK twice to apply the changes.

Find Cells That Have Conditional Formatting

1. To find all cells that have conditional formatting, click any cell. To find cells that have conditional formatting settings identical to the settings of a specific cell, click the specific cell.
2. Pick Edit > Go To.
3. Click Special.
4. Click Conditional formats.
5. Do one of the following:
   To find cells with any conditional formatting, click All below Data validation.
   To find cells with identical conditional formats, click Same below Data validation.
6. OK to return to the worksheet; cells with conditional formatting will be selected.

Copyright 1995-2008 The Software Pro^® & Dawn Bjork Buzbee - All Rights Reserved
(303) 699-6868 § Dawn@SoftwarePro.com § 11675 Bent Oaks Street, Suite 200; Parker, Colorado 80138 USA § www.SoftwarePro.com
Geek Speak Becomes People Friendly™
Microsoft Certified Application Specialist (MCAS) Instructor (Office 2007 & Windows Vista)
Certified Microsoft Office Specialist Master Instructor § Certified Microsoft Office Expert § Certified Women's Business Enterprise (WBE)
The Software Pro^® is a registered trademark