For conditional indentation in Google Sheets, we should use a helper (extra) column. Because we may require to use a formula for the same as at the time of writing this tutorial, there is no built-in tool for conditionally indent values in a column.

For indentation, we can use custom formatting and that I have already explained in my tutorial here – Increase Decrease Indent in Google Sheets [Macro].

In document writing, indentation (indent) is adding a space at the beginning of a line. It’s, generally, to represent the beginning of a paragraph.

In spreadsheets, we can use it to format text strings like in a hierarchy.

What about conditional indentation in Google Sheets?

In conditional indentation, the formula controls the number of spaces in the indent based on values in a column.

Here is a quick example before going to the formula and its explanation.

In this example, the formula in cell C2 uses the conditions in A2:A to conditionally indent the text in B2:B. The result is in C2:C. So you can hide the column B later.

Five Tier Conditional Indentation Formula in Google Sheets

Let’s understand the sample data, i.e. the values in the columns A and B first.

The numbers in column A are the conditions to indent. Based on the numbers in column A, we will conditionally indent the values in column B. The result will be in column C.

In column B, “Task 1”, “Task 2”, and “Task 3” are the main tasks that I don’t want to indent. So I put the number 1 against those values in column A, to specify no indent.

Wherever the # 2 appears in column A, the formula indents the corresponding texts.

When the number increases, the formula increases the indent. That’s what I have meant by a conditional indentation in Google Sheets.

I am going to provide you a 5 tier (0 to 4 indent) conditional indentation formula. You can easily increase the level as you want.

Insert the below formula in cell C2 which covers the range A2:B.

=ArrayFormula(
     if(
        len(A2:A),
        switch(
           A2:A,
           2,text(B2:B,"    @"),
           3,text(B2:B,"      @"),
           4,text(B2:B,"        @"),
           5,text(B2:B,"          @"),
           B2:B
        ),
     )
)

To add one more level to the above conditional indentation formula in Google Sheets do as follows.

Just insert the formula 6,text(B2:B," @"), before B2:B.

Formula Explanation and Two Alternatives

Let me split the formula part and explain each and every part to you. Actually, it’s a very simple formula using the SWITCH function.

There are two parts in the above conditional indentation formula in Google Sheets. They are the IF + LEN part and the SWITCH part.

The above formula is as per the following generic form formula.

=ArrayFormula(if(len(A2:A),switch_formula),))

It means if there are values in A2:A, execute the switch formula, else return blank.

The Switch formula is like this.

switch(
     A2:A,
     2,text(B2:B,"    @"),
     3,text(B2:B,"      @"),
     4,text(B2:B,"        @"),
     5,text(B2:B,"          @"),
     B2:B
)

In simple terms;

If A2:A = 2 insert 4 spaces, if A2:A = 3 insert 6 spaces, if A2:A = 4 insert 8 spaces, if A2:A = 5 insert 10 spaces, else return B2:B.

I have doubled the space in indentation to make the conditionally indented text visually appealing.

In detail;

Syntax: SWITCH(expression, case1, value1, [case2_or_default, …], [value2, …])

The Switch formula returns values by comparing cases to expressions. In the above example formula, the said expression is A2:A.

The numbers 2, 3, 4, and 5 are representing the arguments case1, case2, case3, and case4.

Similarly, the four TEXT formulas are representing the arguments value1, value2, value3, and value4.

If the cases don’t match the expression, the default values in B2:B will be returned.

We can also use the IF or IFS function to write the conditional indentation formula in Google Sheets. Here are them.

Using IF;

=ArrayFormula(
     if(
        A2:A=2,text(B2:B,"    @"),
        if(A2:A=3,text(B2:B,"      @"),
        if(A2:A=4,text(B2:B,"        @"),
        if(A2:A=5,text(B2:B,"          @"),
        B2:B)))
     )
)

Using IFS;

=ArrayFormula(
     if(
        len(A2:A),
        ifs(
           A2:A=2,text(B2:B,"    @"),
           A2:A=3,text(B2:B,"      @"),
           A2:A=4,text(B2:B,"        @"),
           A2:A=5,text(B2:B,"          @"),
           LEN(A2:A),B2:B
        ),
     )
)

All of the above formulas are applicable to indent text strings only.

That’s all about conditional indentation in Google Sheets.

Thanks for the stay, enjoy!

Resources:

• Inserting Bullet Points in Google Sheets.
• 5-Star Rating in Google Sheets Including Half Stars.
• Insert Special Characters Without Add-on in Google Sheets.
• Clean Function in Google Sheets and Non-Printable Characters.