ALL ABOUT OFFICE

EXCEL

HOME

ACCESS

EXCEL

WORD

OUTLOOK

POWERPOINT

Pivot Tables

Pivot tables are useful for summarising data from a spreadsheet under different groupings.  For this example we are going to analyse the results of a team’s games from the spreadsheet shown (you can right-click here and choose 'Save Target As' to download the data for this spreadsheet) below .

To start creating the pivot table we click on the Data drop-down menu and choose Pivot Table and Pivot Chart Report.  The pivot table wizard starts and we can choose where our data is located and the type of report we want to produce.

Click the Next button and the wizard may now automatically highlight your table of data, including the column and row headings.  If the data is not selected or is incorrect, click on the icon to the right of the range box in the window shown below.

You will now be returned to your spreadsheet and can highlight the data you want to include.  Remember to include the column and row headings as these will be used as your field labels.

When you have selected the data you require, hit the Enter key and you will be returned to the wizard.

The range you selected is now shown in the window.  Click the Next button to continue and you will see the window shown below where you can choose the location of the pivot table.

It is often best to place your pivot table in a new worksheet to save crowding the sheet containing your data.  However, you may wish to select an area of the existing sheet if you only have a small amount of data and want to print everything together.

When you have decided on the location, click on the Layout button and you can set up your pivot table on the window shown below.

The field buttons on the right have been set up from your column headings.  To set up your pivot table, drag a field button into the areas marked Column, Row, Data and Page on the diagram.  You do not have to use a Page field if you do not want to.  When you have positioned the fields, click the Next button and you will be returned to the previous window where you can click the Finish button to see the final pivot table.

Here is the resulting pivot table.

This pivot table is very wide because it lists individual match dates.  We could get a better summary if we grouped the data by month.  It is possible to do this by changing the field settings for the date field.

Click your right mouse button over the grey Date field and choose Group and Outline and then Group from the menu that appears as shown below .

A window now appears as shown below allowing you choose the type of grouping you would like, we will select Months.

When you have made your selection, click the OK button and your pivot table will reflect the grouping you have chosen as shown below.

It is possible to sort this pivot table by clicking in a column and then clicking on Excel’s sort icon, you may decide to sort by descending total number of goals, for example.

Once you have set up your pivot table you can choose to only show certain items from the page, row and column fields.  To change the team shown on the page, click the arrow to the right of the grey page field box and select the team you are interested in.

When you click OK only the chosen team’s players will be shown.

If you are only interested in certain players then you will have to click on the arrow beside the grey Player field and untick the boxes for all the players you are not interested in.  Unfortunately, there is no quick way of showing an individual player, you have to individually untick all the boxes you do not want.  In the same way, it is also possible to choose only certain months to display by using the Date field, once the grouping has been set you will be offered a selection of months rather than individual dates.

If you want to change the type of summary information given by the pivot table you can change the field settings for the data field.  To do this, click on the top left corner data field (Sum of Goals in this case) and then click on the pivot table toolbar to show Field Settings from the drop-down menu.  (If you cannot see your pivot table toolbar, click on the View drop-down menu, choose Toolbars and then PivotTable).  Another way of selecting the Field Settings is to right-click your mouse button on the same corner data field to display a similar menu.

When you have chosen the Field Settings a window appears as below and you can change the summary to another setting.  We have changed from Sum to Average.

If you change to average you may get some rather long numbers as a result (e.g. 3.3333333).  It is a good idea to click on the Number button in this window to change the format of the numbers in the data area of the pivot table.

Choose the number type you require and the number of decimal places you would like displayed.

Click the OK button and you will be returned to the field settings window.  Click OK again and your new field settings will have been applied to the pivot table.

When you have created a pivot table, you may decide that you need to move the fields around to obtain a different type of summary.  We are going to change the row headings to the Team field and change the Page field to the Player so that we can show the data for one player at a time.

Click anywhere on the pivot table to display the fields on the pivot table toolbar.  Now drag the Team field from the Page Field section and drop it to the left of the current Player field.  You can now see two columns to the left of the data area.

Now click and drag the Player field from the top of the second left hand column and drop it in the box at the top that says ‘Drop Page Fields Here’.

You can now show each player’s achievements per month and the team (or teams) he plays for by changing the selection in the Page field.

There is an example of an interactive pivot table you can experiment with here.

Click here to return to the Excel index.

webmaster@allaboutoffice.co.uk