There is a cool but subtle way to select all of the subtotal rows in your pivot table in order to format them with a different color. Episode 502 shows you how. This tip came from an audience member at the 33rd annual Meonske Accounting Conference at Kent State. Thanks to Dr. Norm for inviting me back as a speaker at this excellent conference.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Last week, I was lucky enough to be able to be invited to Cant, Ohio to the 32nd annual Meonske Accounting Conference, as a speaker there and in that great audience a few people pointed out, some cool tricks.
I had never really seen before.
Including this one on pivot tables.
Usually, I've done a podcast about this before when we use data subtotals.
I have tricks for edit, go to special visible cells only.
Where we can and change the color of the subtotal lines.
On a pivot table is also possible to do that, if you use data pivot table.
I'll just click [ finish ] and I'll create a pivot table where we have months going across the top and then maybe region and product along the left-hand side and revenue in the heart of the pivot table.
Now, what we'd like to do is take all of those region totals and change them to a different color.
Now, watch how this work, if I hover above the number 8 and then move slightly into column A and click.
I will be able to very quickly select all of the total row.
Now, once those are selected then I can just go to the formatting toolbar, use the fill color and very quickly add color to all of the different sub total rows.
This also works with a grand total over, on the right hand side.
You need to get just at the edge of the word grand total and click and then you can color the grand totals.
I guess that's not as impressive as coloring all the subtotal rows.
Again, the trick is just to make sure that you're right at the edge of the pivot table.
You kind of have to, have the cell pointer right in the word east.
Another interesting thing that we learned is that it's also possible to select all of the XYZ values.
So, if I get just at the beginning of XYZ.
I can actually change the color of the XYZ values.
DEF different color and ABC different color.
Very subtle how you have to be able to actually, click in a very specific area, in order to pull it off.
But a great way to add a lot of color, very quickly to your pivot tables.
Hey! Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Last week, I was lucky enough to be able to be invited to Cant, Ohio to the 32nd annual Meonske Accounting Conference, as a speaker there and in that great audience a few people pointed out, some cool tricks.
I had never really seen before.
Including this one on pivot tables.
Usually, I've done a podcast about this before when we use data subtotals.
I have tricks for edit, go to special visible cells only.
Where we can and change the color of the subtotal lines.
On a pivot table is also possible to do that, if you use data pivot table.
I'll just click [ finish ] and I'll create a pivot table where we have months going across the top and then maybe region and product along the left-hand side and revenue in the heart of the pivot table.
Now, what we'd like to do is take all of those region totals and change them to a different color.
Now, watch how this work, if I hover above the number 8 and then move slightly into column A and click.
I will be able to very quickly select all of the total row.
Now, once those are selected then I can just go to the formatting toolbar, use the fill color and very quickly add color to all of the different sub total rows.
This also works with a grand total over, on the right hand side.
You need to get just at the edge of the word grand total and click and then you can color the grand totals.
I guess that's not as impressive as coloring all the subtotal rows.
Again, the trick is just to make sure that you're right at the edge of the pivot table.
You kind of have to, have the cell pointer right in the word east.
Another interesting thing that we learned is that it's also possible to select all of the XYZ values.
So, if I get just at the beginning of XYZ.
I can actually change the color of the XYZ values.
DEF different color and ABC different color.
Very subtle how you have to be able to actually, click in a very specific area, in order to pull it off.
But a great way to add a lot of color, very quickly to your pivot tables.
Hey! Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
