The data used in the Climate example are just a small extract from a larger set on the Met Office Historical Data Archive. The complete file for Greenwich can be found on the K drive. (This file was created by copying and pasting from the site into Word. The spaces between characters were removed with Edit/Replace, converting spaces to commas. The leading commas on each row were deleted with the mechanical procedure of pressing the Delete and the down arrow keys. Remaining spaces were replaced with nothing. Some additional editing got the data scrubbed up so it could be pasted into Notepad and saved as a CSV file (comma separated variable), which can be opened directly in Excel. For a task like this it pays to know your software tools and file formats!)
The full data set, from 1961 to 2003, once in Excel format, can be used to investigate the climatic pattern of the place concerned. The web site includes data from 26 places so if each person in a class works on one and then presents their findings, a picture of climate across Britain and through time can be seen.
The data on the web site is arranged by month within year but this is not ideal for extracting the sort of information we might want. It is relatively easy to extract data for a single year and create effective charts to show patterns and trends but it is more difficult to extract any changes through time because the data are arranged in a 12-row repeating pattern. To extract the data for, say, rainfall in January over the period 1961-2003 it would be necessary to select individual cells from top to bottom of the sheet and then copy and paste them, a very tedious and time-consuming task. Fortunately there is a better way: the pivot table.
The pivot table can take the data in a spreadsheet and rearrange, rotate or group them in different ways - this is what 'pivot' means.
To investigate the data we must first select them: start at the bottom right hand corner (G518) and work upwards to A2. Now choose Data/Pivot Table...
The wizard will ask you to confirm that the source is an Excel spreadsheet, that the range is the cells you selected and that you want to create the pivot table in a new worksheet. When you have completed the wizard Excel adds a new worksheet with an empty pivot table and a list of the column headers (Year, Month, TMax, etc.).
Drag the Year field from the Pivot Table Field List into the Row Fields area:
Now drag the Month field into the Column Fields area. Finally, drag one of the five climatic variables into the Data Items area:
The data in the original spreadsheet has been turned around or 'pivoted' on its axes. Instead of occupying 12 rows, one for each month, the data is now arranged by year with the data for a single variable in columns by month, which is much better for analysis.
To create a table for another variable drag the 'Sum of TMax' label from its cell (A3) onto the Field List and drag another field from the list into the Data Area.
To change the data display from 'Sum' to another mathematical property choose the Field Settings button on the Pivot Table toolbar (second from the right). This gives a range of options for analysing your data:
The data can be turned into a chart by clicking on the chart wizard. If you need to select data for further analysis and manipulation you will have to use copy and paste to move them to another part of the spreadsheet.
Compare Temperature and Rainfall for 1961 and 2003:
Create the pivot table shown above and copy and paste the data for 1961 and 2003 for TMax, TMin and Rainfall into a new worksheet. Calculate the mean average temperature from TMax and TMin and then plot this average along with rainfall on a suitable chart. Are there any striking differences between the two charts for 1961 and 2003?
In this example we examine the data by month. Drag the Month field into Row Fields area:
Now drag the TMax variable into the Data Items area. You immediately see a list of figures for TMax for the 12 months:
This figure represents the total rain over all 43 years. The Pivot table adds up the values for TMax, which may seem strange at first but it does provide a valid means of comparing one month with another over a number of years. Where the data is text the pivot table counts up the values of a particular type.
Convert the data into a chart by clicking on the chart wizard.
To perform further calculations or to make charts of your own choice it is best to select the data and copy and paste them to another location such as just underneath or in another worksheet.
Find the average for each of the five climatic variables for the 12 months shown. There are 43 years in the data (1961-2003) and the calculation is straightforward. The shape of any charts based on the averages will be the same as the ones for the totals, only the values on the Y axis will be different - can you see why?.
For practice you should choose a second data set and follow the instructions above to create a clean spreadsheet file from which you can create a pivot table. If a number of pupils choose different locations then the pattern of climate across the country can be investigated.
The combined temperature and rainfall chart is a common sight in weather and climatology. For any given year in the data set the statistics can be extracted so that they can be compared with the total and average values for 1961-2003 we obtained earlier. For temperature we could show TMax and TMin on the same chart or we could calculate the average of these two and combine it with rainfall on a combination chart like this:
Click the Back Button to Return