Microsoft Excel Tutorial: Sorting months in a pivot table when they are alphabetic.
I met people who are downloading data from Oracle through Analysis Services. Their dates are coming in as text in the format of Sep-20 for September 2020. When they create pivot tables, the months are alphabetic instead of sequential.
To download the workbook from today: Excel - Oracle Sending Dates as Text MMM-YY Episode 2624 Sample Files - MrExcel Publishing
There are two ways to solve it:
1) A convolunted method from Sam Radakovitz that you only have to do once
2) A simple method that you will have to do 1000 times a year.
In this episode, I show Sam Rad's method of setting up a custom list with the text months in the correct sequence. If you use this method with a pivot cache pivot table (any pivot table where the data is a regular Excel range), the months will start sorting correctly.
But if your data is a cube or coming from external sources, then you have an extra eight steps in each pivot table to correct the month sequence. However, this is still faster than manually rearranging fields in the pivot table.
In the outtake, I show my fastest method for changing text months in Excel to real dates and ask if you have anything faster.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
#excel
#microsoft
#exceltutorial
#exceltips
#microsoftexcel
#exceltricks
#pivottable
Table of Contents
(0:00) Pre-roll ask for help
(0:36) Problem Statement: Text Dates show up as MMM-YY
(1:01) Pivot table is sorting months alphabetically
(1:17) Setting up a text custom list of months
(1:39) Converting dates to text using TEXT()
(2:06) Importing the custom list
(2:49) Regular pivot table automatically works
(3:12) If pivot table based on external data, does not work
(3:37) Sorting pivot table on custom list if based on data model
(4:03) Wrap up
(4:16) Outtake: Convert with Text to Columns
(5:08) Outtake: Convert with Ctrl+H & then Text to Columns
(5:48) Another Ctrl+H solution that works faster
(6:11) Power Query Column from Examples
I met people who are downloading data from Oracle through Analysis Services. Their dates are coming in as text in the format of Sep-20 for September 2020. When they create pivot tables, the months are alphabetic instead of sequential.
To download the workbook from today: Excel - Oracle Sending Dates as Text MMM-YY Episode 2624 Sample Files - MrExcel Publishing
There are two ways to solve it:
1) A convolunted method from Sam Radakovitz that you only have to do once
2) A simple method that you will have to do 1000 times a year.
In this episode, I show Sam Rad's method of setting up a custom list with the text months in the correct sequence. If you use this method with a pivot cache pivot table (any pivot table where the data is a regular Excel range), the months will start sorting correctly.
But if your data is a cube or coming from external sources, then you have an extra eight steps in each pivot table to correct the month sequence. However, this is still faster than manually rearranging fields in the pivot table.
In the outtake, I show my fastest method for changing text months in Excel to real dates and ask if you have anything faster.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
#excel
#microsoft
#exceltutorial
#exceltips
#microsoftexcel
#exceltricks
#pivottable
Table of Contents
(0:00) Pre-roll ask for help
(0:36) Problem Statement: Text Dates show up as MMM-YY
(1:01) Pivot table is sorting months alphabetically
(1:17) Setting up a text custom list of months
(1:39) Converting dates to text using TEXT()
(2:06) Importing the custom list
(2:49) Regular pivot table automatically works
(3:12) If pivot table based on external data, does not work
(3:37) Sorting pivot table on custom list if based on data model
(4:03) Wrap up
(4:16) Outtake: Convert with Text to Columns
(5:08) Outtake: Convert with Ctrl+H & then Text to Columns
(5:48) Another Ctrl+H solution that works faster
(6:11) Power Query Column from Examples
Transcript of the video:
Hey, before we get started today, this is after the video was done.
I knew as I started today that there's two ways to solve the problem.
Convoluted way, which I wanted to show in the video.
That's the Sam Radakovitz this way, you just do it once and never again. But I knew that there was also a super easy way that you wouldn't just have to do thousands of times a year. I did show the super easy way.
And then when I'm editing the video, I'm like, wait a second.
It didn't work. It changed the dates.
So after the video, there's an outtake.
And for my friends who are the Power Query experts and Formula experts.
I want to know how you would do the super easy way because it is not working for me.
All right?
So they're getting data sent down from Oracle and the dates are showing up as text in this format, SEP-23.
This is a refreshable query, so it's coming in this way all the time.
They're not using Power Query, it's just coming in from SQL Server Analysis Services.
30 people in the department doing this dozens of times a week.
So the dates come in like this, and when you create a pivot table.
It's just chaos because it's alphabetical across the top.
Rather than convert these to dates every single time.
It seems like a good way would be to use a trick the Sam Radakovitz show me many years ago.
So I asked them how far back they want to go. Three years.
All right? And I'm going to use a custom list for this.
So I put in a date 1/1/2020.
I'm going to right click and drag and go down to 252. In this case just because that's a multiple of 12.
And then say fill months. So that gives me monthly dates going down.
And then in column B I'm going to use this formula.
The TEXT of A1, "MMM-YY".
And that's going to create something that looks like their text coming down.
Now the problem is with custom lists, you cannot use a formula and a custom list.
So we have to copy that and then here we'll paste as values. You can do that however you'd like.
I'm going to do that one right there. All right, so now I've selected these 252 cells.
We're going to go into File, Options, Advanced.
And scroll all the way to the bottom and choose Edit Custom Lists. So File, Options, Advanced.
Scroll all the way to the bottom. Edit Custom Lists.
And then we'll import that list of 252 items. The list can only be 254 or less.
So this goes out all the way to 2040, which is the rest of my work life for sure.
If you're very young, you might want to put a note in your calendar for January of 2041 of how the heck you did this. All right, so now we just have to do that once.
Once you've done that and you close Excel without Excel crashing, then we should be good to go. Let's get rid of this pivot table.
If your data is in Excel like this, Insert, Pivot Table.
Okay, click OK. Okay, we'll put dates across the top.
Oh, there we are. It's already working.
Sales down the side.
All right, that's great if your data's coming from inside of Excel.
But they mentioned that this is a refreshable query coming in from analysis services.
So that tells me that their data's not coming from inside of Excel.
That's going to require a few extra steps. So essentially.
Insert, Pivot Table, I'm going to choose this box, add this data to the data model.
Dates across the top, products down the side, sales.
And we still have the wrong sequence here.
So in my case, I'm going to go to the date dropdown.
A lot of people will still have this in Compact Form. So you'll go to the column labels dropdown.
We're going to go into More Sort Options. Choose Ascending based on date.
Go to More Options. Uncheck Automatically Sort.
And then the First Key Sort Order, we're going to choose that custom list that we just created.
Click. Okay.
I hate those eight clicks, but it does solve the problem.
All right, well hey, I want to thank you for stopping by.
We'll see you next time for another Netcast from MrExcel.
If you like these videos, please down below Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Hey, as an outtake, here's a challenge.
Let's say that someone has these text dates that look like this, and it's not a refreshable query. They just need to fix it, right?
I was convinced that I could very quickly do this with Alt+D E F.
Which is just essentially a way of doing Data, Text to Columns, Finish.
And sure enough, we get dates. But wait!
They are not the right dates. This is May of 2020.
And because I'm recording this in 2023. It's giving me May 20th, 2023.
So then I thought what we really have to do is Text to Columns.
Go to the third step.
And specify that it's a date in month, day, year, I guess. But that doesn't solve it either.
And in fact, I tried all six options there in step three of the Wizard.
And none of those will get me the correct date in May of 2020.
So then I thought, let's be clever. Change every occurrence of the dash.
There's only one dash. And change it into -01-20.
So we go from May-20 to May-01-2020, right?
At least then it'll look like the right date and we can do something with it. Replace All.
And then try Data, Text to Columns.
As month, day, year, and we finally get the right dates. But the fastest I found so far.
Same approach. Use Find and Replace.
But we're just going to change each occurrence of - to -20.
Replace All. And that automatically changes it to a date.
Brought my Power Query friends out there.
I figured there'd be a beautiful way that I could use Power Query.
See it comes in as the wrong date. So I have to uncheck Change Type.
And then on Add Column, Column from Examples. But I'll tell you what, download the workbook.
I can not put anything in over here that will cause this to be correct.
I knew as I started today that there's two ways to solve the problem.
Convoluted way, which I wanted to show in the video.
That's the Sam Radakovitz this way, you just do it once and never again. But I knew that there was also a super easy way that you wouldn't just have to do thousands of times a year. I did show the super easy way.
And then when I'm editing the video, I'm like, wait a second.
It didn't work. It changed the dates.
So after the video, there's an outtake.
And for my friends who are the Power Query experts and Formula experts.
I want to know how you would do the super easy way because it is not working for me.
All right?
So they're getting data sent down from Oracle and the dates are showing up as text in this format, SEP-23.
This is a refreshable query, so it's coming in this way all the time.
They're not using Power Query, it's just coming in from SQL Server Analysis Services.
30 people in the department doing this dozens of times a week.
So the dates come in like this, and when you create a pivot table.
It's just chaos because it's alphabetical across the top.
Rather than convert these to dates every single time.
It seems like a good way would be to use a trick the Sam Radakovitz show me many years ago.
So I asked them how far back they want to go. Three years.
All right? And I'm going to use a custom list for this.
So I put in a date 1/1/2020.
I'm going to right click and drag and go down to 252. In this case just because that's a multiple of 12.
And then say fill months. So that gives me monthly dates going down.
And then in column B I'm going to use this formula.
The TEXT of A1, "MMM-YY".
And that's going to create something that looks like their text coming down.
Now the problem is with custom lists, you cannot use a formula and a custom list.
So we have to copy that and then here we'll paste as values. You can do that however you'd like.
I'm going to do that one right there. All right, so now I've selected these 252 cells.
We're going to go into File, Options, Advanced.
And scroll all the way to the bottom and choose Edit Custom Lists. So File, Options, Advanced.
Scroll all the way to the bottom. Edit Custom Lists.
And then we'll import that list of 252 items. The list can only be 254 or less.
So this goes out all the way to 2040, which is the rest of my work life for sure.
If you're very young, you might want to put a note in your calendar for January of 2041 of how the heck you did this. All right, so now we just have to do that once.
Once you've done that and you close Excel without Excel crashing, then we should be good to go. Let's get rid of this pivot table.
If your data is in Excel like this, Insert, Pivot Table.
Okay, click OK. Okay, we'll put dates across the top.
Oh, there we are. It's already working.
Sales down the side.
All right, that's great if your data's coming from inside of Excel.
But they mentioned that this is a refreshable query coming in from analysis services.
So that tells me that their data's not coming from inside of Excel.
That's going to require a few extra steps. So essentially.
Insert, Pivot Table, I'm going to choose this box, add this data to the data model.
Dates across the top, products down the side, sales.
And we still have the wrong sequence here.
So in my case, I'm going to go to the date dropdown.
A lot of people will still have this in Compact Form. So you'll go to the column labels dropdown.
We're going to go into More Sort Options. Choose Ascending based on date.
Go to More Options. Uncheck Automatically Sort.
And then the First Key Sort Order, we're going to choose that custom list that we just created.
Click. Okay.
I hate those eight clicks, but it does solve the problem.
All right, well hey, I want to thank you for stopping by.
We'll see you next time for another Netcast from MrExcel.
If you like these videos, please down below Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Hey, as an outtake, here's a challenge.
Let's say that someone has these text dates that look like this, and it's not a refreshable query. They just need to fix it, right?
I was convinced that I could very quickly do this with Alt+D E F.
Which is just essentially a way of doing Data, Text to Columns, Finish.
And sure enough, we get dates. But wait!
They are not the right dates. This is May of 2020.
And because I'm recording this in 2023. It's giving me May 20th, 2023.
So then I thought what we really have to do is Text to Columns.
Go to the third step.
And specify that it's a date in month, day, year, I guess. But that doesn't solve it either.
And in fact, I tried all six options there in step three of the Wizard.
And none of those will get me the correct date in May of 2020.
So then I thought, let's be clever. Change every occurrence of the dash.
There's only one dash. And change it into -01-20.
So we go from May-20 to May-01-2020, right?
At least then it'll look like the right date and we can do something with it. Replace All.
And then try Data, Text to Columns.
As month, day, year, and we finally get the right dates. But the fastest I found so far.
Same approach. Use Find and Replace.
But we're just going to change each occurrence of - to -20.
Replace All. And that automatically changes it to a date.
Brought my Power Query friends out there.
I figured there'd be a beautiful way that I could use Power Query.
See it comes in as the wrong date. So I have to uncheck Change Type.
And then on Add Column, Column from Examples. But I'll tell you what, download the workbook.
I can not put anything in over here that will cause this to be correct.
