Using the PARENTCELL Function - Learn Spreadsheet.com

In a typical spreadsheet, there is no need for the PARENTCELL function. However, parent-child relationships can be created in spreadsheet.com using the Indent rows and Outdent rows buttons on the toolbar. When you indent a row, it becomes a child of the row above it. As shown below, a + icon appears in the primary column to the left of the parent value. Get the 🔗template used in this tutorial here and make your own copy to follow along.

Columns of spreadsheet data shown before and after indentation — Ident Rows – Before and After

Using the image above as the example, Rick, Jean, and Hall are typical values on the left. However, on the right, they are children of the value North in cell A2. Said another way, the cells in A3:A5 on the right are all children of the parent in cell A2.

Using + sign to collapse indented rows — Collapsing Indented Rows

The – sign also indicates that you can hide the indented values beneath it.

If you need a function to return the value of a cell’s parent, that’s where PARENTCELL comes in.

Contents

Purpose

The PARENTCELL function returns the value of a given cell’s parent.

Video Explanation

Tutorial video showing how to use the PARENTCELL function — PARENTCELL Video Tutorial

Syntax

=PARENTCELL([cell])

The value of the cell should be the column and row number, such as A9.
The [cell] argument is optional. When omitted, the function will return the parent of its containing cell.

Examples

Example 1 – Simple Indents

Data without indentations — No Indentations

=PARENTCELL(A3)

Returns -> [Blank]

Data with indentations which will then return values when used with the PARENTCELL function — Indentations Set

=PARENTCELL(A3)

Returns -> North

In this simple example, the list on the left above has no indentations. As a result, it isn’t apparent that Rick, Jean, and Hall belong to the North location. However, on the right, you can see the – sign to the left of North. This minus sign shows you that the values underneath belong to it.

The return value of the PARENTCELL function is dynamic. Therefore, if you were to add or remove indents, the result of the function would update accordingly.

Example 2 – Multi-Level Indents

Indents can be as many levels as you want. For instance, in the example image below, the indents created three levels. The first level is a city, the second level is a compass direction, and the third level is a person’s name.

Larger table of data with multiple levels of indents for the PARENTCELL function to work through — What cell is the parent of A19?

=PARENTCELL(A19)

Returns -> Chicago

The two top-level values in the table are Denver and Chicago. If you go down one level, you are in the compass directions. Therefore, the parent of West in cell A19 is the value of Chicago in cell A12.

Example 3 – Starting One Level Lower

Table with multiple levels of indented values for use with the PARENTCELL function — Parent-child relationship starting one level down

=PARENTCELL(A21)

Returns -> West

A parent-child relationship is only one level deep. Unlike the ANCESTORCELLS function, the PARENTCELL function only returns one value. In the case above, cell A21 has two ancestors, West and Chicago, but A21 has only one parent which is West.

Example 4 – Using in Other Columns

The PARENTCELL function does not need to be limited to the primary column.

SUM and CHILDCELL functions — Using SUM and CHILDCELLS to Create Subtotals

We’ve added subtotals in the parent rows for this following example with a combination of the SUM and CHILDCELLS functions. Now that we have our subtotals, we can use the PARENTCELL function to return the subtotal for the parent they belong to.

For example, using the data above:

=PARENTCELL(D6)

Returns -> 114.64