When Grouping by Dates in Pivot Tables won't work

I started telling you how much I loved Pivot tables, and then abandoned my blog. I apologize.

After I discovered that you can group by dates in Pivot tables, I also realized that sometimes it didn't work. It took me awhile, but I finally figured it out. Basically, if your data set includes blanks, the Pivot table is unable to Group By Dates. Why? Because Blank is not a valid date. I'm hoping Microsoft fixes this in the next iteration. They may already have, but I don't have that version of Excel, yet.

Let me walk you through why you would want to include blank rows in your data set, how to get rid of the blanks in your pivot table and how to group by dates in your pivot table without using the group by function.

There are times when I create a Pivot Table when I know that my source data will not always be $A$1:$C$67


Knowing that I will continue to add to my source data, I choose A:C as my Pivot table. This would happen if you, as a flea market owner, intend to continue to add rows to your original source data as you continue to sell items. You could use the pivot table wizard to redefine your data set every day, or you could expand your data set and just refresh your pivot table as needed.


Now, you have a row for (blank) in your pivot table. Bleh.


And, you don't really want that because it serves no purpose in this application, so you get rid of that blank line by clicking the pull down on the item column, scrolling through until you find blank, and then un-checking it.


But, now your nicely summarized months are gone, replaced by specific dates because a blank is not a valid date format. What was once nice to look it:


Now makes you cringe and frown.


When you try to resummarize it by month following instructions provided in my post Pivot tables and why I love them, instead of getting the nice box where you can choose Months, years, hours, quarters, etc, you just get one group called Group 1.

But, you've reached the conclusion that you will continue to add to your original data set and it's not feasible to redefine the data set every time you want to see fresh data.

Here's what you do!
Go back to your original data set and add three calculated fields. One each for Month, Year, and Quarter


The calculation for month is above. The calculation for year is pretty straightforward:
=YEAR(C2)

The calculation for Quarter is more involved:
=IF(OR(D2=1,D2=2,D2=3),1,IF(OR(D2=4,D2=5,D2=6),2,IF(OR(D2=7,D2=8,D2=9),3,4)))

In English, if the month is 1, 2 or 3, the quarter is 1. If the month is 3, 4 or 5, the quarter is 2. If the month is 7, 8 or 9, the quarter is 3. Otherwise, the quarter is 4.

I like that calculation. They're may be a different way to get at quarters, but this works. You could also change the 1 to "Q1", etc, if you want to get fancy.

Now, go back to your pivot table, right click and choose "Pivot Table Wizard."


It will start you at Step 3 of 3, and you are redefining your data set here and you need to back to step 3. So, choose Back. You can either drag your cursor from A to F, or just type it in manually:
Data!$A:$F

Now, choose next and then finish. Now, in your field list, you have the newly created calculated columns:


Drag and drop years, months, and quarter to summarize the data the way that you like. Change up as needed.


Do those odd column totals bug you? Me, too.

Hover your mouse over those year total columns until you see a black arrow, then right click. This will highlight all year total columns. Now, right click and choose hide.


If you want to get it back, right click on the word, "YEAR" and choose field settings.


Under subtotals, click automatic. You can also choose sum (same at automatic), count, average, etc. Or, a combination of any or all of those in the option box. Use this wisely, people. You don't want your pivot table to get too complex or you'll lose your users which may just be yourself and it's no good to lose yourself over a pivot table.

So, now it's looking pretty OK.


But, you probably want to format it. So, hover your mouse on one of your category totals. Once you get that hover arrow, left click to highlight all category totals as shown in the image above. Now, right click and format as you see fit.

That was a little more than When Grouping By Dates in Pivot Tables won't work. But, what can I tell you. I get wordy...