Hide Pivot Table Item: How to Do It in Excel

To hide a pivot table item in Excel, right-click the item in the pivot table and select Filter > Hide Selected Items. This removes the item from the pivot table view without deleting the underlying data, and you can bring it back anytime through the field’s filter dropdown.

Why Hide Pivot Table Items?

Pivot tables summarize large datasets, but sometimes you don’t want every category cluttering your report. Hiding items helps you focus on the data that matters—like excluding a specific product line, removing blank entries, or dropping a region with no sales. It’s a purely visual change: the item remains in your source data, and other pivot tables or charts using the same data are unaffected unless you hide it there too.

How to Hide a Single Item

The quickest method works for any pivot table item (row label, column label, or value field item):

1. Right-click the item you want to hide (e.g., a product name in the Row Labels).
2. Choose Filter from the context menu.
3. Click Hide Selected Items.

The item disappears from the pivot table immediately. To unhide it, click the filter arrow in the field header (e.g., the “Row Labels” dropdown), clear the checkbox for the hidden item, and click OK. The item reappears.

Hiding Multiple Items at Once

When you need to hide several items, use the field’s filter dropdown instead of right-clicking each one:

1. Click the filter arrow next to the field name (Row Labels, Column Labels, or a field in the Filters area).
2. Uncheck the items you want to hide.
3. Click OK.

This method is also how you hide items that appear in the filter dropdown but not in the table (e.g., items with no data). You can also type in the search box at the top of the dropdown to quickly find and uncheck multiple items.

Hiding Items with No Data (Blanks)

A common annoyance is seeing “(blank)” as a row or column label. This usually means some source rows have empty cells in that field. To hide these blank items:

• Right-click the blank item and choose Filter > Hide Selected Items.
• Or, in the filter dropdown, uncheck “(blank)”.

If blanks keep reappearing after refreshing, check your source data—empty cells in the field will create a blank item again. You can also use the Field Settings > Layout & Print tab and uncheck “Show items with no data” to prevent items with zero values from showing, though this affects all items in that field.

Hiding Items vs. Filtering: What’s the Difference?

Hiding an item is essentially a manual filter, but there’s a subtle distinction. When you hide an item via right-click, Excel adds it to the field’s hidden items list. This is the same as unchecking it in the filter dropdown. However, hiding via the dropdown also lets you apply other filters (like Top 10 or Label Filters) simultaneously. Both methods exclude the item from calculations (sums, averages, counts) in that pivot table. The item’s data is never deleted—it’s just not displayed or included in the pivot’s aggregate calculations.

Why Hidden Items Still Appear in the Filter Dropdown

Even after hiding an item, it remains visible in the field’s filter dropdown (with its checkbox unchecked). This is intentional—it lets you easily unhide it later. But it can clutter the dropdown if you have many hidden items. To remove hidden items entirely from the dropdown (and the pivot cache), you can:

• Right-click the field, choose Field Settings, go to the Layout & Print tab, and check “Hide items with no data”. This only hides items with no data, not items you manually hid.
• For a complete cleanup, refresh the pivot table after deleting the source rows that contain those items. But if you want to keep the items in source data and just declutter the dropdown, you can’t fully remove them—Excel keeps them in the pivot cache for potential reuse.

A practical workaround: if you have a large list of hidden items and want to see only active ones, use a slicer instead of the dropdown. Slicers only show items that exist in the pivot’s current data, and you can hide items from the slicer by unchecking them in the slicer settings.

Common Problems and Fixes

Here are typical issues users run into:

• Hidden items reappear after refresh: This happens if the source data changes and the item is no longer considered “hidden” by Excel. To prevent it, right-click the field, go to Field Settings > Layout & Print, and check “Hide items with no data”—but note this only affects items with zero data. For items with data, you may need to re-hide them after each refresh.
• Cannot hide a specific item: If the item is a date or number, Excel may group it into ranges (e.g., months, years). You can’t hide individual dates within a group—you must ungroup first (right-click > Ungroup) or hide the entire group.
• Hide option is grayed out: This usually means the pivot table is in a compact layout and the item is a subtotal or grand total. You can’t hide subtotals individually; instead, turn off subtotals via Field Settings or the Design tab.
• Hidden items still count in totals: If you hide an item, the pivot’s grand totals and subtotals exclude it by default. If they don’t, check if you’re using a calculated field or if the item is being included via a filter that isn’t actually applied.

Alternatives to Hiding Items

Hiding is not always the best approach. Consider these alternatives:

• Filter the source data: If you never need certain items, filter them out in the source table before creating the pivot. This keeps the pivot cache smaller and the dropdown cleaner.
• Use a slicer: Slicers are visual filters that let you toggle items on and off. They’re easier to manage than hiding items one by one, and you can multi-select.
• Create a new field: If you’re excluding items based on a condition (e.g., sales below $100), add a helper column in your source data (e.g., “Include?” with Yes/No) and filter on that.
• Use Report Filters: Drag a field to the Filters area and select “(All)” or specific items. This is useful when you want to hide entire categories without affecting the layout.

Frequently Asked Questions

Does hiding an item delete my data?

No. Hiding an item only removes it from the pivot table’s display and calculations. The original data in your worksheet or external source remains untouched. You can always unhide the item later.

Why does my hidden item keep coming back?

This usually happens when the pivot table refreshes and the item’s status is reset. If the item has data, Excel may re-add it. To keep it hidden, you can try using a slicer (which remembers selections better) or apply a label filter that excludes the item (e.g., “does not equal”). For items with no data, check “Hide items with no data” in Field Settings.

Can I hide items in a pivot chart too?

Yes. If your pivot chart is based on the pivot table, hiding items in the table will automatically update the chart. If the chart is separate, you can hide items directly in the chart’s field buttons (if enabled) or by filtering the underlying pivot table.

How do I hide all items except one?

Use the filter dropdown on the field, uncheck “Select All”, then check only the item you want to keep. Click OK. This is faster than hiding each item individually.

What’s the difference between hiding an item and using a label filter?

Hiding an item is a manual, one-time action. A label filter (e.g., “does not contain”) is a dynamic rule that automatically hides any item matching the criteria, including new items added after a refresh. Use label filters when you want consistent exclusions.

Related

• Grams to PPM Calculator: Convert Mass to Concentration
• Heat Pump Water Heater Calculations and Sizing
• Hot Water Heater Calculator: Estimate Tank Capacity