How to Hide Excel PivotTable Rows with Blank Values
Question details
The user wants to hide rows in a PivotTable where all corresponding value fields contain no data or are completely blank.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing data using PivotTables with multiple row fields and multiple value fields (such as separate columns for January and February).
- Observed behavior
- The PivotTable continues to display row labels even when all the value fields for those specific rows contain no data.
Ensure your PivotTable is refreshed to reflect the most current source data, and check if any blank-looking cells actually contain invisible spaces or zero values hidden by custom formatting.
Disable the Show items with no data Option
The most common reason blank rows appear is a specific setting within the Field Settings of your PivotTable row items.
PivotTables often retain row labels for items that previously had data or exist in the dataset but have no values in the current filtered view. Disabling this default behavior forces the PivotTable to hide empty rows.
Right-click on the row field label (e.g., State, City, or Product) directly inside your PivotTable and select Field Settings from the context menu.
In the Field Settings dialog box, click on the Layout & Print tab at the top.
Locate the checkbox that says Show items with no data. Uncheck this box, then click OK to apply the changes.
Create a Helper Column in Your Source Data
If multiple value fields are blank simultaneously and built-in options fail due to data complexity, a helper column can evaluate the rows before pivoting.
Apply a Value Filter to Exclude Blanks
You can force the PivotTable to hide rows that do not meet a certain condition by applying a Value Filter directly to the row labels.
Easily Manage Pivot Tables with WPS Spreadsheet
WPS Spreadsheet offers a seamless, highly compatible environment for creating and managing complex PivotTables. You can easily filter out blank rows, adjust field settings, and analyze your data without hassle.
- 1. Open the file in WPS: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx).
- 2. Access PivotTable options: Click any cell inside your PivotTable to reveal the PivotTable Tools tab on the ribbon.
- 3. Modify Field Settings: Right-click the targeted Row Label, choose Field Settings, and navigate to the Layout tab.
- 4. Hide empty data: Uncheck Show items with no data and click OK to instantly clean up your PivotTable view.
Frequently Asked Questions
Why is 'Show items with no data' grayed out in my PivotTable?
This option is typically grayed out if the data model is being used (such as Power Pivot), if the field is not a row or column field, or if the PivotTable is connected to certain types of external OLAP data sources.
Does filtering out blank rows delete the underlying data?
No, filtering or hiding rows in a PivotTable only changes the visual display of the summary report. Your original source data remains completely intact and unchanged.
How do I hide zero values instead of blanks in a PivotTable?
You can hide zero values by using a Value Filter on the row labels (setting it to 'Does Not Equal' 0), or by changing the number format of the value field to a custom format like '0;-0;;@', which visually hides zeroes.
