Viabahv asks how to replace the grand total column in a Pivot Table with a Delta Column. Bill re-introduces us to the oft hated Calulated Item feature to solve this problem today, in Episode #1435. "Learn Excel 2007 through Excel 2010" by Bill Jelen!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1435: pivot table delta.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is sent in by Viabahv.
Creating a pivot table, insert pivot table, OK.
We want products going down the left-hand side, period going across the top and then amount in the heart of the pivot table.
Let's take a look at the pivot table.
So, we have last year, this year, and then a total, but no one really wants a total there.
We want to see the Delta like this.
This is what we're trying to create.
So, last year, this year, and then the difference.
Well, how do we do that?
Okay, first thing we want to do is we want to get rid of the grand total over here.
So, I go to the design tab and under grand totals-- I always have to think about this.
I only want the grand total at the bottom of the columns and so leave that there.
Then, I'm going to go to this cell or this cell.
One of those two cells I'm going to insert a brand new calculation.
Now, in Excel 2010, they have this nice calculations drop-down out here.
Outside of review, I realized that fields, items, and sets and then calculated item.
Calculated item.
Back in Excel 2007, I think it was about this point on the ribbon: formulas, calculated item.
So, we're going to create a brand new formula here called Delta and we’re going to say, equal to this year.
I double-click and then minus last year, double-click, click OK and we now have the calculated Delta.
This is nice.
If we decide to add something else in, you know, we'll still get those calculated deltas.
So, it's better than building a formula outside this spreadsheet.
So, using just a straight pivot table, calculated item-- I know, I know, I've said before I hate calculated items.
They screw up the data, but in this one particular case, this is the right way to go.
All right, well, hey, 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 1435: pivot table delta.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is sent in by Viabahv.
Creating a pivot table, insert pivot table, OK.
We want products going down the left-hand side, period going across the top and then amount in the heart of the pivot table.
Let's take a look at the pivot table.
So, we have last year, this year, and then a total, but no one really wants a total there.
We want to see the Delta like this.
This is what we're trying to create.
So, last year, this year, and then the difference.
Well, how do we do that?
Okay, first thing we want to do is we want to get rid of the grand total over here.
So, I go to the design tab and under grand totals-- I always have to think about this.
I only want the grand total at the bottom of the columns and so leave that there.
Then, I'm going to go to this cell or this cell.
One of those two cells I'm going to insert a brand new calculation.
Now, in Excel 2010, they have this nice calculations drop-down out here.
Outside of review, I realized that fields, items, and sets and then calculated item.
Calculated item.
Back in Excel 2007, I think it was about this point on the ribbon: formulas, calculated item.
So, we're going to create a brand new formula here called Delta and we’re going to say, equal to this year.
I double-click and then minus last year, double-click, click OK and we now have the calculated Delta.
This is nice.
If we decide to add something else in, you know, we'll still get those calculated deltas.
So, it's better than building a formula outside this spreadsheet.
So, using just a straight pivot table, calculated item-- I know, I know, I've said before I hate calculated items.
They screw up the data, but in this one particular case, this is the right way to go.
All right, well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
