When a filter is applied to a Pivot Table, you may see rows or columns disappear. This is because pivot tables, by default, display only items that contain data. In the example shown, a filter has been applied to exclude the East region. Normally the Blue column would disappear, because there are no entries for Blue in the North or West regions. However, Blue remains visible because field settings for color have been set to “show items with no data”, as explained below.

Fields

The pivot table shown is based on three fields: Region, Color, and Sales:

Field list used for pivot table as shown - 1

Region has been configured as a Row field, Color as a Column field, and Sales is a Value field.

Data has been filtered by Region to exclude East:

Filter applied to exclude East region - 2

To force the display of items with no data, “Show items with no data” has been enabled on the Layout & Print tab of the Color field settings, as seen below:

Color field settings - 3

To force the pivot table to display zero when items have no data, a zero is entered in general pivot table options:

General pivot table settings - zero for empty cells - 4

Finally, the Accounting number format has been applied to the Sales field to display empty cells with a dash (-).

Note: the same problem can occur with dates are grouped as months, and no data appears in a given month. You can use the same approach, with a few extra steps, described here .

Steps

  1. Create a pivot table
  2. Add Region field to Rows area
  3. Add Color field to Columns area Enable “show items with no data”
  4. Add Sales field to Values area Apply Accounting number format
  5. Set pivot table options to use zero for empty cells

To create a pivot table with a filter for day of week (i.e. filter on Mondays, Tuesdays, Wednesdays, etc.) you can add a helper column to the source data with a formula to add the weekday name, then use the helper column to filter the data in the pivot table. In the example shown, the pivot table is configured to show data for Mondays only.

Pivot Table Fields

In the pivot table shown, there are four fields in use: Date, Location, Sales, and Weekday. Date is a Row field, Location is a Column field, Sales is a Value field, and Weekday (the helper column) is a Filter field, as seen below. The filter is set to include Mondays only.

Pivot table filter by weekday field configuration - 5

Helper Formula

The formula used in E5, copied down, is:

=TEXT(B5,"ddd")

This formula uses the TEXT function and a custom number format to display an abbreviated day of week.

Steps

  1. Add helper column with formula to data as shown
  2. Create a pivot table
  3. Add fields to Row, Column, and Value areas
  4. Add helper column as a Filter
  5. Set filter to include weekday(s) as needed

Notes

  1. You can use the helper column to group by weekday as well