Neville from Germany sends in a good suggestion for overcoming pivot formatting problems from Episode 865. By using conditional formatting on the underlying worksheet, Neville comes up with a way to make sure that the total rows are always highlighted in a pivot table. Episode 875 shows you how.
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.
Boy, a great note today from Nevel.
Now, Nevel is from Germany, and was watching Episode 865 where I talked about how frustrating it is when the formatting of a Pivot Table gets in trouble when you start to change the Pivot Table.
Nevel sent in a great idea-- it uses just some basic Excel blocking and tackling.
So I'm going to do a Pivot Table here-- let's put Region and Product down the left hand side, and then in the heart of the Pivot Table, maybe, Revenue and Profit.
Of course, back in Excel 2003, they build this going down the report-- I'm going to change that so that way it goes across.
And here's Nevel's suggestion, he says, "Hey, look, we're just going to select a larger area than the Pivot Table could ever possibly encompass." So I'm just going to choose a few hundred rows here, and then we're going to go to Format, Conditional Formatting, and change it from "Cell Value Is" to "Formula Is".
Now, when you go to Formula Is, you have to write a formula that is going to work as if Cell A1 is the cell that the formula refers to.
Okay, so, basically, it starts out here, =IF, and then he uses the OR function, (OR(RIGHT($A1,5)="total",.
So, that's the first condition.
Now, in this case, that would work-- that would be enough-- but we might add new fields to the left hand side of the Pivot Table.
So, Nevel says, "Let's handle a few more conditions." So, RIGHT($B1,5)="total",, and then we'll do it for a few more.
RIGHT($C1,5)="total",, finally the last condition-- (RIGHT($D1,5)="total".
Now, in Nevel's note, it was actually interesting.
He says, "I don't know if I'm going to be dealing with the English version of Excel or the German version of Excel." So he actually adds in additional conditions there to catch the German word for total.
Now, we don't have to do that here.
I'll close the OR, and then if all of that is true we, put true.
Otherwise, we put false.
And then he applied some Format.
He says, “Well, you know, I use bold for the font and then choose some sort of a background color; let's go with a blue here, click OK, click OK, and initially what you see happen, is that the totals do get highlighted.
Now, I showed back in Episode 865, how we can do this but, my method does not allow you to add new items in.
So, now, if i add Customer along the left-hand side, you see that we have all these Customer tools and, basically, now anytime that we have any total, the row gets highlighted.
So, here, both the customer totals and if i scroll down the West Regional for Totals, here is highlighted.
And I can continue to do additional things.
I would add New Fields in, maybe a Date field here.
It still is smart enough to go through, and because of the Conditional Formatting of the underlying worksheet, we end up formatting all the cells that are total rows within the Pivot Table.
Nice trick.
Want to thank Nevel for sending that in.
I'll be sending one of my Exccel Master Pins.
And I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Boy, a great note today from Nevel.
Now, Nevel is from Germany, and was watching Episode 865 where I talked about how frustrating it is when the formatting of a Pivot Table gets in trouble when you start to change the Pivot Table.
Nevel sent in a great idea-- it uses just some basic Excel blocking and tackling.
So I'm going to do a Pivot Table here-- let's put Region and Product down the left hand side, and then in the heart of the Pivot Table, maybe, Revenue and Profit.
Of course, back in Excel 2003, they build this going down the report-- I'm going to change that so that way it goes across.
And here's Nevel's suggestion, he says, "Hey, look, we're just going to select a larger area than the Pivot Table could ever possibly encompass." So I'm just going to choose a few hundred rows here, and then we're going to go to Format, Conditional Formatting, and change it from "Cell Value Is" to "Formula Is".
Now, when you go to Formula Is, you have to write a formula that is going to work as if Cell A1 is the cell that the formula refers to.
Okay, so, basically, it starts out here, =IF, and then he uses the OR function, (OR(RIGHT($A1,5)="total",.
So, that's the first condition.
Now, in this case, that would work-- that would be enough-- but we might add new fields to the left hand side of the Pivot Table.
So, Nevel says, "Let's handle a few more conditions." So, RIGHT($B1,5)="total",, and then we'll do it for a few more.
RIGHT($C1,5)="total",, finally the last condition-- (RIGHT($D1,5)="total".
Now, in Nevel's note, it was actually interesting.
He says, "I don't know if I'm going to be dealing with the English version of Excel or the German version of Excel." So he actually adds in additional conditions there to catch the German word for total.
Now, we don't have to do that here.
I'll close the OR, and then if all of that is true we, put true.
Otherwise, we put false.
And then he applied some Format.
He says, “Well, you know, I use bold for the font and then choose some sort of a background color; let's go with a blue here, click OK, click OK, and initially what you see happen, is that the totals do get highlighted.
Now, I showed back in Episode 865, how we can do this but, my method does not allow you to add new items in.
So, now, if i add Customer along the left-hand side, you see that we have all these Customer tools and, basically, now anytime that we have any total, the row gets highlighted.
So, here, both the customer totals and if i scroll down the West Regional for Totals, here is highlighted.
And I can continue to do additional things.
I would add New Fields in, maybe a Date field here.
It still is smart enough to go through, and because of the Conditional Formatting of the underlying worksheet, we end up formatting all the cells that are total rows within the Pivot Table.
Nice trick.
Want to thank Nevel for sending that in.
I'll be sending one of my Exccel Master Pins.
And I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
