Ozgrid, Experts in Microsoft Excel Spreadsheets




Excel Training Level 2 Lesson 30

Download the associated  Workbook for this lesson




Outlining is a means of viewing summary information as required by collapsing or expanding to hide or display detail.

You can organise worksheet data into summary and detail levels.  An example of how this could be used is that you could have numeric data organised into specific groups, such as divisions or departments within your company, sales of a product, sales by salesperson etc. and display or hide the information that you require. You can identify subtotals and hide or collapse detail so that only subtotals appear on the screen. Outlining will also help you make detail data disappear so that only the higher level subtotals remain visible when you are using larger worksheets.  An outlined Worksheet will print exactly as it appears on the screen. This makes Outlining an extremely useful presentation tool.

You can nominate summary levels and collapse the worksheet so that only the summary sub-totals are displayed; this would be useful if you only wanted to show quarterly totals and not each month. When you Outline a worksheet within Excel, buttons will appear around the rows and/or columns that have been outlined. Buttons with the numbers 1, 2, 3 indicate an outlining level. These buttons can be clicked on to expand or collapse all summary levels to that level. You will also notice the collapse button (-) and an expand (+) button. The collapse button will indicate that the associated summary level has been expanded to show the detail levels. Click the button and you will collapse that summary level and hide the detail levels. The expand button works in the same way. It indicates that the associated summary level has been collapsed to hide the detail levels. When clicked, this button will expand the summary level and display the detail levels.

There are two ways you can create an Outline, automatically or manually. We will first look at creating an Automatic Outline.

Creating an Automatic Outline

If you create an Automatic Outline within Excel, it creates an outline structure based on formulas and their dependent ranges. Formula references must consistently point in the same direction. Usually, summary columns refers to detail columns to the left and summary rows refer to detail rows above.

Try this.

  1. Open the attached workbook (Mybusinessbudget.xls) which contains monthly expenditure for a small business.

  2. Select the Automatic worksheet and click in cell A1 (note that it is important that you select a single cell as this will tell Excel that you wish to outline the whole worksheet. If you select a range, only the rows and columns within that range will be Outlined).

  3. Select Data>Group and Outline/Auto Outline

  4. Click on the number 1 in the horizontal row of buttons at the left end of the window. Notice how the row details collapse leaving only the Total Expenses row, which is the first heading level) in view.

  5. Click on the number 1 in the vertical column of buttons at the top of the window. You will now notice columns B – I are hidden and only column J (Total) is visible.

  6. Click on the number 2 in the horizontal row of buttons at the left end of the window, then click on number 2 in the vertical row of buttons at the top of the window. Notice now that the secondary level headings are displayed.

  7. Now click on the + (plus) button to the left of Total Labour and notice how the Total Labour details have been expanded.

  8. Click on the + (plus) button above “2nd Quarter” (column I) and notice how the detail has been expanded.

  9. Have a play with the other buttons and see what happens.

  10. If the outline is no longer required, select Data>Group and Outline/Clear Outline. Note that if you save a workbook with an Outline in it, the Outline will be active when you re-open the workbook.

Creating a Manual Outline

You can create a Manual Outline yourself by grouping items to your own requirements.

Try this:

  1. Click on the Manual worksheet tab. Notice that this worksheet contains expense forecasts for two expense categories and two divisions.

  2. Select cell A1, then select Data>Group and Outline/Auto Outline and have a look at how an automatic Outline would look if you used that option.

  3. Clear the Outline by going to Data>Group and Outline/Clear Outline

  4. Highlight rows 6 – 18 inclusive. Select Data>Group and Outline/Group. Notice how these rows are now grouped as the detail rows for the totals showing in row 19.

  5. Highlight both rows 7 and 8 and select Data>Group and Outline/Group. You will notice how a sub-group has now been created.

  6. Highlight rows 12 and 13 and select Data>Group and Outline/Group to create another sub-group.

  7. Highlight rows 17 and 18 and select Data>Group and Outline/Group to create a final sub-group.

  8. Experiment with the various Outline buttons and notice how your data can be expanded and collapsed.

A good tip for when your worksheet is structured in such a way that you require manual Outlining, or you only wish to outline part of your worksheet, is to group in layers, starting from the top and working down. You can then develop a comprehensive system of outlining in your Worksheet with relative ease allowing you to maximum functionality of this useful feature.

So as you can see, Outline view will show you your document structure in a very easy to use manner. Remember, the indentations and symbols in Outline view do not affect the way your document looks in Normal view and do not print.