Lasma wants her pivot table to group daily dates up to months and years. But she has some empty rows and the Group Field is greyed out.
In this video:
Surprise! Microsoft 365 now allows blanks in the date field and you can still group.
Solution 1: Fill blank date fields with 2199 December 31
Solution 2: Add your own Year and Month fields to the data set
And, a warning from Debra Dalgleish at Contextures: if you mix dates and text, the Group Field will still be greyed out.
Table of Contents
(0:00) Pivot Group Dates with Blanks
(0:15) Fix from Excel Team
(1:10) Select empty date cells
(2:09) Add Year Month to source data
(2:40) Month Name from date using TEXT
(3:30) Debra: mix of dates and text
(4:00) "empty" cells by spacing through
(4:48) Wrap-up
In this video:
Surprise! Microsoft 365 now allows blanks in the date field and you can still group.
Solution 1: Fill blank date fields with 2199 December 31
Solution 2: Add your own Year and Month fields to the data set
And, a warning from Debra Dalgleish at Contextures: if you mix dates and text, the Group Field will still be greyed out.
Table of Contents
(0:00) Pivot Group Dates with Blanks
(0:15) Fix from Excel Team
(1:10) Select empty date cells
(2:09) Add Year Month to source data
(2:40) Month Name from date using TEXT
(3:30) Debra: mix of dates and text
(4:00) "empty" cells by spacing through
(4:48) Wrap-up
Transcript of the video:
Well, hey, this is a wild discovery. So, this question from Lasma.
I want to group dates in a pivot but there's blank cells in the date column.
Why can't I group dates? Excel doesn't recognize the data as a date.
All right, common problem.
I've been around this a lot, but I'm shocked and amazed that someone at Microsoft has fixed this. So, see this Date column?
I'm in Microsoft 365 now in, of course, the beta channel.
Insert pivot table. Okay.
Take Dates, and drag them to the rows area. Take Revenue, and drag it to the values area.
And normally, in the past, I wouldn't be able to group this because of that blank cell, but it's amazing, the group field is showing that it's a date, and I can group it up to months and years.
And they do this thing where all of the blank cells are showing up as less than August 6, 2022, with a date of blank, which is crazy. When did this get fixed?
I have no idea when this got fixed, all right?
But obviously, for Lasma, she's having the same problem than in the old days. So, she's not on Microsoft 365.
So, my two suggestions.
Suggestion number one is, first off, take everything in the date field and go to all of the blank cells, all right?
So, I'm going to choose all of the cells like this.
I'm holding down the shift key while I press Page Down.
And then F5 to open Go-To.
Click special, choose Blanks, which should get me all the blanks.
And then, one of two dates, either January 1st of 1900 or 12/31 of, let's say, 2199, just to be insanely far in the future.
Control+Enter that will fill all of that data in. And then, we can do Insert, Pivot Table, OK.
Dates down the left hand side, revenue.
And now you should successfully be able to group these dates by months and years. And you just have this 2199 thing.
Why don't those have dates? I don't know why they don't have any dates.
But if you don't want them in here, just open this and take out any dates in 2199.
Click OK, all right? And there you have the solution.
Or think about what the date grouping is doing.
It's taking this date ,and it's converting it in memory to a year and a month.
So, let's just do that right here in the original dataset. So, the =YEAR( function there.
Copy that down to all of our date cells.
And then for everything else, I'll just put N/A or whatever you want do. I don't care = whatever makes sense to you.
And then, for the month, ah, check this out.
Instead of the =MONTH, which will give me a month number, like here, this would give me a month number of eight.
Instead of that, I'm going to use =TEXT( of the date, MMMM inside of quotes.
Four M's will spell out the month, August. Three M'S will just give you A-U-G.
See?
So, that's going to actually give me the month name there.
That'll be a little bit better than how Excel creates these.
And then, for everything here, we'll put N/A all the way down. Good.
Now, Insert, Pivot Table, okay. We're not going to use the Date field this time.
Instead, we'll use the Years and the Month field, and then the Revenue field like that.
And to get rid of the things without days, just open this year of, and unselect N/A.
And there you go. All right.
Shout out to Debra Dalgleish from Contextures.
I went to Debra, and I said, "Debra, am I misremembering this? This used to be broken".
And she confirms that it is. But then, Debra pointed out to be careful here.
Now Lasma's saying that she has blank rows, which implies completely blank rows.
But Debra pointed out, if it's a mix of dates and text, like this, then it's not going to allow grouping here on the pivot table. Analyze tab - Grouping is going to be grayed out.
So, that's not going to work. Or the other one.
Is if when they say, "I have a blank cell". I'm assuming that there was never data there.
Or they simply pressed Delete, all right?
But unfortunately, I've been burned enough to know.
That some people say a “blank cell” means that they pressed a space bar and enter. Or even worse, space, space, space, space, Enter.
Why would you do that?
So, something like that with spaces instead of blank cells.
Insert, pivot table, okay, date, and revenue is going to create something that we cannot group, all right?
So, if the Group Field is still great out, and you're on Microsoft 365, it's possible that what's really there is a space or space, space, space, space.
Or just simply text. Thanks to Debra for pointing that out.
So, fascinating that Excel is now dealing with this and allowing me to group dates when there's a few blank cells in the middle. Crazy that that's working now.
I have no idea when that got added.
A second solution is fill all of the blank empty date cells with a ridiculous date, December 31st of 2199.
Or use the YEAR function like that and the TEXT function to convert a year in order to create a year and month field, and then base the pivot table on that.
Why, I want to thank Lasma for that great question, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I want to group dates in a pivot but there's blank cells in the date column.
Why can't I group dates? Excel doesn't recognize the data as a date.
All right, common problem.
I've been around this a lot, but I'm shocked and amazed that someone at Microsoft has fixed this. So, see this Date column?
I'm in Microsoft 365 now in, of course, the beta channel.
Insert pivot table. Okay.
Take Dates, and drag them to the rows area. Take Revenue, and drag it to the values area.
And normally, in the past, I wouldn't be able to group this because of that blank cell, but it's amazing, the group field is showing that it's a date, and I can group it up to months and years.
And they do this thing where all of the blank cells are showing up as less than August 6, 2022, with a date of blank, which is crazy. When did this get fixed?
I have no idea when this got fixed, all right?
But obviously, for Lasma, she's having the same problem than in the old days. So, she's not on Microsoft 365.
So, my two suggestions.
Suggestion number one is, first off, take everything in the date field and go to all of the blank cells, all right?
So, I'm going to choose all of the cells like this.
I'm holding down the shift key while I press Page Down.
And then F5 to open Go-To.
Click special, choose Blanks, which should get me all the blanks.
And then, one of two dates, either January 1st of 1900 or 12/31 of, let's say, 2199, just to be insanely far in the future.
Control+Enter that will fill all of that data in. And then, we can do Insert, Pivot Table, OK.
Dates down the left hand side, revenue.
And now you should successfully be able to group these dates by months and years. And you just have this 2199 thing.
Why don't those have dates? I don't know why they don't have any dates.
But if you don't want them in here, just open this and take out any dates in 2199.
Click OK, all right? And there you have the solution.
Or think about what the date grouping is doing.
It's taking this date ,and it's converting it in memory to a year and a month.
So, let's just do that right here in the original dataset. So, the =YEAR( function there.
Copy that down to all of our date cells.
And then for everything else, I'll just put N/A or whatever you want do. I don't care = whatever makes sense to you.
And then, for the month, ah, check this out.
Instead of the =MONTH, which will give me a month number, like here, this would give me a month number of eight.
Instead of that, I'm going to use =TEXT( of the date, MMMM inside of quotes.
Four M's will spell out the month, August. Three M'S will just give you A-U-G.
See?
So, that's going to actually give me the month name there.
That'll be a little bit better than how Excel creates these.
And then, for everything here, we'll put N/A all the way down. Good.
Now, Insert, Pivot Table, okay. We're not going to use the Date field this time.
Instead, we'll use the Years and the Month field, and then the Revenue field like that.
And to get rid of the things without days, just open this year of, and unselect N/A.
And there you go. All right.
Shout out to Debra Dalgleish from Contextures.
I went to Debra, and I said, "Debra, am I misremembering this? This used to be broken".
And she confirms that it is. But then, Debra pointed out to be careful here.
Now Lasma's saying that she has blank rows, which implies completely blank rows.
But Debra pointed out, if it's a mix of dates and text, like this, then it's not going to allow grouping here on the pivot table. Analyze tab - Grouping is going to be grayed out.
So, that's not going to work. Or the other one.
Is if when they say, "I have a blank cell". I'm assuming that there was never data there.
Or they simply pressed Delete, all right?
But unfortunately, I've been burned enough to know.
That some people say a “blank cell” means that they pressed a space bar and enter. Or even worse, space, space, space, space, Enter.
Why would you do that?
So, something like that with spaces instead of blank cells.
Insert, pivot table, okay, date, and revenue is going to create something that we cannot group, all right?
So, if the Group Field is still great out, and you're on Microsoft 365, it's possible that what's really there is a space or space, space, space, space.
Or just simply text. Thanks to Debra for pointing that out.
So, fascinating that Excel is now dealing with this and allowing me to group dates when there's a few blank cells in the middle. Crazy that that's working now.
I have no idea when that got added.
A second solution is fill all of the blank empty date cells with a ridiculous date, December 31st of 2199.
Or use the YEAR function like that and the TEXT function to convert a year in order to create a year and month field, and then base the pivot table on that.
Why, I want to thank Lasma for that great question, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
