Show PivotTable Months without Data


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.

PivotTable by Jeff Lenning

Let’s build this thing.


The underlying data table that we want to summarize is shown below.

Data Source by Jeff Lenning

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.

Basic PivotTable by Jeff Lenning

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.

Grouping dialog by Jeff Lenning

We are getting closer! The resulting report is shown below.

Missing April by Jeff Lenning

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.

Show Items checkbox by Jeff Lenning

Yes…the report now includes an April label as shown below.

Includes April by Jeff Lenning

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.

Filter by Jeff Lenning

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.

For empty cells show zero by Jeff Lenning

We did it…yay! The resulting PivotTable is shown below.

PivotTable by Jeff Lenning

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!

Additional Resources


This article was written by jefflenning


  1. Olateejay Iles

    Thank you!!!!!!

    1. jefflenning Post author

      Welcome 🙂

  2. Isabel

    Thanks! Very helpful article!
    I am using Excel 2011 for Mac and cannot find the “Show items with no data” option in the individual fields but only at the PivotTable Options dialog and still, it appears as grayed (disabled) and not sure how to use it. There are two checkboxes for Display items with no data “on rows” and “on columns” but both are disabled.
    Could you help?
    Thank you!

    1. jefflenning Post author

      Hi Isabel! I’m so sorry, but I don’t happen to have a copy of Excel 2011 for Mac so I’m unable to be of much help. Excel’s features and options are often based on the version, so, it could be that this option isn’t available in that version of Excel.
      Best if luck,

  3. Jim Tortorelli

    Thanks for the article. Very clear.

    Do you know of any tricks to add a year to the month display above? My reports always nee the LAST 12 months, so I’d like the left column to be “Jul 15” instead of “Jul”

    1. Kurt LeBlanc

      Hey Jim

      I was unable to find EXACTLY what you need because Excel only formats a group in one way. I couldn’t change the format, and if I changed the format of the date in the data, the Pivot Table would not display each month only once. It also wouldn’t show only subtotals as to have each month only once.

      The closest I could find was to group the date field by year as well as month, show the report in tabular form, and repeat all labels. I also turned off the +/- buttons and got:

      2017 Jan ###
      2017 Feb ###
      2017 Mar ###
      and so on…

      Let me know if that isn’t what you are looking for and if I can be of further help:)
      Kurt LeBlanc

  4. James Tortorelli

    Thanks for the response. Fighting storms at home right now so I apologize for being slow to acknowledge. The easiest work around I have found to date is to make an extra column in the source data with the format I want as a text field: =text(date,”mmm ‘yy”).


    1. Kurt LeBlanc

      Hey Jim,

      I understand:)

      OK great! I’m glad you found a way that works for you.

      Kurt LeBlanc

  5. Bert Rich Onstott

    Does “show items with no data” work for dates as well as months? It doesn’t seem to for me. I have this data:
    Date Session # Tables Session
    Thursday, January 22, 2015 10 22.5 Thu Morn
    Thursday, January 22, 2015 12 4.0 Thu Eve
    Friday, January 23, 2015 13 34.0 Fri Morn
    Saturday, January 24, 2015 17 24.5 Sat Aft
    Sunday, January 25, 2015 21 13.0 Sun Eve
    Monday, February 2, 2015 1 9.0 Mon morn\

    Note that dates January 26-31 are missing.

    When a create a pivot table to show tables by day, it does not include the missing days, even after I check that box. It doesn’t seem to update the table after I check that box.

    Any help?

    1. jefflenning Post author

      Yes…The “show items with no data” option works on grouped fields. Fortunately, you can group by Days. In the Grouping dialog, select Days and then define the start and end dates. Once the date field has been grouped, then the “show items with no data” option will take effect, and display an item for each date in the range.
      Hope it helps!

      1. Bert onstott

        Thanks. It works!

        1. jefflenning Post author

          Welcome 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.