How to have cell values of 0 showing as blank

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am linking data from another worksheet, but I want any value which is 0 to
show a blank cell. What would be the easies way to do this?
 
=IF(Sheet3!A1<>"",Sheet3!A1,"")


=IF(Sheet3!A1=0,"",Sheet3!A1)#

try either of these
 
Worked beautifully, is there any way of not getting those 0's to not show on
a chart as well?
 
Hi Alex

If you want the chart to ignore zero values you would need to use NA()
in place of Null in Mike's formulae

=IF(Sheet3!A1<>"",Sheet3!A1,NA())
This will show #N/A in the cells, which the Chart will ignore as a
datapoint.

If the aesthetics of having #N/A showing on the sheet bothers you, then
you could apply conditional formatting on the cells to set the Font the
same colours as the background of the cell.
In the CF, use Formula Is and =NOT(ISNUMBER(A1))
 
I did that, but it gets stranger, the value pn the chart also shows as #N/A.
Why would this be?
 
I am having a problem similar to Alex's. I am trying to hide the #N/A
symbol. I tried the conditional formatting angle, but it didn't work. Any
suggestions?
 
Hi Catherine

Mark the range of cells where your #N/A's appear.
Format>Conditional Formatting>use dropdown for Formula is>
=NOT(ISNUMBER(A1))

Replace A1 with the cell address of the first cell in your marked range.
 
Thank you very much. That worked perfectly.

Roger Govier said:
Hi Catherine

Mark the range of cells where your #N/A's appear.
Format>Conditional Formatting>use dropdown for Formula is>
=NOT(ISNUMBER(A1))

Replace A1 with the cell address of the first cell in your marked range.
Click to expand...
 
assuming that the 0 is a result of a formula

=if((yourformula)=0,"",(yourformula))
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel formula, sum the value cell and if empty cell left blank 0
Linking sheets how do I display the zeros (0) as blank cells 1
When I link blank cell in one sheet registers as 0 for averaging 3
If function across sheets prints value of cell 4
I need a cell to be blank. 4
conditional format if cell value 0 pattern red value blank nothing 1
#value error for sumproduct using RIGHT cmd 0
formula finds values in apparently blank cells 1

Back
Top