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: https://www.mrexcel.com/youtube/BpgjOMlh39E/

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: https://www.mrexcel.com/products/latest/

#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