Skip to Content

Google Sheets Conditional Formatting Based On Another Cell Value Or Text

It is strongly advised to use Google Sheets conditional formatting based on the value or text of another cell.

If you want to fully utilize conditional formatting, you should do so based on the information or data that is already in your worksheet.

To do so, you’ll need to use cell references, which are Google Sheets’ way of pointing to other cells in a spreadsheet.

Why use another cell for conditional formatting in Google Sheets

Adding conditional formatting in Google sheets based on the values of other cells can save a ton of time.

They come in handy when you need to create a conditional formatting rule or formula that involves a large number of scattered cells with frequently changing values.

It will make it easier for your conditional formatting to work dynamically with the data in those cells or ranges.

For example, if your conditional formatting is dependent on the value in cell C2 and you change the value, the result will be updated to reflect the new value.

If you didn’t use references in your formatting rules, you’d have to edit the conditional formatting rules themselves to change the values.

Take a look at the screenshot below for instance. It is an illustration of a spreadsheet with conditional formatting. If a month selected in cell C2 appears in the first column, the entire row for that month is highlighted.

Example of Google sheet's conditional formatting based on another cell

Now we know how versatile it is to use Google Sheets conditional formatting that is based on the text in another cell.

Let’s dive into our first example.

Example 1: Google Sheets Conditional Formatting Based on Another Cell Text

Let’s begin with a simple example that applies conditional formatting to cells based on the text another cell contains.

In below screenshot has a spreadsheet of a company’s monthly sales for three countries. When text is entered into cell C2, conditional formatting checks to see if there is a matching text in column A and then formats or does nothing based on the results.

Google sheet's conditional formatting based on another cell text

The below steps will show you how to achieve the above functionality in your spreadsheets.

Step 1: Select the Cells for the conditional formatting

This is a no-brainer.

Click and drag your mouse pointer over all the cells you want the conditional formatting to apply to.

In this case, we’ll select all of the cells in the Months column. This way, whenever we type a month in cell C3, Google sheets will highlight that month.

Step 2: Go to Format > Conditional formatting

Here’s another easy step.

Simply move your cursor to the Google Sheets Menus and select the Format option.

This action will reveal all of the Format menu items, including Conditional formatting. Simply click on it to open it in the right-hand pane.

Step 3: Set the “Text Contains” Conditional Formatting Rule ($C$2)

When the conditional formatting pane is open, you can add the rule with a few simple clicks.

To do so, first, under the Format rules, click on the drop-down and select Text Contains from the list.

You’ll then be given a text box to enter your value, kindly enter =$C$2, or any cell according to your data.

Remember to include two dollar signs in your cell references, one for the column letter and one for the row number. This will lock the cell, preventing it from referencing other cells.

Conditional Formatting in Google Sheetps Text Contains rule

Step 4: Set the Formatting

There are a lot of formatting styles you can apply to the cells when they met the condition you set.

formatting style menu on the Conditional formatting pane

You can do that by using the font controls under the Formatting style.

There are several preset formatting you can choose from (including green background, yellow background, red background, green text, yellow text, and red text)

Click on the Default button under the Formatting style to reveal all the available formatting presets.

Step 5: Click Done

As soon as you click the done button, Google Sheets will now evaluate the condition and set the formatting as needed.

When you change the text (month) in cell C2, Google Sheets evaluates the condition and adjust formatting as needed.

Google sheet's conditional formatting based on another cell text

Example 2: Google Sheets Conditional Formatting Based on Checkbox

Using checkboxes is an essential part of building spreadsheets.

And it’s fun as well.

But what is even more fun is linking those checkboxes to conditional formatting rules.

This way, whenever a checkbox is selected, the formatting you want will be applied.

In this example, you’ll learn how to add conditional formatting in Google Sheets based on a checkbox. We’ll use a simple checklist for the example.

See the action in the animation below:

Strikethrough in Google Sheets using checkboxes

Without wasting much, let’s get into the steps.

Step 1: Open your Google Sheets spreadsheet

Obviously, you must first open Google Docs before you can add conditional formatting with the checkbox.

Do this, and then proceed to the next step.

Step 2: Select the cell or cells that will contain the checkbox

At this step, you need to select all the cells to which you want to add the checkboxes.

It could be a single or multiple cells. There aren’t any limits here.

Hold down the Control key while clicking on the cells to select non-adjacent cells.

In this task example, we want the checkboxes to go into the cells under the status column.

So I’ll select all the cells in this column.

Click and drag over the cells to select them for the checkbox

Note: When you add a checkbox to a cell with data, the checkbox will replace the content that was there. We, therefore, recommend that you insert checkboxes in cells that have no content in them.

Step 3: Click on the Insert menu

This step is easy.

Just navigate your mouse pointer to the Menus at the top section of Google Sheets and hit on the Insert menu.

Click on the Insert tab in the Google Sheets menus

You should see a list of items in the drop-down menu.

Step 4: Select Checkbox

The checkbox is placed under the Insert menu.

Once you click on the insert menu and the drop-down list appears, locate and click on the Checkbox near the bottom.

Go to the Insert Menu and select Checkbox from the drop-down

The simple steps above will allow you to seamlessly add a checkbox or checkboxes to your Google sheets.

Checkboxes added to google Docs

Note: At this point, nothing will happen if you click on the checkbox. The remaining steps will show you how to apply the strikethrough formatting when a checkbox is clicked.

Step 5: Select all the cells in the Tasks Column

Using your mouse, click and drag to select all the cells under the My Tasks columns.

These are the cells you want to apply conditional formatting on when the checkboxes are selected.

Step 6: From the Menus, Go to Format > Conditional Formatting

After selecting all the cells that contain the items, simply click the Format menu to display all of its functions which includes Conditional formatting.

Click once on Conditional formatting to launch the conditional formatting pane.

Opening Conditional formatting in Google sheets

Step 7: Click the drop-down under Format Rules on the conditional formatting pane.

Setting Conditional formatting rules in Google sheets

Step 8: From the list, scroll to the bottom and select “Custom formula is”.

Step 9: In the field with label ‘value of formula’, enter this formula: =$C3=TRUE

This formula simply states that cell C3:C9 must contain the value True in order for the formatting to work.

When a checkbox is selected, it passes the value True, and when not selected, it passes the value False.

Thus, when you click on a check box, you get a true value, and the formatting is applied. If you disselect a checkbox, it indicates False value and thus no formatting.

Step 10: Under the Formatting style, apply the formatting you want

This is the formatting that will apply when the checkbox is selected.

The formatting settings you can apply include Bold, Italic, Underline, Strikethrough, Font Color, or Fill Color.

The formula for strikethrough when a checkbox is ticked

Step 11: Click done

Strikethrough in Google Sheets using checkboxes

It’s pretty simple if you know the conditional formatting formula to use which I’ve explained in step 9.

Example 3: Google Sheets Conditional Formatting If Another Cell is Empty

This is another useful example.

It made use of the conditional formatting based on other cells in another column.

As seen in the screenshot below, we have a weight tracking spreadsheet that was supposed to be recorded every day.

However, for some reason, some days didn’t have records in the weight tracking column.

As a result, we’ve created a conditional formatting formula that highlights and crosses out that weren’t recorded.

This means that the conditional formatting depends on whether other cells were empty or not.

Google sheet's conditional formatting based on another cell being empty

Just obey the following steps.

Step 1: Select the Cells for the conditional formatting

Simply click and drag your mouse pointer over all the cells you want the conditional formatting to apply to.

In this case, we’ll select all of the cells in the Date column. This way, whenever there’s an empty or blank cell in the Wight tracking column, Google sheets will highlight and cross that date.

Step 2: Go to Format > Conditional formatting

Step 3: Click the drop-down under Format Rules on the conditional formatting pane.

Step 4: From the list, scroll to the bottom and select “Custom formula is”.

Entring Custom formula in Google Sheets conditional formatting

Step 5: In the field with the label ‘value of formula’, enter this formula: =ISBLANK(D3)

What this conditional formatting setting do is check in the Weight Tracking column for blank cells and apply formatting to the corresponding cells in the Date column when an empty cell is detected.

Google sheet's conditional formatting if another cell is empty

Step 6: Set the Formatting

There are a lot of formatting styles you can apply to the cells when they met the condition you set.

formatting style menu on the Conditional formatting pane

You can do that by using the font controls under the Formatting style.

There are several preset formatting you can also choose from (including green background, yellow background, red background, green text, yellow text, and red text)

Click on the Default button under the Formatting style to reveal all the available formatting presets.

Step 7: Click Done

As soon as you click the done button, Google Sheets will now evaluate the condition and set the formatting as needed.

Example 4: Google Sheets Conditional Formatting If Another Cell Contains Text/Not Empty

This example is the exact opposite of the previous example.

It checks other cells to see if they contain text or are not empty, and then applies the formatting as needed.

See the example in the below screenshot where dates are highlighted based on the condition that the corresponding cells in the Weight Tracking column are not empty.

Google sheet's conditional formatting if another cells contains text or are not empty

Obey the below steps to achieve this functionality in your Google Sheets.

Step 1: Select the Cells for the conditional formatting

In this example, we’d select cell A3:A18. That’s all the cells in the Date column.

Step 2: Go to Format > Conditional formatting

Step 3: Click the drop-down under Format Rules on the conditional formatting pane.

Step 4: From the list, scroll to the bottom and select “Custom formula is”.

Step 5: In the field with label ‘value of formula’, enter this formula: =COUNTA(D3)>0

This is the formula that does all the magic.

It uses the COUNTA function to determine the number of characters in a cell, then it compares that number to zero using the greater than.

If that number is greater than zero, a FALSE value will be returned and the formatting will not be executed.

However, if that value is equal to zero, which can only happen when the cell is empty, a TRUE value will be returned and the formatting will be executed.

Step 6: Set the Formatting

Step 7: Click Done

This is how you may create Google Sheets Conditional Formatting I another cell contains text or is not Empty

Google Sheets Conditional Formatting Based on Another Cell Color

As you journey with Google Sheets, you are likely to come across the need to perform conditional formatting based on another cell color.

This would’ve been a very useful conditional formatting feature in Google Sheets.

As of now, if is unfortunate that there’s no feature like that.

We’ll update this post as soon as this becomes available in Google Sheets.

Removing conditional formatting in Google Sheets that is based on another cell

In Google Sheets, deleting the content of a cell does not remove the cell’s conditional formatting, whether it is based on another cell or not.

As a result, you must take the necessary steps in order to completely remove conditional formatting from your spreadsheet.

To remove conditional formatting from your Google Sheets, simply click the Formatting menu, a drop-down list will appear, select Clear Formatting at the bottom of the drop-down. This will remove all the conditional formatting as well as all other cell formattings.

However, if you don’t want to remove another cell formatting, simply delete the conditional formatting rule.

Conclusion

It is very pleasing to know that you can add conditional formatting in Google Sheet’s which is depending on the values of other cells.

And knowing how to utilize this feature will go a long way to turn you into a spreadsheet ninja.

Please let me know how you think about this guide and examples in the comments.