Someone asked me the other day if they could create a timeline where different groups of data could be automatically hidden or shown by clicking on a - or + button. They must have seen this done in Excel before, because that is exactly what the Data > Group and Outline feature does.

Advertisement

Take a look at the image below. This is a screenshot from my Gantt Chart template. See the buttons to the left of the row numbers? The sub-tasks under Task Category 1 have been hidden (notice that the row numbers skip from 10 to 15), and if I clicked on the (+) button next to row 15, they would reappear. The [1][2][3] buttons in the corner can be used to expand or contract an entire level. Very cool.

Grouping and Outlining in Excel
Figure 1. Grouping and Outlining in a Gantt Chart

Note: Whether the grouping/outlining symbols are displayed or not is a setting stored with each individual spreadsheet. To display the symbols, go to Tools > Options > View tab, and make sure the 'Outline Symbols' box is checked.

Grouping and Ungrouping

To group rows all you need to do is select the rows that will be hidden/shown and go to Data > "Group and Outline" and select "Group..." or "Ungroup..."

Outlining with Manual Numbering

You can create manual outline numbering such as 1, 1.2, 1.2.3, etc. by converting the cells used for the outline numbers to text. If the number of items will never be greater than 9, you can use a custom number format such as #.#.#.# to display 4.234 as 4.2.3.4.

The problem with manual outline numbering is that when you make a change, you may need to manually change all the rest of the numbers in the outline.

Outlining with Automatic Numbering

The Work Breakdown Structure (WBS) numbering system used for project schedules and Gantt charts uses a numbered outline where # is a first-level task, #.# is a second-level task, #.#.# is a third-level task, etc.

If you never have more than 9 tasks within a given level, then you could use a custom number format #.#.# to display 2.34 as 2.3.4 and then you could just add 0.01 to the next task to get 2.35 which would display as 2.3.5.

To prevent reference errors when inserting or deleting rows, you can use the OFFSET function to increment the outline number (see the example in my article Volatile Functions - What's the Big Deal).

If you could end up with more than 9 items in a given level within your outline, the formula gets a lot more complicated. One solution to this problem would be to use a custom VBA function (see the reference below). However, it IS possible to do this without VBA or macros, as I have done with the formulas I created for the gantt chart template. I'm not going to explain the formula in detail, because in the gantt chart template all you have to do is copy the row with the formula that you want to use (there are different formulas for Level 1, Level 2, Level 3, etc.) and everything will work out.

If you simply MUST know how the formula works, then I'll just give you a place to start: The key is to use the SUBSTITUTE() function to replace a period "." with some other character and then use the FIND() function to locate the position of that character with the text.

References

Disclaimer: This article is meant for educational purposes only.

Cite This Article

To reference this article from your website or blog, please use something similar to the following citation:

- Wittwer, J.W., "Grouping and Outlining in Excel" from Vertex42.com