How to hide blank rows in a PivotTable in Excel?
Pivot tables in Excel offer a dynamic and convenient way to summarize and analyze data, helping users gain valuable insights quickly. However, during the process of organizing and viewing summary data, you may notice that some rows in your PivotTable appear with blank contents, as shown in the example screenshot below. These unwanted blank rows can make your report look less tidy and sometimes affect the clarity of your data analysis. Understanding how to efficiently hide or remove these blank rows can ensure your PivotTable displays only the information you need.
Hide blank rows in the pivot table
If you find blank rows appearing in your PivotTable, removing them can help keep your report clean and easy to read. The most direct method is to filter out blank values in the row labels area. This is especially useful when blanks are caused by incomplete source data or when certain categories have missing values.
1. Click on the arrow icon next to the Row Labels heading in your PivotTable. This will open a filter menu for the field currently shown as rows.
2. A drop-down list will appear. Click the field selector box under Select field if necessary, then choose the field where blank values appear. In the list of filter choices, uncheck the (blank) option. This action will exclude all rows with blank values in the selected field.
3. Click OK to confirm and apply the filter. Your PivotTable will now immediately refresh and only display rows with non-blank values in the chosen field.
Tip: If you need to temporarily show hidden blank rows again, simply revisit the same filter menu and check the (blank) option. The PivotTable will update to display the previously hidden blank rows. This quick reversal is useful if you want to review or correct data in those rows.
This manual filtering approach is best for situations where you occasionally need to hide blanks and where the source data changes infrequently. However, for larger PivotTables or when the source data is updated regularly with potential new blanks, you may want to consider automated or pre-filter solutions covered below.
Relative Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in