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.

Hi I am looking for help on conditional formatting I have two ranges of 11 cells and I would like a singular cell in the first range to flash red if it less than the bottom range, could you please assist?
Solved by D. L. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 21/08/2017 - 12:19
so first range is G9:G21
User 21/08/2017 - 12:19
and the second is G32:44
Excelchat Expert 21/08/2017 - 12:20
Okay,
Excelchat Expert 21/08/2017 - 12:20
When you say if it is less than,
User 21/08/2017 - 12:20
so like if G32 is less than G9, i need it to flash red
Excelchat Expert 21/08/2017 - 12:20
do you mean the sum of all the numbers or just that one number compared to the others?
User 21/08/2017 - 12:20
the one number
User 21/08/2017 - 12:20
so like if G33 is less than G10
User 21/08/2017 - 12:21
i need G10 to flash red
User 21/08/2017 - 12:21
and i need this repeated for the whole range
User 21/08/2017 - 12:22
cause as of now, it highlights the entire range
User 21/08/2017 - 12:23
so just need it to highlight the one cell that is too large
Excelchat Expert 21/08/2017 - 12:23
https://docs.google.com/spreadsheets/d/1f6UlO3EshEdGd21QRyFWTq0Hr2iMLqouICrtgQd3IXM/edit?usp=sharing
Excelchat Expert 21/08/2017 - 12:23
Please click on on the link.
User 21/08/2017 - 12:23
could you copy and paste it? sorry my company website doesnt let me use google docs
User 21/08/2017 - 12:24
or how do i edit this formula?
User 21/08/2017 - 12:24
=$G$32:$G$43<$G$9:$G$20
User 21/08/2017 - 12:24
cause thats what i have currently
Excelchat Expert 21/08/2017 - 12:25
I'm reviewing now. One moment please.
User 21/08/2017 - 12:25
ok ty
User 21/08/2017 - 12:29
any idea?
Excelchat Expert 21/08/2017 - 12:29
Yes.
Excelchat Expert 21/08/2017 - 12:29
Now we can continue into the explanation step of this session. I am going to perform the following:
User 21/08/2017 - 12:29
ok
Excelchat Expert 21/08/2017 - 12:29
=$G9<$G32
Excelchat Expert 21/08/2017 - 12:29
Put that in custom formula within conditional formatting.
Excelchat Expert 21/08/2017 - 12:30
I’ve completed the solution, take a look if you would like. We can now continue to the discussion stage and I can answer any questions you may have with the solution I provided.
User 21/08/2017 - 12:30
how do you copy it to the rest though?
User 21/08/2017 - 12:30
is there an easy way?
Excelchat Expert 21/08/2017 - 12:30
Once you have that in G9 you copy the rest through by using the format painter at the top of excel.
User 21/08/2017 - 12:30
oh ok
User 21/08/2017 - 12:30
one second gonna test
Excelchat Expert 21/08/2017 - 12:30
Okay, thank you.
User 21/08/2017 - 12:32
doesnt seem to be working//
Excelchat Expert 21/08/2017 - 12:33
look at G9
User 21/08/2017 - 12:33
yeah im at G9
User 21/08/2017 - 12:33
so if G9>G32
User 21/08/2017 - 12:33
i want it to flash red
Excelchat Expert 21/08/2017 - 12:33
Nothing happens.
Excelchat Expert 21/08/2017 - 12:34
I thought is was less than, not greater?
User 21/08/2017 - 12:34
no its greater
Excelchat Expert 21/08/2017 - 12:34
=$G10<$G33
Excelchat Expert 21/08/2017 - 12:34
You use this formula then.
Excelchat Expert 21/08/2017 - 12:35
Sorry for the confusion.
User 21/08/2017 - 12:35
ok i think i got it
User 21/08/2017 - 12:35
how do you use the format painter to quickly copy it?
User 21/08/2017 - 12:35
i also need to add it to the other columns as well
Excelchat Expert 21/08/2017 - 12:36
So go to G9, press the format painter icon and drag to the end of your range.
Excelchat Expert 21/08/2017 - 12:36
Take the $ out of your formula and then you can drag it whereever.
Excelchat Expert 21/08/2017 - 12:36
so =G10<G33
Excelchat Expert 21/08/2017 - 12:36
instead of what I sent before.
User 21/08/2017 - 12:37
and i can drag horizontally as well?
User 21/08/2017 - 12:37
like into other columns?
Excelchat Expert 21/08/2017 - 12:37
yup.
User 21/08/2017 - 12:37
ok awesome it works
User 21/08/2017 - 12:37
thanks for your help
Excelchat Expert 21/08/2017 - 12:38
Great! :)
Excelchat Expert 21/08/2017 - 12:38
You're very welcome is was my pleasure.

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