Format column so that if a cell has no value then it's blank

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I have a column of data where I've added in two conditional formatting rules:

1) if the cell is 0, then the cell is filled with the colour green.

2) if the cell is NOT equal to zero, then the cell is filled with the colour red.

However, the formatting has made blank cells RED, as well!

Is there a way to add in a condition to say if the cell is blank, then fill it with NO COLOUR?

I used the following steps for the first two rules:

Conditional Formatting / Highlight Cells Rules / Equal To ....then selected 0 and chose the green fill.

Conditional Formatting / Highlight Cells Rules / More Rules / Format Only Cells That Contain / Cell Value / Not Equal To ....then selected 0 and chose the RED fill.

I'd be grateful for your thoughts.

Thanks in advance
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
1) if the cell is 0, then the cell is filled with the colour green.

2) if the cell is NOT equal to zero, then the cell is filled with the colour red.
Use the formula option, and use this formula for your first condition (assuming cell A1):
Excel Formula:
=AND(A1=0,A1<>"")
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,980
Messages
5,545,322
Members
410,676
Latest member
M0J0jojo
Top