Tuesday, 17 February 2009

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.

4 comments:

  1. Thanks. Very good advice that we all think we know but rarely use.

    ReplyDelete
  2. You are right and it links to your reporting post as well. We do need to focus on key areas and often we find ourselves immersed in data and need just relevant information.
    One caution i would have is the need to beable to stand back and see the whole picture, the conditioned information might be focussing us or distorting the perspective.

    The FSA has just highlighted that their focus on process and compliance meant they missed the picture on risk management in the big picture. A role you would expect a board to have. Robert

    ReplyDelete
  3. What a good point about missing the big picture.

    If we are measuring the wrong things, then no amount of visualisation is going to help us see what we are missing.

    Figuring out what is significant, rather than just what is measureable, is an art that comes through experience and good judgement. And it implies paying attention to the little voices, as well as the big voices.

    The FSA is a particularly good example that is on all our minds at the moment.

    ReplyDelete
  4. Thank you. From my experience often your senses will pick up vibs that things are not right before the data shows , you then start to test and explore the reason your senses are alerted.Look for evidence to support your feeling/hunch. But still accept data is key.
    Maybe an example of Heuristics at work? certainly has helped me

    ReplyDelete