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.
1
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.
- Select the formatting of the cell if the condition is true. I have selected a green fill.
- Hit the OK button.
=$D2>$C2
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.
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.
Related articles
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…