Hide or Show Pivot Table Items without Data On Your Reports

By Michael Alexander

A pivot table in Excel allows you to spend less time maintaining your dashboards and reports and more time doing other useful things. By default, your pivot table shows only data items that have data. This inherent behavior may cause unintended problems for your data analysis.

Look at this figure, which shows a pivot table with the SalesPeriod field in the row area and the Region field in the filter area. Note that the Region field is set to (All), and every sales period appears in the report.

image0.jpg

If you choose Europe in the filter area, only a portion of all the sales periods will show. See the figure. The pivot table will show only those sales periods that apply to the Europe region.

image1.jpg

Displaying only those items with data could cause trouble if you plan on using this pivot table as the feeder for your charts or other dashboard components. From a dashboarding-and-reporting perspective, it isn’t ideal if half the year’s data disappeared each time customers selected 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. Select Field Settings.

    The Field Settings dialog box appears.

  3. Select the Layout & Print tab in the Field Settings dialog box.

  4. Select the Show Items with No Data option, as shown in this figure.

    image2.jpg

  5. Click OK to apply the change.

As you can see in this figure, after choosing the Show Items with No Data option, all the sales periods appear whether the selected region had sales that period or not.

image3.jpg

Now that you’re confident that the structure of the pivot table is locked, you can use it to feed charts and other components on your dashboard.