In this week’s blog we will be looking at an example using a downloadable, interactive spreadsheet filled with real data.

Readers, please click here to download the locked spread sheet.

Lounge members, you can download the unprotected spread sheet from within the lounge.

Example Data

Most of the data I use is confidential for one reason or another so for this exercise I have downloaded publicly available data from the office of National Statistics.
I chose the Output in the Construction Industry, January 2012 (Adapted from data from the Office for National Statistics licensed under the Open Government Licence v.1.0.) – check the website for conditions of use

http://www.ons.gov.uk/ons/publications/re-reference-tables.html?edition=tcm%3A77-229191

Nothing special about the data except it gives us some opportunities to use differing styles of outputs. In particular it has a timeline, which is in quarters of a year but hopefully you can see how this could be adapted to minutes for a sport situation.

Firstly, I’ve kept the original data ‘as is’ in the ‘Original data’ tab.
For this example I will only use the Housing maintenance values, and for convenience we’ve only used the data from 1996.

Arranging the Data

I have linked the data I need into another tab ‘Ho_Maint’, this places the data into a manageable format for us to start visualising – the vast majority of graphs must begin with proper arrangement of the data.

I kept the layout (regions in columns and dates in rows) the same as the original.

I plan to use dynamic graphs that will allow the user to interact with the visualisation tool – far more interesting than just plain graphs.
In this example we will not be using VBA (visual basic for applications), focusing instead on using the built in tools in Excel, in particular we will be using the following functions:-
• MATCH
• AVERAGE
• INDEX
• REPT

To mange the data I started by created a separate sheet called ‘setup’, this sheet does most of the calculations.

With this done, I created a few ‘names’.
In Excel you can define a range of cells by a name, this makes life so much easier when referring to them in formulae.
In this example I created the following names:

• ‘raw_data’ for the whole table in Ho_maint and also
• ‘raw_year_column’ for column A (where the years are stored) in the same sheet.

Output 1

The idea in this output is to produce a tabular output to represent the ratio of costs for individual regions within a specified year, without using a graph.
The user selects the required year from the table on the left and the information automatically updates.
In this example the map is not active and is only there to assist visualisation.

My choice of output style was the  symbol (letter ‘n’ with font style wingdings – the more  s the higher the costs.
This can be changed in cell I1 of the set up tab, but if you change the font don’t forget to change the font style in cells H2 to H24 in Output 1 tab.

Firstly the original time data had blanks within the year column (see below)

I then created a list box, which is linked to the year table, cell H8 is also linked just for visual reaffirmation to the user.So for convenience, I created a table of the years and the regions in ‘set up’.

List box is on the Developer tab in Excel 2010/2011

The  representation in cells H10 to H24 show the ratio of the variations in the regional costs.
Selecting the year changes these figures, as well as the actual cost value in column I.

Next week we will look at the details of how to achieve this output.