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.

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.

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 Menu section 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.

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.

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.

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:

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 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.

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.

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.

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

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.

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

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

Step 9: In the field with the 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 deselect a checkbox, it indicates a 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.

Step 11: Click done

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.

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”.

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.

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.

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.

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 the 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 which is depending on the values of other cells.

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

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

Other Google Sheets Resources You May Find Useful

• How to Create Data Entry Form in Google Sheets
• How to Add a Target Line in Google Sheets
• How to Create Pie Chart in Google Sheets
• How to Make a Scatter Plot in Google Sheets
• How to Make a Pareto Chart in Google Sheets
• How to Make a Bell Curve in Google Sheets
• How to Create a Combo Chart in Google Sheets
• How to Make a Histogram in Google Sheets
• How to Create a Timeline Chart in Google Sheets
• How to Find Slope in Google Sheets? Using Formula & Chart
• How to Create Dynamic Chart Range in Google Sheets
• How to Create an Area Chart in Google Sheets
• Step Chart in Google Sheets – A Step-by-Step Tutorial
• How to Add a Trendline in Google Sheets Charts
• How to Create Pivot Table in Google Sheets
• How To Create Drop-Down List In Google Sheets (With Examples)