Pivot tables are a powerful way to summarise spreadsheet data. They make it easy for you to display complex data in a simple way. You can use them to answer questions such as what value of goods you purchased from a particular supplier in a particular month, or the total value of orders of dairy products for the two months, for example. We’ll show you how to get started with pivot tables in Microsoft Excel and Google Docs' spreadsheet app.
To begin with Excel, you’ll need some data in a list arrangement, such as that shown in the example below. Our data shows information about product orders through the months of September and October. To format the data as a table, click in the table and select ‘Format as Table’ from the Home tab on the Ribbon. Then, from the ‘Table Tools’ —> Design tab, click ‘Summarize with PivotTable’.
In the ‘Create PivotTable’ dialog, the table will already be selected, so all you need to do is select where to place it. We created ours on the same worksheet so you could see the data, but it’s best to choose ‘New Worksheet’.
The ‘PivotTable Field List’ is where you organise your data. Drag Supplier into the ‘Row Labels’ box and their names will appear in column A. Drag the Category field into the ‘Column Labels’ box and the categories will appear as column headings. Drag the field Value into the ‘S Values’ box and the table will be populated with the summary data.
You can then read results from the table and organise it to see only the information you want. For example, to see the data only for Condiments, select the down-pointing arrow to the right of ‘Column Labels’, select Condiments, deselect all the other options and click OK. New in Excel 2010, Slicers allow you to go further by adding set of buttons that enable you to quickly filter the data in a PivotTable report, and the step-by-step guide below shows you how to use these.
To edit a PivotTable, click in the table and move items around in the ‘PivotTable Field List’ panel. To add the Date field, click its checkbox. To summarise the dates, right-click any date in the table, select Group —> Month and click OK. The orders will then appear grouped by month.
To turn off column or row totals, right-click in the table and select ‘PivotTable Options’ from the menu. From the ‘Total & Filters’ tab, deselect either or both of the options: ‘Show Grand Totals for Rows’ and ‘Show Grand Totals for Columns’.
PivotTables are a key tool for summarising data and they make it easy for you to display complex data in a simple way. You can use them to answer questions such as what value of goods you purchased from a particular supplier in a particular month, or the total value of orders of dairy products for the two months, for example.
Pivot tables with Google Docs
Google has recently added pivot tables to its spreadsheet app. While not as advanced as Excel's, it does provide sophisticated tools for quickly analysing large amounts of data. To get started, open your spreadsheet and select the data to consolidate into a pivot table. From the Data menu, select ‘Pivot Table Report’ and you’ll see an outline of the pivot table appear on the screen. From the ‘Report Editor’ panel down the right of the screen, select the field to use for the Row titles. Here we’ve selected the Supplier field from a list of products on order.
In the Columns area, click ‘Add Field’ and select a field for the columns. Here we’ve selected the Category field. In the Values area, select the data to sum — we’ve selected ‘Units on Order’. This shows the units on order in each category of item from each supplier.
In the Filter area, click ‘Add Field’ and add a field to use to filter the list. For example, you can filter by supplier by selecting Supplier and then choosing the suppliers to show in the table. You can filter on multiple fields if you select a second filter to use. Then, from the ‘Show All Items’ drop-down list, select only those items that you want to view in the pivot table report.
The pivot table will update as you work with it, so if you don’t want this to be the case, select the Manually option at the foot of the ‘Report Editor’. You can then make multiple changes to the pivot table settings as desired. When you’re ready to see the results, click ‘Update Table’ to update it manually. You can disable column and row totals by deselecting the ‘Show Totals’ checkboxes under each of these items in the ‘Report Editor’.
A pivot table’s data can easily be charted. To do this, select the data to chart (not including the grand totals), click the chart icon, select the type of chart and click Insert. To customise the chart, click it and from the drop-down list in the top-left corner, choose ‘Edit Chart’ and make your choices from those available.
One thing to be aware of is that the charts aren’t linked to the pivot table, so if you make changes to a filter that’s applied to the pivot table, the chart may no longer be correct. For this reason, when you make changes to a pivot table, make sure you click the chart to select it, choose ‘Edit Chart’ and in the Start panel, update the data range the chart is based on.
To return to the data that the pivot table summarises, click the sheet name that appears on the bar at the foot of the browser window. The pivot table is live, so any changes in the data it summarises will be reflected in the pivot table itself.
Add Slicers to Excel 2010 pivot tables
