You probably know that you can hide columns or rows in an Excel spreadsheet simply by selecting one or more rows or columns (for simplicity, I’ll only talk about rows, but everything here applies to columns also), then right-clicking and choosing Hide. You can unhide them by selecting a range of rows that includes the hidden rows, right click, and choose Unhide. This is very useful if you have information in your spreadsheet that you don’t want to display but is needed for other calculations. I also see people hiding rows temporarily when they don’t want to see all of the detail. That works fine if you only want to view the spreadsheet like that occasionally. But if you routinely want to choose between hiding or showing certain rows you can use the Group command.
Start by selecting the rows you want to be able to hide, then click on the Data tab. On the right end of the ribbon, in the Outline section, is the Group button. Click it and a line will appear next to the selected rows in the margin and a “-” button will appear in the next row down (presumable where those rows are totaled or summarized). Click the – button and rows will be rolled up, or hidden, and the – button changes to +. Click the + button to expand the section again or unhide the rows. If you have inserted several groups you can use the 1 and 2 buttons at the top to expand or collapse all of them at the same time. You can have groups within groups so you can show/hide multiple levels of detail. To undo your grouping, select the rows again and click the Ungroup button.
At the bottom of the Group button, is a down arrow that gives you the Auto Outline option. When you choose this option, Excel will examine your worksheet and automatically insert groups anywhere that it sees a total.
If you like that option, you should also read this previous tip on automatically inserting subtotals and grand totals.
