'%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)
How to Hide Blank PivotChart Columns After Filtering in Excel
Question details
The user wants to hide a calculated column in a PivotChart or PivotTable that becomes blank after applying year filters.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering a PivotTable report connected to a Data Model where a calculated percentage-difference column retains its structure but has no data for the selected period.
- Observed behavior
- The calculated column remains visible as an empty space in the PivotTable and PivotChart even though it contains no values for the filtered years.
Ensure your PivotTable is fully updated and check if any active filters are unintentionally forcing empty data fields to remain in the layout.
Disable the 'Show items with no data' Option
The most common reason blank columns appear after filtering is a specific PivotTable layout setting. Disabling this option forces the table and connected chart to hide empty fields.
Excel provides built-in options to handle empty rows and columns in PivotTables. By default, some tables may retain items with no data to preserve the structure of the report, but this can easily be toggled off.
Right-click anywhere inside your PivotTable and select 'PivotTable Options' from the context menu.
In the PivotTable Options dialog box, click on the 'Display' tab.
Look for the checkbox labeled 'Show items with no data on rows' or 'Show items with no data on columns' and uncheck it.
Click 'OK' to apply the changes. Then, go to the 'PivotTable Analyze' tab on the ribbon and click 'Refresh' to update your PivotChart.
Modify Individual Field Settings
If adjusting the global PivotTable options does not hide the calculated blank column, you may need to adjust the specific settings for that individual field.
Create and Manage PivotTables Seamlessly in WPS Office
WPS Spreadsheet offers powerful PivotTable and PivotChart features that are fully compatible with Excel formats. You can easily manage data layouts, filter out blank items, and analyze complex datasets without any hassle.
- 1. Open Your Data: Launch WPS Spreadsheet and open your dataset or existing Excel file containing the PivotTable.
- 2. Insert or Modify PivotTable: Go to the 'Insert' tab and click 'PivotTable' to select your data range, or click on your existing table to activate the PivotTable Tools tab.
- 3. Adjust Display Options: Right-click the PivotTable, select 'PivotTable Options', and uncheck the setting for displaying empty items to clean up your layout instantly.
Frequently Asked Questions
Why does my PivotChart still show a blank space after hiding the column?
The PivotChart might be referencing a fixed chart axis. Try right-clicking the horizontal chart axis, selecting 'Format Axis', and ensuring the axis type is set to 'Automatically select based on data' rather than a fixed text or date axis.
Can I hide a calculated field that results in zero instead of blank?
Yes. You can apply a Value Filter to the PivotTable row or column to hide zeros. Click the filter drop-down on the row or column label, select 'Value Filters', choose 'Does Not Equal', and enter '0'.
Will disabling 'Show items with no data' affect my raw data?
No, this setting only changes how the data is visually represented in the PivotTable or PivotChart. Your original dataset in the Data Model or source spreadsheet remains completely untouched.
How do I refresh a PivotChart automatically when data changes?
While spreadsheets don't refresh PivotTables in real-time by default, you can force a refresh upon opening the file. Right-click the PivotTable, go to 'PivotTable Options', navigate to the 'Data' tab, and check the box for 'Refresh data when opening the file'.
