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.

iPhone Prototyping in Excel?

I just completed what has to be the funniest project I've ever worked on.  Not a typo, I really mean Funny, as in I was laughing my ass off the whole time.  This is a departure for me, as I'm usually working on financial data (lately: not so funny), and like I've said before, there's no office banter around here.  But this particular gig amused me because I was "skinning" a workbook to appear on a Television Show, and the designers who supplied me with the graphics really nailed it.

The application is sort of secret so I can't say too much about it just yet, but the TV Show, on Canada's CBC network, is called Dragon's Den.  On this "reality" program, entrepreneurs pitch their ideas to a panel of wizened business executives - Dragons - who show no mercy and pay no compliments.  Occasionally, someone impresses one or more of the Dragons, and they literally "buy-in" to the idea.  Usually, they cut the hapless entrepreneur down and send him or her off, humiliated, (or worse: try to gain full control (51% ownership) of the start-up for a fraction of the asking amount.)

My Client will be appearing on Dragon's Den, which is taping next week.  He has invented an algorithm that he intends to evolve into an iPhone applicaton, among other things.  For now it resides as an Excel model, involving some reasonably sophisticated statistical analysis and math.  My mandate was to make the model presentable on the show.  Specifically, I needed to make the spreadsheet's input screen look like an iPhone app, so my client can get the actual Dragon's themselves to provide the input data.  This is a one-off re-skin, created just for this program, with the Dragon hosts depicted with oversized heads... it's going to be really funny when it airs.

My approach was to overlay a set of masking graphics over the input section of the spreadsheet, which contains a series of Drop-down list boxes.  Anyone interacting with the file should be able to select values in the list boxes as if doing so on an iPhone.  This design simultaneously proves the technical side (the model is running in Excel and it calculates... this!), while at the same time putting the idea into context - a portable application that you use... like this. Brilliant! I wish I'd thought of it.

It was easy enough to find the elements I needed: a hi-rez PNG file of a hand holding an iPhone does the trick.  You then need to slice it up into seperate pieces so you're removing the phone's screen - that's where your spreadsheet's elements will show through.  A transparent section won't do, because you'll need to click on the controls under it.   This graphic looks really good on a spreadsheet who's cells all have a black background.

Some traps: the graphics move around and stretch as you adjust column widths and such, so you'll want to anchor those down to keep them nice.  This is done by right-clicking, selecting Format Picture... PropertiesTab... "Don't Move or Size with Cells".  Excel is a bit buggy in that it can lose this setting if you group, copy or otherwise modify the graphic, so I wrote a little routine to flip this on and off universally.  This came in handy as I was laying out the screens.

Sub float_All_Objects(blnFloat As Boolean)
  
   Dim ctl As OLEObject
   Dim shp As Shape
   Dim plcmt As Variant
   
   On Error Resume Next
   
   plcmt = xlMove
   If blnFloat Then plcmt = xlFreeFloating
   
  'form controls
   For Each ctl In ActiveSheet.OLEObjects
       ctl.Placement = plcmt
   Next
   
  'shapes, meaning graphical elements
   For Each shp In ActiveSheet.Shapes
       shp.Placement = plcmt
   Next
   
   Set ctl = Nothing
   Set shp = Nothing
   
End Sub

Another issue is that you cannot change the font size in the standard screen controls (list boxes, buttons, etc) so if you need to scale your worksheet's zoom, (to make it readable on TV, for example,) the default font size is usually incorrect.  Instead, you must use oleControls - the difference is these controls require VBA code to interact with, but they also give you full... well, control over their property set, including size, font, source data, 3-D appearance, and a bunch more.

Many Power Users get these control types confused.  It's all about which tool bar you get them from, as this illustration illustrates:  Basic form controls come from the Forms toolbar, but oleControls come from the Control Toolbox.

It's not obvious how to configure the ole controls after you have placed one on your spreadsheet.  When you right-click on one, all you can do is control basic properties such as placement, border, and caption.  For more advanced properties you need to use the Visual Basic IDE.  Your worksheet must be in Design Mode so you can select the control, and then you may edit it's properties on the VB side.  One warning, these controls won't work at all if your user's fail to enable Macros, even if you haven't written any actual code.

I really enjoyed this challenge: make the model interactive, while completely removing any hint of "spreadsheet" so it doesn't beguile the television audience.  When the segment airs in a few months, you might not even realize that Excel is running the demo, which is just one (more) of the reasons why Excel is the ultimate prototyping framework.