Learn Pivot Tables Tutorial
In this Excel Pivot Tables tutorial you will learn how to create a Pivot Table and perform some essential techniques to work with your data. This tutorial covers;
- Creating a Pivot Table
- Specifying Pivot Table data
- Changing a Pivot Tables calculation
- Sorting and filtering a Pivot Table
- Grouping fields of a Pivot Table
- Refreshing a Pivot Table
- Creating a Pivot Chart
The file used in this tutorial can be downloaded to follow along. Download the learn Pivot Tables tutorial file.
Excel Pivot Tables are a powerful reporting tool in Excel that allow you to easily organise and summarise a large list of data. They can make analysing even the most complex list of data like a walk in the park.
At first they can seem quite daunting, but fear not, because once you have created your first one you will see that they are very easy and user friendly to use.
To create a Pivot Table:
- Select the range of cells you want to use for the Pivot Table.
- Click the Insert tab on the Ribbon and then the PivotTable button.
- The Create PivotTable dialogue box appears.
- Decide whether you want the Pivot Table to appear in a new worksheet or within an existing worksheet.
- The Add this data to the Data Model checkbox is a new feature to Excel 2013. It is an advanced area of Pivot Tables where you can related data tables and is not covered in this tutorial.
- Click Ok
The range of cells that you selected is shown within the Table/Range field. If the range is not correct, select the data range you wish to use again.
An empty PivotTable appears on the left and the PivotTable Fields list is on the right. It doesn't look very impressive yet, but that's because we need to pull some data into the table to give it something to work with and let the magic happen.
To specify the data to use in the Pivot Table, you need to move the required fields into the relevant report areas at the bottom of the field list.
By simply moving the fields between the different report areas you change the configuration of the report, and is why they are known as Pivot Tables.
- Click the checkbox for the Product Category field and Excel will move it into the Rows area. Click the checkbox for the Total Sales Value field and it will be moved into the Values area of the field list.
- Clicking a checkbox always puts a label field into the Rows area. So for more control you can drag a field into the appropriate area instead.
- Click and drag the Product Category field below the Sales Rep Field to change the order of the groupings.
- Finally, click and drag the Product Category field into the Columns area. This will create a nice crosstab report with sales rep totals along a row, and product category totals down the columns.
The Values area is where the calculations are performed. By default, the Sum function is used on any field containing numbers. We will look at changing this calculation later in this tutorial.
The Pivot Table displays the total sales by each category.
Drag the Sales Rep field into the Rows area below the Product Category field. The PivotTable will look like below with subtotals for the sales by category, and then a sum of those sales by sales rep.
As you can see it's very easy to adjust the data in a PivotTable. It's just a case of moving fields into different areas of the table.
It can take some practice to get more familiar with PivotTables, and the more you use them the better you will understand them and very quickly manipulate data and generate your reports.
A Pivot Table summarises values using the Sum function by default. However, instead of totals you may wish to know the number of orders taken, or the average amount of those orders.
To change a Pivot Tables calculation:
- Click the list arrow on the field being used in the Values area of the Pivot Table and select Value Field Settings.
- Choose the function you wish to use from the list on the Summarize by tab.
- Click Ok
You can sort and filter the data in a Pivot Table much like you would a normal data range.
Filtering a Pivot Table
The Report Filter area can be used to filter the Pivot Table. To use this filter, you first need to drag a field into the Report Filter area.
- Click and drag the Country field into the Report Filter area. This field will appear above the PivotTable on the worksheet.
- Click the filter list arrow and select the required criteria. Click the Select Multiple Items checkbox to select multiple countries from the list.
You can add more fields to the Report Filter area for more advanced filtering. You can also filter the table using the row and column filters.
Sorting a Pivot Table
You can sort a Pivot Table at any level. To sort the data, just right mouse click on one of the values that you want to use in the sort and choose the required sorting option.
The image below shows a sort being applied to the values of the product category field.
You can group Pivot Table fields to identify different subsets of data. You can group any numeric field. Grouping is commonly applied to date and time fields. For example, dates can be grouped into months, quarters or years.
- Select the date field in the Pivot Table and click the Group Field button on the Analyze tab of the Ribbon.
- Specify start and end dates to group and the interval to group by. You can group a field by more than one date or time period.
To group by weeks, select days and enter 7 in the Number of days box.
The Pivot Table is grouped as specified. In this example the table was grouped by months and years. A new field called Years has been added to the field list. This field can be used in further analysis with the Pivot Table.
In the image below the Years field has been added to the Columns area.
If changes are made to the source data, the Pivot Table needs to be manually refreshed. It does not update automatically.
To refresh a Pivot Table, click the Refresh button on the Analyze tab. Use the list arrow to refresh all the Pivot Tables in the workbook.
Excel Pivot Tables are awesome, but this data could also be presented graphically in a beautiful Pivot Chart.
Report filters would appear on the Pivot Chart making is easy to interactive with and different data selected on the fly.
- Click the PivotChart button on the Analyze tab of the Ribbon.
- Select the chart you want to use from the dialog provided. Different chart types are displayed down the left, sub types along the top and a preview is shown in the main part of the dialog.
The Pivot Chart appears in the worksheet with the PivotTable. Use the tabs on the Ribbon to work on your PivotChart further.
If you have found this learn Pivot Tables tutorial useful, why not view more Pivottable tips.
Popular Excel Tips
More Pivot Table Tutorials