A pivot table is a summary of a data set that is represented as a table.
In pivot tables, the data can be summarized in multiple dimensions by positioning different fields of the original data set in rows, columns, or filters of the pivot table. Also, pivot tables can use various statistical functions to handle the data: sum, average, count, min, max, etc.
Why do we need it?
A pivot table is an extraordinarily powerful tool to analyze large data sets. If it didn’t exist, it would take users a lot of time and work to replicate the same functionality with formulas and other tools.
Let’s say, you have your bank account report with dates, names, and amounts of all your bank transactions over the last three months (Figure 1). Based on this information, you want to quickly create a summary with total amounts spent or received by transaction type (Figure 2).
First and foremost, you need to make sure that the data set is structured appropriately: the table should have column headers in it’s first row, there should be no untitled columns and no empty rows. As soon as the data is cleaned up, the actual process is very straightforward:
- Select the data set, including the header row (Figure 3)
- In Excel ribbon, go to Insert >> Tables >> Pivot table (Figure 4)
- When Insert PivotTable window appears, click OK (Figure 5)
- Excel would create a new worksheet with Pivot table canvas on the left and Field list on the right (Figure 6). If the Field list doesn’t appear, select any cell inside the canvas.
- In the Field list (Figure 7):
- Click on the field with categories (Transaction in our case), hold and drag it to the Rows area at the bottom
- Click on the field with values (Amount in our case), hold and drag it to the Values area at the bottom
- Make sure that the name of this field in Values area starts with Sum of… If it starts with something else (e.g., Count of…), then double-click on it, select Value Field Settings… and double-click on Sum in Summarize Values By section
As soon as you drag and drop a field into a specific area (Rows, Columns, Values, or Filters), its summary appears in Pivot table canvas on the left-hand side of Excel window. Figure 7 shows how your screen should look like after you completed the steps mentioned above.
The same way, fields can be added to Columns area to create a two-dimensional table.
Pivot Tables Quiz
0 of 3 questions completed
Take a quick quiz to understand how well you know Excel pivot tables:
You have already completed the quiz before. Hence you can not start it again.
Quiz is loading...
You must sign in or sign up to start the quiz.
You have to finish following quiz, to start this quiz:
Time has elapsed
You have reached 0 of 0 points, (0)
Question 1 of 3
Which of the following best describes the functionality of Excel Pivot tables?Correct
You might consider reviewing the section about what is a pivot table
Question 2 of 3
How should the source data for a pivot table be structured?
Select all that applyCorrect
You should consider reviewing the section about how to create a pivot table
Question 3 of 3
Please sort the following steps for creating a simple pivot table
Drag and drop the items to sort them from the earliest in the process (at the top) to the latest (at the bottom)
Make sure the data set is structured correctly
Select the data set
Insert a pivot table (e.g., click on a Ribbon item)
Add the category field to Rows or Columns areas; add the value field to Values area
Make sure a correct summary function is applied (e.g., if the sum function is implied, the value field name in Values area should start with "Sum of...")
You might consider reviewing the section about how to create a pivot table