Pivot Table Display Items with No Data? Fix It
Yes, pivot tables can display items with no data, but by default they often don’t. When you add a field to the Rows or Columns area, Excel only shows items that have at least one value in the source data. To force the display of all items—even those with zero or blank values—you change the field’s layout settings, specifically the “Show items with no data” option. This article explains how that option works, when to use it, and how to troubleshoot common issues.
Why Pivot Tables Hide Empty Items by Default
A pivot table is a summary of your source data. Its purpose is to aggregate values—sums, counts, averages—grouped by the fields you choose. If a category (like a product name or a month) has no rows in the source data, there’s nothing to summarize. Excel assumes you don’t want to see a blank row or column for something that doesn’t exist in the data. This keeps the pivot table compact and focused on what actually has data.
However, there are many situations where you do want to see those missing items. For example:
- You’re comparing monthly sales across several regions, and one region had no sales in March. You want to see March with a zero, not have it disappear.
- You have a list of all employees, but some have no recorded hours this week. You want their names to appear with a zero total.
- You’re building a report that must show a complete list of categories, even if some are empty, for consistency or for further analysis.
How the “Show Items with No Data” Option Works
This setting is controlled per field, not for the entire pivot table. It lives in the Field Settings dialog box for each row or column field. Here’s how to enable it in Excel (versions 2013 through Microsoft 365):
- Click anywhere inside your pivot table to activate the PivotTable Tools on the ribbon.
- Right-click on a field in the Rows or Columns area (for example, the “Month” field).
- Choose Field Settings from the context menu.
- In the dialog box, go to the Layout & Print tab.
- Check the box that says “Show items with no data”.
- Click OK.
After this, the pivot table will display all items from that field’s source list, even those with no corresponding data. The values for those items will show as blank or zero, depending on how your pivot table is configured (see the section on blanks below).
What Counts as “No Data”?
It’s important to understand what Excel considers “no data.” The option shows items that exist in the source data for that field, but have no rows in the current filter context. For example, if your source data has a list of months from January to December, but your data only covers January to June, enabling the option will show July through December with no values. However, if the month “January” doesn’t appear anywhere in your source data (because you only have data from February onward), it won’t be shown—even with the option enabled. The option only displays items that are present in the underlying data list, not every possible value you might imagine.
Common Problems and Their Fixes
Even after you check the box, you might still not see the empty items. Here are the usual culprits:
- The field is in the Filters area, not Rows/Columns. The option only applies to row and column fields. If you put a field in the Report Filter, you can’t force it to show all items this way. Instead, you might need to use the “Select Multiple Items” checkbox in the filter dropdown, but that’s not the same as showing empty items.
- The item is filtered out by a report filter or slicer. If you have a filter on another field that excludes the rows containing that item, the item won’t show. For example, if you filter to “Region = West” and the West region has no data for “Product A,” Product A won’t appear, even with the option enabled.
- The field is a calculated field or has a custom layout. In some cases, calculated fields or fields with custom subtotals may not respect the “show items with no data” setting. Try removing any custom calculations and test again.
- You’re looking at the wrong field. Remember, the setting is per field. If you want all months to show, you must enable it on the Month field specifically, not on the Values field.
How to Display Zeros Instead of Blanks
When you show items with no data, the cells for those items are typically blank. If you prefer to see a zero (which is often clearer for reporting), you can change the pivot table’s layout settings:
- Right-click any cell in the pivot table and choose PivotTable Options.
- Go to the Layout & Format tab.
- In the “Format” section, find the “For empty cells show:” box.
- Type
0(or any text like “N/A”) in the box. - Click OK.
Now any empty cells in the values area will display as 0. Note that this only affects cells that are truly empty; it doesn’t change cells that contain a real zero from your data.
Alternatives: When the Option Doesn’t Work
If the “Show items with no data” option isn’t giving you the result you need, there are a few workarounds:
- Add a row for every item in your source data. If you manually add a blank row for each missing item (e.g., a row with the month but no values), the pivot table will include it. This is tedious but works.
- Use a PivotTable with the “Add this data to the Data Model” option. When you create a pivot table from a table and check “Add this data to the Data Model,” you get access to DAX measures, and you can use functions like
CALCULATEwithALL()to force display of all items. This is more advanced but gives you full control. - Use a regular Excel formula. If you just need a list of all items with their sums, you can use
SUMIFSorCOUNTIFSwith a complete list of items, and it will naturally show zeros for missing ones. - Use a Power Pivot or Power Query solution. These tools let you create a “calendar table” or “dimension table” that contains all possible items, then join it to your data. This is the most robust method for complex scenarios.
Frequently Asked Questions
Why does my pivot table still not show empty items after I checked the box?
Check if the field is in the Rows or Columns area (not the Report Filter). Also, ensure that the item actually exists in your source data. If you have a slicer or report filter active, it might be excluding the rows that contain that item. Try removing all filters and test again.
Can I show items with no data for multiple fields at once?
Yes, but you must enable the option separately for each field. There’s no global setting. Right-click each row/column field, go to Field Settings, and check the box.
Does this work in Google Sheets?
Google Sheets pivot tables work differently. They don’t have a direct “show items with no data” checkbox. To achieve the same effect, you can add a helper column in your source data that lists all possible items, or use a formula-based approach. Some users find that adding a blank row for each missing item is the easiest workaround.
What if I want to show items that aren’t in the source data at all?
The option only shows items that exist in the source data. If you need to display items that are completely absent (e.g., a full year of months when your data only has two months), you’ll need to add those items to your source data or use a calendar table in Power Pivot.
Will showing items with no data affect my pivot table’s performance?
It can, especially if you have a large number of items. The pivot table has to render all those rows/columns, which increases the file size and may slow down calculations. But for typical datasets, the impact is minimal.
Can I use this option with a pivot chart?
Yes, the setting applies to the pivot table, and any pivot chart based on it will reflect the same display. If you want the chart to show empty categories, enable the option on the relevant field in the pivot table first.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.