What is a Pivot Table?
Pivot Tables provide an easy and powerful way to analyse large amounts of data. The good news is that in Excel 2013, Pivot Tables have been given a facelift from earlier versions of Excel and are now more accessible and easier to manage than ever. Although they are universally known by this name I prefer to think of them as “dynamic worksheets” because that is what they actually are in terms of functionality.
Many people I come across in my professional life are scared of them, but in this Pivot Table Tutorial I will attempt to explain the basics…
How to Create Pivot Tables in Excel 2013
A pivot table can be inserted into a worksheet by using the quick analysis tab. Highlight the data that you want to use for your pivot table, then click on the quick analysis tab that appears at the bottom right of the data and scroll across to tables.
When the tables area appears, you have the choice of selecting one of four pre-formatted pivot tables:
Simply click on the field that you want the analysis performed on and a pivot table will be created and placed on a separate sheet.
Alternatively, you can insert a pivot table manually.
Inserting a Pivot Table Manually
The Pivot Tables command has been moved from the data menu to the Insert Tab on the ribbon.
To insert a pivot table, select a single cell in the data you wish to analyse and go to the insert Tab. The first button on the left allows you to insert a pivot table, and next to it Excel 2013 gives you the choice of inserting a recommended pivot table
Alternatively you can click in the data you want to analyse and use the keyboard shortcut Alt+D+P. If you have your data already in a table there is a “Summarise with Pivot Table” button on the ribbon.
The create Pivot Table dialogue box will be displayed.
Select where you wish the data for the Pivot Table to come from. This can be from an external source such as an Access database, a SQL Server database or another Excel workbook. If you click on your data before you start to create the Pivot Table, Excel will assume that this is the data you wish to analyse.
Choose where you would like the table to be created. This can either be in a new worksheet, which is the default setting, or an existing worksheet.
A Pivot Table will be created in the location you specified and the Pivot Table tools contextual tabs will open on the ribbon. The Pivot Table field list will be displayed on the right-hand side of the screen.
Using a Pivot-Table for Analysis in Excel
Use the field list on the right to specify the layout of your table i.e. which fields you want as row/column headings and which field you wish to summarise by. This can be done by dragging the fields from the top of the list into either the Report Filter (previously known as Page Field), Column labels, Row labels or Values areas as the bottom of the field list.
It is also possible to right click over a field heading to specify which area it should go into:
Simply clicking a field in the list will automatically add it to the Row labels if the field is a text field or the Values if the field is a numerical one.
The optional Page Field functionality of a Pivot Table in previous versions of Excel has been renamed as the Report Filter. This allows for a top-level filter to be applied at the very top of the sheet. It makes it clearer that by adding a field here you can filter by that field without changing the main layout of the Pivot Table.
To Pivot the data, simply change the position of your fields in the boxes within the field List, or remove them by unchecking the checkbox next to their name: