'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix PivotTable Drill-Through Not Showing Underlying Data in Excel
Question details
The user is unable to view the underlying data when using the drill-through (double-click) feature on specific PivotTable cells, particularly those involving sum fields or Power Pivot data models.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to double-click PivotTable cells to drill through and view the detailed source records.
- Observed behavior
- Drill-through works for count fields but fails for regular cells when a sum field is included. The Power Pivot data model appears to have lost calculations and variables across multiple worksheets.
Ensure your source data range is intact and that any external data connections or Power Pivot data models are fully refreshed and accessible before troubleshooting the PivotTable.
Verify and Restore Power Pivot Data Model Calculations
Check if the underlying data model has lost its calculations, measures, or variables, which commonly blocks the drill-through function.
When drill-through fails specifically on sum fields or calculated values, it often indicates a broken link or missing measure within the Power Pivot data model.
Navigate to the Power Pivot tab on the Excel ribbon and click 'Manage' to open the data model window.
Check the calculation area beneath your tables to ensure all measures and variables are still present and computing correctly without returning errors.
If calculations return no results, recreate the missing measures and verify the table relationships in the Diagram View.
Recreate the PivotTable in a New Workbook
If the current workbook or data model is corrupted, building the PivotTable from scratch in a fresh file can isolate and resolve field-specific glitches.
Experience Flawless PivotTables with WPS Office
If complex data models and broken Power Pivot calculations are causing recurring drill-through failures in Microsoft Excel, consider switching to WPS Office. It offers a lightweight, highly compatible spreadsheet tool that handles standard PivotTable reports seamlessly without the heavy overhead or corruption issues.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your device.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx file containing your raw data.
- 3. Create and Drill-Through: Select your data, insert a PivotTable, and effortlessly double-click any aggregate cell to view the underlying records.
Frequently Asked Questions
Why does PivotTable drill-through work for count but not sum?
This typically happens when the sum field relies on a calculated measure within a Power Pivot Data Model that has missing variables, or when the data type of the source column contains non-numeric text preventing proper summation.
How do I enable the drill-through feature in a PivotTable?
Right-click anywhere inside the PivotTable, select 'PivotTable Options', navigate to the 'Data' tab, and ensure the 'Enable show details' checkbox is ticked.
Can I recover a corrupted Power Pivot data model?
You can attempt recovery by opening the Power Pivot window, going to 'Design', and selecting 'Manage Relationships' to fix broken links. However, severe corruption often requires rebuilding the data model from scratch or restoring a previous file version.
What exactly does 'Show Details' do in a PivotTable?
'Show Details' (also known as drill-through) automatically generates a new worksheet displaying the exact rows of raw source data that make up the aggregated value of the specific cell you double-clicked.
