Conditional formatting in excel based on value in another column

Question

I have situation like this in excel:

Col1 Col2
3.4  1
5.6  0
4.6  0
8.7  1
10.1 0

I want to format Col1 based on values of Col2. I want to fill the background of Col1 with say green if corresponding value in Col2 is 1

I saw some examples of coloring where value>value in other cell. But none of this type.

Thanks

office.microsoft.com/en-us/excel-help/…

Dmitry Pavliv
– Dmitry Pavliv

2014-05-24 06:49:30 +00:00
Commented May 24, 2014 at 6:49 — Dmitry Pavliv
– Dmitry Pavliv, Commented May 24, 2014 at 6:49

jweaks · Accepted Answer · 2014-05-24 06:50:43Z

1

Here's how to do exactly what you say:

Select first cell in column A.
Select Conditional Formatting/New Rule.
Select Classic Style conditional formatting.
Choose Use a formula to determine which cells to format.
Make the formula: =$B1=1
Choose your formatting (as in green)
Choose the Format Painter
Select the column to apply the conditional formatting to all cells in that column. Note: some variants in terminology and location of options between versions of Word, but the basic functionality is the same.

answered May 24, 2014 at 6:50

jweaks

3,8021 gold badge22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

munish bansal Over a year ago

Thanks. In addition, what i want is . if value =1 and greater than another col3 value then green if less then say red

jweaks Over a year ago

You simply create: =B1>1 then green, then create a second rule on the same cell for: =B1<1 then red. The Format painter will copy both rules as you copy/past them.

munish bansal Over a year ago

Actually situation is little different. After i found the green. I want to apply new rule, that if color is green in that column i would compare the corresponding value in say col3 and want it green if greater and red if less

jweaks Over a year ago

You use the AND() function in you condition will work for you for your second rule, it looks like: =AND(A1>1, B1<1). If that tests true, then the formatting will override the previous one.

jweaks Over a year ago

You may have to post more info. But you may have misunderstood me... each new rule will only add to the formatting, unless it is the same kind of formatting (like background color), in which instance the last rule overrides the previous.

Collectives™ on Stack Overflow

Conditional formatting in excel based on value in another column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related