Manage your message with conditional formatting

30 years ago Dan Bricklin and Bob Frankston developed VisiCalc - the world’s first electronic spreadsheet. The basic idea of a spreadsheet hasn’t changed much since, but the functionality has improved enormously. From SuperCalc to Lotus 1-2-3 and then Excel, the spreadsheet has been an immoveable favourite on our desktops.

But as revolutionary as VisiCalc was, it was only when the spreadsheet started to get visual that it went mainstream. Charting, graphing, plotting and showing data as pictures moved the spreadsheet out of the accountant’s office and into the marketing department, and indeed most other departments.

Numbers were talking a language that people could understand.

Conditional formatting

Microsoft Excel 2007 is the latest in a long and increasingly useful line of wonder-spreadsheets. The oh-so-easy-to-use Conditional Formatting in Excel does a great deal more than Microsoft could ever fit on the box.

It is no exaggeration to say that Excel is the world’s leading Business Intelligence tool.

Anyone faced with a screen full of figures can now quickly and intelligently make sense of them. And I mean quickly. Conditional formatting is perhaps more useful as a personal sense-making tool. With a click and a sweep coloured bars show you how big the big things are and how small the small things are. I could have said that the coloured bars show you how big the big NUMBERS are and how small the small NUMBERS are, but numbers represent things: sales, people, widgets, cases, key words.

So what these graphical bars are really telling us is where the problems and opportunities are: where we should direct our energies, or who we should pick the phone up to first.
Conditional formatting tells us what’s important and where we can leave well alone.

Conditional formatting is also a great presentation tool. It enables figures and graphics to be reported in one hit – a weakness of the traditional graph. Although graphs and charts are worth a thousand words, they also needed a table below it to enable the full story to be communicated. Conditional formatting allows both actual numbers, and the visual representation, to be reported together in a very compact space. Ideal for busy managers, complex reports, and for simply getting an important message across.

Key word visualisation

Consider the key words for this blog. Each time I do a posting the numbers increment on the key words, and new ones get added. After a few postings the numbers start to build. Put conditional formatting against those numbers and they look totally different.

I begin to see areas that perhaps should be more central to my blogging, and are presently being ignored. I’m not particularly surprised by some, but importantly I start to look at the key words differently.

Visualisation is not neutral

Interestingly, the two types of conditional formatting in this example also make me look at the numbers in a different way. With the yellow bars I see a continuum – more of some and less of others. With the traffic light flags I see “high performing” key words and “poorly performing” keywords. They are the same key words, the same numbers, and it’s the same person looking at the data – I just draw different conclusions. If I were using this to explain performance, I would need to think carefully about the presentation.

What we measure gets attention, and what we show visually gets us thinking in new ways about what we are doing. As the power of visuals improves in spreadsheets, so does our ability to demonstrate a particular angle of what we want the reader to understand. It’s something to think about when presenting information, and when consuming it.