In Spreadsheet.com, indenting and outdenting rows allows you to create hierarchical relationships between rows. More than just a tab, indenting and outdenting turns rows into "parents" with "children." 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 data above or below a given row in the hierarchy.
Hierarchies are useful when you have rows of data that are related to each other. For instance, to denote subtasks, managers, or direct reports, or budget line items that roll-up. Hierarchies can be especially useful in complex project plans where, for example, parent row due date values are automatically rolled up from the child due dates.
Indenting and Outdenting
In Spreadsheet.com, rows can be indented and thereby become "children" to their "parent" row. To indent data in a row, select the button in the toolbar, and that row will become the child of the parent row above it. Or reverse your indent with the outdenting button in the toolbar. You can pick any cell in a row and the entire row will indent or outdent to the row above it.
Indenting a row makes a collapse icon appear in the parent cell that is in the primary column. For instance, in the animation below, we've recently identified and added two new tasks to our Product Launch plan. Both tasks are children of the Business Requirements task, so we indent them to be children to that parent. Then we test the collapse and expand buttons to show that we can view or hide these sub-tasks.
Parent-child relationships have a few unique behaviors worth calling out:
- 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 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.
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:
Returns the 'C' column value in the parent row of Row 10.
Returns the 'B' column values (as an array) of all the direct child rows of Row 5.
Returns an array of cell values for any descendant rows of row and column it was called from.
By wrapping a call to DESCENDANTCELLS() with the MAXA function, such as MAXA(DESCENDANTCELLS()), we can easily retrieve the maximum value of all descendant cells below a given cell in the row hierarchy.
This is especially useful for parents to inherit the values of their descendants. For instance, in the animation below we change the end date value of our Business Requirements row to match the max end date of its descendants.
Hierarchies in Kanban Views
Kanban views allow you to show or hide rows based on parent-child relationships. Specifically, Kanban views have a Levels button from which you can show all levels, only parents, children, grandchildren, etc.
For instance, in a project plan like the one below, some tasks are children of others. Here, the "Gather Requirements from Stakeholders" task is a sub-task of "Business Requirements" which is a sub-task that lives under "Product Plan."
In our Kanban view, if we show all levels then we'll see all three tasks:
But if we show only level 3 rows, we'll only see the "Gather Requirements from Key Stakeholders" task.