Advanced skills and common shortcut keys for pivot tables

Key points of this article

Convert the source data into a table first (Ctrl+T), then add new rows and then refresh them to automatically include them in the summary. There is no need to change the data source range every time.
Proportion, month-on-month, cumulative and other requirements are completed using "value display mode", which is less error-prone than adding additional formula columns.
The date field must be confirmed to be in the true date format before it can be combined by year, quarter, and month.
Slicers are suitable for quickly switching the same report by region, department, etc., and the current filtering status can be clearly seen.
Check the total and format before and after refreshing. Text numbers and blank items are two common problems.

Many people can build pivot tables and drag a few fields to get a summary. However, when making formal reports, they often get stuck in several places: they have to be redone as soon as the data is updated, they have to calculate the proportions themselves, dates can only be viewed by day, and the filtering conditions of multiple tables do not match. Most of these problems do not require writing complex formulas and can be solved with a few built-in functions.

This article is written for readers who are already able to create pivot tables. It organizes the top 10 commonly used tips and high-frequency shortcut keys for pivot tables, and gives a set of operating steps and checklists that can be followed. The methods are all common Excel operations. After reading, you can organize the source data into an extensible structure, and use value display, grouping and slicers to complete common summary analysis.

How to advance the pivot table? One sentence conclusion

The key to advanced pivot tables is to first think clearly about what questions the report wants to answer, and then select the corresponding functions. The table below provides recommended practices for common situations.

your situation	Recommended practices	reason
Data is added to the table every month	Convert the source data to a table (Ctrl+T), select the table name for the pivot table data source	The table will automatically expand, and new data will go directly into the summary after refreshing.
Want to see the sales ratio of each product	In the value field settings, select Display as a percentage of the total.	Calculate directly from the pivot table, no need to write division formula
Want to see trends by quarter or month?	Right-click the date field, use the grouping function, and check Quarter and Month	Grouping changes with date, no need to create additional auxiliary columns
To switch the same report by region or department	Insert slicers and connect multiple pivot tables	Click to filter and the current conditions are clear at a glance
You need to add a derived indicator such as "profit rate" to the pivot table	Use calculated fields	Indicators are calculated along with the row and column structure of the pivot table

Here's a quick checklist of 10 common tips:

Convert the source data into a table to avoid missing new rows in the data range.
Use value display mode to do proportion, chain, cumulative and ranking.
The date column combination is year, quarter, month, and you can see the trend by cycle.
Numeric ranges are grouped by numbers, such as grouping by amount range.
Replace manual filtering with slicers and connect multiple pivot tables.
The report filtering page can be split into multiple worksheets by a certain field with one click.
Null or error values can be displayed in the PivotTable options.
Supplement the derived metric with a calculated field, noting that it is calculated as an aggregate result.
The data model is suitable for association of multiple tables, provided that each table has corresponding fields.
Check the data source before refreshing, and check whether the format is preserved after refreshing.

Before you start: Confirm these points first

Preparation before starting often determines whether to rework later.

Whether the source data is standardized. Each column needs a clear field name, and only the same type of data should be placed in the same column. There should be no merged cells, empty rows or columns in the middle. Otherwise, the pivot table will treat blank or misaligned content as independent categories.
Whether the format of numbers and dates is correct. What appears to be numbers may be text, and they do not participate in the sum, or dates may just be text. This type of problem manifests itself as a small total or inability to group in the pivot table.
Whether the data will continue to increase. If it is a one-time analysis, use a normal area; if it will be continuously appended, it should be converted to a table and set as the data source of the pivot table.
Excel version and operating system. Slicers, schedules, and data models are more complete in newer versions, and shortcut keys are different on Windows and Mac. The shortcut keys in this article take the Windows version as an example, Mac users please refer to the menu prompts.
Whether multiple tables need to be associated. If the analysis involves multiple tables, first confirm whether they have the same key fields, otherwise the results may be duplicated or missing after association.

Steps to create an expandable pivot table in Excel and add a slicer

Take a sales detail as an example, which includes four columns: date, region, product and amount.

Select any cell in the source data, press Ctrl+T to convert to a table, and confirm that the table contains a title. In this way, the new rows will be automatically included in the table.

two. Press Alt, N, V to open the window that creates the PivotTable. Confirm the table name in the data source and select to put it on the new worksheet.

Drag date to the line area and amount to the value area. Right-click the date field, use the grouping function, check quarter and month, and then you can view the summary by quarter and month.
Drag the amount to the value area again, open its value field settings, select to display as a percentage of the sum, and you can see the percentage of each quarter.
Select any cell in the PivotTable, add a slicer to the insert tab, and check "region" and "Product".
Click the region name in the slicer to filter. Press and hold Ctrl for multiple selections, and click the clear button in the upper right corner of the slicer to recover all data.
When the data changes, select any cell in the PivotTable and press Alt+F5 to refresh (Windows version). You can also right-click on the PivotTable to refresh.
After the refresh is complete, check the totals, groups, and formats item by item, as detailed in the next section.

# check item by item after completion

After the report is finished, don't send it to others directly, check it with the list below first.

Check item	What do you think?	What if there's a problem?
Whether the total is consistent with the source data	Use the summation function to sum the amount column of the source data and compare it with the total of the PivotTable	If it is too small, check whether there are text numbers; after selecting the data, correct it with the conversion to number or sorting function, and then refresh it.
Whether the date grouping is correct	Whether the row label is a year, quarter, month, rather than a string of dates	If it cannot be combined, the date column is text and needs to be changed to date format first.
Whether the "(blank)" item appears	Check to see if there is (blank) in the line label.	There are blank lines in the source data or the spelling of the classification name is not uniform. Refresh after correction.
Is the proportion of the benchmark correct?	Check whether the proportion addition corresponds to the total, and whether the benchmark is a row total or a column total.	Reselect the display method in the value field settings
Whether the format is retained after refresh	Check to see if the column width, number format, and background color change	Automatically adjust the column width when you turn off updates in the PivotTable option, and set the number format to retain the cell format

# Core comparison

There are three common ways to filter PivotTable according to different conditions, each with a suitable scenario.

Screening mode	Operation method	Advantages	Limitation	Suitable for the scene
Field drop-down filter	Click the filter arrow next to the row or column header to check the desired item	Directly, no additional settings are required	Do not see the current filter, cannot be linked with multiple PivotTable	Temporary viewing, single analysis
Slicer	Insert the slicer on the PivotTable and click the button to switch conditions.	The current conditions are clear at a glance, and multiple PivotTable can be connected	You need to occupy a page, and you have to make a choice when there are many fields.	Interactive reports that require frequent switching conditions
Report filter page	Split the PivotTable into multiple worksheets according to a field, each with a value	It is convenient to distribute to different departments or personnel.	The number of worksheets increases with the value of the field, so it is more tedious to maintain later.	Periodic reports distributed by department or person

If it is only a temporary view, it is enough to use the field drop-down filter; if you want to make an interactive report for others to see, the slicer is clearer.

# frequently asked questions (FAQ)

1. How to count the PivotTable by week?

There is usually no direct "week" in the options for date combinations. You can first add a column to the source data, use the formula to calculate the week of each date, and then drag this column into the row area. The weekly definition of this practice is up to you and can be written in the header or description.

2. The data has been updated, why has the PivotTable not changed?

The common reason is that the source data range does not cover the newly added rows, or the refresh is not performed. First check whether the data source points to the table name, and then select the PivotTable to perform a refresh. If it remains the same, check to see if the new data is in text format.

3. What is the difference between a calculated field and a normal formula column?

The normal formula column is calculated on each row of the source data, and the result is saved with the data row. The calculated field is calculated after the summary, according to the current column structure of the PivotTable, so the summary result does not make an error by dragging the field. The two scenarios are different and need to be selected according to the requirements of the report.

4. If there is no response when the shortcut key is pressed, what should I do?

First verify that the cursor is in the PivotTable area, and many PivotTable shortcuts take effect only when the PivotTable is selected. Second, check the keyboard layout and the Excel version. The keyboard shortcuts on Mac are different from Windows. If it still doesn't work, you can view the shortcut key tips for the corresponding command in the menu.

5. How to quickly correct text-based numbers?

Select the column of suspected text, use the prompt in the upper left corner of the cell to choose to convert to a number, or use the sorting function, and then convert it step by step. Remember to refresh the PivotTable after correction. For the results of machine recognition or batch conversion, the key numbers are still recommended for spot check.

# Summary

The main purpose of PivotTable is to sort out the source data, and then use value display, date combination, slicer and refresh settings to complete common analysis. Transfer the form first, and then check the total, which can avoid most of the rework. Familiarize yourself with the above 10 common skills and keyboard shortcuts, the efficiency of doing summary reports will be significantly improved.

Open OmniDoc and process your form file