James asks how to take 10 years of check data and convert it to a summary table with months going across the top and years going down the side. James would prefer not to have to add new YEAR and MONTH columns to the original data. Episode 1752 will show you how to solve this in a few clicks.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1750 - Convert Dates to Months and Years Table!
Alright, today's question sent in by James, James has 10 years’ worth of check data, dates and amount, and he wants to build a table, years going down and months going across, and would prefer not to have to add new columns with =YEAR and then- I wouldn't use =MONTH because MONTH just gives us a month number.
I would probably use =TEXT , this "MMM" for the month abbreviation, or "MMMM" for the month name, you know, either way.
Alright so, select our data, Insert, PivotTable, and today I'm just going to create the Pivot table on an Existing Worksheet right there, click OK.
And initially we're going to put dates down the left hand side and check mark amounts, and so we end up with almost the original data that we have there.
But we're going to use a great feature in Pivot tables.
Now, you need to go to the first date field, our first date cells are right there in D5, and then on the PivotTable Tools, Options if you're in Excel 2010 or earlier, or, it's called Analyze in Excel 2013, it's the first PivotTable Tools tab.
Now we're going to choose Group Field, and choose both Months and Years, click OK.
And what that does for us, that gives us a brand new field that we didn't have before, it's a virtual field called Years, and the old Date field is now really Months.
Alright, so we'll take the Years field and pivot it to go, you wanted years to go down the side.
I'll take the Date field and pivot it to go across the column labels, and we now have Jan Feb Mar.
Column Labels, Row Labels, let's get rid of those, that's Design, Report Layout, Show in Tabular Form.
Now we have normal columns there, and our data for August through December is not filled in yet because we don't have that data.
If you wanted those to be filled in with zeros, easy enough, come here to the Options and say "For empty cells show: 0", click OK.
But, you know what, this is one case where I think I actually want to leave those as blank, because we just don't have the data yet.
Alright well hey, I want to thank James for sending that question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1750 - Convert Dates to Months and Years Table!
Alright, today's question sent in by James, James has 10 years’ worth of check data, dates and amount, and he wants to build a table, years going down and months going across, and would prefer not to have to add new columns with =YEAR and then- I wouldn't use =MONTH because MONTH just gives us a month number.
I would probably use =TEXT , this "MMM" for the month abbreviation, or "MMMM" for the month name, you know, either way.
Alright so, select our data, Insert, PivotTable, and today I'm just going to create the Pivot table on an Existing Worksheet right there, click OK.
And initially we're going to put dates down the left hand side and check mark amounts, and so we end up with almost the original data that we have there.
But we're going to use a great feature in Pivot tables.
Now, you need to go to the first date field, our first date cells are right there in D5, and then on the PivotTable Tools, Options if you're in Excel 2010 or earlier, or, it's called Analyze in Excel 2013, it's the first PivotTable Tools tab.
Now we're going to choose Group Field, and choose both Months and Years, click OK.
And what that does for us, that gives us a brand new field that we didn't have before, it's a virtual field called Years, and the old Date field is now really Months.
Alright, so we'll take the Years field and pivot it to go, you wanted years to go down the side.
I'll take the Date field and pivot it to go across the column labels, and we now have Jan Feb Mar.
Column Labels, Row Labels, let's get rid of those, that's Design, Report Layout, Show in Tabular Form.
Now we have normal columns there, and our data for August through December is not filled in yet because we don't have that data.
If you wanted those to be filled in with zeros, easy enough, come here to the Options and say "For empty cells show: 0", click OK.
But, you know what, this is one case where I think I actually want to leave those as blank, because we just don't have the data yet.
Alright well hey, I want to thank James for sending that question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
