Condition "L"

Back in 1989 while using Lotus 1-2-3 in college, printer quality was not measured in dots per inch, but in dots, as in, I just got a new 9-dot printer.  A Lotus add-in called Sideways let you print your spreadsheets in landscape mode by rendering the output as a picture, scaling it, rotating it, and printing it out 9 dots at a time.  This was a major breakthrough for me, because suddenly my spreadsheets were transformed from IBM, holes on the side, six-page, Utility Bills into one-page, big-picture, Annual Reports.  Design meets Computation / Left meets Right - it all made sense to me, and was the beginning of what is shaping up to be a life-long mania for the idea of the spreadsheet.

Forward in time: it's post-2007 and Excel, in version 12.0, gives us complete control over every aspect of the look and layout of our reports. But now the screen is where we consume the majority of our spreadsheet salad; what was a static page is now dynamic.  One way to exploit this trend is to let the data control it's own appearance.  As new numbers stream in, (conditions change) the visual appearance of the numbers also change, allowing our eyes to zero in on the Information hidden amongst the Data.

Excel's Conditional Formatting feature helps us achieve this.  Though somewhat limited in 2003, the 2007 version of Excel has greatly expanded the ways conditional formatting can be applied, and the user interface has been improved dramatically.  (This is, perhaps, the only actual improvement in Excel 07, but that's another topic entirely.)  Regardless of what version you're using, it is the "Formula" option where the power of this really comes through.

For those who haven't tried it, conditional formatting lets us apply a format to a cell that is only visible if a certain condition is met.  The interesting thing is, the actual condition doesn't even have to be directly related to the cells in question.  It's one thing to say "If the value is negative, make it red.", it's another to say "If the Region is North East or North West, and the current month is between November and April, highlight the "Salt" expenditures in Blue, otherwise in show them in Yellow.  In this second example, the salt expenditures are a dollar amount, but it's the intersection of several other factors that controls how the number will appear.  That right there is pure formatting power.

The secret to correctly applying a Formula-based conditional format is knowing how relative vs. actual cell references affect the condition, and this is only a factor if you're applying the format to a range of cells.  One tip I use is to plan my format formula as though I'm writing  it for the first cell in the range, and intend to copy it to the rest of the cells - because effectively that's what Excel is doing.

For example, say I wanted to highlight every other row of cells, to give that retro altenating green & white striped computer printout look to my report.  In Excel 2003, select the entire report, and choose Conditional Formating from the Format menu, and select "Formula Is..." in the first condition's drop-down.  In the formula field, enter an IF function to evaluate the current cell's row number, like so:

=IF(MOD(ROW(A4),2)>0,TRUE,FALSE)

And then select the formatting you want - a light green cell background in this case.  Notice that we're not actually testing the data in the actual cells, but just the row numbers of the cells.  The reference to cell A4 in this case is a relative reference to the first cell in the table.  After saving the format, if I select any other cell in the table and inspect it's conditional formatting, that cell's address should appear in place of the A4.  If not, Excel might have substituted a named range or something, which is equivalent to an absolute reference - no good, because this doesn't "move" with the formula.

Using this formula, we won't disturb the alternating row coloring if the data is re-sorted or we remove or add rows.  And using this approach, along with the boolean functions AND and OR, there's no limit to how many different conditions we can include in the formatting rules for a given cell.

In Excel 2003 you ARE limited to 3 distinct conditional formats, or looks, for a given cell, plus the default format when no conditions are true.  Unfortunately, the different visual effects are not cumulative, so you will only see the formatting for the first condition of the three that evaluates to True.  No so in 2007 - you can display the first applicable format OR indicate that the rules to be combined.  For example: a value exceeding the Budget will be red, and an item covering more than 10% of the overall total will be bold, and forecasts have a blue background but actuals are green.  One glance tells me where a given value lies in all three of these axis.

In Excel 2007 you can also define up to 5 distinct looks or formats for a cell, and you get a lot more fidelity over the order and way the conditions are evaluated.  You also get expanded formatting options - specifically, the ability to create sliding color scales and to highlight unique or duplicate values are both very cool additions.

If you haven't yet mastered the conditional formatting power of Excel, I encourage you to dive in and start experimenting.  I would also love to hear about any interesting c.d. tricks you have gleaned.  Happy formatting.