After adding subtotals by Customer, Excel adds three group and outline buttons. Most people use the #2 and #3 buttons but rarely use the #1 button. Could you simply remove the #1 button? Podcast 849 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:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Another question from the road, and this is one that kind of stumped me when they asked it-- no one had ever asked me before.
When we use Subtotals-- Data, Subtotals, At each change in Customer, Add the Subtotal here-- of course, we get the Group & Outline buttons over there, and I always talk about how the #2 button is a great view of the data; #3 shows all the detal rows; but #1, it's kind of silly-- I don't even understand the perpose.
Who would add Subtotals just so that we can see a Grand Total?
And someone said, "Well, why don't you get rid of the #1 button and, you know, obviously you can't right-click it or anything like that, but it turns out what we need to do is select everything from the Grand Total row, all the way up to the top.
And then do Data, Group, and then Ungroup, and what it does is it gets rid of the outermost level, and now we just have two buttons-- the 1 shows us the Customer totals, the 2 shows us the detail rows.
Much less confusing.
If you're handing the spreadsheet off to someone else, just teach them to use those two buttons.
Well I want to send thanks out to Matthew in Birmingham.
Matthew dropped me an email after he figured out how to do that.
So, thanks for stopping by we'll see you next time for another netcast for MrExcel.
[ music ]
Another question from the road, and this is one that kind of stumped me when they asked it-- no one had ever asked me before.
When we use Subtotals-- Data, Subtotals, At each change in Customer, Add the Subtotal here-- of course, we get the Group & Outline buttons over there, and I always talk about how the #2 button is a great view of the data; #3 shows all the detal rows; but #1, it's kind of silly-- I don't even understand the perpose.
Who would add Subtotals just so that we can see a Grand Total?
And someone said, "Well, why don't you get rid of the #1 button and, you know, obviously you can't right-click it or anything like that, but it turns out what we need to do is select everything from the Grand Total row, all the way up to the top.
And then do Data, Group, and then Ungroup, and what it does is it gets rid of the outermost level, and now we just have two buttons-- the 1 shows us the Customer totals, the 2 shows us the detail rows.
Much less confusing.
If you're handing the spreadsheet off to someone else, just teach them to use those two buttons.
Well I want to send thanks out to Matthew in Birmingham.
Matthew dropped me an email after he figured out how to do that.
So, thanks for stopping by we'll see you next time for another netcast for MrExcel.
[ music ]
