1-866-245-5224 sales@keystonelearning.com

Excel 2007: Set Conditional Formatting

Excel 2007: Set Conditional Formatting

Another way to ensure the proper data is being entered into a cell is with Conditional Formatting. With Conditional Formatting, you can set parameters on individual cells. When those parameters are met, Excel will automatically change the formatting of the affected cells for you.

For example, in the event you wanted only whole numbers between 1 and 100, if a cell was updated to display 150, you can have Excel flag that cell by changing the data within to red. This way, the invalid entry would still be allowed in your worksheet, but it would trigger a reminder to you that it needs to be checked.

Conditional Formatting is also great when watching budget numbers or stock prices. If the numbers fall below a set range, Excel can format them one way; yet, if they rise above a set range, Excel can format them another way.

Adding Conditional Formatting

     
  1. Select a cell or a range of cells
     
  2.  
  3.  Click the Conditional Formatting command in the Styles group on the Home tab
     
  4.  
  5. Choose Highlight Cells Rules
     
  6.  
  7. Choose the Rule that most closely matches your need
     

Figure 38. Conditional Formatting - Less Than Rule

    5.       Set your Value and your Format

    6.       Click OK

You can continue adding conditional formats to the same range of cells. Previous versions of Excel only allowed three conditional formats per cell – that number has significantly increased with Excel 2007.

Changing Conditional Formatting

     
  1. Select a cell or a range of cells
     
  2.  
  3. Click the Conditional Formatting command in the Styles group on the Home tab
     
  4.  
  5. Choose Manage Rules…
     

Figure 39. Conditional Formatting Rules Manager

    4.       Highlight the rule you want to change

    5.       Click Edit Rule…

    6.       Make your changes

    7.       Click OK

    8.       Click OK

Deleting One Rule

     
  1. Select a cell or a range of cells
     
  2.  
  3. Click the Conditional Formatting command in the Styles group on the Home tab
     
  4.  
  5. Choose Manage Rules…
     
  6.  
  7. Highlight the rule you want to change
     
  8.  
  9. Click Delete Rule…
     
  10.  
  11. Click OK
     
  12.  
  13. Click OK
     

Deleting All Rules from Selected Cell(s)

     
  1. Select a cell or a range of cells
     
  2.  
  3. Click the Conditional Formatting command in the Styles group on the Home tab
     
  4.  
  5. Choose Clear Rules
     
  6.  
  7. Click Clear Rules from Selected Cells
     

Deleting All Rules from the Worksheet

     
  1. Click the Conditional Formatting command in the Styles group on the Home tab
     
  2.  
  3. Choose Clear Rules
     
  4.  
  5. Click Clear Rules from Entire Sheet