Diane has a fairly complex pivot table. She is using GETPIVOTDATA to pull data from the pivot tables to a summary report in another workbook. In another section of the report, she needs to pull YTD. But this complicates the formula, because she has to add an expanding range as the months continue.
I mentioned that she could create a second pivot table that shows the sales numbers as YTD numbers. This video shows how to copy the first pivot table and how to use Running Total In to convert from monthly sales to YTD Sales.
I mentioned that she could create a second pivot table that shows the sales numbers as YTD numbers. This video shows how to copy the first pivot table and how to use Running Total In to convert from monthly sales to YTD Sales.
Transcript of the video:
Alright, today I'm going to show you how to add year to date to an Excel pivot table.
Today's question from Diane.
I was watching Diane take data from a pivot table yesterday to a summary report.
We talked about how to use GETPIVOTDATA to help that.
But then we had an issue where Diane had another section in the summary report where she was reporting year to date numbers.
And actually adding them up and having to increase the cell reference each time.
I said, you know, there's a way that we can show the year to date numbers right in the pivot table.
So I'm going to take this entire pivot table, copy it, and paste it off to the right here like this.
And we're going to go to one of those value fields.
And under pivot table analyze go to field settings.
Alright, and we'll leave it at SUM, but go to Show Values As.
And I have what I have here, I have a field called Years-Date and Months.
So you have to figure out you know which, which field is your month field.
And under No Calculation, change it to a running total in. And then choose whatever your month field is here.
Click OK. Alright.
And you see what we have, let's just choose these cells and see that the total of 630,115.
That’s right.
And it resets when we get to 2021 or the next year (Diane had five or six years of history) there and so those cells 610,243 and 610,243 seems to be working pretty well.
And then for Diane, you'd use the exact same trick to GETPIVOTDATA from these for your year to date section of the report.
Great question from Diane.
I want to thank her for being at my Excel seminar this week. And I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Today's question from Diane.
I was watching Diane take data from a pivot table yesterday to a summary report.
We talked about how to use GETPIVOTDATA to help that.
But then we had an issue where Diane had another section in the summary report where she was reporting year to date numbers.
And actually adding them up and having to increase the cell reference each time.
I said, you know, there's a way that we can show the year to date numbers right in the pivot table.
So I'm going to take this entire pivot table, copy it, and paste it off to the right here like this.
And we're going to go to one of those value fields.
And under pivot table analyze go to field settings.
Alright, and we'll leave it at SUM, but go to Show Values As.
And I have what I have here, I have a field called Years-Date and Months.
So you have to figure out you know which, which field is your month field.
And under No Calculation, change it to a running total in. And then choose whatever your month field is here.
Click OK. Alright.
And you see what we have, let's just choose these cells and see that the total of 630,115.
That’s right.
And it resets when we get to 2021 or the next year (Diane had five or six years of history) there and so those cells 610,243 and 610,243 seems to be working pretty well.
And then for Diane, you'd use the exact same trick to GETPIVOTDATA from these for your year to date section of the report.
Great question from Diane.
I want to thank her for being at my Excel seminar this week. And I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
