Excel’s Auto Outline quickly hides data details to simplify viewing

Excel offers a simple outlining feature that’s helpful, but often overlooked by users. Auto Outline hides details, showing only the header and summarizing rows or columns. In a simple sheet, it’s overkill, but if you’re working with lots of data, a quick click is all that stands between you and this simplified view.

To illustrate this simple feature, we’ll hide the sales figures in the sheet below, as follows:

1. Select the data range. In this case, that’s A1:F14.
2. Click the Data tab. In Excel 2003, choose Group and Outline from the Data menu and skip to step 4. In Excel 2007, there’s an Outline option on the Data ribbon.
3. In the Outline group, click the Group option’s dropdown.
4. Choose Auto Outline.

The change isn’t obvious – look for the brackets above and to the left of the header cells. Using the outline is simple. Initially, each bracket displays a minus sign. Click it to remove the detail cells.

To display all the data, click the plus sign. Similarly, you can click the 1 and 2 icons to collapse and expand the sheet. To remove the outline, choose Clear Outline from the Ungroup dropdown.

Hiding

Auto Outline is simple to implement and easy to turn off if you don’t want to leave the outline in place. However, there’s more to it than first meets the eye. As you might know, I don’t recommend hiding columns and rows – it isn’t wrong, but users tend to forget they’ve hidden data and that forgetfulness can cause trouble. The outline feature can help when you need to hide data.

To temporarily hide a row or column of data, use this feature as follows:

1. Select the row or column you want to hide. For instance, select row 5 to hide the April data.
2. Click the Data tab. In Excel 2003, choose Group and Outline from the Data menu.
3. Click Group in the Outline group and Excel will display an outline bracket to the left of row 5.
4. To hide row 5, click the minus sign. (You can also click the Hide Detail icon in the Outline group.)

We hid a row, but using the same technique, you can hide a block of rows, or a single column or a block of columns. To remove the partial outline, click Ungroup in the Outline group. Note, this feature won’t work with a non-contiguous selection.

You’ll use Auto Outline when the summary rows contain formulas; Excel uses the formulas to distinguish between the values and the summary rows and columns. When the data range is literal values, set groupings manually. In addition, when using Auto Outline in 2003, the formulas must be to the right or below the detail values. If they aren’t, change the orientation via the Settings command from the Group and Outline submenu. The example has just one level of detail values and summarizing formulas. You can have up to eight! Also note, that Auto Outline doesn’t work with Excel’s new Table object.

For your convenience in testing this technique, we have made two example Excel worksheets available for download.