Follow these easy steps to create an Excel pivot table, so you can quickly summarize Excel data. Watch the short video to see the steps, or follow the written steps. Get the free workbook, to follow along. There's also an interactive pivot table below, that you can try, before you build your own!
NOTE: This tutorial gives you a quick overview of creating a pivot table, with a short video, and basic steps. For detailed tutorial, go to the How to Plan and Set Up a Pivot Table page.
Watch this video, to see the steps for creating a pivot table in Excel 2013 and later. There are written instructions below the video, and the full Video Transcript is at the end of the page.
Before you create a pivot table, organize your data into rows and columns, and create an Excel Table. There are instructions here.
In this example the source data contains information about food sales, in two regions -- East and West.
After your source data is prepared, you can create a pivot table.
First, follow these steps, to see which pivot table layouts are suggested by Excel.
As soon as you click the OK button, a pivot table is created in your workbook.
In the PivotTable Fields pane, you can see that:
You can change the layout of the pivot table, after it's been created.
In this example, we'll add the TotalPrice field to the pivot table.
Excel added the TotalPrice field to the Values area because it is a number field.
When you added the TotalPrice field, Excel automatically added it to the Values area, and used the Sum function to summarize the prices.
Excel did that because:
However, when the pivot table was created, the Quantity field was added to the Values area, because it's a number field too. But the Quantity field shows a count, instead of a sum.
Why did Quantity use the Count function, instead of Sum?
Before you build your own pivot table in an Excel workbook, you can see how a pivot table works, by trying the interactive Microsoft Excel example shown below.
Below the interactive pivot table, there are a few things you can try, to see different results in the pivot.
Note: You can download a copy of the file, to test the pivot table features on your own computer.
Above the data in the pivot table, there are filter fields - State, Business Type (BusType), and Flood.
Make the following changes to the pivot table filter fields, to change what the pivot table is showing:
Currently, the pivot table is sorted alphabetically by the values in the Rows area -- the Construction type, and the Region.
To see the data in a different way, you can follow these steps to sort by the policy count
After you click OK, in the pivot table, under each construction type, the regions are listed from largest to smallest, based on the policy count field.
Click here to download the zipped sample file with the Region Sales data. The zipped file is in xlsx format, and does not contain macros.
Here is the full transcript for the Create a Pivot Table video.
A pivot table is a great way to summarize data in Excel. Here we have a table with sales records.
We can see where things were sold, what we sold, and how much and how much money we got on each sale. We can scroll down and see row after row of data.
Now I could create formulas to summarize things, but with a pivot table it will just be a few clicks to get totals. In Excel 2013 it's easier than ever to create a pivot table because there's a new tool to help you with some suggested arrangements for your data.
It opens up a new window, and based on the data that I've got here, it's suggesting some layouts
You can scroll down, there are lots more options. If I go down further, we're looking at the product categories and then the product names below that. This time it's the quantity, rather than the price.
Here's the layout that I selected. Now it's got the quantity. I'm also going to add the total price. Over here, I can see all the fields from that data sheet.
This one went in as Sum of Total Price. This one is Count of Quantity. If I go back and look at my data, I can see that going down the quantity column, there's one item here that's text rather than a number.
So Excel sees this not as a number column, but as either text or mixed numbers and text. So it defaults to Count. But we can change that.
So now we can see the total quantity and the total price for each product category and product.
It's a quick way to get started. You can change the layout after you've selected one of the sample layouts, but you're up and running very quickly.
Last updated: December 14, 2021 8:49 PM