MrExcel.com - Your One Stop for Excel Tips & Solutions

Show Months with Zero Sales

February 01, 2023 - by

Show Months with Zero Sales

Problem: I built a pivot table to show sales by month for one customer. For the large customers, I get all 12 months.

A pivot table with Customer in the Filter, Months in the Rows, and Revenue in the Values. When you filter to Customer = Wal-Mart, all 12 months appear in the pivot table.
Figure 990. All 12 months appear.

But for the smaller customers, I don’t see all of the months. I was thinking of adding 12 dummy records for every customer, one per month, but with 300 customers, that would be 3600 fake records just to solve this stupid problem.

But when you filter to Customer = Chevron, only the months with sales appear: Feb, Mar, Jul, Oct. You want the pivot table to always show all 12 months, with sales as zero.
Figure 991. Months without sales are missing.

Strategy: Select a cell in the data column. Click the Field Settings icon in the Analyze tab. This time, go to the second tab in the dialog, called Layout and Print. On that tab, click Show Items With No Data.

Still showing Chevron, the pivot table now has 14 "months". Jan through Dec appear. But a new first row has <1/2/2014. A new last row has >12/28/2014.
Figure 992. Strange entries in row 4 & 17.

Gotcha: You are not done yet. The months now appear, but they are empty cells instead of zero. Also, a strange entry appears at the top and the bottom of the data set. There are no records in the data set before 1/1/2014, so this is a pure annoyance from Microsoft.

To fix the empty cells, right-click the pivot table and choose Options. Fill in the For Empty Cells Show box with a zero.

In Pivot Table Options, choose For Empty Cells, Show: 0.
Figure 993. Replace empty cells with zero.

Go to the filter dropdown for Dates. Uncheck the <1/1/2014 and the >12/29/2014 entries.

Open the Filter drop-down for Month and unselect the first and last entries: <1/2/2014 and >12/28/2014.
Figure 994. Turn off the values.

The result: a pivot table that will show all 12 months for every customer.

A better-looking pivot table for Chevron. All 12 months appear. Zeroes appear for Jan, Apr, May, Jun, Aug, Sep, Nov, and Dec.
Figure 995. Easier than adding 3,600 fake zero records to the data.

This article is an excerpt from Power Excel With MrExcel

Title photo by Glen Carrie on Unsplash