Outlining Data

If your data contains totals after each section then Excel may be able to automatically outline it for you so you can expand and contract sections to hide the detail and see only the summary.  The example below shows a worksheet that is suitable for outlining, a SUM function has been used to total each year’s rainfall.

To outline this worksheet, click anywhere in the data and then click on the Data drop-down menu and choose Group and Outline and then Auto Outline.  Excel will find the sections that need grouping and an outline bar will appear to the left of your worksheet as shown below .

If you click on the number 1 at the top of the outlining bar you will collapse your data to only see the summary level as shown below .

If you cannot see the outline symbols then you will need to click on the Tools drop-down menu and choose Options.  Click on the View tab of the Options window and make sure there is a tick by Outline Symbols.  You can also show or hide the outline symbols by pressing CTRL and 8 together.

If your data is arranged in a suitable order you can get Excel to automatically add sub-totals to the data for you.

To see a particular section’s data you can click on the + sign beside that section to expand it, when a section has been expanded a – sign then appears beside it which can be used to collapse it again.  If your data is arranged in columns instead of rows the outlining will still work if you have a summary column to the right of each group of columns.  The outline bar will appear at the top of the worksheet if the data is arranged in columns.

To see all your data, click on the highest number (2 in our example) at the top of the outline bar, this will expand all the levels of your data.  To remove an outline, click on the Data drop-down menu and choose Group and Outline and then Clear Outline.

If you do not want to outline your whole worksheet, you can group a section of rows or columns manually by highlighting them and then clicking on the Data drop-down menu and choosing Group and Outline and then Group.  You should not include the summary row or column in the area you select for grouping, just select the rows or columns containing that section’s data.

Click here to return to the Excel index.