If you have ever created a PivotTable report that groups by month, you may have encountered an awkward situation where the PivotTable only displays the months that actually have data in the source. The PivotTable will summarize the data that exists and if there are no transactions for a given month, the PivotTable won’t display it. A common workaround is to add empty data rows for each desired month into the data source to force the PivotTable to display all desired periods. However, this workaround isn’t necessary, and in this post, we’ll discuss a simple setting that will cause Excel to display all months, including those that have no underlying data transactions.
Our objective is rather simple, we want to create a PivotTable that displays the total amount by month for all months, including any months without data. This is illustrated in the screenshot below where April has no transactions in the data source.
Let’s build this thing.
The underlying data table that we want to summarize is shown below.
Note: there are no data rows for April.
Here are the steps to build the desired PivotTable report:
- Create basic PivotTable
- Group by month
- Check the Show items with no data checkbox
- Filter the report
- For empty cells show 0
Let’s dig into the details of each step.
Create the basic PivotTable
To build the basic PivotTable, we select any cell in the data table, and then use the Insert > PivotTable ribbon icon. In the resulting Create PivotTable dialog box we tell Excel to place the report on the desired worksheet and click OK.
Then, we insert the Date field into the Rows area and the Amount field into the Values area. If desired, we can apply some cosmetic touches such as formatting the value field to include commas and updating the report headers. The resulting report is shown below.
Group by month
Now we need to tell Excel to group the report labels by month. We select any date cell in the report and then click the PivotTable Tools > Group Selection ribbon icon or we right-click a date cell and select Group. In the resulting Grouping dialog box, we accept the default selection of Months and click OK.
We are getting closer! The resulting report is shown below.
The big problem at this point is that there is no report label for April. This is because there are no April transactions in the PivotTable’s data source. This is where we use the Show items with no data checkbox.
Show items with no data checkbox
We open up the Field Settings dialog for the Date field either by selecting a month cell and then using the PivotTable > Field Settings ribbon icon or by right-clicking a month cell and then selecting Field Settings.
On the Layout & Print tab, we check the Show items with no data checkbox, as shown below.
Yes…the report now includes an April label as shown below.
The hard work is now complete and we just need to clean it up a bit.
Filter the report
Let’s filter out the report endpoints (<1/1/2017 and >12/16/2017) by using the filter drop-down and unchecking these items. Looking better. The updated version of the report is shown below.
For empty cells show 0
The final step is to show 0 for April. This is done in the PivotTable Options dialog box. We open it by clicking any cell in the report and then using the PivotTable Tools > Options ribbon icon or by right-clicking any cell in the report and selecting PivotTable Options.
In the PivotTable Options dialog, we type 0 into the For empty cells show field, as shown below.
We did it…yay! The resulting PivotTable is shown below.
This report shows 0 for April, even though there are no April transactions in the data source. We accomplished our objective!
If you have any other ideas about using the Show items with no data checkbox, please share by posting a comment below…thanks!
- Sample Excel file
- Month Groups article
- Also works with intervals such as discussed in this histograms post
- Other Excel University posts about PivotTables