When creating project plans, FMECAs, RCMs, Work Breakdown Structures, Organisational Lists, Asset Structure or any other structured list of items in MS Excel it can be difficult to manage viewing the different levels of information. For example, it is difficult to edit and view the following project plan, and failure mode spreadsheets. This article will show you how rows can be collapsed, and the formatting applied based on the level each row is within the structured list. To do this we will be using the "Outline Group Data" functions within Excel.
Note that we also publish VBA code which can automatically create the structured numbers shown in this example, or given a structured number, the row level can automatically be set:
- Excel VBA Code - Create structured numbers
- Excel VBA Code - Set row level from structured numbers
Excel VBA Code - Get level of current row
Features of Outlines Grouped Data
Once rows have been set a particular level, the user can do the following tasks.
(1) Filter complete lists to a particular level. By using the appropriate outline level buttons (circled in red), the complete list can be filtered to the level selected (or above). For example, the following images show if I only select the first or second level tasks.
(2) Drill down into the structured list. By having the data filtered to a higher level, you can use the '+' and '-' buttons on the left to drill down to the appropriate level of detail.
Preparing the Workbook
In order to make this feature easy to use, you must do two things to your excel set up.
(1) Put the group and ungroup buttons into your quick launch area.
- In MS Excel 2007-2013 open the Data tab in the ribbon bar. Right click on the Group button and select Add to Quick Access Toolbar. Do the same for Ungroup.
- In MS Excel 2003 you'll have to rely upon shortcut keys Alt-Shift-RightArrow to Group and Alt-Shift-RightArrow to Ungroup.
(2) Change the direction which summary rows are displayed.
- In MS Excel 2007-2013 click the Data tab, in the Outline group, click the Outline dialog box launcher. Clear the Summary rows below detail check box.
- In MS Excel 2003 on the Data menu, point to Group and Outline, and then click Settings. Clear the Summary rows below detail check box.
Setting Row Levels
To set row levels, you can think of it as setting the 'indent' for each row. Simply select the rows you wish to change the level on, and select the Group or Ungroup button to move the row level left or right.
Setting Row Formatting Based on Level
It is possible to automatically format the rows based on the level.
- In MS Excel 2007-2013 click the Data tab, in the Outline group, click the Outline dialog box launcher. Check Automatic Styles check box.
- In MS Excel 2003 on the Data menu, point to Group and Outline, and then click Settings. Check Automatic Styles check box.
The styles can be modified by selecting Home > Styles > Downbox. Right click on the RowLevel_x style, and select Modify.
Using outline levels is a means to filter your data. There are other means to filter data using Data > Filter which users will be more familiar with. Using both together can produce unexpected results. Furthermore when copying and pasting using outline level filtering and other data filters, the response from MS Excel might not be consistent (check whether the hidden rows also received pasted information).
- Excel VBA Code - Create structured numbers. Creates the structured number for each row automatically based on the Outline Level.
- Excel VBA Code - Set row level from structured numbers. If you already have the structured number, this code will set the Outline Level automatically based on the number depth.
- Excel VBA Code - Get level of current row. This function will return the Outline Level for the current row.