Exceljet

Quick, clean, and to the point

Pivot table months with no data

By default, a pivot table shows only data items that have data. When a pivot table is set up to show months, this means that months can "disappear" if the source data does not contain data in that month.  In the example shown, a pivot table is used to count the rows by color. There is no data in the months of March and May, so normally these columns would not appear. However, the pivot table shown in the example has been configured to force the display all months between January and June.

Fields

The pivot table shown is based on two fields: Date and Color:

Field list includes two fields only

The Color field is configured as a row field, and a value field. In the Values area, the Color field has been renamed "Count" and set to summarize by count:

Color field renamed Count in values area

The Date field is grouped by Months only:

Date field grouped by Months

To force display of months with no data, the Date field has "Show items with no data" enabled:

Check "Show items with no data" for Date field

Date filter is set to display only desired months:

Date filter to show only desired months

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

Pivot table options - enter zero for items with no data

Steps

  1. Create a pivot table
  2. Add Color field the Rows area (optional)
  3. Add Date field to Columns area
    1. Group Date by Months
    2. Set Date to show items with no data in field settings
    3. Filter to show only desired months
  4. Add Color field to Values area
    1. Rename to "Count" (optional)
    2. Change value field settings to show count if needed
  5. Set pivot table options to use zero for empty cells

Notes

  1. Any non-blank field in the data can be used in the Values area to get a count.
  2. When a text field is added as a Value field, Excel will display a count automatically.

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.