Conditional format in Google Sheets

I don’t know about you, but for me this conditional formatting thing in Google Sheets has always been a bit of a headache when I’ve needed it.

I always end up doing the same thing, which is asking Google and trying to remember which website solved the problem for me the last time.

So now I’ve decided to write the steps and lay the groundwork for using conditional formatting. You can find in this template the examples that are going to be indicated in this post. If you want to be able to edit it, simply make a copy of it by clicking File Menu >> Make a copy .

As always, we are going to try to start with the basics, to add as we learn more.

What is conditional formatting in Google Sheets?

The conditional format offered by Google Sheets is nothing more than a technique to change the format of cells in a spreadsheet.

What is conditional formatting in Google Sheets for?

If you want to make certain cells more visible if a certain condition occurs, conditional formatting will help you make those cells stand out.

Imagine that you have a table with the balances of your accounts, and you want if any of them is negative, it will be highlighted with a red background. This is a clear use case for conditional formatting.

How to apply conditional formatting on an entire row?

It’s really not complicated at all, once you know the use of the $ character. Go for it!

  • Select the range of cells you want to format
  • Go to the conditional formatting menu
  • Select Custom Formula in the select of the rules format.

First click here.

And then choose Custom Formula.

  • Enter the formula

This is the most important step, and that we must understand if we do not want the next time we need this functionality, we will have to search for the answer in Google again.

The most important thing is to know how to indicate in the formula that we want to evaluate a certain value, for each row. That is, how to design the left part of the formula. It will be that simple

=$C2

Well, you may ask why? So let’s get to it!

There are several key points to understand.

  • With the $ symbol what we are doing is ‘locking’ the column. With this the Spreadsheet will know that it will always have to evaluate column C, and the 2 will be like this for row 2, but for row 3, this 2 will change to 3… and so on.
  • It is very important that the number that we indicate coincides with the number of the first row of the range that we have selected to evaluate. That is, if the range to evaluate is A2:F6, the first row to evaluate in our formula should be 2.

Once we have the left part of the formula defined, now we need to add the condition. In this case we simply want the content to be “Spain”, so we will indicate it like this ="Spain".

Examples of using conditional formatting

If you have read me before you will already know (and if not, I will tell you now) that I believe that the best way to learn is to do, so… make yourself comfortable, open a spreadsheet and accompany me by doing these examples that I leave by yourself down here.

Let’s see the following use cases of conditional formatting.

Based on the text content of a cell

To highlight in yellow the rows that belong to banks in Spain…

… the formula should be=$C2="Spain"

There is not much to explain here. Having understood why the left part of the formula, the right part is simply indicating that the value of the cell we want to be Spain . And that when this condition occurs, we are going to select the yellow color to highlight the row.

Based on the numeric content of a cell

The concept is the same as in the previous case, now the formula will change to indicate the numerical comparison.

Imagine that we want to highlight in red the rows that have a negative balance.

We will simply have to indicate that the balance column is less than zero:=$F2<0

If you look at the result obtained:

you will see how row 5, although it has a negative balance, is highlighted in yellow.

This is because when several rules meet the conditions, the format applied is that of the first rule that we have defined.

Based on the content of a cell check

In this sheet you have this example if you want to take a look.

To highlight the rows that have been checked in green, the custom formula will be like this: =$B2. You may be wondering where the right hand side of the formula is. Makes sense. There is no right part because the translation of the formula would be something like ‘When cell B2 is true’ I understand true as 1 and false as 0, or in this case, true when the check is active, false when not.

Multiconditions

Often we will need more than one thing to be true to highlight a row of a certain color.

We are going to be able to do this without any problem, thanks to the fact that the part on the right of the formula can be as complicated as you want.

Using AND for conditional formatting

Find on this sheet this example in question.

We are going to keep the same data from the original table, but in this case we are going to want to highlight in red the rows that have a negative balance and that are also from Spain.

The formula to use should be this:=AND($F2<0, $C2="Spain")

We indicate with this AND that both conditions within the AND must be met for the rule to be evaluated as true.

As you can see, row 4 is not highlighted, since, although the balance is negative, the country is not Spain.

The Google Docs AND function supports as many parameters as you want, so if we needed to evaluate more than 2 columns, there would be no problem in continuing to introduce them as parameters of the AND function.

Using OR for conditional formatting

Exactly the same as we did with AND, we can do it with OR so that the rule is evaluated as true if any of its conditions are met.

In this case I invite you to give it a spin and apply it yourself once you have laid the foundations of AND.