Learn Excel - "Summarize Dates by Month and Year" - Podcast 1752

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jul 16, 2013.
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.
maxresdefault.jpg


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!
 

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top