Yesterday [in Episode #1678], Bill looked at a solution for sorting using Dates that were entered into a Data Set Formatted as Text, using PowerPivot; today he will address Dates entered with Text Formatting in a Pivot Table. Follow along with Episode #1679 as Bill shows us how to 'Fix' the Date Column and work with the Data Set in a Pivot Table.
Pivot Table Data Crunching: Microsoft Excel 2013 -- by Bill 'MrExcel' Jelen and Michael Alexander
Crunch any data, from any source, quickly and easily, with Excel 2013 Pivot Tables! Use Excel 2013 Pivot Tables and Pivot Charts to produce powerful, dynamic reports in minutes instead of hours... understand exactly what's going on in your business... take control, and stay in control! Excel 2013 Pivot Table Data Crunching (MrExcel Library): Jelen, Bill: 9780789748751: Amazon.com: Books
Pivot Table Data Crunching: Microsoft Excel 2013 -- by Bill 'MrExcel' Jelen and Michael Alexander
Crunch any data, from any source, quickly and easily, with Excel 2013 Pivot Tables! Use Excel 2013 Pivot Tables and Pivot Charts to produce powerful, dynamic reports in minutes instead of hours... understand exactly what's going on in your business... take control, and stay in control! Excel 2013 Pivot Table Data Crunching (MrExcel Library): Jelen, Bill: 9780789748751: Amazon.com: Books
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel with MrExcel Podcasts episode 1679: Sort Pivot Table Text Dates.
Hey welcome back to the MrExcel nercast, I'm Bill Jelen.
Does this sound like déjà vu?
Didn't we just do this yesterday?
Evan wrote in and said “hey I'm sorry I’m not using Power Pivot, I'm just using a regular pivot table.” So the problem we have is the data that Evan is getting is text and it's coming in he said every Monday.
So, yesterday-- talked about one kind of date, today he says “well now I have a different kind of date, it's every single Monday and when I create the pivot table the dates are appearing alphabetically,” of course.
All right, so here's the solution in regular pivot tables not Power Pivot.
I put it in the first Monday of the year, second Monday of the year, then select both of those grab the fill handle then drag and that will create all of the Mondays of the year and then here, actually I'm going to do it over in C, I use the formula =TEXT(A2,”mmm-d”) format double click to copy that down, convert to values and we now have this list of dates in the proper sequence.
Now one year, 52 data points, that's going to work great so we go to File, Options then Advanced.
Scroll all the way down to the bottom of Advanced and you'll see the button for edit custom lists and because I pre-selected B2 to B53 I can just import that list.
Click OK.
Now that we've created that custom list on this computer it's going to be very easy when we create the pivot table.
I'll delete the pivot table.
I'll just insert pivot table, OK, Field list over here put dates down the left-hand side products going across the top and amounts in the middle, you see the dates appear in the proper sequence based on the custom list.
We don't have to go through all those hassles you had to do in Power Pivot creating the second table.
It's actually much easier to do it this way.
Now I think we're trying to create a pivot chart so let's try maybe column charts, stacked column click OK.
This isn't the best way to show the state of it see the table on the axis now appear in the way that Evan wants them to appear.
So there you go, sorting text dates when they are going to appear in a pivot table using custom lists.
All right, well, hey I wanna thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel with MrExcel Podcasts episode 1679: Sort Pivot Table Text Dates.
Hey welcome back to the MrExcel nercast, I'm Bill Jelen.
Does this sound like déjà vu?
Didn't we just do this yesterday?
Evan wrote in and said “hey I'm sorry I’m not using Power Pivot, I'm just using a regular pivot table.” So the problem we have is the data that Evan is getting is text and it's coming in he said every Monday.
So, yesterday-- talked about one kind of date, today he says “well now I have a different kind of date, it's every single Monday and when I create the pivot table the dates are appearing alphabetically,” of course.
All right, so here's the solution in regular pivot tables not Power Pivot.
I put it in the first Monday of the year, second Monday of the year, then select both of those grab the fill handle then drag and that will create all of the Mondays of the year and then here, actually I'm going to do it over in C, I use the formula =TEXT(A2,”mmm-d”) format double click to copy that down, convert to values and we now have this list of dates in the proper sequence.
Now one year, 52 data points, that's going to work great so we go to File, Options then Advanced.
Scroll all the way down to the bottom of Advanced and you'll see the button for edit custom lists and because I pre-selected B2 to B53 I can just import that list.
Click OK.
Now that we've created that custom list on this computer it's going to be very easy when we create the pivot table.
I'll delete the pivot table.
I'll just insert pivot table, OK, Field list over here put dates down the left-hand side products going across the top and amounts in the middle, you see the dates appear in the proper sequence based on the custom list.
We don't have to go through all those hassles you had to do in Power Pivot creating the second table.
It's actually much easier to do it this way.
Now I think we're trying to create a pivot chart so let's try maybe column charts, stacked column click OK.
This isn't the best way to show the state of it see the table on the axis now appear in the way that Evan wants them to appear.
So there you go, sorting text dates when they are going to appear in a pivot table using custom lists.
All right, well, hey I wanna thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
