How to Use Google Sheets Conditional Formatting Based on Another Column

How to use google sheets conditional formatting based on another column 18

In this article we will learn about how to use google sheets conditional formatting based on another column.

It is relatively easy to apply conditional formatting based on the current value of the cell. But let’s say you encounter a problem where you have to highlight or format a column based on another column’s values. This type of problems can be solved using google sheets conditional formatting based on another column. In this article, we will discuss how to use google sheets conditional formatting based on another column to highlight or especially format a column based on another column’s values.

Download/Copy Practice Workbook

How to use Google Sheets Conditional Formatting based on another column

If a problem involves to apply conditional formatting based on the value of (same) cell. That’s an easy task. All you have to do is to:

Select the cell -> Apply Conditional Formatting -> Set conditions / rules -> Done

But sometimes you may get across a type of problem where you have to conditionally format a column of cells based on another column’s values. This is relatively difficult task. But don’t worry, this article describes step-by-step easy procedure to apply google sheets conditional formatting based on another column with easy guide and screenshots.

Suppose you have a worksheet containing products and their sales for the past month. Now you have to highlight the product names which had less than 10 sales in red to limit their stock and highlight the products having sales more than 100 to buy more of their stock.

See also  Conditional Formatting based on Another Cell in Google Sheets

How to use google sheets conditional formatting based on another column 1

Required format is as:

How to use google sheets conditional formatting based on another column 2

Generally, there are two methods to achieve this.

  1. Apply conditional formatting to range of cells (column)
  2. Apply conditional formatting to a single cell and copy it to range of cells (column)

 

1

Method 1: Google Sheets Conditional Formatting based on Another Column using a Range of Cells (Column)

Step 1:

Select the column which needs to be formatted. In this case, select the product name column (excluding the heading). It will highlight in blue as:

How to use google sheets conditional formatting based on another column 3

Step 2:

Apply Conditional Formatting either by selecting it from “Format” toolbar as:

How to use google sheets conditional formatting based on another column 4

OR

Right click the selected column -> Choose “View more cell actions” -> Conditional Formatting

Conditional Formatting Rules Box will appear as:

How to use google sheets conditional formatting based on another column 5

Remember that we can choose a range of cells by manually typing in the conditional formatting rules box as well. For example, if we choose to Conditionally Format 100 elements of B, we can specify range as “B2:B101”.

Step 3:

Now choose the required Formatting for the range.

select “Single Color” -> In “Format cells if…” column, choose “Custom Formula Is” as:

How to use google sheets conditional formatting based on another column 6

Now in Formatting Rules Section, box will appear for writing formula as:

How to use google sheets conditional formatting based on another column 7

Step 4:

Write the formula describing the condition for Conditional Formatting.

Here, we need to apply two Conditional Formatting.

  • Highlight Products with Sales less than 10 in Red
  • Highlight Products with Sales greater than 100 in Green

Here, the Formula is that Sales cell corresponding to Product Name cell is less than 10. As Sales lies in D Column and Row number for first cell is 2. So formula becomes “=D2<10” and change the formatting below according to choice (Highlight in red for this case).

You can see side by side that changes are applying to columns as you apply the formula and set the formatting as:

How to use google sheets conditional formatting based on another column 8

We also need to apply another formatting to highlight in Green if sales are more than hundred, we choose “Add another Rule” below the “Done” as:

How to use google sheets conditional formatting based on another column 9

Add the next formula “=D2>100” in the same way and apply format of Green Highlight as:

How to use google sheets conditional formatting based on another column 10

Step 5:

Click “Done”.

How to use google sheets conditional formatting based on another column 11

You will see that Formatting have been applied to the required column as:

How to use google sheets conditional formatting based on another column 12

 

2

Method 2: Google Sheets Conditional Formatting to a Single Cell and Copy it to Range of Cells (Column)

Step 1:

Select a single cell from the column which needs to be formatted.

Here, we will select the first cell as:

How to use google sheets conditional formatting based on another column 13

Step 2:

Apply Conditional Formatting Rules in the same way (as described earlier)

Right Click -> View More Cell Actions -> Conditional Formatting

Write down the formulae and set formatting rules as:

How to use google sheets conditional formatting based on another column 14

Step 3:

Copy the cell either by

Right Click -> Copy

OR

Ctrl+C

OR

Select “Copy” from “Insert” Toolbar as:

How to use google sheets conditional formatting based on another column 15

Step 4:

Now select the range of cells (column cells) where you want to apply the formatting.

In this case, we need to apply to column “Product Name” cells. Select them by dragging the mouse over them as:

How to use google sheets conditional formatting based on another column 16

Step 5:

Paste “Conditional Formatting” by:

Right Click -> Paste Special -> Conditional Formatting Only

How to use google sheets conditional formatting based on another column 17

Now the Conditional Formatting is applied throughout the column.

To know more specific about Google Sheets copy Conditional Formatting, you can refer the article:

How to Copy Conditional Formatting in Google Sheets

 

Now, we have the final result as:

How to use google sheets conditional formatting based on another column 18

Notes

If you are using method 2, make sure to remember that any change applied to the cell which was copied will change the rest of the cells of the column where Conditional Formatting was pasted using Paste Special.

Sometimes formula apparently looks correct but does not work properly, some of the major mistakes in applying the formula are:

Excluding “=” when writing the formula:

Some user may forget to put = sign while writing the formula, if so, sheet would not recognize the given condition as formula ad hence no Conditional Formatting will appear although Conditional Formatting is applied.

Cell number might be wrong:

In large worksheets, it is quite difficult to calculate the cell number just by observation so it is better to use a more convenient way.

Click on the cell, whose cell number is required. You can see its number on top left portion of sheets as:

How to use google sheets conditional formatting based on another column 19

Conclusion

Applying google sheets conditional formatting based on another column provides an easy way to perform Conditional Formatting based on another column values. In this article, easy step-by-step method is described. Any queries are most welcomed. Feel free to comment below for any questions.

Thanks for Reading!

Content Protection by DMCA.com

Widad Khan

Hi, I am Widad Khan. I was born and raised in Pakistan. I have always loved the field of Computer Sciences and since I was a kid, I would say I wanna grow up to be a scientist, now this term is mostly related to "Computer Scientist" and not scientist alone. I am pursuing my Bachelor Degree in Computer Sciences from GCU, Lahore. I have always loved keeping records of different types of data. Earlier, I would use MS Excel for that. But with the advancements in Google Sheets, my work has become much easier. And also it's very easy to share and coordinate data with fellows and coordinates.

OfficeDemy.com
Logo
Enable registration in settings - general