Useful tips for using Excel PivotTable that you may not know

PivotTables can summarize thousands of rows of data in Excel in just a few secondsbut many people still waste time filtering data. The tips for using Microsoft Excel’s PivotTable below will help make your spreadsheet work simpler.

View source data by double-clicking the value you want

Let’s say you want more details about one of the values ​​in your PivotTable. Instead of switching between distracting tabs:

1. Find and double-click the PivotTable value you want to view.
2. Review the newly created spreadsheet that contains only the source rows for that value.
3. When the review is complete, right-click on the new sheet tab at the bottom of the window and click Delete.

Create separate spreadsheets for each category

This is a PivotTable feature that helps Excel users save significant time. First, let’s set up automation:

1. Drag the category field you want to split into the Filters of the PivotTable fields pane.
2. Click inside a PivotTable to display contextual ribbon tools.
3. Open tab PivotTable Analyze.
4. Click the small drop-down arrow right next to the button Options on the left side.
5. Select Show Report Filter Pages from the contextual drop-down menu.

Next, to create the sheets:

1. Check that the selected filter field in the pop-up dialog box matches your target column.
2. Click OK to run automatic worksheet generation.
3. Click through the newly created worksheet tabs to view each report.
4. To export a specific report, right-click a worksheet tab, then click Move or Copy.

Use the Distinct Count function to track unique values

By adding source data to Excel’s Data Model when you first create the table, you unlock the option to count hidden differences, completely ignoring duplicates.

Start by initializing the Excel Model Data workspace:

1. Select your raw source table and open the tab Insert.
2. Click PivotTable to open the standard creation dialog box.
3. Select the target sheet location.
4. Tich Add this data to the Data Model.
5. Click OK to create your new PivotTable.

Now you’re ready to switch your pivot table to separate count mode:

1. Drag the identity field into the Values ​​box.
2. Right-click any number in the newly added column and select Value Field Settings.
3. Scroll down the list of calculations and click Distinct Count.
4. Click OK.

The PivotTable will update immediately to show discrete quantities, meaning each customer is counted only once per region, no matter how many times they purchased.

Group related items without adding auxiliary columns

Instead of modifying the main database or creating auxiliary columns, you can do it all in a PivotTable.

Here’s how to create and clean up custom groups:

1. Hold down the key Ctrl while clicking on each individual text label in the rows of your first custom group.
2. With those items still selected, right-click any of them, then select Group.
3. This will initially make the PivotTable look messy, so right-click the left PivotTable column header and select Expand/Collapse > Collapse Entire Field to clean everything up.
4. Select the cell containing the common group label, then overwrite the existing text with a more understandable name and press Enter.

After repeating the selection, grouping, and renaming steps for the remaining items:

1. Right-click on the newly created parent field header in the grid.
2. Click Field Settings.
3. Rename the field to reflect the category it represents, then click OK.

While overriding individual group labels in a PivotTable is completely valid and only affects how those items display, the field header at the top represents the grouped field itself below, not a single label, which is why you need to use Field Settings.

Now all related items and their associated numeric values ​​are grouped together, so you can quickly summarize categories, detect higher-level trends, and create clearer reports without having to refactor the source data yourself.

Calculate monthly growth without having to write formulas

To configure the period growth view:

1. Drag your key performance metric into the Values ​​box a second time so it appears duplicated in the grid.
2. Right-click any cell inside that new duplicated value column.
3. Mouse over Show Values ​​Asthen select % Difference From.
4. Set drop-down options Base Field become a school Month created from group Date your.
5. Set the Base Item drop-down option to (previous), then click OK.
6. The Excel PivotTable displays the Revenue field added twice in the Values ​​area in preparation for comparison calculations.

Now that the PivotTable has successfully displayed the percentage difference by month, you can click the duplicate value column header and rename it directly in the PivotTable grid.

Add new data to your source table, refresh the PivotTable, and the calculations will be updated immediately without damaging your structure.