Travis has a spreadsheet where you can quickly hide and unhide detail rows using the tiny "1" and "2" buttons. In Episode 799, I will show how to add these Group and Outline symbols to your worksheet.
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.
Today we have a question sent in by Travis.
Travis said, hey, I got a spreadsheet from someone else and they have this amazing quick way to hide and unhide data using these GROUP AND OUTLINE buttons up here in the top left corner of the spreadsheet.
He said, well, how did they get that set up?
That’s a cool feature.
Well, there's a couple of ways to do this, and let's say the annoying way is if you have data that already has totals, we could choose the detail row -- so here's all the AIG rows -- and then DATA, GROUP AND OUTLINE, and then choose GROUP, and what that does is that sets up a manual group, and we can either press this – sign to collapse and the + sign to see everything, or if we have multiple groups set up, we can press the 1 and 2 button.
So, like, for example, here I have a whole bunch of AT&T records.
I'll select down to the AT&T total but not including the AT&T total, DATA, GROUP AND OUTLINE, GROUP, and now I can basically very quickly switch between seeing just the totals or seeing all the detail rows.
Well, it would be very monotonous to go through and group all these, you know, manually, so what we generally do is use the subtotals command to add all of those groups at once.
Here, I have the original data without the totals, I just choose DATA, SUBTOTALS, AT EACH CHANGE IN CUSTOMER, use the SUM function, and we'll choose all the numeric fields, click OK, and, in addition to adding totals in, the great thing here is that it very quickly goes through and gives me the GROUP AND OUTLINE button so I can collapse it down to see just the customer totals or all the totals or just the grand total.
So, using the subtotal command is a great way to very quickly add in all the group indicators, although, if your data already has subtotals, you could, in theory, go through and manually group those using DATA, GROUP AND OUTLINE, GROUP.
Great question from Travis.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Today we have a question sent in by Travis.
Travis said, hey, I got a spreadsheet from someone else and they have this amazing quick way to hide and unhide data using these GROUP AND OUTLINE buttons up here in the top left corner of the spreadsheet.
He said, well, how did they get that set up?
That’s a cool feature.
Well, there's a couple of ways to do this, and let's say the annoying way is if you have data that already has totals, we could choose the detail row -- so here's all the AIG rows -- and then DATA, GROUP AND OUTLINE, and then choose GROUP, and what that does is that sets up a manual group, and we can either press this – sign to collapse and the + sign to see everything, or if we have multiple groups set up, we can press the 1 and 2 button.
So, like, for example, here I have a whole bunch of AT&T records.
I'll select down to the AT&T total but not including the AT&T total, DATA, GROUP AND OUTLINE, GROUP, and now I can basically very quickly switch between seeing just the totals or seeing all the detail rows.
Well, it would be very monotonous to go through and group all these, you know, manually, so what we generally do is use the subtotals command to add all of those groups at once.
Here, I have the original data without the totals, I just choose DATA, SUBTOTALS, AT EACH CHANGE IN CUSTOMER, use the SUM function, and we'll choose all the numeric fields, click OK, and, in addition to adding totals in, the great thing here is that it very quickly goes through and gives me the GROUP AND OUTLINE button so I can collapse it down to see just the customer totals or all the totals or just the grand total.
So, using the subtotal command is a great way to very quickly add in all the group indicators, although, if your data already has subtotals, you could, in theory, go through and manually group those using DATA, GROUP AND OUTLINE, GROUP.
Great question from Travis.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
