Creating a basic pivot table
- By Bill Jelen
- 2/17/2025
- Format your source data before creating a pivot table
- How to create a basic pivot table
- Understanding the Analyze Data, Copilot, and Recommended PivotTable features
- Using slicers to filter your report
- Keeping up with changes in the data source
- Sharing the pivot cache or creating a new cache
- Saving time with PivotTable tools
- Next steps
Saving time with PivotTable tools
Microsoft has invested a lot of time and effort in the overall pivot table experience. The results of these efforts are tools that make pivot table functionality more accessible and easier to use. The following sections look at a few of the tools that help you save time when managing pivot tables.
Deferring layout updates
The frustrating part of building a pivot table from a large data source is that each time you add a field to a pivot area, you are left waiting while Excel crunches through all that data. This can become a maddeningly time-consuming process if you have to add several fields to your pivot table.
Excel offers some relief for this problem by providing a way to defer layout changes until you are ready to apply them. You can activate this option by selecting the relatively inconspicuous Defer Layout Update checkbox in the PivotTable Fields list, as shown in Figure 2-34.
){kind=link}
FIGURE 2.34 Select the Defer Layout Update checkbox to prevent your pivot table from updating while you add fields.
Here’s how this feature works: With the Defer Layout Update checkbox selected, you prevent your pivot table from making real-time updates as you move your fields around without your pivot table. When you are ready to apply your changes, click the Update button on the lower-right corner of the PivotTable Fields list.
NOTE
Remember to clear the checkmark from the Defer Layout Update checkbox when you are done building your pivot table. Leaving it selected results in your pivot table remaining in a state of manual updates, preventing you from using other features of the pivot table, such as sorting, filtering, and grouping.
TIP
Incidentally, the Defer Layout Update option is available through VBA. It can help improve the performance of any macro that automates the creation of pivot tables.
NOTE
For detailed information on how to use VBA to create pivot tables, refer to Chapter 13, “Using VBA or TypeScript to create pivot tables.”
Starting over with one click
Often, you might want to start from scratch when working with your pivot table layouts. Excel provides a simple way to essentially start over without deleting your pivot cache. Select the PivotTable Analyze tab and select the Clear dropdown. As you can see in Figure 2-35, this command enables you to either clear your entire pivot table layout or remove any existing filters you might have applied in your pivot table.
){kind=link}
FIGURE 2.35 The Clear command enables you to clear your pivot table fields or remove the applied filters from your pivot table.
Relocating a pivot table
You might find that after you have created a pivot table, you need to move it to another location. It might be in the way of other analyses on the worksheet, or you might simply need to move it to another worksheet. Although there are several ways to move a pivot table, the easiest is Excel’s no-frills way: Select Move PivotTable from the PivotTable Analyze tab in the ribbon. This icon activates the Move PivotTable dialog, shown in Figure 2-36. All you have to do here is specify where you want your pivot table moved.
){kind=link}
FIGURE 2.36 The Move PivotTable dialog enables you to quickly move your pivot table to another location.
Show Details for any cell in the values area
You’ve produced a pivot table and sent it to your manager, who takes one look at it and says, “This can’t be right! There is no way we sold $205K of Concession Equipment to Canada!”
Any time someone questions one of the numbers in the values area of your pivot table, simply select the cell containing the value. On the PivotTable Tools tab, click Show Details (see Figure 2-37).
){kind=link}
FIGURE 2.37 Click any number in the Values area and then click Show Details.
Excel will insert a new sheet to the left of the current sheet. This sheet will contain a title indicating what cell was active when Show Details was clicked. There will be a blank row 2 and then all of the rows from the original data set that make up the selected number, as shown in Figure 2-38.
)
FIGURE 2.38 Excel inserts a new worksheet to the left of the active sheet with all of the rows that make up the selected number.
Before 2024, you could achieve this Show Details report by double-clicking any cell in the pivot table Values area. In 2024, Microsoft added the Show Details button to the ribbon and improved the result by adding the title.
If you use Show Details on the total number at the intersection of the Grand Total row and the Grand Total column, the Show Details report will be an exact copy of the entire original data set.
You can use Show Details over and over. Each time will create a new worksheet to the left of the active sheet. If you don’t need these worksheets, you can press Ctrl+Z to undo; Excel will remove the new sheet.