Google Sheets Conditional Formatting (2023)

What is conditional formatting in Google Sheets?

Why do we need conditional formatting in a table? Isn't it easier to format the cells manually?

Highlighting specific data with color is a great way to draw attention to records. Many of us do this all the time. If the cell values ​​meet our conditions, e.g. are greater than or less than some value, are greater than or less than, or maybe contain certain characters or words, so we find those cells and change their font, font color, or background color.

Wouldn't it be great if those formatting changes happened automatically and drew even more attention to those cells? We would save a lot of time.

This is where conditional formatting comes in handy. Google Sheets can do that job for us, all we need is to explain what we want to get. Let's look at some examples together and see how simple and effective it is.

To add a format rule with a condition

Suppose we have chocolate sales data in our table. Each row of the table contains an order that we receive from a particular customer. We use dropdown menus in column G to indicate if it is complete.

What could be interesting for us to see here? First, we can highlight orders that exceed $200 in total sales. We have these records in column F, so we use the mouse to select the range of values ​​with the order value: F2:F22.

so findFormatmenu item and clickconditional format.

Let's consider conditional formatting in Google Sheets to get started.using only one color.

ClickForm cells are..., select the "Greater than or equal to" option from the dropdown list you see and enter "200" in the field below. This means that within the range we have selected, all cells with values ​​greater than or equal to 200 will be highlighted in the format we have defined in the same place: bold red text on a yellow background.

We can see our formatting rule applied immediately: all the necessary cells have changed in appearance.

You have the ability to configure conditional formatting not just with a tone, butusing a color scale. To do this, selectscale of colorsin the sidebar to view conditional formatting rules and use ready-made color sets. You can also choose shades for the minimum and maximum points and the center point if necessary.

Here we create a color scale where the cells become lighter as the order quantity decreases and darker as the sum increases.

Format cells in Google Sheets based on different conditions

If the color scale seems too bright for you, you can create multiple conditions in the "Single Color" tab and specify a format for each condition separately. To do this, click "Add another rule".

We'll highlight orders that are over $200 in total sales and those that are under $100.

As you can see, we have two formatting conditions here. The first is for values ​​greater than 200, the second for values ​​less than 100.

Advice.You can add as many conditional formatting rules as you need in Google Sheets. To remove it, just point it and click the buttonRemoveicon.

Google Sheets conditional format with custom formulas

The proposed list of conditions that we can apply to our data area is quite extensive. However, it may still not be enough. Sooner or later you will have to create a state that cannot be described by standard means.

That's why Google Sheets offers the ability to enter your own formula as a condition. This formula allows you to describe your requirements using standard functions and operators.In other words, the result of the formula must be "True" or "False".

Use the last item in the dropdown list to enter your formula: "Custom formula is".

Let's see how this works.

Let's say we want to know which of our orders were placed over the weekend. None of the standard conditions work for us.

We select the range of dates in A2:A22, we are going toFormatmenu and clickconditional format. Select the "Custom formula is" item from the "Format cells if" drop-down list and enter the logical formula that will help us identify the day of the week from the date.

=DIA SEMANA(A2:A22,2)>5

If the number is greater than 5, it is Saturday or Sunday. In this case, the format defined below will be applied to the cell.

As you can see, all the weekends are now highlighted in color.

Here is another example. Let's bring the dark chocolate orders in a different format. To do this we follow the same steps: we select the data range with the types of chocolate (D2:D22) and use the following condition:

=REGEXMATCH(D2:D22;"Escuro")

This function returns "True" if the name of the type of chocolate contains the word "Dark".

Look what we've achieved: orders for dark chocolate and extra dark chocolate won as well. No need to search through hundreds of lines to find them now.

Use wildcards with conditional formatting in Google Sheets

If we want to format text values, the default condition "Text contains" is essential.

You can usespecial jokersto add some flexibility to the search condition.

Advice.Wildcards can be used in the "Text Contains" and "Text Does Not Contain" fields, as well as in your custom formulas.

There are two most commonly used characters: the question mark (?) and an asterisk (*).

The question mark matches any character. For example, as you can see in the screenshot, the text rule containing "??d" formats cells with values ​​like "Red" but not "Dark".

"??d" means that the letter "d" must come third from the beginning of the word.

Use an asterisk to skip zeros in any number of characters. For example, a rule containing "*d*" should format both cells: with "Red" values ​​and also with "Dark" values.

To prevent questions and asterisks from being interpreted as wildcards in text values, a tilde (~) is usually added before them. E.g. the text rule containing "Re?" in our example, the cell format with "Red", while the rule with "Re~?" it will not find any cells as it will look for the value "Re?".

How to use Google Sheets conditional formatting to highlight entire rows

In the examples described above, we apply conditional formatting to specific cells in a column. Maybe you thought: "It would be great if we could apply this to the whole table!". And you can!

Let's try to highlight unfulfilled requests with a special color. For this we need to use the data format condition in column G, where we indicate whether the request was successful and format the entire table.

Observation.Note that we format the entire table A1:G22.

We then use our custom formula where we specify that:

=$G1="Yes"

Advice.You must use the dollar sign ($) before the column name. This creates aabsolute referenceAlso, the formula will always refer to this specific column, while the row number can change.

That is, we ask you to go down the column starting with the first line and find all the cells with the value "No".

As you can see, not only the cells we checked for our condition were formatted. Conditional formatting now applies to entire lines.

So let's remember three basic rules for conditional formatting of rows in a table:

• The area to format is the entire table.
• We use conditional formatting with a custom formula.
• We must use the $ character before the column name.

Google Sheets conditional formatting based on another cell

We often hear the question "How do we apply conditional formatting and make it easy to change the condition?" This is not difficult at all.

Just use your own formula referring to the cell where you specify the required condition.

Let's go back to our example data for chocolate orders in Google Sheets. Suppose we are interested in orders of less than 50 and more than 100 items. We'll go ahead and enter these conditions in column H at the side of our table.

We will now create conditional formatting rules for the orders table.

We set the format range to "A2:G22" to keep the table header as it is.

So we follow the steps you know and use our formula.

This is what the conditional formatting formula looks like for orders with more than 100 items:

=$E2>=$H$3

$H$3 in our example means an absolute reference to the cell, i.e. No matter what you do with the table, the formula will still refer to this cell.

Observation.We need an absolute reference for column E and an absolute reference for cell H3, where we have our limit of 100. If we don't, the formula won't work!

Now let's add the second condition to highlight orders with less than 50 items. Click "Add another rule" and add another condition just like we did with the first one.

Check out the formula we used in our conditional formatting rule:

=$E2<=$H$2

The largest and smallest orders are now highlighted in color. The work is finished. However, it is not good to have extra numbers in our spreadsheet, which can be confusing and spoil the look of the table.

Putting the ancillary data in a separate spreadsheet would be the best option. I'll describe this in more detail in my next post as we learn.How to make drop down lists.

Let's go to worksheet 2 and enter these new conditions there.

Now we can create conditional formatting rules for the orders table by querying these limits.

This is where we may face a problem. If we simply use the cell address of sheet 2 in the formula, we get an error.

Observation.Direct cell references in conditional formatting formulas are only possible in the current worksheet.

So what do we do now? The INDIRECT function helps. Allows you to get the cell reference by typing its address as text. This is what the cell reference looks like in a conditional formatting formula:

=$E2>=INDIRETO("2!G2")

Here is the second formula:

=$E2<=INDIRETO("2!G1")

As a result, we get the same result as before, but our worksheet is not cluttered with additional entries.

We can now change the formatting conditions without updating the rule settings. Just change the entries in the cells and you will get a new table.

Google Sheets and conditional formatting based on other cell text

We learned how to apply conditional formatting rules using numeric data from a given cell. What if we want to base our state on a cell with text? Let's see how we can do this together.

We will try to find the orders of dark chocolate:

In cell G5 of sheet 2, we enter our mode: "Dark".

Then we return to sheet 1 with the table and again select the area to format: A2:G22.

So we chooseFormatmenu selectconditional formatand enter the following formula in the fieldThe custom formula iscampo:

=REGEXMATCH($D2:$D22,INDIRETO("2!$G$5"))

Advice.Remember to enter absolute references to the range needed to check the word "Dark" (D2:D22).

The INDIRECT("2!$G$5") function allows us to obtain the value of cell G5 on Sheet2, that is, the word "Dark".

Thus, we highlight the orders that have the word in cell G5 of sheet 2 as part of the product name.

Of course we could make it easier. Our formula looks like this:

=REGEXMATCH($D2:$D22,"Escuro")

But if we wanted to find another product, we would have to edit the conditional formatting rule. This takes a bit longer than just updating the value in cell G5.

Remove conditional formatting from your Google Sheet

Of course, you should remove all conditional formats from your table.

To do this, first select the range of cells where the conditional formatting was applied.

You will see all the rules you have created in the sidebar.

Point the mouse at the mode you want to delete and click "Remove"Icon. Conditional formatting will be cleared.

If you don't remember the exact range of cells you formatted, or if you want to get rid of the formatting as quickly as possible, select the range of cells and go toFormatmenu -clear format. You can also use the key combination.Ctrl+\.

Observation.Remember that in this case not only the conditional formatting will be deleted, but all other formats used in your table.