Excel Tip: Collapsing Rows with Structured Data

Andrew O'Connor
Relken Engineering
Have a question or want to speak with Andrew O'Connor ? Contact us with your details.

Problem Statement

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:

Before

Project Plan which is difficult to read   

After

            Project Plan using Outline Grouping Levels

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.

MS Excel Outline Levels 1   MS Excel Outline Level 2     MS Excel Outline Level 3

(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.  

Drill down in MS Excel Outline Group Levels

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.   

Level adjustment in Quick Launch Toolbar

  • 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.

Excel 2013 Outline levels settings

Changing direction of summary rows

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.

 

Check Automatic Styles for Outline Levels in MS Excel

The styles can be modified by selecting Home > Styles > Downbox​.  Right click on the RowLevel_x style, and select Modify.

 Change Outline Level Style

Warning

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). 

Related Topics

Example File

Relken Excel Tip - Outline Levels.xlsm

 

Comments

Submitted by enrique luengo (not verified) on

Enjoyed your concept, but I tried this implementation (not trailing point on structured numbering, or just provide outline level in column "A"):

Const lgBase As Long = 0

Private Sub sLevel_Agroupation()
Dim rgTreeData As Excel.Range 'Range of selected cells
Dim rgRow As Excel.Range 'Range used in loop

Dim bGroup As Boolean
Dim bLevel As Boolean
Dim aLevel() As Long
Dim lgLevelMax As Long
Dim lgLevel As Long
Dim lgLevelParent As Long
Dim lgStep As Long
Dim lgR As Long
Dim lR_Start As Long
Dim lR_End As Long
Dim lC_Level As Long
Dim lR_StartTree As Long
Dim lR_EndTree As Long

Dim strErr As String
Dim lgRetVal As Long

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet
lgLevelMax = 8
lR_StartTree = 2
lC_Level = Application.InputBox(Prompt:="Column level", Title:="Column", Default:="$A:$A", Type:=8).Column

'Set outline configuration
With .Outline
Cells.ClearOutline
.AutomaticStyles = False
.SummaryRow = xlAbove
.SummaryColumn = xlLeft
End With

lR_StartTree = 2
lR_EndTree = .UsedRange.Rows.Count

'Detect if levels or numbers
bLevel = False
For lR_Start = lR_StartTree To lR_EndTree
'Get level or structured number
For lgR = lR_Start + 1 To lR_EndTree
If VBA.Val(.Cells(lgR, lC_Level).Value2) = 0 Then Exit For
If .Cells(lgR, lC_Level).Value2 = .Cells(lR_Start, lC_Level).Value2 Then
bLevel = True
Exit For
End If
Next lgR
If bLevel = True Then Exit For
Next lR_Start

'If Cells references not provided then use the selection
'If rgTreeData Is Nothing Then
' Set rgTreeData = ActiveSheet.UsedRange
' 'Set rgTreeData = Application.InputBox(PRompt:="Select range", Title:="Tree range", Type:=8)
'End If

ReDim aLevel(lgBase To lR_EndTree - lR_StartTree + lgBase)
If Not bLevel Then
'Get levels into array
For lgR = lR_StartTree To lR_EndTree
aLevel(lgR - lR_StartTree + lgBase) = UBound(VBA.Split(VBA.Trim(.Cells(lgR, lC_Level).Value2), ".")) + (1 - lgBase)
Next lgR
Else
'Store levels into array
For lgR = lR_StartTree To lR_EndTree
aLevel(lgR - lR_StartTree + lgBase) = VBA.CLng(VBA.Val(.Cells(lgR, lC_Level).Value2))
Next lgR
End If

For lgR = lR_StartTree To lR_EndTree - 1
lgLevel = aLevel(lgR - lR_StartTree + lgBase)
If lgLevel > lgLevelMax Then GoTo ErrLevel
If lgLevel < aLevel(1 + lgR - lR_StartTree + lgBase) Then
lR_End = lgR
Do
lR_End = lR_End + 1
If lR_End >= lR_EndTree Then Exit Do
Loop While aLevel(1 + lR_End - lR_StartTree + lgBase) > lgLevel
'Rows(lgR + 1 & ":" & lR_End).Rows.OutlineLevel = lgLevel
Rows(lgR + 1 & ":" & lR_End).Rows.Group
End If
ResumeTree:
Next lgR
End With

ExitProc:
'Deactivate error control
On Error GoTo 0
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

Exit Sub

ErrControl:
lgRetVal = VBA.MsgBox(strErr, vbCritical, "W A R N I N G")
If lgRetVal = vbYes Then
Resume ResumeTree
Else
Resume ExitProc
End If

ErrLevel:
strErr = "Level is above Max level [8]"
lgRetVal = VBA.MsgBox(strErr, vbCritical, "W A R N I N G")
Resume ExitProc
End Sub

Private Sub sLevel_Set()
Dim lgRetVal As Long
Dim lgR As Long
Dim lR_StartTree As Long
Dim lR_EndTree As Long
Dim aLevel() As Long
Dim lgLevel As Long
Dim lC_Level As Long
Dim strStructureParent As String
Dim strStructureNumber As String
Dim lgStructureNumber As Long
Dim aStructureNumber() As String
Dim bLevel As Boolean
Dim strErr As String
Dim lgLevelStart As Long

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

lC_Level = 1
bLevel = False
lgLevelStart = 1
With ActiveSheet
lR_StartTree = 2
lR_EndTree = .UsedRange.Rows.Count

ReDim aLevel(lgBase To lR_EndTree - lR_StartTree + lgBase)
'Store levels into array
For lgR = lR_StartTree To lR_EndTree
aLevel(lgR - lR_StartTree + lgBase) = .Rows(lgR).Rows.OutlineLevel
Next lgR

If bLevel Then
For lgR = lR_StartTree To lR_EndTree
.Cells(lgR, lC_Level).Value2 = .Rows(lgR).Rows.OutlineLevel
Next lgR

Else
strStructureNumber = VBA.CStr(lgLevelStart)
strStructureParent = strStructureNumber
With .Cells(lR_StartTree, lC_Level)
.NumberFormat = "@"
.Value2 = strStructureNumber
End With

For lgR = lR_StartTree + 1 To lR_EndTree
If aLevel(lgR - lR_StartTree + lgBase) = aLevel(lgR - lR_StartTree + lgBase - 1) Then
If aLevel(lgR - lR_StartTree + lgBase) = 1 Then
strStructureNumber = VBA.CStr(VBA.Val(strStructureParent) + 1)
strStructureParent = strStructureNumber
ElseIf aLevel(lgR - lR_StartTree + lgBase) > 1 Then
aStructureNumber() = VBA.Split(strStructureParent, ".")
lgStructureNumber = lgStructureNumber + 1
strStructureNumber = VBA.CStr(strStructureParent & "." & VBA.CStr(lgStructureNumber))
End If

ElseIf aLevel(lgR - lR_StartTree + lgBase) > aLevel(lgR - lR_StartTree + lgBase - 1) Then
lgStructureNumber = 0
strStructureParent = strStructureNumber
aStructureNumber() = VBA.Split(strStructureParent, ".")
lgStructureNumber = lgStructureNumber + 1
strStructureNumber = VBA.CStr(strStructureParent & "." & VBA.CStr(lgStructureNumber))

ElseIf aLevel(lgR - lR_StartTree + lgBase) < aLevel(lgR - lR_StartTree + lgBase - 1) Then
aStructureNumber() = VBA.Split(strStructureParent, ".")
strStructureParent = vbNullString
For lgLevel = LBound(aStructureNumber) To (aLevel(lgR - lR_StartTree + lgBase) - 1 - (1 - lgBase))
strStructureParent = strStructureParent & aStructureNumber(lgLevel) & "."
Next lgLevel
strStructureNumber = strStructureParent & (VBA.Val(aStructureNumber(lgLevel) + 1))
strStructureParent = strStructureNumber
End If
With .Cells(lgR, lC_Level)
.NumberFormat = "@"
.Value2 = strStructureNumber
End With
Next lgR
End If
End With

ExitProc:
'Deactivate error control
On Error GoTo 0
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

Exit Sub

ErrControl:
strErr = "Couldn't set level (or structure)"
lgRetVal = VBA.MsgBox(strErr, vbCritical, "W A R N I N G")
Resume ExitProc
End Sub