Conditional Formatting - Three colors for various values

bigpahpa

New Member
Joined
Feb 27, 2018
Messages
2
I have a column that has a percent value and I'd like <1% to be green, 1-2% yellow and >2% to be red.
Is it possible to have multiple conditions on a single row? I can't figure out how to make that work. Any help would be GREATLY appreciated!
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,987
Office Version
365
Platform
Windows
Welcome to the Board!

You can set up three different Conditional Formatting rules.
If you use the formula option of Conditional Formatting, let's say that the value you want to check is in cell A1.
Then you would just highlight all the rows you want to apply this to, and write the CF formula as it applies to the first row, i.e.
Code:
=$A1 < 1%
and choose your green color.

For the second one, your formula would look like:
Code:
=AND($A1 >= 1%,$A1 <= 2%)
and choose the yellow color.

And the third one:
Code:
=$A1 > 2%
and choose the red color
 

bigpahpa

New Member
Joined
Feb 27, 2018
Messages
2
Thank you for the welcome and the crazy quick reply!

I was able to do the first formula fine but the second formula fails to highlight anything and the third formula just turns my whole selection red.
It's confusing but I'm going to make another attempt when I have more time.

Thanks again for the reply and the help!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,987
Office Version
365
Platform
Windows
A few things to check:

- Make sure that the formulas you are writing match up with the range you are selecting to apply this to.
If you are selecting a multi-cell range, write the formula as it pertains to the FIRST cell in your selection, and Excel will adjust the rest accordingly if you have used the proper mix of absolute and relative range references (see: http://www.cpearson.com/excel/relative.aspx)

- Make sure your values are really entered as values and not text.

- Make sure that you refer to the number properly.
Note that 1% is the same as .01. So you can use either value.
But some people mistakenly think 1% is the same as 1, and that is NOT the case.

If you still cannot get it to work, please let us know what range you are selecting, and exactly what formulas you are entering in.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,481
Messages
5,444,736
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top