Conditional Formatting In Excel 2007

In Microsoft Excel, Conditional Formatting is a technique to format cells based on one or more rules.Conditional Formatting helps you visually explore and analyze data collected in an Excel worksheet, detect critical issues and identify trends, patterns and exceptions.Conditional Formatting in Excel 2007 is a lot more improved and it is now possible to specify any number of rules for a cell or range of cells.

How To Apply Conditional Formatting in Excel 2007?

  • Select the cell or the relevant range of cells to be formatted
  • On the Home tab Click “Conditional Formatting“.
  • Choose Highlight Cells rules, select the appropriate rule and specify the condition for formatting the chosen cell or range of cells. For eg., to format the cells that are greater than a specified value, choose Greater Than, specify the value and the fill color.

How to specify custom rules for conditional formatting in Excel 2007?

To specify a custom rule,

  • Select the cell or the relevant range of cells to be formatted
  • On the Home tab Click “Conditional Formatting“.
  • Choose New Rule, select a rule type and edit the rule description.

To specify another rule for the same range of cells, repeat the above steps.

How to manage conditional formatting rules in an Excel 2007 workbook?

One can create, edit, delete, and view all conditional formatting rules in the workbook by using the Conditional Formatting Rules Manager dialog box. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. Against Show Formatting rule for, choose the worksheet whose rules you want to manipulate. All relevant rules for the worksheet are lists. These rules are evaluated in order of precedence.

Conditional Formatting in Excel

A rule higher in the list has greater precedence than a rule lower in the list. By default, new rules are always added to the top of the list and therefore have a higher precedence, but you can change the order of precedence by using the Move Up and Move Down arrows in the dialog box.

When two or more conditional formatting rules apply to a range of cells, you can have more than one conditional formatting rule that evaluates to true. In this scenarios, the rules may either conflict or may not conflict.

When rules don’t conflict – For example, if one rule formats a cell with a bold font and another rule formats the same cell with a green color, the cell is formatted with both a bold font and a green color. Because there is no conflict between the two formats, both rules are applied.

When rules conflict – For example, one rule sets a cell font color to green and another rule sets a cell font color to blue. Because the two rules are in conflict, only one can apply. The rule that is applied is the one that is higher in precedence.

How to Use Stop If True check box for Conditional formatting in Excel 2007?

Stop If True check box is used to control when conditional formatting rules’ evaluation stop.For backwards compatibility, you can select the Stop If True check box in the Manage Rules dialog box to simulate how conditional formatting might appear in earlier versions of Microsoft Office Excel that do not support more than three conditional formatting rules, or multiple rules applied to the same range.

For example, if you have more than three conditional formatting rules for a range of cells, Excel 2003 or any version earlier than MS Excel 2007:

  • Only evaluates the first three rules.
  • Applies the first rule in precedence that is true.
  • Ignores rules lower in precedence if they are true.

You can select or clear the Stop If True check box to change the default behavior:

  • To evaluate only the first rule, select the Stop If True check box for the first rule.
  • To evaluate only the first and second rules, select the Stop If True check box for the second rule.
  • To evaluate only the first, second, and third rules, select the Stop If True check box for the third rule.

But it must be noted that you cannot select or clear the Stop If True check box if the rule formats by using a data bar, color scale, or icon set.

Enjoy formatting cells with Conditional formatting in Excel 2007.

2 comments on “Conditional Formatting In Excel 2007
  1. I have added a conditional format rule that reads c$7 + 2 then format cell green colour.
    I have 3 other conditional format rules ( 0,=1,-1) that also color the cell.

    the new format is in ” ” in the rules section and doesn’t seem to operate. The other 3 all work fine.
    Any ideas what I am doing wrong?

  2. My bad – I was saving in 2003 format so it couldn’t take the 4th rule.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Recommended Videos

Conditional Formatting in Excel 2007
Conditional Formatting in Excel 2007
MS Excel - Conditional Formatting Part 1
MS Excel - Conditional Formatting Part 1
Excel 2007 Tutorial 6 - Intro to Conditional formatting
Excel 2007 Tutorial 6 - Intro to Conditional formatting
Excel Conditional Formating - Create your own rules - Complex Examples
Excel Conditional Formating - Create your own rules - Complex Examples
Excel 2007: Using Conditional Formatting
Excel 2007: Using Conditional Formatting