Excel - How to Use Conditional Formatting With IF Statement - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Column A contains name of participants. Column B contains the binary info of whether the participant won. If won, then 1, if lost, 0. So, if I assign 1 to column B(meaning the participant won), I want the cell in column A&B to be highlighted in green. Used Conditional formatting. But unable to assign this rule to all participants at once. Instead, I have to conditional format every participant.
Solved by X. H. in 10 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 26/08/2018 - 08:02
Hello! How are you?
User 26/08/2018 - 08:03
Hi.
User 26/08/2018 - 08:03
So, any idea how to deal with this?
Excelchat Expert 26/08/2018 - 08:03
Looks like you need help with conditional formatting... Is it possible for you to share your worksheet with me?
User 26/08/2018 - 08:03
I can't share the worksheet.
User 26/08/2018 - 08:03
But if you allow me to edit the document, I could add in.
Excelchat Expert 26/08/2018 - 08:03
Sure. Thanks
Excelchat Expert 26/08/2018 - 08:04
A couple of sample data would be fine :)
User 26/08/2018 - 08:04
Now, I assigned the winners with 1, and the loser/s with 0.
User 26/08/2018 - 08:05
I want the winner rows to be automatically highlighted with green.
Excelchat Expert 26/08/2018 - 08:05
both a and b right?
Excelchat Expert 26/08/2018 - 08:05
Okay, one moment
User 26/08/2018 - 08:05
Yeah. But want to do it at once.
Excelchat Expert 26/08/2018 - 08:05
Like this?
User 26/08/2018 - 08:05
Not seperately conditional formatting the concerned rows.
Excelchat Expert 26/08/2018 - 08:06
Try entering new entries to the columns
User 26/08/2018 - 08:06
Great, it works!
User 26/08/2018 - 08:06
It works!
User 26/08/2018 - 08:06
Thanks.
Excelchat Expert 26/08/2018 - 08:07
By the way, are you using Excel or Google sheet?
User 26/08/2018 - 08:07
So, what is the method?
User 26/08/2018 - 08:07
Exel
Excelchat Expert 26/08/2018 - 08:08
Okay, so for excel - just highlight the columns you would like to apply the conditional formula. In our case, columns A and B
Excelchat Expert 26/08/2018 - 08:08
Go to Home>Conditional Formatting > New Rule
User 26/08/2018 - 08:08
ya
User 26/08/2018 - 08:09
And?
Excelchat Expert 26/08/2018 - 08:09
One moment
Excelchat Expert 26/08/2018 - 08:09
I'll send you a screenshot
User 26/08/2018 - 08:09
ok
Excelchat Expert 26/08/2018 - 08:09
Follow this screenshot please
[Uploaded an Excel file]
Excelchat Expert 26/08/2018 - 08:10
So Select a Rule Type > Use a formula....
User 26/08/2018 - 08:10
And then add that formula?
Excelchat Expert 26/08/2018 - 08:10
and in the formula bar type =$B1=1
User 26/08/2018 - 08:10
and drag it down?
Excelchat Expert 26/08/2018 - 08:10
No more dragging required
Excelchat Expert 26/08/2018 - 08:11
As you may see, the conditional formula is pegged to the value of column B (notice the $ before B)
User 26/08/2018 - 08:11
Oh. because we highlighted the entire columns and its pegged
User 26/08/2018 - 08:11
ok
User 26/08/2018 - 08:11
let me just try it
User 26/08/2018 - 08:11
one sec
Excelchat Expert 26/08/2018 - 08:11
so for any of the rows where the value of the corresponding cell in B equals 1, the conditional formatting will be activated
Excelchat Expert 26/08/2018 - 08:11
Sure. Take your time :)
User 26/08/2018 - 08:12
Yes! It is working fine! Thank you very much!
User 26/08/2018 - 08:12
Have a nice day!
Excelchat Expert 26/08/2018 - 08:12
Happy to hear that!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc