In this article:

Conditional Formatting Based on Another Column in Google Sheets

In this article we will show you how to apply conditional formatting based on another column in google sheets and how this can be used to compare two columns. Simply follow the steps below:

For the purpose of these demonstrations we have created a sample sheet. Click here to access the sheet and follow along at home.

Apply Conditional Formatting Based on Another Column

We can apply conditional formatting by comparing the values of another column.

In this example our data set contains a list of names with sales figures next to them. We will apply conditional formatting to the name column where sales cell values are below 3,000.

Google Sheets conditional formatting based on another column: Example data set

1. Select the range of cells that you want to apply conditional formatting to.

Highlight the cell range by clicking and dragging over the cells to select them.

To select cells that are separate from each other you can hold the Ctrl (Cmd ⌘ key for Mac) key while clicking the individual cells.

In our example we will apply the formatting to the name column therefore we will highlight all the data in this column excluding the header row.

Google Sheets conditional formatting based on another column: Cell selection

2. Click "Format" in the file menu followed by “Conditional formatting”.

From the top file menu select Format followed by Conditional formatting

Google Sheets conditional formatting based on another column: File menu

3. In the "Format cells if" dropdown menu, select "Custom formula is".

The Conditional format rules menu will now appear on the right hand side of the display.

Google Sheets conditional formatting based on another column: Conditional format

From the section labelled Format rules expand the dropdown menu labelled format cells if.. Scroll to the bottom of the menu click Custom formula is.

Google Sheets conditional formatting based on another column: Custom formula is

4. Enter the custom formula =$Cell_ref[operator]value

An input box will now appear, enter the custom formula in the format below:

In the input box, enter the formula:

=$Cell_ref[operator]value

Formula Breakdown:

$Cell_ref: This is the column the formula will search followed by the first row number of your selection. It is important to ensure the first row number matches the first row in your selected cell range. 

[operator]: This will be dependent on what condition you wish to use. In our example we will use the < (Less than) operator to check if the value is less than the specified value.

Value: This will be the value the formula is searching against. Text values need to be contained in double quotation marks (“  ”), numeric values do not.

In our example we will apply a custom formula that will check the values in the sales (Column C) to see if any are less than (<) 3000. 

Our example formula is:

= $C3<3000

Google Sheets conditional formatting based on another column: Custom formula

5. Specify the formatting style.

Use the options in the Formatting style section to specify the formatting that you want to apply. You can choose from a variety of formatting options, including font color, cell color, and various text formatting.

In our example we will change the cell color to light red.

Google Sheets conditional formatting based on another column: Format style

6. Click the "Done" button to apply the formatting.

Click the done button, the conditional formatting is applied as specified by the conditions in the custom formula.

As can be seen in the example, the formatting has been applied to the name Steven as the sales value next to the name contains a value less than 3,000 which was specified in the custom formula. 

Google Sheets conditional formatting based on another column: Results

Compare Two Columns with Conditional Formatting in Google Sheets

We can also apply conditional formatting to cells by comparing the data in two columns. 

In the below example we will compare the values in the Sales column (Column C) to the Sales target column (Column D). Formatting will be applied to any cells where the Sales do not exceed the sales target.

1. Select the range of cells that you want to apply formatting to.

To select cell range, click and drag over the cells to select them.

You can select cells that are separate from each other by holding the Ctrl (Cmd ⌘ key for Mac) key and clicking on the individual cells.

In our example we will select the full range of data. Our data contains a header row, we do not want to apply data to this row so will not include this in our selection.

Google Sheets conditional formatting compare two columns: Cell selection

2. Click "Format" in the file menu followed by “Conditional formatting”.

From the file menu at the top of the screen select Format. Click Conditional formatting from the menu.

Google Sheets conditional formatting compare two columns: File menu

3. In the "Format cells if" dropdown menu, select "Custom formula is".

The Conditional format rules menu will now appear on the right hand side of the screen.

Google Sheets conditional formatting compare two columns: Conditional format menu

In the format rules section expand the Format cells if.. Dropdown menu, scroll to the bottom and select Custom formula is.

Google Sheets conditional formatting compare two columns: Custom formula is

4. Enter the custom formula =$Cell_ref1[operator]$Cell_ref2

An input box will now appear, enter the custom formula in the format below:

In the input box, enter the formula:

=$Cell_ref1[operator]$Cell_ref2

Formula Breakdown:

$Cell_ref1: This is the first column the formula will search followed by the first row number of your selection. It is important to ensure the first row number matches the first row in your selected cell range. 

[operator]: This will be dependent on what condition you wish to use. In our example we will use the < (Less than) operator

$Cell_ref2: This is the second column the formula will search followed by the first row number of your selection. It is important to ensure the first row number matches the first row in your selected cell range. 

In our example we will apply the custom formula that will check the values in the sales (Column C) that are less then the values in the Sales Target column (Column D)

Our example formula is:

= $C3<$D3

Google Sheets conditional formatting compare two columns: Custom formula

5. Apply the formatting style.

In the Formatting style section are options to bold, italic, underline or strikethrough text. Here you can also choose a different text or cell background color.

In our example we will choose to apply the cell color of red to any cells where the condition is met.

Google Sheets conditional formatting compare two columns: Format style

6. Click “Done” to apply the conditional formatting.

Select the Done button, the conditional formatting will be applied to all cells where the specified condition is met.

As can be seen in our example the formatting has been applied where the value in column C is less than the value in column D.

Google Sheets conditional formatting compare two columns: Results

Use our payment due reminder software to easily set up custom reminders from your spreadsheet in just a few clicks. 

If you enjoyed this article, you might also like our article on how to use Google Sheets conditional formatting based on cell color or our article on how to use Google Sheets conditional formatting based on another sheet. 

If you want to learn how to automatically send emails from Google Sheets, we also suggest checking out our detailed guide.

Schedule a free automation consult
Learn more

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started