A four-layer design for an Excel-based dashboard

When producing dashboards and reports in Excel it is tempting to build them as a single entity. There are good reasons to take a more modular approach. The modules can be visualised as a series of connected layers;

In their book Excel Dashboards and Reports, Michael.Alexander and John Walkenbach recommend a data model with three layers: data, analysis and presentation.The "analysis" layer is seen as a staging area.

The four level model set out here is similar, but  a separation is made been calculation and collation.

Where possible it is best to avoid having any data calculations in the Presentation Layer, This allows alternative versions of the Presentation to use the same data with limited risk of introducing inconsistencies.

Calculations are made as close as possible to the source Data

Although the data model has four layers, this does not necessarily mean that it is constructed in four different files. The physical design will depend upon the size and complexity of  the dashboard. It would be possible to have all four layers within a single Excel file.

Bibliography

Dashboards for Excel. by Jordan Goldmeier and Purnachandra Duggirala. Kindle Edition. APress 2015

Excel Dashboards and Reports, Michael.Alexander and John Walkenbach .

The Pie Chart and levels of consciousness

I have been through a succession of stages of understanding and enlightenment in respect of the pie chart. These can be seen as a progression through levels of consciousness (how pretentious does that sound?). Anybody for whom a discussion of pie charts would quickly lead to a state of unconsciousness,  stop reading now - save this for your next bought of insomnia

First Level
I realised that the name referred to pie the food not to pi the strange number with lots of decimal places. Maybe I was unique in mistakenly assuming that the name had something to do with the properties of a circle.

Second  Level

I realised that pie charts were a pain to draw. I was brought up in the Lower Palaeolithic, before the advent of spreadsheets. At school, drawing pie charts involved compasses (to draw circles) and protractors (to measure out angles). When I worked as a professional illustrator, I had to do all this but also very neatly and using ink.  Differentiating the different slices involved very carefully ruled hatching, precise stipple,  or sticking on pieces of expensive patterned film ("Letratone") cut precisely. In those days a spreadsheet was a major undertaking. You had to be really sure you wanted one.

Third  Level
Spreadsheets. Along came Loutus 1-2-3 and then Excel. I was delighted to find that Excel can produce very quickly. Not only that, it offered aesthetically pleasing variations such  as 3D and "exploding pie". I felt very grateful for the existence of computers. I recreated Manhatten using 3 D column charts.

Fourth Level
Having read the works of the data visualisation gurus - I realised, with a bit of an embarrassed sinking feeling, that I was supposed to deprecate pie charts. I realised that the exploding 3D charts made me look like a novice rather than an expert. I stopped using pie charts immediately. I scoffed at other people who still used pie charts and I felt superior.

Fifth Level
I realised that the pie chart can be good after all - if used in the right way. I felt stupid for not holding onto this when I jumped smugly to Level  Four.

Sixth Level
It's a pie chart...but not as we know it. I then realised that there are other kinds of charts which look like pie chart but are really something else. I could not see any missiles directed at these from the data visulaistaion gurus, so concluded that they may be safe to use


Seventh Level
Is there a seventh level or beyond? I have not got there yet. I will let you know

Some Books

I have stumbled about in the information arena for many years. Every now and then a particularly noteworthy book comes to my attention. Here are a few:


How to Lie with Statistics by Darrell Huff. Originally published in 1954, with illustrations by Irving Geis. A revised edition was published by Penguin Books (1973) with illustrations by Mel Calman. This was one of the first books I ever bought and is still excellent today
http://www.amazon.co.uk/How-Lie-Statistics-Penguin-Business/dp/0140136290


Meaningful Graphs: Converting Data into Informative Excel Charts by James M. Smith  (2014).  I have seen a lot of books on Excel- and owned a quite few over the years. Many are bloated and not very interesting. This one is very good - and one of the few I've felt worth buying since I discovered good online resources, such as Chandoo.org.

http://www.amazon.co.uk/Meaningful-Graphs-Converting-Informative-Charts/dp/0986054909

Dashboards for Excel by Jordan Goldmeier and Purnachandra Duggirala (2015). Apress. A really useful book - it has tips and tricks that would take you years to discover for yourself
https://www.amazon.co.uk/Dashboards-Excel-Jordan-Goldmeier/dp/1430249447

Storytelling with Data: A Data Visualization Guide for Business Professionals by Cole Nussbaumer Knafic. Kindle Books
A really good book. It looks at systematically at the different aspects of communicating using graphs. Because I like the book,  I can forgive the author for her overuse of the word "leveraging"

Some Books by Edward Tufte
http://www.edwardtufte.com/tufte/ebooks
Edward Tufte introduced the term “chartjunk” and the concept of data-ink ratio in his first book, The Visual Display of Quantitative Information (1983). The second edition was published in 2001 (ISBN 0-9613921-4-2).

His second book, Envisioning Information was published in 1990 Graphics Press, ISBN 0-9613921-1-8.(2001)

Visual and Statistical Thinking: Displays of Evidence for Making Decisions (1997) provides two fascinating case studies: the 1854 cholera epidemic and the 1986 Challenger space shuttle disaster

The Cognitive Style of PowerPoint: Pitching Out Corrupts Within (2006). 

So that's two of us who don't like Powerpoint! Hooray!

Visual Explanations: Images and Quantities, Evidence and Narrative
Graphics Press (1997)  ISBN 0-9613921-2-6.

Beautiful Evidence. Graphics Press, ISBN 0-9613921-7-7. 2006 


Some Books by Stephen Few
If you are interested in data visualisation then it is just a matter of time before you encounter Stephen Few. As well as the books listed below, he also publishes a lot of very good short articles, available free, on his website at www.perceptualedge.com


Now You See It: Simple Visualization Techniques for Quantitative Analysis, Stephen Few,  Analytics Press, 2009


Show Me the Numbers: Designing Tables and Graphs to Enlighten, Second Edition, Stephen Few, Analytics Press, 2012

Information Dashboard Design: Displaying data for at-a-glance monitoring, Second Edition, Stephen Few,  Analytics Press, 2013. Although this book is now in its second edition, I only have the first edition at present.

Signal: Understanding What Matters in a World of Noise, Stephen Few, Analytics Press, 2015


Some Books to avoid?
There are some books in which I have been particularly disappointed. At the risk of upsetting their authors, the following are books which I would definitely not recommend:

Using Charts to Create Effective Dashboards by T.Doyle
Having got this electronically to read on a Kindle, I was dispapointed to find that this book is very short and is barely more than a list of types of chart. There is little on dashboard design at all. Most of the samples it provides are good examples of how not to use charts. This book seems even to have vanished from the Amazon Kindle store now, making my copy an endangered species - soon to be deleted.

MS Excel as a Graphics Design Tool by Clarence Bjorkmann
This was an interesting concept but in the end I was left wondering why on earth anybody would want to use Excel in this way when even Windows Paint could produce better results and there are freeware graphics tools available. Furthermore, the graphic design in the book itself is not impressive. £3.47 I wish I'd spent differently.