Romas has data with both date and time in column A...but needs to summarize the data by date. In Episode 751, we take a look at using a pivot table to solve this problem.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Today, a question from Lithuania. This is Romas. Romas has energy usage data.
What's interesting, though, is the data came from wherever with both date and time in column A.
He needs to summarize it by day.
Well, a couple of ways to do this.
I'm in Excel 2007.
I'm going to use INSERT, PIVOT TABLE.
We’ll click OK, and just choose both of those fields -- TIME and USAGE.
Now, USAGE, because this is numeric, is going to go to the SUM OF VALUES section and TIME, because it’s a date, is going to go to the ROW LABELS.
Well, it's very cool.
In a pivot table, we can right click, choose GROUP and show detail, and what we're going to do is group it up to DAYS and YEARS.
You want to make sure to include YEARS.
If you don't include YEARS, it will take January 1st of this year and January 1st of last year, and put it in a single cell.
That’s no good.
So, we click OK.
Now, back in 2007 here, we have to go to the DESIGN tab and change the REPORT LAYOUT back to the OUTLINE FORM.
So, that way, we can actually see the YEARS and TIME field separated.
I'm going to take the YEARS field and I'm actually going to drag it over to COLUMN LABELS, and so now I can see, for every day, 2006, 2007, 2008.
Now, this is the pivot table approach.
Tomorrow, we're going take a look at using a formula approach.
There is certainly some pivot table problems depending on how much data you have in Excel 2003.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Today, a question from Lithuania. This is Romas. Romas has energy usage data.
What's interesting, though, is the data came from wherever with both date and time in column A.
He needs to summarize it by day.
Well, a couple of ways to do this.
I'm in Excel 2007.
I'm going to use INSERT, PIVOT TABLE.
We’ll click OK, and just choose both of those fields -- TIME and USAGE.
Now, USAGE, because this is numeric, is going to go to the SUM OF VALUES section and TIME, because it’s a date, is going to go to the ROW LABELS.
Well, it's very cool.
In a pivot table, we can right click, choose GROUP and show detail, and what we're going to do is group it up to DAYS and YEARS.
You want to make sure to include YEARS.
If you don't include YEARS, it will take January 1st of this year and January 1st of last year, and put it in a single cell.
That’s no good.
So, we click OK.
Now, back in 2007 here, we have to go to the DESIGN tab and change the REPORT LAYOUT back to the OUTLINE FORM.
So, that way, we can actually see the YEARS and TIME field separated.
I'm going to take the YEARS field and I'm actually going to drag it over to COLUMN LABELS, and so now I can see, for every day, 2006, 2007, 2008.
Now, this is the pivot table approach.
Tomorrow, we're going take a look at using a formula approach.
There is certainly some pivot table problems depending on how much data you have in Excel 2003.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
