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 are two reasons why you might want to group and hide columns of data. The first is 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 Ctrl + Space bar, and then hold Shift and the right arrow to select the phone number column. We then need to group these columns with Alt + AGG.
When we group columns, you can see that a bracket appears above the columns to tell you that they are grouped. Now we need to hide these columns. And to do this, I'll use the shortcut Alt + AH.
And then when I go to print by pressing Ctrl + P, you can see in our print preview that the address and telephone number columns are hidden. I'll now press Esc to return to the Excel sheet. If I want to show the hidden columns again, I can use the shortcut Alt + AJ.
And then to hide, again, Alt + AH. 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 dataset. I'll select the first row and then hold Shift and down arrow to select the next four.
Again, Alt + AGG to group. And we can hide with Alt + AH.
Very simple. Apart from printing, the other good reason for hiding columns and rows of data is to make your spreadsheet easier to navigate. In this new Excel spreadsheet shown onscreen, 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 the section on operating cash flows, I simply navigate to this row and press Alt + AJ.
Then I can close it again with Alt + AH. 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 the 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.