HomeGoogle DocsSpreadsheetFormula Based Conditional Indentation in Google Sheets

Formula Based Conditional Indentation in Google Sheets

Published on

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.

Example

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.

Conditional Indentation Example in Google Sheets

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:

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

4 COMMENTS

  1. Hi,

    I came up with this simple formula 🙂

    =if(ISBLANK(A2), "", text(B2,concat(Rept(" ",2*A2), "@")))

    It is more compact, removes the clutter of the switch statement, and can grow indefinitely.

    Best wishes,

    Hassan Shahin

LEAVE A REPLY

Please enter your comment!
Please enter your name here