Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
There may be times when you don't want to display or print all your data. Maybe your worksheet is too big, or maybe you are presenting your work to someone who shouldn't have access to all your information. Rather than keeping track of separate worksheets, which I really advice against because just way too room for error, you can temporarily hide your rows and columns. Now there are two ways of doing this. First, I'll show you the standard way that I see most people using, but I will show you a better way that is a little more interactive. So let's say we want to show this worksheet to someone who shouldn't see what our Cost or Profit is.
So put your mouse-pointer up here on the header for Column F and you see the mouse-pointer is pointing down. And click-and-drag straight across to Column H so we have those three columns selected. Make sure your mouse pointer is still pointing down, right-click, and from the pop-up menu, choose Hide and they are hidden. You can deselect and now we see they are hidden. Now if you want to get these columns back again, well how can you click on something to unhide if it's not there to click on it? Well, what you do is you put your mouse-pointer up here. Do you see the column skip from Column E to Column I? Put your mouse-pointer on the header for Column E, click-and-drag straight across to Column I so those columns are selected, and keeping your mouse pointer to that down arrow on the column header, right-click and from the pop-up menu choose Unhide and now the columns are back again.
We can do this with rows also. Let's say we want to show our Total and Average, but we don't want to show the underlying data. Click on the header here for Row 5, make sure you've got that right pointing arrow, click down here let's say on Row 34, make sure you have that rightward pointing arrow, hold down your Shift key and click so we have that entire section selected, right-click, choose Hide, and you can deselect. And again we have just the Total and Average shown without the underlying data.
And we can see here on the row headers that it's skipping from Row 4 to Row 35. So put your mouse-pointer on the header for Row 4, click-and-drag down to Row 35, right-click, choose Unhide, and now we have the underlying data back again. Well, that's great, but you see there is a lot of steps involved and it's very easy to miss if you have anything hidden or not. So here is a way that I like a little better. Let's go over here to the Data tab, and let's select the same columns that we did before. Columns F, G, and H, but over here, click Group and choose Group and hey, look what happens.
By the way, the shortcut for Group is Alt+Shift+Right Arrow. That is hold down the Alt key, and the Shift key, and the Right Arrow. Now we have this Minus sign. Click that Minus sign and the columns collapse as before but now we can click that Plus sign to get them back again. We could do this for rows also and you see we have three groups. First, let's group all of them. So click the header for Row 5, scroll down, and let's Shift+Click the header for Row 35. So hold your Shift key down, click the header for Row 35, and again, either press Alt+Shift+Right Arrow or click Group and choose Group. Let's deselect.
So now we have this whole area. Here you can click that minus. That collapses to just the Total and Average. Click that plus. Now we can nest groups. So let's select here from the header of Row 5 down just to the last one and again, Alt+Shift+Right Arrow, or click Group and Group, that's for Group 2, select the header, down to the last one, Group and Group, and again the header down to the last one. Now this time I will press Alt+Shift+ Right Arrow and we have that grouped. So now we can collapse some of them and nest them.
So for example, now I have just Group 2 showing and the Total and Average without the other groups, or I can collapse that and maybe show just Group 1. Also, do you notice these little symbols here? If I click the number 1 going across, that hides it and group 2 brings it back. And here we have three levels. Level 1 is just the Total and the Average. Level 3 is everything expanded. Level 2 is we are seeing just the components but we don't have anything visible there. So here is where you can click that plus.
So if you wanted to show just one of those groups as opposed to all the others. One thing you should know is that this affects the printout. Let's do a Print Preview. Either press Ctrl+P, or go to the File tab and choose Print, and now you can see it's going to print just that particular group that's visible. I will just press the Escape key to come out. Also, if for whatever reason you want to hide these little 1, and 2, and 3 symbols here, there is only one way to do it. Press Ctrl+8, and Ctrl+8 hides them and Ctrl+8 brings them back.
Well, now let's say you decided that you want to remove these groups. Few ways to do it. Let's say we want to remove the column grouping. Down put your mouse-pointer up here so it's a down-pointing arrow on Column F, click-and-drag across to Column H, and I can choose Ungroup and Ungroup, and let's click that number 3 so there are all expanded. I will show you how to do with rows. This is the same thing. Choose from Group 1 down to the last. One of the shortcuts for that is Alt+Shift+Left Arrow. So that's how you could ungroup one at a time.
If you decide you want to ungroup all of these and get rid of all of these outlining on the whole sheet at once, go up to Ungroup, choose Clear Outline and now it's cleared it from the entire sheet. I think that's a much easier way to do it.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64853 Viewers
80 Video lessons · 124397 Viewers
52 Video lessons · 60322 Viewers
59 Video lessons · 46147 Viewers