Excel conditional formatting entire column based on another column

I want to have a cell highlighted green if the next cell is greater than the current cell. This formatting should apply for an entire column.

E.g. if A2 > B2 then A2 should become green. If A3 > B3 then A3 should become green, and so on.

Note: I want to not have this done by individual formatting, but by a general rule.

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.

Related Articles:

Expanding References in Excel

Relative and Absolute Reference in Excel

Shortcut To Toggle Between Absolute and Relative References in Excel

Dynamic Worksheet Reference

All About Named Ranges In Excel

Total number of rows in range in excel

Dynamic Named Ranges in Excel

Popular Articles:

50 Excel Shortcut’s to Increase Your Productivity

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

Have you ever tried to apply conditional formatting to a column based on values in a separate column in Excel? This tutorial will guide you how to solve the problem.

Apply conditional formatting based on values in another column
More tutorials for Conditional Formatting…

Apply conditional formatting based on values in another column

Supposing you have a table as the below screenshot shown, and want to highlight cells in column B if the adjacent cell values in column C are greater than 800, please apply the Conditional Formatting function as follows.

1. Select the column cells you will highlight (here I select range B2:B13), and then click Home > Conditional Formatting > New Rule.

2. In the New Formatting Rule dialog box, please configure as follows.

2.1 Click Use a formula to determine which cells to format option in the Select a Rule Type section;
2.2 Copy the below formula into the Format values where this formula is true box;
=$C2>800
2.3 Click the Format button to specify a fill color;
2.4 Click the OK button. See screenshot:

Note: In the formula, $C2 is the first cell of the column that contains the criteria you need, and >800 is the criteria you will highlight cells based on. Please change them to meet your needs.

You can see cells in column B are highlighted by certain fill color if the adjacent cells in column C are more than 800.

Conditional format dates less than/greater than today in Excel
You can conditional format dates based on current date in Excel. For example, you can format dates before today, or format dates greater than today. In this tutorial, we will show you how to use the TODAY function in conditional formatting to highlight due dates or future dates in Excel in details.

Ignore blank or zero cells in conditional formatting in Excel
Supposing you have a list of data with zero or blank cells, and you want to conditional format this list of data but ignore the blank or zero cells, what would you do? In this article, we will show you how to use conditional formatting with ignoring blank or zero cells in Excel.

Highlight cells based on length of text in Excel
Supposing you are working with a worksheet which has list of text content, and now, you want to highlight all the cells that the length of the text is greater than 15. In Excel, there is no direct way for you to highlight the cells, but you can apply the Conditional Formatting function along with a formula to solve it.

Apply conditional formatting search for multiple words in Excel
It may be easy for us to highlight rows based on a specific value, this article, I will talk about how to highlight cells in column A depending if they are found in the column D, which means, if the cell content contains any text in a specific list, then highlight as left screenshot shown.

More tutorials for Conditional Formatting…