Using Pivot Tables to Analyse Data
You can download an example spreadsheet from pivottable.xls Pivot tables can be quite fiddly to set up. Once you have done so though, it is easy to display different pieces of information with a few mouse clicks. If you were using, say, an array formula to do the same thing you would have to keep editing the formula. Another advantage is that a pivot table eliminate errors than could otherwise creep into your formula, either through typing mistakes or a faulty understanding of how the formula works. A pivot table's data is valid and transparent: it is easy to see if you have made a mistake creating your pivot table. It is not always obvious if you have made a mistake when using a formula to analyse data. Imagine you have a table of data with temperature and humidity readings. With a pivot table you can quickly display the average temperature at each humidity level.
Creating a Pivot Table
To create a pivot table your data needs to be laid out in a particular way. Data with lists of timestamped data.
For example:
Time (Secs) |
Temp(°C) |
Humidity % |
16:27:42
|
9.23 |
63 |
16:27:43 |
9.23 |
63 |
16:27:44
|
9.22
|
62
|
To create a pivot table the first row must contain the name of the column of data below it.
The next row must contain the first set of data items.
Now, in Excel, click anywhere in any of the data columns. From the Data menu select Pivot Table. Choose to analyse an Excel list and create a PivotTable. Step through the rest of the Wizard.
A grid appears with instructions to "drop data and fields here". Above this is the PivotTable dialogue box showing your column names. For our example you need to drag "Humidity" to the Rows area and "Temperature" to the Columns area. By default the pivot table thinks you want to sum the data. However, we want the average. So double-click "Sum of Temperature" and change to "Average of Temperature". If you make a mistake right-click the pivot table, select "Wizard > Layout" and change your selections. To remove a data column just drag it off the pivot table.
The pivot table will show the average temperature for each distinct humidity reading. With a pivot table you can easily change the data you are analysing. For instance, you could drag Humidity off the table and replace it with Windspeed (assuming you have a column of Windspeed readings of course). You can also show average Temperature against both Humidity and Windspeed. Using Wizard > Layout put Humidity in the Row area, Windspeed in the Column area and Average of Temperature in the Data area. (Just drag anything in the wrong area away in the Layout Wizard.)
How do you find a conditional value?
For example, the average temperature when humidity was 62%. You can't do this directly with a pivot table, but can achieve a similar result by hiding some results. Click the down arrow by Humidity in the pivot table, and deselect values below 63%. Only average temperatures for readings over 62% are now shown. Hiding some readings in this way can be extremely useful. For instance, using a pivot table you can not only show the three largest values but also how often they occurred and at what temperature or humidity. You can sort and format the data in a pivot table exactly as you would normally. Just select the data values then use Excel's Format and Data menus. A pivot table has many advantages but it does have limitations. For instance, you can only use the functions provided: sum, count, average, max, min, product, standard deviation, etc. You cannot create your own custom formulae.
To answer the question posed at the beginning of this article - are pivot tables always the answer? Of course not, but as they offer flexibility and error free analysis I've been won over to them and they are now the first analysis technique I consider. |