You have a pivot table in Excel. The months and weekday names are appearing alphabetically instead of in calendar sequence. This happens when your pivot table is based on external data or the data model.
There are two solutions. This video prefers using the Custom Sort Order option. This takes 8 clicks to fix the months and 8 clicks to fix weekdays.
If your Pivot Table Slicers are in the wrong sequence, then you should use the Data Model setting to sort one column by another column.
There are two solutions. This video prefers using the Custom Sort Order option. This takes 8 clicks to fix the months and 8 clicks to fix weekdays.
If your Pivot Table Slicers are in the wrong sequence, then you should use the Data Model setting to sort one column by another column.
Transcript of the video:
Here's a frustrating one. You are in Excel. You have a pivot table and the months and or weekdays are appearing alphabetically instead of in the correct sequence.
This question from Jose Antonio today - his pivot table months down the left hand side instead of January, February, March, he has April, August, December.
Weekdays across the top - instead of Sunday, Monday, Tuesday?
Friday, Monday, Saturday which is correct, alphabetically.
Now, look, there’s two different ways to solve this, but given the fact that we already have the pivot table, for me, it's the eight clicks to solve the months, 8 clicks to solve the weekdays. Come here to this drop down.
It currently says month or row labels depending on which layout you have in your pivot table.
Open that, choose More Sort Options. Choose Ascending.
Look at that – Data Source Order - that's brand new, there will have to be a new video about that.
And then in the lower left hand corner, More options.
Uncheck Auto Sort.
That is required because otherwise First Key Sort Order is greyed out.
And then the list of months should be the 5th item in the list. January, February, March.
Click OK. Click OK and the months are now fixed.
Same 8 clicks here for this drop down which either says column labels or weekday.
More Sort Options, choose ascending, choose more options.
Uncheck Auto Sort. And then Sunday, Monday Tuesday, click OK.
Click OK and we're back to normal. Why did this happen?
It happened because your data is either external to Excel or when you created the pivot table, you checked the box for Add This Data To The Data Model.
Now, given that the data is likely in the data model, the other way to fix this would be to add 2 new columns to the table, one for month number and one for weekday number.
And then we can come in and Manage the Data Model. Choose the Month and under Sort By Column.
We're going to say that we're to sort the Month column by the Month Number column.
But that would have required you to have thought that you're going to have this problem and to add the month number to add the weekday number.
And given that Jose Antonio already has this pivot table, it seems to me that it's easiest just to come in here and fix it.
Now that doesn't fix the order of the slicers.
If you're slicers are in the wrong order, then you definitely want to use the Data Model setting for Sort This Column By Another Column.
Check out my new book MrExcel 2022, Boosting Excel.
Click that I in the top right hand corner For more information about that.
I want to thank Jose Antonio for sending that question in and 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.
Now if you love Excel, check out my new courses on the Retrieve platform.
They are video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It 's a super fast way to learn.
This question from Jose Antonio today - his pivot table months down the left hand side instead of January, February, March, he has April, August, December.
Weekdays across the top - instead of Sunday, Monday, Tuesday?
Friday, Monday, Saturday which is correct, alphabetically.
Now, look, there’s two different ways to solve this, but given the fact that we already have the pivot table, for me, it's the eight clicks to solve the months, 8 clicks to solve the weekdays. Come here to this drop down.
It currently says month or row labels depending on which layout you have in your pivot table.
Open that, choose More Sort Options. Choose Ascending.
Look at that – Data Source Order - that's brand new, there will have to be a new video about that.
And then in the lower left hand corner, More options.
Uncheck Auto Sort.
That is required because otherwise First Key Sort Order is greyed out.
And then the list of months should be the 5th item in the list. January, February, March.
Click OK. Click OK and the months are now fixed.
Same 8 clicks here for this drop down which either says column labels or weekday.
More Sort Options, choose ascending, choose more options.
Uncheck Auto Sort. And then Sunday, Monday Tuesday, click OK.
Click OK and we're back to normal. Why did this happen?
It happened because your data is either external to Excel or when you created the pivot table, you checked the box for Add This Data To The Data Model.
Now, given that the data is likely in the data model, the other way to fix this would be to add 2 new columns to the table, one for month number and one for weekday number.
And then we can come in and Manage the Data Model. Choose the Month and under Sort By Column.
We're going to say that we're to sort the Month column by the Month Number column.
But that would have required you to have thought that you're going to have this problem and to add the month number to add the weekday number.
And given that Jose Antonio already has this pivot table, it seems to me that it's easiest just to come in here and fix it.
Now that doesn't fix the order of the slicers.
If you're slicers are in the wrong order, then you definitely want to use the Data Model setting for Sort This Column By Another Column.
Check out my new book MrExcel 2022, Boosting Excel.
Click that I in the top right hand corner For more information about that.
I want to thank Jose Antonio for sending that question in and 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.
Now if you love Excel, check out my new courses on the Retrieve platform.
They are video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It 's a super fast way to learn.
