Conditional Formatting and blank fields

Pumpk1n

Board Regular
Joined
Jan 31, 2017
Messages
80
I have an incoming CSV file which has blanks where no data exists. When I apply conditional formatting using the EXCEL functionality (i.e. where cell value is greater than a specific value), the blank rows are also shaded. There are different columns -- one is formatted as "accounting" and the other is formatted as "custom" but neither treats the CSV value as 0. I'd like those blank CSV fields treated as 0 so they don't meet the criteria. Is there a way to do tha within existing conditional formatting functionality?
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,169
Office Version
  1. 2016
Platform
  1. Windows
Hi Pumpk1n,

Have you tried and AND statement to also check for a null cell?

csv2.csv
DE
1Value1Value2
27
355
43330
5022
csv2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:E5Expression=AND(D2<>"",D2>0)textNO
 

Pumpk1n

Board Regular
Joined
Jan 31, 2017
Messages
80
I don't believe I can apply that formula based on what I see in the formatting selection box. Can you confirm.

1599054664623.png
 

Attachments

  • 1599054624343.png
    1599054624343.png
    20 KB · Views: 0

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,169
Office Version
  1. 2016
Platform
  1. Windows
Pumpk1n,

You need to select the formula option

1599055327912.png
 

Attachments

  • 1599055187083.png
    1599055187083.png
    30.9 KB · Views: 2

Watch MrExcel Video

Forum statistics

Threads
1,118,888
Messages
5,574,842
Members
412,620
Latest member
sharma7s
Top