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.
Why group and hide cells?
Printing: If you need to remove some data columns for printing, grouping & hiding works very well.
Navigation: If your worksheet has a number of subsections, grouping & hiding makes navigation easier
- Make sure confidential information is not hidden when distributing Excel workbooks!
Useful keyboard shortcuts
ALT + A, G, G: Group selected rows / columns
ALT + A, H : Hide columns / rows
ALT + A, J : Show columns / rows
ALT + A, U, U: Ungroup selected rows / columns.
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 + A G G. 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 + A H. And now 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 Escape to return to the Excel sheet. If I want to show the hidden columns again, I can use the 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 and 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 spreadsheet 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 the section on operating cash flows, I simply navigate to this row and press Alt + A J. And 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 the mouse and press the number 2, you'll see that it expands all of the sections. And if I click back on number 1, 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.