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.
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.
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.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.