Wednesday, September 16, 2015

Making sense of Conditional Formatting in Excel

Conditional Formatting in Excel is a bit like Charting in Excel. If you start simple and don't hit traps, you get good results first time. However, if you hit a trap or try to do complex work, it catches you easily. One of those features where the software makes simple tasks easy and difficult tasks difficult.

There are several articles, of course, in the Microsoft Support knowledge base, but they don't illuminate the detail as well as this one: Manage conditional formatting rule precedence

The points to bear in mind are are:
  • There are several types of criteria. Format only cells that contain is not the same as Use a formula to determine which cells to format.  The latter allows you to format neighbouring cells (usually on the same row) that don't contain values  for the criteria. The former only formats the cells that contain values for the criteria
  • For the formula method when used in a row wise manner
    • Select the range that will be formatted. It will be an absolute range - use an Excel Table for ease of data management
    • If the formula uses criteria that refers to relative cells in the range, use cells in the first row.
    • Make the cell addresses in the formula absolute in the column and relative in the rows. 
    • The formula should evaluate to True / False
  • 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
  • When rules conflict, the rule that is applied is the one that is higher in precedence (higher in the list in the dialog box).
  • For a range of cells, if a formatting rule is true, it takes precedence over a manual format.
  • The Stop If True check box is for backwards compatibility with older versions of Excel
I've got an Excel file you can play with.

No comments: