Clean Code for Google sheets: Advanced tips

In software development there is a very useful concept called “Clean Code”, introduced by “Uncle Bob” in his book.

Defined in one sentence: How much effort would it take for an outsider to understand the code and be able to work with it without problems.

Without going into details on how to write Clean code in any programming language, in this article I will give you 4 tips that will make your formulas much more understandable and maintainable in the long term.

Tip 1: Named ranges

In Google sheets when we make reference to a range of cells, we do it with what is called A1 Notation.

For example G2, G2:G100 or A1:H20.

In this case, the person reading the formula has to look at what is in column G.

Don’t:

Do:

How to name a range:

Tip 2: Using a named range for a Boolean variable

Boolean variables are those that result in TRUE or FALSE.

For example, if we put in a cell: = 2 > 4 the result of this will be FALSE since 2 is less than 4.

But… how do we use this to make our formulas more readable?

Generally, we abuse the IF or IFS in our formulas, which requires the person to go through more than 1 column to understand the logic.

For this reason, we can do the following:

In the above gif table, we can see that the database contains the gender and salary of the people.

If we wanted to bring the salary only for the female persons we should

=ArrayFormula(IF(E2:E=”Female”,G2:G,””))

Good practice, in this case, is to store the logic tested in the IF(E2:E=”Female”) as a boolean variable:

Don’t

Do

How to name a logic piece as a Range:

Tip 3: Indentation

Have you ever seen how to write software code? Does this sound familiar?

If you can see, the return is below and to the right of the IF.

This has some advantages:

- It is more readable

- You can see the hierarchies

- You can see the impact of each line

A good practice in Google sheets is to index our formulas to make them easier to read.

Don’t

Do

How to indent your formula:

In the editor you click:

- Windows: ctrl + enter

- Mac: command + enter

Then you can simply use the space key to move the lines to the right.

Tip 4: Comments

Did you see in the formula above this piece of logic?

What does it mean? It is very difficult for the next person to understand what is going on if he/she does not have such an advanced level of Sheets.

So what we can do is to post comments:

Don’t

Do

How to add comments:

2 possibilities:

1. if the formula has a numerical output.

Formula =N() this formula will convert the text you enter to 0, so if you add 0 to the output of the formula, nothing is changed.

2. If the formula has a text as output

Formula =T(N(“Comment”))

In this case T(N()) will return an empty string that we can concatenate to the text and not alter the result.

Looking for the formulas? Here is the template

Looking for other G Suite workflow solutions? Check out the template gallery

Need help automating your spreadsheet? Get in touch