asked on # MS Excel Conditional Formatting - Ignore "-"

I'm using conditional formatting in Excel 2010 to identify values in a table greater than a certain value. Unfortunately, the table has a dash ('-') in each cell where there is no value and for some reason this meets the criteria so the cell is shaded along with the cells that have the relevant value. Can anyone suggest a way around this?

If it involves using multi-criteria, can you tell me how to set up this as cant see how to do so in Excel 2010 (used to be able to do this in Excel 2003).

Thanks

If it involves using multi-criteria, can you tell me how to set up this as cant see how to do so in Excel 2010 (used to be able to do this in Excel 2003).

Thanks

Microsoft Excel

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Thanks Rorya. How should formula be written so it works for multiple cells...ie I have cells within 4 columns and 300 rows that I want to highlight if their value is greater than 50.

Select all the cells and use that formula but replace A1 with whatever is the active cell. Note the A1 is relative - i.e. no $ signs anywhere.

This would eliminate the zero, as represented by the "-" in the number format.