Microsoft Office Tutorials and References
In Depth Information
Customizing Your Pivot Table
Figure 14-30: Filtering for the Europe region causes some of the sales periods to not display.
But displaying only those items with data could cause trouble if we plan on using this pivot table as
the source for your charts or other dashboard components. With that in mind, it isn’t ideal if half the
year disappears each time a customer selects Europe.
Here’s how you can prevent Excel from hiding pivot items without data:
1. Right-click any value within the target field.
In this example, the target field is the SalesPeriod field.
2. To display the Field Settings dialog box, select Field Settings.
3. Click the Layout & Print tab in the Field Settings dialog box.
4. Select Show Items with No Data (see Figure 14-31).
5. Click OK.
Figure 14-31: Select the Show Items with No Data option to display all data items.
As you can see in Figure 14-32, after you select the Show Items with No Data option, all the sales
periods appear whether the selected region had sales that period or not.