People say, "it's easy to make a pivot table" in Microsoft Excel, but you're having trouble getting started. In the sections below, I'll explain what a pivot table is, and how it can help you save time. Next, I'll help you make a quick plan, then build a pivot table from sample data. Download my Excel workbook, and follow along. Soon, you can say, "Pivot tables are easy!"
Author: Debra Dalgleish
What is a Pivot Table in Excel?
A pivot table is a quick and flexible way to show a summary for thousands of rows of data in an Excel workbook. With a few mouse clicks, Excel builds the report for you, using your dataset, and it's easy to change the layout, at any time later.
You can use pivot tables instead of old-style Excel reports. Those old reports can take a long time to build, because they have typed headings and complicated formulas to calculate the totals.
For example, the pivot table in the screen shot below shows a summary of 500 sales orders. Excel did all the construction work for me, and it only took a minute or so!
Video: Pivot Table Demo
To give you a quick peek at pivot tables, I've got a short video that shows the world's simplest pivot table. It's built from a Excel worksheet list that only has one column -- the months when 100 people have their birthday!
In the video, Sarah shows the quick steps to build a pivot table, based on that list of month names. In just a few seconds, the survey results are summarized, showing a count for the birthdays in each month..
There are step by step instructions on the Count Duplicates in Months List page, and you can get the sample file there too.
Save Time with Pivot Tables
Pivot tables are a powerful tool in Excel that can save you a lot of time when analyzing data. By using pivot tables, you can streamline your data analysis and save time that you can use to focus on other important tasks.
Here are three examples of how pivot tables can help you save time, and make your job easier, when working with large datasets.
Filter Your Data
Pivot tables can filter data based on certain criteria, such as date ranges or specific products.
This can save you a lot of time when analyzing large amounts of data because you can quickly filter out irrelevant information and focus on the data that is most important to your analysis
For example, you can filter your large data sets to show only the total amount exported of each product to a specific country or region. This can help you identify your best-selling products or markets.
Pivot Tables make it easy for you to change and rearrange your data quickly, depending on your needs. You don't have to waste your time, starting from scratch.
You can add more fields, remove fields that you don't need in the summary or move any of the pivot table fields to another area, to get a different view of your data.
Group and Sort Data
Pivot tables can group data by categories such as dates, regions, or products. So, instead of manually sorting and counting data, you can use a pivot table to quickly see how much data falls into each category.
You can also sort your data easily, by any column or field, such as amount, product, or order ID. This can help you rank your products or compare their performance.
For example, you can sort your sales data to show the products with the highest or lowest sales amount in each country or category.
Pivot tables can perform calculations like sums, averages, and percentages. Rather than manually writing out complex formulas in your workbook, you can use a pivot table to calculate totals and averages for different categories.
For example, you can show the number of orders, for each product, and the total sales amount for each product or country.
There are a few important things for you to do, before you build a pivot table. Being prepared can save you lots of time and help you avoid problems later!
Here are the 3 preparation steps that I recommend you follow, before building your first few pivot tables.
The details for each step are shown below.
Note: After you build a few pivot tables, you might be comfortable doing the preparation steps in your head, instead of on paper.
Step 1. Check Source Data
Before you build, check the source data that you plan to use for the pivot table, and see if it is set up correctly.
First, be sure that the data is:
For the pivot table example that I'm building, the source data is a named Excel table, with sales records for a food company.
Here are a few ways that I could summarize the data in my pivot table:
Step 2. Set a Goal
The next step is to think about what you want to show in the pivot table. Why are you building it?
What do you want to count or sum? (You can use other calculation functions too, but count and sum are used most often.)
For example, you might have these goals, if you're creating a pivot table to share with a client, or your boss, or co-workers:
For my example pivot table, I'd like to get a simple report that shows the total number of orders for each City. That could help me set an advertising budget in each city.
Step 3. Think About Layout
The final preparation step is to make a rough sketch of what the completed pivot table should look like.
Don't worry about this step too much though! Try making a simple outline on an Excel sheet, like I often do.
Or, if you think better with a pencil in your hand, sketch out the pivot table layout on a sheet of scrap paper.
Either of these methods - Excel spreadsheet or pencil & paper, will help you picture what should go where in the pivot table that you're going to build.
The screen shot below shows my layout plan, sketched out on an Excel sheet.
Now that you’ve done the preparation steps, you’re ready to create the pivot table.
There are two pivot table commands in the Tables group, at the left side of the Insert tab:
Both options are explained in the sections below.
If you’re using Excel 365, or Excel 2013 (or later version), the Recommended PivotTables feature is a great way to get started.
With this feature, Excel suggests different pivot table layouts that you could start with, based on the source data that you selected.
Use Recommended PivotTables
To try the Recommended PivotTables feature, follow these steps:
Choose a Recommended PivotTables layout:
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 the most appropriate layout, from all of Excel's suggestions:
For this example, the Sum of Quantity by City is similar to what we need, so click on that layout, then click OK.
See the Pivot Table
When the Recommended PivotTables dialog box closes, Excel inserts a new worksheet in the workbook, with the pivot table that you selected. At least that step is quick and easy!
In the screen shot below, you can see the completed pivot table for Sum of Quantity by City.
Next, go to the PivotTable Field List section below, to see how to make changes to the pivot table. (You can skip over the Blank Pivot Table section below)
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:
Choose the Data
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.
Change Pivot Table Layout
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 you make those changes to the pivot table layout.
--4) Add More Fields
--5) Move Fields
--6) Add a Filter Field
--7) Keep Experimenting
1) Open PivotTable Field List
To see the PivotTable Field List, follow these steps:
What's in PivotTable Fields List?
In the next section, you’ll use the PivotTable Fields List to add or remove fields.
First, take a look at the 3 main sections in the PivotTable Fields pane.
Each section is described below the screen shot.
Near the top of the PivotTable Fields List pane, there is Field List - this shows a list of the column headings from your Excel table. If a field has been added to the pivot table layout on the worksheet, that field has a check mark.
Note: If you used a Recommended PivotTable layout, you will see a check mark beside the fields that are in the pivot table.
In the field list shown above:
Your field list might have different fields checked, or no fields checked.
In Excel 2016 and later versions, there is a Search box above the list of fields. This can help you quickly find a field name in a long list.
To use the Search box:
As you type, 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 different areas of the pivot table. These areas have slightly different names in some versions of Excel:
Note: If you used a Recommended PivotTable layout, you will see the fields from that layout in those areas.
In the next section, you'll see how you can drag the pivot table fields into these areas, to add them to the pivot table layout on the worksheet.
After you add a field to a layout area, that field will appear in the matching area of the pivot table layout on the worksheet
The quickest way to add a field to the pivot table layout is to use the check boxes in the field list.
Recommended Pivot Table
If you started with a recommended pivot table, add a check mark to the OrderCount field.
Blank Pivot Table
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 large 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.
Food Sales: Download the sample Food Sales workbook for this tutorial, to follow along with the instructions on this page. The zipped file is in xlsx format, and does not contain any macros.
Last updated: May 10, 2023 12:32 PM