Select the entire range (using your example, B1:B10). Add a new conditional formatting rule.
Select Use a formula....
the formula you use should be =(B1>=A1) (or =(B1<A1) for the red)
you will have to type this, as using arrows or clicking on the cells will insert a fixed reference with $ signs, which we have to avoid.
Use the Format button to change the color (fill color for the example).
Now when you click OK, you will see the color change for the entire range based on the value next to them, rather than a fixed value you get when clicking on cells
In the Greater Than window, you have to delete the value that appears in the box, and then select the cell reference, in this example $I$6, or input specific value that is the lowest limit for you.
To drop down the list for formats click Custom Format, click the Fill tab, and click on the blue dark fill color that you want.
To close the Format cells window click Ok, the cells with values greater than 90 would be colored dark blue as you choose the color format. Again press OK
Using customer Formula Rule
You can conditionally highlight the cells which are greater than or equal to set a value using customer-formula rule.
Select the cells to be formatted. In this example, cells C5:G10 is selected, From the Home tab, click the Conditional Formatting selection. Click on “New Rule” and click on “Use a formula to determine….” and enter following formula in Edit Rule Description window. Choose Format > Fill > Dark Blue color to preview and press OK.
=C5>=$I$6
This formula will check all the active cells in the selected range and will test each cell value against the set value in cell $I$6. All those cells will be highlighted which are greater than equal to a set value of 90.
Need some additional help with Conditional Formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.
Are you still looking for help with Conditional Formatting? View our comprehensive round-up of Conditional Formatting tutorials here.
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