Quick Start: Indenting Rows to Create Hierarchies

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

More than just a visual indicator of hierarchy, indenting and outdenting established "parent" and "child" relationships between rows, and unique Hierarchy Formulas allow you reference specific data within the hierarchy. In Kanban views, row hierarchies allow you to sort by level, showing all rows or only those at a specific level in the hierarchy, like parent rows.

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

Indenting and Outdenting

By indenting a row, it becomes a "child" row to the "parent" above it. To indent data in a row, select the indent button mceclip8.png in the toolbar, and that row will become the child of the parent row above it. Or reverse your indent with the outdent button mceclip9.png in the toolbar. You can pick any cell in a row and the entire row will indent or outdent to the row above it.

Indent-Button-Locate.jpg

When you indent a row, a collapse icon mceclip0.png will appear in the primary column of the parent row. Clicking it will collapse all child rows below it, and clicking it again will expand them.

Collapse-Expand.gif

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 our task list, the "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.

In the example below, we use the function

=SUM(DESCENDANTCELLS())

in C9 to take the sum of the values of the three child cells in Column C (C10, C11, C12).

Formula-Example.jpg

Hierarchies in Kanban Views

We've explored hierarchies in Kanban views previously in our Quick Start series. 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.

Levels-Dropdown.jpg

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

Without-Kanban.jpg

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.

Show-All-Hierarchy.jpg

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.

Show-Level-1-Hierarchy.jpg

In the next part of our Quick Start series, learn about working with automations and integrations ➡️.