It’s easy to create a pivot table, with a few mouse clicks. However, the hard part, especially at first, is deciding what goes where. If you aren’t sure where to begin, these steps will help you set up an Excel pivot table. Download the free workbook to follow along.
A pivot table is a quick way to show a summary for many rows of data. It is a flexible alternative to a structured worksheet report that has typed headings, and formulas to calculate the totals.
There are a few things to do though, before you build a pivot table. Being prepared can save you lots of time and troubleshooting later!
Check your source data, to be sure that it is organized correctly. There are guidelines on the Create an Excel Pivot Table page. Take a few minutes to read those, if you haven’t done so already.
In this tutorial, the source data is a named Excel table, with sales records for a food company. Each record has details on one product sale. We know:
Think about what you want to show in the pivot table. What do you want to count or sum? (You can use other functions too, but count and sum are used most often.)
For our pivot table, we will start with a simple report that shows the total number of orders for each City.
Even if you haven’t made a pivot table before, you have probably created Excel reports on a worksheet. How would you set up a worksheet report to show a total for each city?
Imagine what that would look like, and set up a small report on a worksheet, with a couple of headings, and some fake numbers. Or sketch out the report on paper, to help you picture what should go where.
The screen shot below show a worksheet report plan, with city names (shortened) in column A. The numbers in column B show where we want the total number of orders for each city.
Now that you’ve done the preparation steps, you’re ready to create the pivot table.
There are two pivot table commands on the Insert tab of the Excel Ribbon, and both options are explained below.
If you’re using Excel 2013 or Excel 2016, the Recommended PivotTables feature is a great way to get started. It shows you different pivot table layouts that you could start with, based on your source data.
After you've looked through the list of recommended pivot tables, click on the one that you want to use. Here's how you can choose a layout:
For this example, the Sum of Quantity by City is similar to what we need, so click on that layout, then click OK.
When the Recommended PivotTables dialog box closes, Excel inserts a new worksheet in the workbook, with the pivot table that you selected. Quick and easy!
In the screen shot below, you can see the completed pivot table for Sum of Quantity by City. Now, go to the PivotTable Field List section below, to see how to make changes to the pivot table.
If you’re using an earlier version of Excel, or if you don’t want to use the Recommended PivotTables feature, you can start with a blank pivot table, and create your own layout.
To create a blank Pivot Table:
NOTE: If you want to use a different table or range, you can type an Excel table name or select a different range address in the Table/Range box
NOTE: Instead of adding a new sheet, you can click the Existing Worksheet option, and select the sheet where you want the pivot table to be created.
When the Create PivotTable dialog box closes, Excel inserts a new worksheet in the workbook, using the next available sheet number. The outline of an empty pivot table starts in cell A3.
Now, go to the PivotTable Field List section below, to see how to add fields to the pivot table.
Now that you have a pivot table started, you can add or remove fields, or move the fields to a different area. There is a built-in PivotTable Field List, to help with that task.
To see the PivotTable Field List:
In a minute, you’ll use the PivotTable Field List to add or remove fields. First, take a look at the sections in the PivotTable Field List.
In the field list shown below, the Region field has a check mark, and the Region field appears in the Rows box. Your field list will have different fields checked, or no fields.
Near the top of the PivotTable Field List pane is a list of the column headings from your Excel table; they appear in the same order as in the Excel table. In the pivot table, these are called fields.
If you used a Recommended PivotTable layout, you will see a check mark beside the fields that are in the pivot table.
In Excel 2016, there is a Search box above the list of fields. This can help you quickly find a field name in a long list. Click in that box and start typing. The field list will be filtered automatically, to only show the field names that contain the string of letters that you typed.
At the bottom of the PivotTable Field List pane are the four areas of the pivot table: Report Filter, Column Labels, Row Labels, and Values. You can drag the fields into these areas, and they’ll appear in the matching area of the pivot table layout on the worksheet
If you used a Recommended PivotTable layout, you will see the fields from that layout in those areas.
The quickest way to add a field to the pivot table layout is to use the check boxes in the field list.
If you started with a recommended pivot table, add a check mark to the OrderCount field.
If you started with a blank pivot table, add a check mark to the City and OrderCount fields.
The quickest way to remove a field from the pivot table layout is to clear its check boxes in the field list.
Excel removes the field from the pivot table layout, so only the City and OrderCount fields are showing.
After you create your pivot table, you can add more fields, to show additional details about the data. Currently, the pivot table shows the total number of orders for each city. In this example, you can add another field, to see which product categories were sold.
Category is added to the Rows area of the pivot table layout, below the City field. Now you can see the total number of orders for each city, and the number of orders for each product category, in each city.
You could also add a field to show when the orders were sold. The source data has a Year field, so add a check mark to Year in the field list. Because the years are numbers, Excel adds the field to the Values area, as Sum of Year.
We don't want a Sum of the Years -- we want to use that as a heading in the pivot table. Let's fix that!
Instead of using the default location for a pivot table field, you can move it. In this example, the Year field should not show a Sum. Even though the field contains numbers, we don't want to use is in the Values area.
To change the Year field to a heading, you can move it to the Rows area or to the Columns area.
First, follow these steps to move the Year field to the Rows area:
Now the pivot table shows the number of orders for each year, and the city and category subtotals.
That makes a tall narrow pivot table, that is a bit hard to read. To make it easier to compare the totals for each year, you can move the Year field to the Columns area. That will create year heading across the top of the pivot table.
Now you can see the data for the years side-by-side, so it's easier to compare the data for each city and the products sold there.
Pivot tables make it easy to summarize a larg amount of data, and sometimes you'd rather focus on part of the data, instead of seeing everything. To focus on data for one of the regions, you can add the Region field as a filter.
The Region filter appears above the Row labels on the worksheet.
The pivot table changes -- only the cities in the West region are visible -- Los Angeles and San Diego.
Those are the basic steps for planning, creating, and changing a pivot table. Get the sample workbook at the link in the next section, and experiment with that data. Plan a different type of pivot table, and try to build that. For example:
Then, make a copy of one of your own workbooks, and plan a simple pivot table using your data.
Based on the fields in your data, what other types of pivot table reports could you create, to show the where, when, what and how much of that data?
The more you experiment, the better you'll get at showing the reports that you need. And no matter how long you've been making pivot tables, it's worthwhile to try a different layout, to see if it makes the information easier to understand.
Download the sample workbook for this tutorial, to follow along with the instructions. The zipped file is in xlsx format, and does not contain any macros.
Last updated: September 12, 2016 11:41 AM
Contextures RSS Feed