Excel’s automatic outlining feature makes outlining a worksheet fairly straightforward. Automatic outlining works best with numerical data organized into groups and sub groups by formulas or functions. The following worksheet, for example, contains monthly financial data for a business, organized into quarterly and yearly totals using formulas and the SUM function:

It can be difficult to discern quarterly and yearly totals at a glance because these figures are lost in with all of the other data.

To automatically outline this worksheet, click the arrow next to the Group button in the Outline group of the Data tab:

This action will display a menu with two options: Group and Auto Outline. If you click the Auto Outline button, the spreadsheet will be outlined automatically:

Here are the results of Excel’s automatic outline:

All of the original data is shown, as well as outline group indicators (the thick black lines that look like large brackets) and collapse buttons (marked with a minus sign). You can see that the quarters (sets of three months) are grouped together and summarized by Quarterly totals, and that each year has been grouped to be summarized by Yearly Totals. Notice also that the columns Supplies, Wages, and Utilities have been grouped under Total Expenses, and that there is an overarching group of all columns under Profit.

In the image that follows, you can see the summary results of Excel’s automatic outline. The original information is still available in all of its detail, but it is now presented in a summary view, showing only the yearly profit totals. Of course, automatic outlines will differ from worksheet to worksheet depending on your headings and data and the way they are organized.

We’ll look at how to expand and collapse levels in the next lesson.

To remove the outlining from your worksheet, click Data → Ungroup → Clear Outline:

Displaying and Collapsing Levels

Here is an outlined spreadsheet:

Notice that there are numbered buttons arranged in a row beside the column letters and also in a column above the row numbers. Clicking on one of the numbered buttons arranged in a row will expand the rows in the worksheet to provide a given level of detail. Clicking on one of the numbered buttons arranged in a column will expand the columns in a similar way.

The view of the data in the preceding image is provided by the buttons numbered 1. Clicking on the number 2 buttons will expand the worksheet to the second level of detail for the rows, columns, or both as required:

This is the same worksheet after both number 2 buttons have been clicked. You can see that the worksheet rows and columns have been expanded to show another, secondary level of detail. You can now see rows with quarterly totals as well as yearly totals. You can also see columns with figures for Income and Total Expenses.

If you click on an expand button, marked with a plus sign (+), a specific section of the outlined worksheet corresponding to the button will be shown. Clicking the collapse (–) button will collapse the corresponding expanded section.

If you click the number “3“ button for the rows and columns, all levels of detail will be expanded to expose all of the data, as shown in the original picture
at the beginning of this lesson.

Remember that with outlines, only the data that is visible on your screen will be printed. This is a great way to print only the pertinent information from a large or complicated worksheet. You can expand and collapse the outlined worksheet with the numbered outline buttons, or with the expansion (+) and collapse (–) buttons, to reveal the level of detail that you want in your printed copy.

Download our Useful Planning Contents:

Name

Email address:

Leave this field empty if you're human: