OmniDocHow to view detailed data in a pivot table (double-click to drill)
Pivot tables only show summary numbers, and you often need to see the original records behind them for verification. This article explains how to double-click the summary value to generate details, how to confirm that the details are consistent with the summary, and what to do when the details do not match.
Key points of this article
- Double-click a summary value in a pivot table and Excel will generate a new table listing the original records that make up the value.
- Double-click the total cell to view all source data, double-click a subtotal to view only the corresponding part.
- Refresh the pivot table first after modifying the source data, otherwise the details obtained by double-clicking may be inconsistent with the current data.
- The total amount of the detail row should be equal to the summary value. This is a direct way to judge whether the drill result is correct.
- When there is only a PDF version of the report, you need to convert the table to Excel before pivoting and drilling on the values.
Pivot tables are good at compressing thousands of rows of records into dozens of summary numbers, but the summary numbers themselves cannot answer "how did this number come from?" During financial reconciliation, sales review, and inventory reconciliation, readers often need to find the specific records that constitute a certain total. This article is written for people who already know how to make a pivot table, but don't know how to go from summary numbers back to original details.
The core method is simple: double-click the summary value in the pivot table, and Excel will create a new worksheet listing all the source data rows behind the value. This operation is often called "drill-down". The following will first give the method of selecting according to the situation, then explain the specific steps, and finally explain how to check the results.
How to view the detailed data of the PivotTable? Conclusion in one sentence: Double-click the corresponding summary value
Find the number that needs to be checked in the pivot table, double-click the cell, and Excel will generate a new table containing the corresponding detail records. If you want to see all the data, double-click the total cell. The following table provides recommended practices based on common situations:
| your situation | Recommended practices | reason |
|---|---|---|
| Want to check the source of a summary number | Double-click the value cell | Directly generate the detail row corresponding to the value |
| Want to view all source data of the entire table | Double-click the total cell | The total covers all source data with complete details |
| The source data has just been changed and the numbers don’t match. | Refresh the pivot table first, then double-click | Drill results are based on the pivot table's current data cache |
| Just keep a few of the lines | Copy after filtering in details table | Avoid leaving the entire schedule in the report |
| Report only available in PDF version | Export the table to Excel first, and then do the pivoting | Numbers in PDF cannot be used directly for pivoting and drilling |
Before you start: Confirm these points first
1. Whether the pivot table is based on the complete source data area. If new rows are added to the source data range after making the pivot table, the pivot table will not automatically include them. The data source range should be checked before verification, otherwise the details will be less than the real record.
2. Current page fields and filter conditions. Page fields or slicers at the top of a pivot table affect summary values. The details obtained by double-clicking only include records under the current filtering conditions. This is often overlooked and can easily be misjudged as "missing data".
3. Summary method. Pivot table values may be sums, counts, averages, or other means. What is obtained by drilling is the original row that constitutes the value, so the value of the counting class corresponds to the number of rows, and the value of the summation class corresponds to the total amount. When checking, it must be checked in a summary manner.
4. Whether the value comes from a calculated field. The breakdown of values derived from a calculated field or calculated item may be counterintuitive because it is not a direct summary of a column in the source data. In this case, it is recommended to go back to the source data and manually filter and check based on the row and column conditions of the pivot table.
Steps to double-click drill details in a PivotTable report
- Open the workbook containing the pivot table and confirm that the source data table has not been deleted or moved, and the data source of the pivot table is still valid.
- If the source data has just been modified, perform a refresh first to let the pivot table read the new data.
- Find the summary value that needs to be reconciled, such as the total sales of a certain department in a certain month.
- Double-click the value cell. Excel will automatically create a new worksheet.
- Check the detail rows in the new worksheet and confirm that their row fields and column fields are consistent with the value you clicked, such as the department and month.
- Sum the amount columns in the detail table and compare the result with the numbers in the pivot table. Both should be equal.
- If you need to check multiple times, you can double-click on different summary values repeatedly, and a new detailed table will be generated each time.
- After the verification is completed, copy the details that need to be retained to a separate worksheet, and indicate the data source and filtering conditions to facilitate future traceability.
Check each item after completion
After getting the detailed table, don't just look at it as "can be opened", but also confirm that it is consistent with the summary value.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Are the total amounts equal? | Sum the detailed amount column and compare it with the double-clicked value | If they are not equal, refresh the pivot table first and then double-click again. |
| Do row fields and column fields correspond? | Check whether the department, month and other columns in the details are consistent with the values you clicked | If they are inconsistent, check whether the field layout of the pivot table has been changed after double-clicking. |
| Is the number of records reasonable? | Compare with the number of rows in the source data that meet the same conditions | If there are obviously fewer, check the source data area or page field filtering |
| Whether data beyond filtering is included | View the current status of slicers and page fields | Clear unnecessary filters and drill again |
| Whether the data is from a new version | Check the update time or version number of the source data | The source data must be refreshed after modification, otherwise the details will still be the old cache. |
If the detail totals and summary values always do not match, it is usually because the source data area is incomplete, there are duplicate records, or the values come from calculated fields. At this time, you should directly filter by conditions in the source data table and check item by item instead of relying on drill results.
📊 Core comparison
There is more than one way to view details: double-click drilling. There are three common ways, each with their own applicable scenarios:
| Way | Operational points | advantage | limitations |
|---|---|---|---|
| Double click drill | Double-click the summary value to generate a new table | Quick, direct mapping of summary numbers | The generated table is temporary and needs to be saved to be retained. |
| Filter in source data | Filter by row and column conditions in the original table | The results are controllable and easy to review and save | Conditions need to be set manually, which can be cumbersome when there are many conditions. |
| Copy pivot table to new table | Paste the pivot table values as numerical values and then sort them | Suitable for forming fixed reports | The value is no longer updated with the source data and needs to be recreated. |
Double-click drilling can save trouble for daily verification; when you need to leave traces or hand it over to others for review, it is recommended to filter the source data according to conditions and record the filtering conditions.
❓ Frequently Asked Questions (FAQ)
1. There is no response when double-clicking the pivot table. Why?
Usually the double-clicked cell is not a numerical range, such as a row label or column header. Please double-click the specific number in the value area. If nothing happens, check to see if the pivot table is empty or if the data source has been deleted.
2. The number of rows in the detail table is more or less than I expected, what should I do?
First check whether the page fields and slicers are in filtering state, and then check whether the source data area contains all records. After modifying the source data, remember to refresh the pivot table first.
3. Can the detailed table generated after double-clicking it be used directly to make a report?
It can be used as a basis for verification, but it is a temporary table and will change as the pivot table is refreshed. For a formal report, it is recommended to copy the required details as numerical values and indicate the data source and date.
4. The report only has PDF files. Can pivoting and drilling be done?
Tables in PDFs usually cannot be viewed directly through perspective. If the PDF is a text version, you can use the PDF editor in the desktop client to export it to Excel, and then perform pivoting in the table. If it is a scanned version, you need to do OCR recognition first, and check the numbers one by one after exporting, because the recognition results may be wrong.
5. The numbers in the pivot table do not match the totals of the source data. Which one should I believe?
First confirm whether the calibers of the two are the same, for example, whether they contain the same filtering conditions and whether there are duplicate records. If the caliber is the same, check the differences item by item. Rather than directly basing the difference on either side, seek out specific documentation of the discrepancy.
📝 Summary
The core operation of viewing detailed data in a pivot table is to double-click the summary value, and the system will generate the corresponding detailed table. When checking, remember to refresh the data, check the filter conditions, and compare the detailed total and summary value to confirm that they are consistent.
