Do you want to learn how to conditionally indent values in Google Sheets? Look no further! In this tutorial, I will show you a formula-based approach to achieve conditional indentation, even though there is no built-in tool for it.
The Need for Conditional Indentation
Indentation is commonly used in document writing to represent the beginning of a paragraph. But how can we apply the same concept to spreadsheets?
Conditional indentation in Google Sheets allows you to control the number of spaces in an indent based on values in a column. For example, you can indent text based on specific conditions, creating a hierarchy-like structure.
Check out this quick example:
In this example, the text in column B is conditionally indented in column C based on the values in column A. This allows you to hide column B and keep a clean and organized spreadsheet.
Five Tier Conditional Indentation Formula in Google Sheets
To achieve conditional indentation, we will use a simple formula. Let’s break it down step by step.
First, let’s understand the sample data in columns A and B:
In this example, the numbers in column A represent the conditions for indentation. The formula will indent the corresponding text in column B based on these conditions.
Now, here’s the formula you can use in cell C2:
=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 ), ) )
This formula covers the range A2:B and provides a five-tier (0 to 4 indent) conditional indentation. You can easily increase the level by adding 6,text(B2:B," @"), before B2:B.
Formula Explanation and Two Alternatives
Let’s dive deeper into the formula and explore two alternative approaches.
The formula consists of two parts: the IF + LEN part and the SWITCH part. The generic form of the formula is as follows:
=ArrayFormula(if(len(A2:A),switch_formula),))
The switch formula is as follows:
switch( A2:A, 2,text(B2:B," @"), 3,text(B2:B," @"), 4,text(B2:B," @"), 5,text(B2:B," @"), B2:B )In this formula, we use the SWITCH function to compare cases to expressions. If the cases match the expression, the corresponding values are returned. Otherwise, the default values in column B are returned.
To achieve the same result, you can also use the IF or IFS function. Here are the alternative formulas:
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 these formulas are applicable to indent text strings only.
And that’s all there is to conditional indentation in Google Sheets! I hope you found this tutorial helpful. If you want to explore more Google Sheets tips and tricks, visit Crawlan.com. Happy indenting!
*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.*
