In this article we will show you how to apply conditional formatting based on another sheet in Google Sheets and how this can be used to compare two columns. Simply follow the steps below:
An example data set was created for the following demonstrations, click here to access the spreadsheet and follow along at home.
We can apply conditional formatting based on the values of another sheet.
In the below demonstration we will use a custom formula with conditional formatting to check if data in column E of Sheet2 matches any data from column E in Sheet1. Any cells that contain matching data will have the formatting applied.
Select the cells you want to include in the conditional formatting, to achieve this click and drag across the selection or to select separate cells simply hold the Ctrl (Cmd ⌘ on Mac) key as you click the individual cells.
In our example we want to apply the conditional formatting to the State data in the E column of Sheet2. As our data contains a header we will exclude this in our selection.
Click Format from the file menu at the top of the display. From the menu that is displayed select Conditional formatting.
The conditional format rules menu will now display on the right hand edge of the display.
In the Format rules section click the Format cells if.. dropdown menu to expand it, scroll to the bottom of this menu and select Custom formula is.
In the input box below the “Format cells if..” dropdown menu enter the custom formula in the following format:
=MATCH(Cell_Ref1,INDIRECT(“Sheet_name!Col_Range”),0)
Formula Breakdown:
=MATCH: This calls the match function in Google Sheets
Cell_Ref1: This is the reference which includes the column in the current sheet to be searched followed by the first row number of your selection. Ensure the row number is the same as the first row of your selected cell range.
INDIRECT: This specifies that the following elements are not direct to the current sheet.
“Sheet_name: Specify the sheet name containing the column you want to match the data to. Ensure this matches the sheet name exactly, for example if the sheet is named Sheet 2 (Note the space between the two words) then you would enter Sheet 2!
Col_Range”: This is the column in the sheet to check for matching values. In our example we want to match values in column E so we will enter E:E.
0: This sets the format position.
When we look at our example we need to check the values of Column E in Sheet1 for matching values. Therefore our example formula will be:
=MATCH(E4,INDIRECT("Sheet1!E:E"),0)
In the formatting style section there are several various ways to format the text including options to bold, italic, underline and strikethrough text. There are also options to change the text and cell background color.
For our example we will simply click the paint bucket icon and choose the cell background color of light green.
With the formatting style applied click the green Done button. The conditional formatting will be applied to any cells that meet the conditions set out in your custom formula.
As can be seen in our example, there are two cells that contain values that match any of the values in Column E of Sheet1. The formatting has been applied to these cells.
We can compare the values of two columns that are located across different sheets.
In this example we will compare the value of the percentage in the C column in Sheet1 with the percentage required values located in Sheet2 in column C.
Highlight the cells you want to apply the conditional formatting to. You can click and drag to highlight cells next to each other or select separate cells by holding the Ctrl (Cmd ⌘ on Mac) key when clicking the individual cells.
In our example we will select all the cells in the Name column, the data contains a header row which we will not include in the selection.
From the file menu located at the top of the display select Format to display the menu. From this menu click Conditional formatting.
The conditional format rules options will now appear on the right hand side of the display.
In these options is a section labelled Format rules, within this section expand the Format cells if dropdown menu, scroll to the bottom and click Custom formula is.
Enter the custom formula in the input box below the Format cells if dropdown menu. The formula should be in the format of:
=Cell_ref[operator]INDIRECT(“SheetCell_ref2”)
Formula Breakdown:
Cell_ref: This is formed of two parts (Example cell ref: C4). The first part is the column letter which is the column the formula will search. And the first row number of your selection. Ensure the row number is the same as the first row of your selected cell range.
[operator]: This will be dependent on which comparison you are making. In our example we will use the > (Greater than) operator to check if the value is more than the specified value.
INDIRECT: This specifies that the following elements are not direct to the current sheet.
“Sheet!: Specify the sheet name containing the column you want to compare against. The sheet name must match exactly, for example if the column is in a sheet named Sheet 2 (Note the space between the two words) then you would enter Sheet 2!
Cell_ref2”: This is the second column the formula will search in comparison to the column in Cell_ref1 followed by the first row number of your selection again. Ensure the row number matches the first row of your selected range.
When we look at our example we need to compare the values of column C in Sheet1 to the values of column C in Sheet2. Our cell selection starts from row 4. Therefore the formula for our example is:
=C4>INDIRECT(“Sheet2!C4)
Use the options located under the Formatting style label to specify which formatting should be applied to any cells where the conditions are met. There are options to bold, italic, underline or strikethrough text as well as the choice to change the text or cell color.
In our example we will make the text bold and the cell color to green.
Once finished specifying the format style simply click the green Done button. The formula will be applied and any cells in your selection that match the conditions of the formula will have the specified formatting applied.
In our example the values in column C on Sheet1 have been compared to the values of Column C in Sheet2. The cells in the selection where the percentage value is greater than the percentage required have been formatted.
If you enjoyed this article, you might also like our article on how to use Google Sheets conditional formatting to compare two columns or our article on how to highlight duplicates in Google Sheets.
If you want to learn how to set automatic reminders in Google Sheets, we also suggest checking out our detailed guide.
