Two Columns of Data with One Highlighted

Google Sheets | Conditional Formatting Based on Another Cell

Using Google Sheets for conditional formatting based on a range’s own values is simple. Formatting based on another range of cells’ values is a bit more involved. Following are step-by-step instructions to format a range of cells using values in another range of cells.

What is conditional formatting

Conditional formatting is used to highlight to the reader of the spreadsheet certain values that meet certain criteria. It can be used to show items over/under a certain amount, later/earlier than a certain date, or only the widgets sold by Joan.

Video explanation

If you want to learn more about the complex subject of conditional formatting, I have created a course about it over at Datacamp. This is an affiliate link and if you use it to make a purchase I will receive a portion of the proceeds. Thank you for supporting my channel!

Conditional formatting based on another cell

Our objective in this example is to highlight all of the dates on which Joan had a sale.

Select the range that you want to have highlighted. Even though you are going to be referencing another range of cells, you still only need to select the range that you are going to be formatting. In this example, we’ll be highlighting the date column.

Two Columns of Data with One Highlighted
Two columns, one highlighted

Click Format in the top menu then Conditional formatting…

Conditional formatting menu option
Menu option for conditional formatting

The Conditional Formatting menu option will pop up a Conditional format rules menu on the right side of the screen (on the desktop version of Sheets).

Rules for conditional formatting
Rules for conditional formatting

Click the plus sign to begin adding the rule. In the drop-down menu for Format cells if choose the last option which is Custom formula is.

Custom formula menu option
Last menu option is custom formula

In the box that appears below Custom formula is enter =B1="Joan". Note that you have to start the formula with a = sign just like any other spreadsheet formula. Also, note that you need to surround the word Joan with “s since it is a word instead of a number.

Custom formula
Custom formula

Now you have the sales data with dates highlighted on which Joan made sales.

After conditional formatting
After conditional formatting

Extending to the entire row

If you want to highlight the entire row based on the value of one cell, continue reading here.

 Live examples in Sheets

Go to this spreadsheet for examples of conditional formatting that you can study and use anywhere you would like.

0 thoughts on “Google Sheets | Conditional Formatting Based on Another Cell

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>