Display Missing Dates in Excel PivotTables

• Share on Facebook.
• Share on Twitter.
• Share on Google+
• Share on LinkedIn
• Pin It!

Q: How do you get the PivotTable to show the missing dates in your data?

A: Not as easily as it should be, but here are a couple of workarounds you can use.

Option 1: If you don’t care how Excel formats your dates

The limitation of this option, as you will see, is that when Excel groups days in a PivotTable it shows the date formatted as “d-mmm” and you cannot change it 🙁

Note: I presume in the US it formats them as mmm-d but I cannot easily check.

Below is my data (loosely based on Phil’s triathlon training regime!), and you can see that there are missing dates for the month of March because he doesn't train everyday:

A regular PivotTable will only display the dates present in the source data. If you want to display the missing dates for March you need to take the following convoluted steps:

1. Right-click one of the date row labels in the PivotTable > select Group > Days and Months:

   

2. Next right-click one of the date row labels in the PivotTable > select Field Settings > Layout & Print tab > check the ‘Show items with no data’ box.

   

   Tip: The ‘Show items with no data’ can be applied to any row label, not just dates. For example, let’s say you have data for regions A, B, C and D but B and C are not appearing in the PivotTable Report because they have no data for the filters you have applied, if you select the ‘Show items with no data’ option they will be included in the PivotTable Report with blanks/zeroes.

3. Now your PivotTable will display every date in the year – annoying I know. To fix this drag the Months field into the Filters area of your PivotTable:

   

4. From the Months filter select the months you want to display in your PivotTable – for me this is March.

   

Now I have my PivotTable with the missing dates represented as I want…... well, almost – see Gripes below:

Gripes with this Method

•  I can’t stipulate the range for my missing dates so I end up with a load of dates I might not want. To fix it I have to apply a workaround which are steps 3 and 4 above. I suppose it’s not that bad, but I think it could and should be better.
• My second gripe, and this is much more serious, seriously, I cannot choose how I want to format the dates in my PivotTable. I am stuck with d-mmm (if you’re in the U.S. it might be mmm-d for you), which is very annoying.

Now, depending on what you want to use the data for, it might not be that big a deal, but I want to put it into a PivotChart (which I’m not a fan of anyway as they’re too limited) and the dates are too big to fit horizontally, plus I’d like to know what day of the week I’m looking at, but I can’t so I’m stuck with a horizontal axis like this:

When what I want is a nice uncluttered horizontal axis more like this (a technique I teach in my Excel Dashboard course):

I know I can add columns to my source data for the day and month fields, but the point is I shouldn’t have to, damn it!

Option 2: If you want to control how Excel formats your dates

Now, since I’m fussy about how my dates are formatted I prefer this method but it’s not ideal either.

1. The trick here is to add dates to your PivotTable source data for every day in the period you want, so for me I want to see every day in March 2014 represented so I’ve added dates to the bottom of my date column for every day in March using AutoFill (see rows 28:58 below):

   

   Note: You'll notice that I've added dates I already have. This is because the quickest way to add all the missing dates is to use AutoFill to create a list of dates from March 1 to 31.

   It doesn’t matter that I’m repeating some dates; since they don’t have any data against these records they aren’t going to affect the values in my PivotTable Report, but they will allow me to use the ‘Show items with no data’ option without the need to Group my dates like I had to in option 1. This also means I have more options when it comes to formatting my dates.

2. Once you’ve built your PivotTable you can format the date row labels: in the Field list click on the down arrow beside ‘Date’ and select ‘Field Settings’, then click on the ‘Number Format’ button in the bottom left of the Field Settings dialog box:

   

3. Choose from the default ‘Date’ formats or set your own ‘Custom’ format:

   

Now you have a simpler PivotTable (no filters) with dates formatted the way you want, like this:

And while you still can’t get your horizontal axis like my preferred chart above (one case in point for why I don't like PivotCharts), you can change the axis label frequency to every 7 days and add tick marks for the days so it’s a bit less cluttered, although I’m not sure it’s any easier to read than the default vertical labels 🙁

[Update] Option 3 - Solve Axis Formatting Issues with a Helper Column

This technique is from Roger Govier, Microsoft Excel MVP and Consultant at Technology4U.co.uk.

Roger gets around the axis label problem with a helper column (B) in the source data. You can see in the Formula Bar below he has used the TEXT function (primarily) to create a text string comprising of the day number and first letter of the day, which is cleverly wrapped onto two lines using CHAR(10).

Note: Apply 'Wrap Text' format to column B of your Table if you want to see your date text string formatted as per the image above, i.e. with the date number above the letter for the day. However, this is not necessary for the PivotChart since it wraps the text because we have used the CHAR(10) character in the text string. More on that formula below.

This allows us to create a chart like the one below with the horizontal axis labels formatted the way I want, AND in a PivotChart to boot:

Let's understand the components of the formula in column B:

The ampersand (&) allows us to join text strings together in the one formula. More on joining text here.

More on the TEXT function here.

More on Excel Tables and Structured References here.

I'd like to say a big thank you to Roger Govier for sharing this tip.

Please Share

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.