Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Conditional Formatting

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. (See Figure 1.)
  3. Figure 1. The Conditional Formatting dialog box.

  4. Use the controls in the dialog box to specify the threshold or ranges you want to set for formatting to be changed.
  5. Click on the Format button. Excel displays the Format Cells dialog box. (See Figure 2.)
  6. Figure 2. The Format Cells dialog box.

  7. 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.
  8. Click on OK to close the Format Cells dialog box.
  9. Click on the Add button and define more conditions (and formats), if desired.
  10. 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. (See Figure 3.)
  4. Figure 3. The Number tab from the Cells option of the Format menu.

  5. 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.
  6. 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 97, 2000, 2002, and 2003.

Related Tips:

More Power! For some people, the prospect of creating macros can be scary. Those who conquer their fears, however, find they become much more confident and productive once they learn how to make Excel do exactly what they want. ExcelTips: The Macros is an invaluable source for learning Excel macros. You are introduced to the topic in bite-sized chunks, pulled from past issues of ExcelTips. Learn at your own pace, exactly the way you want. Check out ExcelTips: The Macros today!

 

Comments for this tip:

Leticia Serrano    29 Oct 2013, 17:40
I need to reverse the order of names in my column, so that it reads Last Name then First in separate column. Thanks
mike crain    12 Apr 2012, 21:47
IHAVE SPREADSHEET WITH NAMES IN COLUMNS
I HAVE 2ND ONE WITH SAME COLUMN ITEMS
AND SO ON JUST WANT TO FILL OUT 1 AND IT
FILLS OUT THE 2ND ONE AT THE SAME TIME

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 3+4? (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.