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: www.mrexcel.com/youtube/BpgjO...
Welcome to episode 2624 of MrExcel's Netcast, where we explore all things Excel. In today's video, we will be discussing a common issue faced by many Excel users - Oracle sending dates as text in the format MMM-YY. This can cause chaos when creating pivot tables, as the dates are sorted alphabetically instead of chronologically. But don't worry, we have some Excel hacks that will help you solve this problem in no time.
In this video, I will be showing you two ways to solve this issue. The first method is a bit convoluted, but it only needs to be done once and never again. The second method is super easy, but unfortunately, it didn't work for me. So, I am calling out to all the Power Query and Formula experts out there to share their solutions in the comments below. Let's work together to find the best and most efficient way to handle this problem.
So, let's dive into the issue. We have a refreshable query from Oracle that sends dates in the format SEP-23. This is causing problems when creating pivot tables, as the dates are not in the correct chronological order. To solve this, we will be using a trick that was shown to me by Sam Radakovitz many years ago. We will create a custom list of dates and use a formula to convert the text dates into the desired format. However, there is a catch - custom lists do not allow formulas. But don't worry, we have a workaround for that too. Just follow along with the video and you'll have your dates sorted in no time.
But what if your data is not in Excel and is coming from SQL Server Analysis Services? Don't worry, we have a solution for that too. We will be using the Data Model and a few extra steps to get the dates in the correct order. And for those of you who are wondering how far back you can go with this solution, the answer is three years. But if you're young and have a long Excel career ahead of you, make sure to put a reminder in your calendar for January 2041 to refresh your memory on how to do this.
I hope you found this video helpful and learned some new Excel hacks. If you did, don't forget to hit the like button and subscribe to our channel for more Excel tips and tricks. Also, make sure to ring the bell icon to get notified every time we upload a new video. And as always, feel free to leave your questions and comments down below. Thank you for watching and we'll see you in the next episode of MrExcel's Netcast.
Buy Bill Jelen's latest Excel book: www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: www.mrexcel.com/like-mrexcel-...
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: 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
Join the MrExcel Message Board discussion about this video at www.mrexcel.com/board/threads...
14 июл 2024