Sign in or start a free trial to avail of this feature.
13. Grouping and Hiding Cells
Grouping and hiding rows and columns can be very helpful when printing. It also makes large spreadsheets easier to navigate.
Group and Hide to print a spreadsheet (00:04)
When you print a worksheet, there may be information that you don’t want to show, like addresses or phone numbers. Grouping and hiding lets you temporarily hide these details before printing.
To group columns together, select the columns and use the shortcut Alt, A, G, G. A bracket on screen indicates the grouping. To hide the columns, you then press Alt, A, H. To show the hidden columns again, press Alt, A, J. When you print a spreadsheet, hidden columns will not be printed. You can group and hide rows using the same commands.
Group and Hide to make navigation easier (01:49)
In a large spreadsheet, like a financial model, scrolling through the whole sheet can be tedious. Grouping and hiding lets you divide the sheet into sections. We can then show and hide sections to navigate through the spreadsheet easily. We can also expand or hide all the sections at once using the numbers in the top left of the sheet.
Finally, hiding cells does not remove information from the spreadsheet. If you’re distributing a spreadsheet, make sure there’s no confidential information hidden in the sheet before you circulate the document.
In the previous lesson, we learned how to format the text within a cell. In this lesson, we'll learn how to group and hide cells on an Excel sheet. There're two reasons why you might want to group and hide columns of data. The first, has to do with printing. Let's say in our current sheet, we need to print off our customer orders without the address or phone number data showing. Instead of deleting or moving these columns to accomplish this, we can simply hide the contact details temporarily, before printing. To do this, we'll first select the column with Control + Spacebar, and then hold Shift + Right Arrow, to select the phone number column. We then need to group these columns with Alt + A + G + G.
When we group columns, you can see that a bracket appears above these columns to tell you that they're grouped. Now we need to hide these columns. And to do this, I'll use a shortcut, Alt + A + H.
And when I go to Print by pressing Control + P, you can see in our print preview, that the address and telephone number columns are hidden. I'll now press Escape to return to the Excel sheet.
If I want to show the hidden columns again, I can use a shortcut, Alt + A + J. And then to hide, again, Alt + A + H.
The commands for grouping and hiding rows are exactly the same. Let's say I'd like to hide the first five rows of our data set. I'll select the first row and then hold Shift + Down Arrow to select the next four.
Again, Alt + A + G + G to group, and we can hide with Alt + A + H, very simple.
Apart from printing, the other good reason for hiding columns and rows of data is to make your spreadsheets easier to navigate.
In this new Excel spreadsheet shown on screen, we have a financial model, that stretches for over 100 rows. To make this model easier to navigate, I'm going to group and hide each section of the model off camera under its heading. Now I can navigate the whole model from one single screen. If for example, I wanted to open this section on operating cashflows, I simply navigate to this row and press Alt + A + J, then I can close it again with Alt + A + H. In addition to these shortcuts, we may also want to expand or hide all of the sections at once. And to do this, we use the numbers in the top left hand corner.
If I navigate with a mouse and press the number two, you'll see that it expands all of the sections. And if I click back on number one, it will collapse all of the sections. Hiding cells in financial models does make them easier to navigate, but it also comes with a health warning. I can recall a number of occasions in my own career when spreadsheets have been circulated externally with confidential information hidden in rows or columns. So if you're keen on grouping and hiding data in your spreadsheets, be sure that no confidential information is hidden before circulating the document.