Best Microsoft Excel Bloggers

Monday, June 7, 2010

Outlining - A Feature Designed for the Accountant

Last week, I attended a great seminar put on by Jon Peltier and Alex Kerin on Charts and Dashboards. I will be sharing some of that information with your shortly.  But in the meantime, I thought you might be interested in the Outline Feature. A useful feature that is often overlooked.

The Outline feature was designed with the CPA in mind. The Outline feature allows you to gain a quick overall view of a complex worksheet such as a P&L or a balance sheet. This feature allows specified row and /or columns to be expanded or collapsed which enables the user to work with details or summary information on an as needed basis. You can get as detailed or as high level as you want.

Excel allows you to create an automatic outline where the program determines what should be grouped through the Auto Outline feature or the user can manually group rows and columns. If you are one of those people who create empty rows within your data so that it looks nice then the Auto Outline feature will not work for you. And yes, the Auto Outline feature looks at the formulas in your worksheet and that is how it determines how to group so if you don't have any formulas in the worksheet then the Grouping feature will not work for you.

How to use the Outline feature
1. Make sure that your mouse is inside the data range
2. Select the Data tab
3. Click Group
4. Click Auto Outline

Notice the horizontal line above the column headings and the vertical lines with minus symbols to the left of the row numbers.

5. Click on minus above Column G and notice that the week detail is hidden. What you are left with is just the Total Month.

Click on the Level 1 button to hide all the detail rows or use the minus at rows 7 and 12 to manually hide the detail rows.

If you click on the + beside Row 7 or row 12 and see that the hidden information is now displayed

To expand or collapse specific columns or rows, click on the outline buttons. Bars connected to box containing a minus and a plus identify groupings of data. The minus outline button collapses rows or columns and the plus sign expands the outline. Individual rows or columns at the lowest level of a grouping are identified by black dots. The building block buttons allow the user to select the levels of outlined desired. Levels can range between 1 and 8 depending on the complexity of the worksheet.

Manual Outline

Excel’s Auto Outline does a great job however at times it may not work the way you wish particularly if you have a lot of blank rows. In that case, Excel allows you to manually select columns or rows and outline them yourself.

To manually outline data:

1. Select a group of rows or columns that you want to group
2. Select the Data tab
3. Click Group
4. Select  if you want to group by columns or rows

5. Click OK


No comments:

Post a Comment

Ms. Excel- Resident Excel Geek