Fix Excel PivotTable Show Values As Percentage of Grand Total Blank
Question details
The user needs to resolve an issue where calculating the percentage of the grand total in an Excel PivotTable returns blank results for a specific month.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using the 'Show Values As' feature in an Excel PivotTable to calculate the percentage of the grand total for monthly data.
- Observed behavior
- The PivotTable returns blank results for one specific month, despite the source data containing numeric values.
Ensure your source data contains no hidden text characters formatted as numbers and that your PivotTable cache is fully refreshed.
Verify Source Data Formatting and Refresh PivotTable
Check for hidden text formatting in the source data and refresh the PivotTable cache to ensure calculations apply correctly to all months.
Even if data looks numeric, Excel might store specific cells as text, which causes PivotTables to ignore them during percentage calculations.
Go to your source data worksheet and select the cells corresponding to the month that is returning blank values.
If you see a small green triangle in the corner of the cells, click the warning icon that appears and select 'Convert to Number'.
Navigate back to your PivotTable, right-click anywhere inside it, and click 'Refresh' to update the PivotTable cache with the corrected number formats.

Check PivotTable Filters and Value Field Settings
Ensure no filters are excluding the data and that the Value Field Settings are correctly configured for percentage calculation.
Recreate the PivotTable
If the PivotTable cache has become corrupted, recreating the PivotTable from scratch can resolve isolated calculation bugs.
Create and Manage PivotTables Easily with WPS Spreadsheet
WPS Spreadsheet offers powerful, highly compatible PivotTable features that seamlessly handle complex data analysis, including percentage calculations, without unexpected glitches.
- 1. Open your file: Launch WPS Spreadsheet and open your existing Excel workbook containing the source data.
- 2. Insert PivotTable: Select your data range, navigate to the 'Insert' tab, and click 'PivotTable'.
- 3. Arrange fields: Use the side panel to drag your desired fields into the Rows, Columns, and Values areas.
- 4. Calculate percentages: Right-click the data in the Values field, select 'Show Values As', and click '% of Grand Total' for instant, error-free results.

Frequently Asked Questions
Why do numbers stored as text cause blank PivotTable values?
PivotTables only perform mathematical calculations (like Sum or % of Grand Total) on numeric data types. If a number is stored as text, the PivotTable treats its value as zero or ignores it completely, which results in blank percentage calculations.
How do I fix a corrupted PivotTable cache?
The easiest way to fix a corrupted cache is to change the PivotTable data source to a blank cell, click refresh, and then change the data source back to your original data range. Alternatively, recreating the PivotTable from scratch generates a completely fresh cache.
Can hidden rows affect my PivotTable % of Grand Total?
Hidden rows in the source data will still be included in the PivotTable calculations. To remove data from a PivotTable calculation, you must explicitly filter it out using the PivotTable filter dropdowns, Report Filters, or Slicers.
Why does '% of Grand Total' show errors like #DIV/0! instead of blanks?
A #DIV/0! error occurs when the grand total itself is zero or empty, making mathematical division impossible. A blank cell, on the other hand, usually indicates the specific row or column item lacks valid numeric data to divide by the grand total.




