Using Excel as a data source for Tableau – Shaping your Data

The purpose of this blog is to help you to see the difference that the underlying data structure has in determining the visual resulting visual output. For me, the underlying data is all-important; if it isn’t clean or well-structured, then this needs to be resolved first. After that, even the neat pivoted and un-pivoted data structures can have a real impact on the visual display of the data.

Excel, particularly Excel 2010, has some great new data visualisations on board, and it looks as if more exciting data visualisation .NET stuff is coming out of Microsoft research, so watch that space!

In the meantime, if you are using Tableau, some neat visualisations exist for you to use. In case you didn’t know, it can be possible to maximise your analysis of Excel data structures by preparing it using the Tableau Shaper add-in. The add-in prepares the data for optimal Tableau analysis by placing the values into one column, thus ‘unpivoting’ the data. Excel, particularly Excel 2010, is very good at analysing data in row orientation. If you’ve tried to use the sparklines in Excel 2010, you will see how easy it is. On the other hand, Tableau can need a little help in viewing data in ‘row’ orientation, and this is served by the Tableau Excel add-in.

To help you, here is a dashboard which shows you the preferred fiction reading habits of Americans. The data is taken from Harris International, who, in addition to providing fascinating data for American citizens, also serve up UK data for the popular UK Channel 4 series 8 out of 10 Cats. 

Here is an example; in this dashboard, the top data has been ‘shaped’ and unpivoted, and the bottom section has remained in the original ‘pivoted’ format. To go to Tableau Public, click on the image to get a bigger and better interactive view: 

Here, the top data has been unpivoted into a single value column, so it is easier for Tableau to analyse and compare. Here is an example of the unpivoted data:

In the case of the bottom dashboard, Tableau sees the row data as different values, even though they both add up to percentages. Here is an example of the pivoted data:

I hope that helps you to visualise how the underlying data structures are important in determining the ultimate visual output. If your data is not clean, you need to go back and clean it, since it will become evident very quickly!