Excel Conditional Formatting Based on Another Column

There will be times when you would want to format cell or column based on another column's value. Maybe this is that time for you. That's why you are here. Anyway, this can be done easily in excel. Let's learn how to format cells based on another column's value with the help of an example.
Example: Highlight months that have sold more than average sales
So, here I have data on sales done by different dealerships in months of different years. I want to highlight sales in 2019 that are greater than sales in 2018.

To do so, follow these steps.

Select range D2:D12 (Sales of 2019)
Go to Home ? Conditional Formatting ? New Rule.
Here, select "Use a formula to determine which cell to format"
In the formula box, write this excel formatting formula.

=$D2>$C2

Select the formatting of the cell if the condition is true. I have selected a green fill.

Hit the OK button.

And it's done. All the values in sales 2019 that are greater than the sales in 2018 are highlighted with green fill.

How does it work?

It is easy. First, we select the range on which we want the formula to apply. Next, we use a formula to determine which cell to format in the selected range. The formula is $D2>$C2. Here we have locked columns and allowed rows to change. This is called half absolute referencing. Now, D2 is compared with C2, since D2 is greater than C2, D2 is filled with green colour. Same happens with each cell. If you wanted to highlight months on instead of sales in 2019, you can directly change "formula applies to" to the range A2:A12.

Select any cell in D2:D12.
Goto conditional formatting. Click on "Manage Rules".
Change the range in "Applies to" box to A2:A12.

Hit OK button.

You can see that formatting is applied to the mentioned reference. Similarly, you can format any range based on any column in excel. The column can be on the different sheet too. You just need to mention the range. You can also mention the non-connected ranges. Just use a comma between ranges in "applied to" section.

So yeah guys, this how can do conditional formatting in excel based on another column. This sounds tricky but in practice, it is quite easy. You just need to work around a little bit. I hope this article was explanatory enough to help you out on conditional formatting in excel. You have any doubt, mention it in the comments section below.