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. 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.
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. The PivotTable will summarize the data that exists and if there are no transactions for a given month, the PivotTable won’t display it. 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.
