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.
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.
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.
From the top file menu select Format followed by Conditional formatting.
The Conditional format rules menu will now appear on the right hand side of the display.
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.
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
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.
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.
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.
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.
From the file menu at the top of the screen select Format. Click Conditional formatting from the menu.
The Conditional format rules menu will now appear on the right hand side of the screen.
In the format rules section expand the Format cells if.. Dropdown menu, scroll to the bottom and select Custom formula is.
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
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.
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.
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, click here!
