Someone in my Michigan seminar posed this question: "Can you get the pivot table grand total column to appear on the left side of the pivot table?"
This is handy when you have so many columns that you're not able to see the Totals Column. In Episode #1459, Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen come up with differing strategies for solving this problem.
Dueling Excel Podcast #89...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
This is handy when you have so many columns that you're not able to see the Totals Column. In Episode #1459, Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen come up with differing strategies for solving this problem.
Dueling Excel Podcast #89...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
Transcript of the video:
Hey, welcome back to another Dueling Excel podcast.
I'm Bill Jelen from MrExcel.
I will be joined by Mike Girvin from Excel is Fun.
This is episode 89: pivot total column first.
All right, so, hey, I want to set up this problem.
I was doing a seminar up in Michigan this week and someone came to me with an interesting situation.
He said, “I have a pivot table that is really, really, really wide and as I change the slicers, the size of the pivot table changes,” but he said, “The thing that drives me crazy is I can't see the grand total column.” All right, so, let's just-- here I’m going to take-- I’m going to split the window here and we'll take a look for A. The columns go all the way out to-- for A the columns go all the way up to IH.
For B, they go to DT.
For C, they go to FP.
So, there's a different number of columns every time.
He says, ”The thing that drives me crazy is I really want to be able to see the grand total column first.
I want to see it over there between the product and the first Center.
I want to bring it to the front.
That way I can always see the grand total.” You know, my initial reaction-- my initial reaction was okay, I wonder if we can actually fool Excel into doing this.
So, I said, let's go back to your data.
I'm actually going to make a copy of this data and I'm going to add a column called Extra.
Extra.
In that Extra column, I'm just going to put the same value all the way.
I'll just put an A everywhere.
It really doesn't matter what it is.
No.
I want to fill-- Okay, then create pivot table, insert, pivot table, OK.
We would put Extra and Center going down the left-hand side with Sales.
You see, there’d be, because of the compact view, that view that I hate all the time, showing compact form, the subtotals end up at the top of the group and so we have this-- let's call it a fake grand total that appears at the top of the list.
I said, you know, that's one way to get the total to the top.
However, that fails when we drag things to go across the column labels.
No matter what we do, that is always going to show up as the last column instead of the first column.
So I said, all right.
No way that we're ever going to get this inside of the pivot table.
Now, what we're going to have to do is try and create a clever formula out here on the left-hand side that is going to go grab the grand total.
So, when it comes to clever formulas that would be Mike.
So, Mike, let's see what you can do.
Mike: Thanks, MrExcel.
Hey, when it comes to clever formulas, it's the MrExcel message board.
That’s where you go.
In fact, the trick I'm going to use I learned years ago at the MrExcel message board.
I think it was actually in the Hall of Fame there.
All right, so our pivot table, we need a formula to get the last number.
So, the problem is, of course as MrExcel said, it could be here it, it could be here, it could be there.
Well, we can-- there's a LOOKUP formula that will find the last number.
So, let's go ahead and see how that works.
Now, I'm going to insert a column.
Alt IC and maybe type Grand Totals here.
All right, I'm going to use LOOKUP.
Now, LOOKUP can only do approximate match.
VLOOKUP and MATCH or LOOKUP functions which can do exact or approximate.
The cool thing about approximate and the-- for any of these functions, if you give a lookup value that's bigger than anything in the table-- the lookup vector or the VLOOKUP table, if you give it a value bigger than anything in the table, it'll get the last one.
So, lookup value; I'm going to give it the biggest number Excel knows.
Nine point 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14 nines and then an e + 3 0 7.
That's the biggest number that Excel knows.
You could put any big number as long as it's bigger than anything here, but if you put that number in, it won't go wrong.
All right, the lookup vector, that's like a lookup table.
I don't know where the end is going to be, so I'm just going to put 7 colon 7.
Oops, there's an ampersand, so 7.
That means the whole 7th row and sure enough when we copy it down, that'll work.
Now, there’ll be a little problem here if we change this, right?
That's looking bad there.
This goes down too far and if we were to do A, then it wouldn't go down far enough.
So, I'm going to do a little amendment here.
I'm going to say, hey, that, IF and C7 is the cell I want to look in-- if I click on the table, it might give me that big ‘get pivot table data,’ so I'm just going to type C7.
Anytime that's equal to nothing or blank, so double quote.
Double quote will say when it's equal to blank then that's the logical test.
If it's blank that means it's down past the grand total.
Then what do I want?
I want to put double quote.
I want to show nothing for value if true, otherwise run the formula.
Right, so now I can copy this down.
Make sure it's further than any last possible number and then we have our formula.
One last thing, it would be nice-- actually let's do some sort of number format.
It’d be nice if we had an indication that this is the grand total, so I'm going to use conditional formatting.
With the range selected and the active cell at the top, I'm going to do Alt OD, new rule, formula.
Now, the formula is hey, I want to look over starting here one, so it's C7.
I want to say if C7-- that's a relative cell reference, equals grand total-- Now, right now it's a relative cell reference and it's as if it's in the active cell.
When you cut-- when it gets copied down in memory, it'll always look-- be looking one, two cells to my left.
So, I’m going to say format.
Now, I would like to put a border, but I don't see any borders there.
I'm going to go over to font.
I'm going to use bold and I still want a double underline for accounting.
So underline.
I'm going to say hey, give me a double underline.
Anytime it finds a grand total to over, it will format that.
Now, this is something that occurs in conditional formatting, sometimes data validation dialog box with formulas, sometimes the name dialog box.
I'm going to Alt OD and sure enough, it looks like-- you can see here it's got some double quotes, so I double-click it to edit it.
It just does this sometimes.
It puts in quotes where it's not supposed to, so I'm going to try and fix this.
All right, I got my fingers crossed this time.
I got rid of all the extra double quotes.
Click OK.
Click OK.
So, there it is.
So, now if I click B or A, there's my grand totals.
All right, throw it back to MrExcel.
Bill: Hey, that is annoying the way that the conditional formatting puts those double quotes in sometimes, but something else you said, Mike, about GETPIVOTDATA.
I started thinking, I wonder if GETPIVOTDATA could solve this problem.
So, I go to pivot table tools, the options tab, open up this little options drop-down, turn back on ‘generate get pivot data’ t.
Everyone turns that off usually.
So, if I just equal sign and now use Ctrl right arrow to get to that grand total and we take a look at formula.
It says equal GETPIVOTDATA.
So, we're looking for the Sales field, for the table B5, the product and see, they hard-coded it to Alabama.
That's really bad.
We need it to not be hard-coded to Alabama.
We want to pick up whatever value is, in this case, B7.
B7 and then we kind of do the same sort of thing we did.
Equal IF ISBLANK B7 then give me nothing, otherwise give me the GETPIVOTDATA.
Copy that down far enough so it covers everything.
This is annoying.
The GETPIVOTDATA formula doesn't work in the grand total.
It is possible to generate GETPIVOTDATA for the grand total, but it just-- it has no arguments at all.
So, then this is-- this is just getting worse and worse as I go.
I continue to hate GETPIVOTDATA, but kind of have to say here, if B7 is equal to grand total, then use this alternate form of GETPIVOTDATA, otherwise use the one that works everywhere else.
Copy that down far enough.
Okay, then it works for whichever version.
So, I'm not sure that that's any better than that great old LOOKUP.
It was a few weeks ago, Mike.
I thought you were actually going to break out that old look out-- LOOKUP, so I'm glad to see that it came in.
All right, well, hey I want to thank everyone for stopping by.
We’ll see you next week for another Dueling Excel podcast from MrExcel and Excel is Fun.
I'm Bill Jelen from MrExcel.
I will be joined by Mike Girvin from Excel is Fun.
This is episode 89: pivot total column first.
All right, so, hey, I want to set up this problem.
I was doing a seminar up in Michigan this week and someone came to me with an interesting situation.
He said, “I have a pivot table that is really, really, really wide and as I change the slicers, the size of the pivot table changes,” but he said, “The thing that drives me crazy is I can't see the grand total column.” All right, so, let's just-- here I’m going to take-- I’m going to split the window here and we'll take a look for A. The columns go all the way out to-- for A the columns go all the way up to IH.
For B, they go to DT.
For C, they go to FP.
So, there's a different number of columns every time.
He says, ”The thing that drives me crazy is I really want to be able to see the grand total column first.
I want to see it over there between the product and the first Center.
I want to bring it to the front.
That way I can always see the grand total.” You know, my initial reaction-- my initial reaction was okay, I wonder if we can actually fool Excel into doing this.
So, I said, let's go back to your data.
I'm actually going to make a copy of this data and I'm going to add a column called Extra.
Extra.
In that Extra column, I'm just going to put the same value all the way.
I'll just put an A everywhere.
It really doesn't matter what it is.
No.
I want to fill-- Okay, then create pivot table, insert, pivot table, OK.
We would put Extra and Center going down the left-hand side with Sales.
You see, there’d be, because of the compact view, that view that I hate all the time, showing compact form, the subtotals end up at the top of the group and so we have this-- let's call it a fake grand total that appears at the top of the list.
I said, you know, that's one way to get the total to the top.
However, that fails when we drag things to go across the column labels.
No matter what we do, that is always going to show up as the last column instead of the first column.
So I said, all right.
No way that we're ever going to get this inside of the pivot table.
Now, what we're going to have to do is try and create a clever formula out here on the left-hand side that is going to go grab the grand total.
So, when it comes to clever formulas that would be Mike.
So, Mike, let's see what you can do.
Mike: Thanks, MrExcel.
Hey, when it comes to clever formulas, it's the MrExcel message board.
That’s where you go.
In fact, the trick I'm going to use I learned years ago at the MrExcel message board.
I think it was actually in the Hall of Fame there.
All right, so our pivot table, we need a formula to get the last number.
So, the problem is, of course as MrExcel said, it could be here it, it could be here, it could be there.
Well, we can-- there's a LOOKUP formula that will find the last number.
So, let's go ahead and see how that works.
Now, I'm going to insert a column.
Alt IC and maybe type Grand Totals here.
All right, I'm going to use LOOKUP.
Now, LOOKUP can only do approximate match.
VLOOKUP and MATCH or LOOKUP functions which can do exact or approximate.
The cool thing about approximate and the-- for any of these functions, if you give a lookup value that's bigger than anything in the table-- the lookup vector or the VLOOKUP table, if you give it a value bigger than anything in the table, it'll get the last one.
So, lookup value; I'm going to give it the biggest number Excel knows.
Nine point 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14 nines and then an e + 3 0 7.
That's the biggest number that Excel knows.
You could put any big number as long as it's bigger than anything here, but if you put that number in, it won't go wrong.
All right, the lookup vector, that's like a lookup table.
I don't know where the end is going to be, so I'm just going to put 7 colon 7.
Oops, there's an ampersand, so 7.
That means the whole 7th row and sure enough when we copy it down, that'll work.
Now, there’ll be a little problem here if we change this, right?
That's looking bad there.
This goes down too far and if we were to do A, then it wouldn't go down far enough.
So, I'm going to do a little amendment here.
I'm going to say, hey, that, IF and C7 is the cell I want to look in-- if I click on the table, it might give me that big ‘get pivot table data,’ so I'm just going to type C7.
Anytime that's equal to nothing or blank, so double quote.
Double quote will say when it's equal to blank then that's the logical test.
If it's blank that means it's down past the grand total.
Then what do I want?
I want to put double quote.
I want to show nothing for value if true, otherwise run the formula.
Right, so now I can copy this down.
Make sure it's further than any last possible number and then we have our formula.
One last thing, it would be nice-- actually let's do some sort of number format.
It’d be nice if we had an indication that this is the grand total, so I'm going to use conditional formatting.
With the range selected and the active cell at the top, I'm going to do Alt OD, new rule, formula.
Now, the formula is hey, I want to look over starting here one, so it's C7.
I want to say if C7-- that's a relative cell reference, equals grand total-- Now, right now it's a relative cell reference and it's as if it's in the active cell.
When you cut-- when it gets copied down in memory, it'll always look-- be looking one, two cells to my left.
So, I’m going to say format.
Now, I would like to put a border, but I don't see any borders there.
I'm going to go over to font.
I'm going to use bold and I still want a double underline for accounting.
So underline.
I'm going to say hey, give me a double underline.
Anytime it finds a grand total to over, it will format that.
Now, this is something that occurs in conditional formatting, sometimes data validation dialog box with formulas, sometimes the name dialog box.
I'm going to Alt OD and sure enough, it looks like-- you can see here it's got some double quotes, so I double-click it to edit it.
It just does this sometimes.
It puts in quotes where it's not supposed to, so I'm going to try and fix this.
All right, I got my fingers crossed this time.
I got rid of all the extra double quotes.
Click OK.
Click OK.
So, there it is.
So, now if I click B or A, there's my grand totals.
All right, throw it back to MrExcel.
Bill: Hey, that is annoying the way that the conditional formatting puts those double quotes in sometimes, but something else you said, Mike, about GETPIVOTDATA.
I started thinking, I wonder if GETPIVOTDATA could solve this problem.
So, I go to pivot table tools, the options tab, open up this little options drop-down, turn back on ‘generate get pivot data’ t.
Everyone turns that off usually.
So, if I just equal sign and now use Ctrl right arrow to get to that grand total and we take a look at formula.
It says equal GETPIVOTDATA.
So, we're looking for the Sales field, for the table B5, the product and see, they hard-coded it to Alabama.
That's really bad.
We need it to not be hard-coded to Alabama.
We want to pick up whatever value is, in this case, B7.
B7 and then we kind of do the same sort of thing we did.
Equal IF ISBLANK B7 then give me nothing, otherwise give me the GETPIVOTDATA.
Copy that down far enough so it covers everything.
This is annoying.
The GETPIVOTDATA formula doesn't work in the grand total.
It is possible to generate GETPIVOTDATA for the grand total, but it just-- it has no arguments at all.
So, then this is-- this is just getting worse and worse as I go.
I continue to hate GETPIVOTDATA, but kind of have to say here, if B7 is equal to grand total, then use this alternate form of GETPIVOTDATA, otherwise use the one that works everywhere else.
Copy that down far enough.
Okay, then it works for whichever version.
So, I'm not sure that that's any better than that great old LOOKUP.
It was a few weeks ago, Mike.
I thought you were actually going to break out that old look out-- LOOKUP, so I'm glad to see that it came in.
All right, well, hey I want to thank everyone for stopping by.
We’ll see you next week for another Dueling Excel podcast from MrExcel and Excel is Fun.
