Creating Row Hierarchies

  • Updated

In Spreadsheet.com, indenting and outdenting rows allows you to create hierarchical relationships between rows in your worksheet. More than just a tab, indenting and outdenting turns rows into "parents" with "children." Sometimes called "grouping", indenting and outdenting rows combines groups with tabs to 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. For instance, to denote subtasks, managers and direct reports, or budget line items that roll up. Hierarchies can be especially useful in complex project plans where, for example, the parent row due date is automatically rolled up from the due dates of all the child tasks.

Topics in this Article:

About 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 the "child" or a "Level 2" row. Two indents in are "grand-child" or a "Level 3" rows, etc. 

blobid0.png

Hierarchies allow you to group rows together in parent or child relationships that can then be collapsed to ride the rows. Collapsing a row hides the entire row from view, and is visually set off by a plus sign mceclip0.png to note that some child rows are collapsed under the parent row.

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.

How to Create Hierarchies

To indent data in a row and make one row child to another row, select the indent rowsmceclip8.png button in the toolbar, and that row will become the child of the one above it. Or reverse this with the outdent rows mceclip9.png 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 mceclip0.png 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 (rows 9 and 10) to our Product 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 all the sub-tasks under our Product Plan.

Indenting_and_Collapsing.gif

You can also indent and outdent rows by right-clicking on the row header and selecting Indent or Outdent rows from the menu options. For more on indenting rows, see the linked article here

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 minus sign next to the parent row mceclip0.png.

mceclip0.png

Clicking on the minus sign will collapse the children rows beneath that parent row

blobid1.png

Similarly, parent rows with their children collapsed can be expanded by clicking on the plus sign mceclip0.png.

blobid3.png

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. For instance:

blobid4.png

Hierarchy Behavior

Parent-child relationships have a few unique behaviors to note:

  • 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.
  • Children can only have one parent.

Hierarchies Formulas and 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 might inherit the due date of a child subtasks beneath it.

The full set of hierarchy functions includes:

Each function takes an optional cell reference as a parameter.
 
For instance, if you don't pass a value to the DESCENDANTCELLS() function, the function will refer to all children below the current cell in the hierarchy. To retrieve the descendants of a specific cell, you can pass a cell reference to the function, such as DESCENDANTCELLS(A9), to retrieve all child cells below cell A9.
 
Other examples:
  • 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 the direct child rows of Row 5.
  • DESCENDANTCELLS()
    Returns an array of cell values for any descendant rows of row and column it was called from.
  • MAXA(DESCENDANTCELLS())
    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.

MAXA_Descendent_in_parent.gif

Hierarchies in Kanban Views

Kanban views allow you to show or hide rows based on parent-child relationships. Kanban views have a Levels mceclip2.png button from which you can show all levels, only parents, children, grandchildren, etc.

mceclip1.png

For instance, in a project plan like the one below, some tasks are children of others. Here, the "Align with stakeholders on requirements prioritization" task is a chid to "Business Requirements" which is a child that lives under "Product Plan."

mceclip3.png

In our Kanban view, if we show all levels then we'll see all three tasks under their corresponding status:

mceclip5.png

But if we show only level 3 rows, then we'll only see the "Align with stakeholders..." task.

mceclip6.png

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.