Excel.Tips.Net Welcome toExcel.Tips.Net

Helpful Links

Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment

Tips.Net Store

ExcelTips FAQ
ExcelTips Premium

Learn Access Now
Free Printable Forms

Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips

Advertise on the
ExcelTips Site

Newest Tips

Converting to Octal

Filtering Columns for Unique Values

Printing Multiple Worksheets on a Single Page

Changing the Default Font

Creating a Drawing Object

Determining a Value of a Cell

Understanding Macros

 

Conditional Formatting

Summary: A few ways to use conditional formatting on cells in your Excel worksheet. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.)

Excel includes a powerful feature that allows you to dynamically change the formatting of individual cells based on the results being displayed in that cell. For instance, you could make the text in the cell larger and red if a result is less than a certain threshold. Likewise, you could color the background of a cell based on the result of a formula.

To take advantage of conditional formatting, follow these steps:

  1. Enter your cell formula as you normally would.
  2. Choose Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box. (Click here to see a related figure.)
  3. Use the controls in the dialog box to specify the threshold or ranges you want to set for formatting to be changed.
  4. Click on the Format button. Excel displays the Format Cells dialog box. (Click here to see a related figure.)
  5. Make the changes in the dialog box to indicate how you want the cell to be formatted if your condition (entered in step 3) is met.
  6. Click on OK to close the Format Cells dialog box.
  7. Click on the Add button and define more conditions (and formats), if desired.
  8. Click on the OK button to close the Conditional Formatting dialog box.

If you are using Excel 95 you don't have access to conditional formatting. You can, however, create your own custom formats for cells so that you can change at least the text color of a result if it falls outside a specific bound. For example, let's say you wanted to have your result appear in red text if it is less than 100. You could do this in the following manner:

  1. Enter your cell formula as you normally would.
  2. Using the Cells option from the Format menu, apply any general formatting you would like for the cell.
  3. Make sure the Number tab is selected. (Click here to see a related figure.)
  4. At the bottom of the Category list, choose Custom. The formatting you had previously selected should appear in the Type box in the middle of the dialog box.
  5. In front of the formatting in the Type box, enter your condition and your color change. Each item should be in square brackets. In our example, you would place [<100][Red] in front of the pre-existing format.

You should note that this approach is a quick solution for conditional formatting using custom number formats, and that there is much more to successfully applying such formats. You can find out more by referring to Excel's on-line help system, which does a pretty good job discussing custom number formats. You can also use custom number formats like this not just in Excel 95, but in all subsequent versions of Excel, as well.

It is possible to get very creative with conditional formatting. However, it is not the answer to every formatting need. If you want to be even more creative (you know--bordering on outlandish), you can always develop a macro that will examine all the cells in your sheet or a specific range of cells you select and then change formatting in any way you wish.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2665) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003

A Picture is Worth Thousands! Your worksheets are not limited to holding numbers and text. You can also add graphics or easily create charts based on your data. Excel Graphics and Charts, available in two versions, helps you make your graphics and charts their absolute best.
 
Check out Excel Graphics and Charts today!