Locked lesson.
About this lesson
Let's look at how to remove created groupings, copy only visible data, and fix borders on cells to work with grouping.
Quick reference
Grouping Data
This functionality in Excel enables you to add your own expandable and collapsible sections to hide and unhide portions of data. This lesson shows you some extras like fixing borders, how to print the visible data only, and solves some pitfalls like merged cells.
When to use
Use this when you have large datasets that you can collapse into main and even nested (or sub-) sections to decrease the amount of noise and expand to drill down into more detail.
Sharing these datasets with clients may need some cosmetic adjustments.
Instructions
- Menu – Data tab, Outline grouping, Group / Ungroup button
- Borders can be tricky when using grouping, sometimes it takes removing all borders and resetting borders to specific cells for a better collapsed result
- To copy and paste only the visible data:
- Select the data using the option on the Home tab, Editing grouping, Find and Select button (magnifying glass), Go To Special, choose visible cells only
-
- Copy and paste as per normal
- Merged cells cause problems here, be sure to unmerge before you select the data
- 00:04 On this sheet, we have an example of an income statement with standard revenue and
- 00:09 expenses that we've applied subtotals to.
- 00:12 If I collapse everything to the highest level,
- 00:15 I have a very short summary of this income statement.
- 00:19 If I want to see more levels,
- 00:20 I can expand it to the necessary level that I want to see.
- 00:24 So just a few notes on this, if I decide to get rid of any groupings,
- 00:28 I can either get rid of them individually or all at once.
- 00:33 To remove the grouping of the quarterly totals, for example,
- 00:36 we'd highlight the column set that the subtotals are applied to,
- 00:40 click on my Data tab, click on the Outline and Ungroup.
- 00:44 But I want to keep that, so let's regroup those,
- 00:48 the alternative is to remove all groupings.
- 00:52 Don't select anything, just click on the Data tab, click on Outline and
- 00:57 Ungroup, and then Clear Outline.
- 01:00 This will get rid of all the outlines in the document in one shot.
- 01:04 But a word of warning, you can't undo the clear outline, so
- 01:08 make sure that's really what you want.
- 01:11 I don't want to in this example,
- 01:13 because I want to show you a few more things before we get rid of our groupings.
- 01:17 Something else you might need to know, let's say you want to send a client
- 01:21 an email and you just want to send the summarized income statement.
- 01:25 There's some formatting in here that I don't really like because we have these
- 01:29 borders, some double lines in here that doesn't really look good.
- 01:34 I'd like to fix this and I could start applying new formatting options right now.
- 01:39 But remember, we grouped and hid a whole bunch of rows.
- 01:44 If we start changing formatting while the rows or columns are still hidden,
- 01:48 you might create some new formatting problems once you expand everything.
- 01:52 So let's expand our rows and see exactly where these borders are applied.
- 01:59 If you go to level two of the grouping,
- 02:02 you can see there's a boxed border around the rows 25 through 46.
- 02:07 The top of that boxed border is actually not part of row 24,
- 02:12 it's at the top of row 25.
- 02:14 Keep this in mind as you apply borders, borders are not necessarily shared by
- 02:19 cells just because the cells are next to each other,
- 02:23 a border gets applied to one cell.
- 02:26 So, in this case, if I'm sure that the border is part of row 25 which is hidden,
- 02:32 then the extra border I'm seeing must sit at the top of row 47.
- 02:38 So let's click on the cell in row 47, and let's Ctrl click on the cell in column
- 02:43 K as well because they both have the same formatting issues.
- 02:47 We can fix them both by going to Home, Font, Grouping,
- 02:52 click and change to No Borders.
- 02:55 So now 47 has no border whatsoever, but
- 02:59 this box border still needs to have a bottom.
- 03:02 So let's expand everything to figure out where to fix the formatting.
- 03:06 The Travel row, row 46, needs a border at the bottom, so
- 03:10 go up to the Borders button, click on Bottom Border.
- 03:14 And the row with Income with Operations, row 48,
- 03:17 that needs a border at the top, so I'm going to add a top border.
- 03:23 All right, now I've added a bottom border to row 46,
- 03:26 a top border to row 48, so row 47 shouldn't have any borders.
- 03:31 Let's check, we'll collapse everything,
- 03:34 and row 47 does not have a top border anymore.
- 03:37 So our problem with the formatting is solved,
- 03:39 it's not something you notice unless you apply grouping.
- 03:44 Now I can copy and paste this, but now see what happens,
- 03:49 we'll select everything Ctrl + C to copy, and
- 03:54 let's go over to a new sheet, hit Ctrl + V to paste.
- 04:00 But you can see it's copied and pasted everything, even the hidden cells,
- 04:04 that is definitely not what I wanted.
- 04:07 Let's go back to our selection, we have a feature that can help us, so
- 04:12 go up to the Home tab.
- 04:14 Go over to the Editing grouping, the Find and
- 04:17 Select button options that you can click and select Go to Special.
- 04:23 On the Go To Special dialog box, choose Visible cells only.
- 04:28 This is not to copy yet, this is just the selection of the visible cells only.
- 04:33 So hit OK and
- 04:34 look at what the screen looks like if you select Visible cells only.
- 04:41 Now you can hit copy, Ctrl + C, and we have a problem,
- 04:45 we cannot do that to part of a merged cell.
- 04:48 So let's expand this and figure this out, 2020 and 2019 and
- 04:53 at the top, they are part of merged cells.
- 04:56 Merged cells have not bothered us, when we set up the subtotals, in fact,
- 05:01 if we collapse, it collapse beautifully to where we want it to be.
- 05:06 But we need to unmerge the cells if we want to copy and paste,
- 05:11 so select the cell that you want to unmerge.
- 05:15 Go up and click on the Merge button and it will unmerge anything that's merged.
- 05:23 Do the same thing with 2019, and then when we collapse,
- 05:26 you'll see it looks fantastic.
- 05:29 Now, before we can copy this, we have to go up to go to Special again,
- 05:35 and remember we need to select only the visible data.
- 05:39 So go to Special, Visible cells only, hit OK.
- 05:43 Now, let's right click and hit Copy to copy this,
- 05:47 let's go over to our new sheet, right-click, and paste, and there it is.
- 05:54 It's only the visible selected cells that we wanted, nothing more, perfect.
- 06:00 I really hope that creating groups helps you manage some of the massive amounts of
- 06:04 data you have to work with, and that it will save you some time in the future.
Lesson notes are only available for subscribers.