Excel freezes when I select "show items with no data" in a pivot table

Question

Excel freezes when I select "show items with no data" in a pivot table

Answer 1

Hi,

Thank you for sharing your question. I understand how frustrating it is when a PivotTable hangs the moment you toggle “Show items with no data,” especially if you’re on a deadline and just want all categories to appear even when some have zero activity.

What’s happening is that “Show items with no data” can force Excel to enumerate every item in your row or column fields even those without records so on large sources or complex models it can spike calculation and rendering work and appear to freeze; the impact is more noticeable when the PivotTable is tied to the Data Model (Power Pivot), where that checkbox is often disabled in Field Settings and needs to be handled at PivotTable Options level or via a dimension table and measure, which adds overhead during refresh.

The quickest path to resolution is to limit the scope and then re‑enable the result cleanly. First, if the PivotTable was created with “Add this data to the Data Model,” try rebuilding it without the Data Model to see if the freeze disappears; many users find that turning off the Data Model restores the standard “Show items with no data” behavior and avoids the stall.  Next, if you must use the Data Model, move the toggle from individual Field Settings to PivotTable Options and ensure your model includes a proper dimension table of all categories related to the fact table, then use a simple measure that returns zero instead of blank to surface the missing members; this is the supported pattern to display items with no data in Power Pivot–backed pivots. 

May I ask a couple of details to tailor this further: is your PivotTable connected to the Data Model or a plain range/table, and roughly how many unique items are in the fields where you’re enabling “Show items with no data”?

If you try these steps and Excel still freezes, reply with a bit more about the data size, whether Power Pivot is involved, and any add‑ins you have enabled, and I’ll help you narrow it down and provide the exact steps for your setup.

I hope this helps.

 

Best Regards,

Noel

Answer 2

If Excel freezes when you select "show items with no data" in a pivot table, it may be due to performance issues associated with this feature. The Show items with no data feature can negatively affect performance and may cause slow rendering of visuals or even timeouts during data exports. This is particularly true if the data set is large or if there are complex calculations involved.

To troubleshoot this issue, consider the following steps:

1. Check Data Size: Ensure that the data being processed is not excessively large, as this can lead to freezing.
2. Optimize Pivot Table: Simplify the pivot table by reducing the number of fields or calculations to see if that improves performance.
3. Manual Calculation Mode: If you are using manual calculation mode, ensure that all necessary calculations are completed before enabling this feature.
4. System Resources: Make sure your system has enough resources (memory and CPU) available to handle the operations being performed.

If the issue persists, you may want to reach out to Microsoft support for further assistance.

---

References:

• Export to an Excel PivotTable
• Show items with no data in Power BI