Create a running total within months in a pivot table. Episode 939 shows you how.
This video is the 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 video is the 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 sent in by Rod.
Rod wants to know how he can set up a pivot table to show a running total within the month, running total within the month.
So, let's set up a pivot table here.
Go to INSERT and PIVOT TABLE, click OK, and I'm going to put dates down the left-hand side and revenue in the data area.
Alright.
Now, here's the first tricky step.
We're going to go to the very first date, right click, say GROUP, and then roll this up to DAYS and MONTHS, DAYS and MONTHS, click OK, and so we get JANUARY, and then, all of the days of JANUARY, they put it in column A. This is the new default in Excel 2007, which I hate to be completely honest.
So, we go back to REPORT LAYOUT and say SHOW IN OUTLINE FORM.
So, we get those into 2 columns where they belong.
Alright.
Now, we're going to come here to the SUM OF REVENUE and I'm going to go to FIELD SETTINGS, so the little blue i here, FIELD SETTINGS.
We're going to SHOW VALUES AS a RUNNING TOTAL IN and the BASE FIELD is DATE, click OK, and you see that we now have a month to date figure as we go down through the month, and very nicely, in FEBRUARY, it starts over again, so you can see exactly how in the date field.
Great question from Rod.
I want to thank you for stopping by.
Today, a question sent in by Rod.
Rod wants to know how he can set up a pivot table to show a running total within the month, running total within the month.
So, let's set up a pivot table here.
Go to INSERT and PIVOT TABLE, click OK, and I'm going to put dates down the left-hand side and revenue in the data area.
Alright.
Now, here's the first tricky step.
We're going to go to the very first date, right click, say GROUP, and then roll this up to DAYS and MONTHS, DAYS and MONTHS, click OK, and so we get JANUARY, and then, all of the days of JANUARY, they put it in column A. This is the new default in Excel 2007, which I hate to be completely honest.
So, we go back to REPORT LAYOUT and say SHOW IN OUTLINE FORM.
So, we get those into 2 columns where they belong.
Alright.
Now, we're going to come here to the SUM OF REVENUE and I'm going to go to FIELD SETTINGS, so the little blue i here, FIELD SETTINGS.
We're going to SHOW VALUES AS a RUNNING TOTAL IN and the BASE FIELD is DATE, click OK, and you see that we now have a month to date figure as we go down through the month, and very nicely, in FEBRUARY, it starts over again, so you can see exactly how in the date field.
Great question from Rod.
I want to thank you for stopping by.
