Grouping Data: Remove Groupings, Fix Borders and Copy Visible Data

Quick reference

• 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.