Row Hierarchies

In Spreadsheet.com, indenting and outdenting rows allows you to create hierarchical relationships between rows.

Indented rows are more than just a visual indicator. Indenting (and outdenting) turns rows into "parents" and "children." Sometimes called "grouping", indenting and outdenting rows create hierarchical relationships that persist across Views. For instance, in Kanban Views you can sort by level, showing all rows, just parent rows, or only rows at a specific level in the hierarchy. In addition, there are unique hierarchy formulas that allow you to reference child data in the hierarchy.

Hierarchies are useful when you have rows of data that are related to each other, like denoting subtasks in a project plan or budget line items that roll-up to subtotals.

This article features Spreadsheet.com's Cap Table with Shareholder Database template and Product Launch Plan template. Explore the Template Gallery to find ready-to-use templates in over 20 different categories, or learn more about starting a new workbook from a Spreadsheet.com template.

Introduction to Row Hierarchies

In Spreadsheet.com, rows can be indented and thereby become "children" to their "parent" row. This ability has multiple names, including row hierarchies, "tabbing" or "indenting" rows, and grouping rows.

The top-level of a hierarchy is generally referred to as the "parent" row or a "Level 1" row. Rows one indent lower are known as "child" or a "Level 2" rows. Two indents in are "grandchild" or "Level 3" rows, etc.

Hierarchies allow you to group rows together in parent or child relationships that can then be collapsed to ride the rows. Hierarchies also change the way that your worksheet sorts data, with sorts only applying to the children within each parent row. That way different children do not get sorted between different parents.

To indent a row, you must have an editor role or higher. You can only indent entries in the primary column and cannot indent the first row.

Creating Row Hierarchies

By indenting a row, it becomes a "child" row to the "parent" above it. To indent or outdent a row, click the row action button to the left of the row index and select the desired option from the dropdown.

You can also access indent and outdent options from the toolbar.

For more information, see our article on Indenting Rows.

Collapsing and Expanding Rows

Once rows are defined as parents or children with indenting and outdenting, those rows can be visually collapsed or expanded to hide or display the information in the rows.

Parent rows that are already expanded and show all child rows will have a filled arrow in the primary column. Clicking on the arrow will collapse the children rows beneath that parent row

Similarly, parent rows with their children collapsed can be expanded by clicking on the arrow outline.

Your tables can have multiple levels of parents and children, with some or all of the children collapsed. Collapsing a parent row will hide all rows beneath the parent but does not collapse grand-children inside their parent's row, expanding the parent will return the children to their previous expanded or collapsed state with regards to their children (the grand-children).

The collapse and expansion buttons only appear in the table's primary column, by default this is the first column (column A). But the primary column can be changed and changing the primary column will move the collapse and expansion buttons.

Hierarchy Behavior

Parent-child relationships have a few unique behaviors worth noting:

Changes to the row hierarchy can only be made in unfiltered, unsorted views.
Parent-child relationships persist unchanged across all defined views.
Whenever a new row is inserted above an existing child row, the new row will inherit the same parent as the existing row.
Whenever a new row is inserted below an existing parent row, the new row becomes a child of that parent row.
When a row is moved, its child rows move with it.
When a row is deleted, its child rows are deleted with it.

Hierarchy Functions

Hierarchies allow you to use special Spreadsheet.com functions to refer to cells above or below a given cell in the row hierarchy. For instance, in a task list, a "Business Requirements" parent task can inherit the due dates of child subtasks.

The full set of hierarchy functions includes:

Each function takes an optional cell reference as parameter. For instance, if you don't pass a value to the DESCENDANTCELLS() function, Spreadsheet.com will reference all descendant cells. To retrieve the data of a specific descendant cell, pass a cell reference, such as DESCENDANTCELLS(A9), to retrieve all child cells below cell A9.

For example:

=PARENTCELL(C10)	Returns the 'C' column value in the parent row of Row 10
=CHILDCELLS(B5)	Returns the 'B' column values as an array of all direct child rows of Row 5
=DESCENDANTCELLS()	Returns an array of cell values for any descendant rows of the row and column it is called from
=MAXA(DESCENDANTCELLS())	By wrapping a call to "DESCENDANTCELLS()" with the "MAXA" function, returns the maximum value of all descendant cells below a given cell in the row hierarchy.

Hierarchies in Kanban Views

In a Kanban View, from the Views navigation bar, the Levels dropdown lets you configure what rows are shown in your Kanban view, when rows in the primary view rows are arranged into parent and child hierarchies.

In this Product Launch Workbook, tasks are organized into a three-level "parent," "child," and "grandchild" hierarchy. For example, in the sheet shown below, "Launch Schedule" (A9) is the child of "Business Requirements" (A8), which itself is the child of "Product Plan" (A6).

By default, the Kanban view shows "All levels" of the hierarchy. In the sheet shown below, all tasks are shown regardless of their hierarchical level.

By selecting "Level 1" from the Levels dropdown, the Kanban view will update to only show tasks from the top of the hierarchy and exclude any "child" or "grandchild" cells.

Hierarchies are an important tool to help you to group rows, tier rows, indent rows, hide rows, refer to groups of rows, and more. To see them in action, check out our templates, particularly the project plan templates. Or see our article on indenting rows for more on how to set up hierarchies.