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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,017
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,017
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,114,652
Messages
5,549,210
Members
410,905
Latest member
Extjel
Top