home | about kurtosis | courses | course calendar | booking information | ideas | technique | work in progress | clients | contact us  

Small multiples and Microsoft Excel's =REPT function

How to get a lot of data onto one page


You want to present a lot of data on one page, and you want it to be clear. In other words, you ideally want a graphical display rather than loads of numbers. Can be quite difficult. But try this.

Let's suppose you work in a district general hospital and you want to look at a year's worth of data for ENT. You want to show the number of day cases done each week by each of the ENT surgeons, and you want the service manager and clinical director  to be able to see the volumes, the patterns and the variation. All very quickly. And graphically.

Your data might look a bit like the data in Sheet0 of the Excel workbook that you can download here:

Small Multiples.xls

Some managers and clinicians will be happy with this table as it stands, but if you want to show it graphically, and you want to be able to compare them all within one eye-sweep, here's what to do.

Step 1

Data like this, where chronological time is a dimension, is best shown graphically where time moves from left-to-right rather than top-to-bottom. So copy the data and paste special onto Sheet1 using the Transpose button.

Step 2

Insert blank rows above each consultant's data. You'll end up with a table like the one in Sheet2.

Step 3

To make sure that everything will fit onto one page, select the whole worksheet and make the font size 8 instead of 10. Also, while you're at it, edit the WOA labels on row 3 so that they read 27, 28, 29 etc. instead of Week 27, Week 28, week 29 etc. Also, change the column width of columns C to BC so that they are 2 units wide (19 pixels) instead of the default setting of 8.43. You'll end up with a table that looks like the one on Sheet3.

Step 4

In the blank rows above each consultant's data you're going to create a graphical histogram within the spreadsheet. Start by going to cell C4, which contains the data for Consultant A in week 27 (as it happens this surgeon didn't do any day cases in that week but it doesn't matter, we'll still enter the function). Type =REPT("|",C5). When you hit return you'll wonder what the point of this is, because nothing will appear in the cell, but hang on in there. You also need to do something else while you're in cell C4. You need to format that cell so that the text alignment in that cell is at 90 degrees. Once you've done that, drag the contents of cell C4 across to all of the cells in row C (but not including the final cell, so stop dragging when you get to cell BB4. You should end up with something that looks like Sheet4, and by this time you'll be starting to see where this is going.

Step 5

You now need to make the height of row C taller than the default setting. Make it 40 units. Then copy the contents of row C to all of the blank rows above each consultant's data to end up with something that looks like Sheet6. If you copy the row rather than the individual cells, the rows will automatically re-size to a height of 40 units. The row with the histogram for the Grand Total data will need to be taller. Try a row height of 100 units.

Step 6

You now need to copy the row labels so that the rows with the vertical columns are labelled appropriately. In cell B4, type =B5, and then copy this formula into cells B6, B8, B10 etc.

You can then hide the rows with the data in them (rows B3, B5, B7 etc.).

The icing on the cake is to get rid of the gridlines in the worksheet (Tools> Options, then in the View tab you need to uncheck the Gridlines box), then do whatever other formatting and titling takes your fancy to make it look nice and presentable.

The finished result is in Sheet7. Or you can view it as a .pdf document here:

ENT Weekly Day Case Workload By Consultant.pdf

By the way, there's lots more stuff like this on the Absolute Excel course.

Also, in case some people are wondering what the phrase "small multiples" has got to do with it, the background to that is that the great guru of information design, Edward Tufte, introduced the concept of small multiples in his first big book, The Visual Display of Quantitative Information. Although the phrase sounds technical, all he means by it is—and I'm taking this from Wikipedia—the business of creating a series of small, similar pictures, making a point through repetition. In his book Envisioning Information, he writes:

At the heart of quantitative reasoning is a single question: Compared to what? Small multiple designs, multivariate and data bountiful, answer directly by visually enforcing comparisons of changes, of the differences among objects, of the scope of alternatives. For a wide range of problems in data presentation, small multiples are the best design solution.

So now you know!



  © Kurtosis 2010 | 99 Giles Street, Edinburgh EH6 6BZ | Tel 0131 555 5300 | email info@kurtosis.co.uk