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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
993
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
993
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,113,824
Messages
5,544,541
Members
410,619
Latest member
gregor222
Top