MS Excel outline features

Unless you know exactly what you’re looking for it’s easy to overlook that Excel has some outline capabilities built-in.

Tree Elements in Ms Excel

Each row and column in Excel have a property called “Level”.

Ms Excel Outline Controls

This section covers the visible controls that Ms Excel exposes when columns or rows have outline levels assigned. These controls are not part of TreeWiz, but we make use of them by setting the outline levels according to one of the seven (7) types of outline structure.

You can see these controls in the screenshot below, highlighted by the red outline. This screenshot shows the controls after we’ve applied the “ Group by Outline ID” function with the values in Column B selected from Row 6 to the bottom of the table (not shown).

There are two control sets:

  1. Level Expand / Collapse buttons: at the top there are 3 square buttons labelled “1”, “2”, and “3”. These control the expansion or collapse of the whole grouped area according to the level. So if you click on “ 2” then only rows with levels between 1 and 2 are shown
  2. Node Expand / Collapse buttons: these buttons are shown at every row or column that is a “ parent”. You can also see a line that extends down or across to show the span of that parent’s children. Clicking on the node will collapse or expand that node individually.

Figure 7: MS Excel Outline Controls

Ms Excel Outline Functions

The only real way to active the outline controls in Excel is to use the grouping function, which you can find in the “Data” tab.

There are two grouping functions:

  1. Group: which allows the user to set grouping levels manually by selecting columns or rows and applying the group function. The level of grouping is determined by the sequence of rows or columns that you group in successive grouping actions.
  2. Auto-Outline: which invokes an automatic grouping function that is determined by subtotal formulas in your data. So if you have a worksheet of financial data with sub-totals and grand totals (either vertically or horizontally), Excel will use those totals to determine the grouping level.

Both these functions are very useful for relatively structured use cases in which the grouping levels are either easily codified (e.g. time hierarchies such as months, quarters, and years), or where there is already a formula set that implies the outline structure.

But these functions don’t help you much if you’re working with large hierarchies, data that doesn’t have formulas or that changes regularly and doesn’t have a pre-defined structure. Excel also can’t help you if your data’s outline structure is implied by one of the many ways in which users choose to show hierarchy, such as indented cells, character padding or column offsets.

This is where TreeWiz comes into play.

A great use-case for TreeWiz is when you are developing an outline structure in the first place, which might then be used in a more regular grouping scenario within Ms Excel.

Excel Native Outline Support

First let’s just repeat Figure 1 here for convenience.

Ms Excel supports only two core features to allow tree structures:

  1. “Nodes” – are whole rows or whole columns. If you’re grouping by rows, then each cell on that row is contained in the same node structure.
  2. Outline “Level” – Excel supports eight levels from 1 to 8. The “Root” node is implied – if the nodes are rows or columns, then the “root” node is the worksheet that contains those rows and columns.

Excel may only support an outline level, but that is sufficient for TreeWiz to build a complete picture of your tree, because of the row and column structure. But remember that the full tree is implied by row or column sequence – it’s not an explicit relationship. If you move a row or a column then that breaks the relationship, and the tree would need to be rebuilt.

Also, keep in mind that the “Level” of an Excel row or column is a hidden, built-in value that cannot be set from the User Interface.

But with those characteristics understood, this still gives us the ability to set up powerful hierarchical structures in Excel.