Excel inserted 0's in cells linked to blank cells

  • Thread starter Thread starter lburg801
  • Start date Start date
L

lburg801

I added a work sheet by copying selected data from main worksheet (copy,
paste special, link) and where there were blank cells in original
worksheet, Excel inserted 0's. Is there a quick way to get rid of them.
Blank cells are to be expected in this database of names, info, etc.

My approach was to turn on auto filter and select for 0, highlight
column and delete contents. Hoping there is a less tedious way to do
this.

Thanks,
Trudy
 
lburg801 said:
I added a work sheet by copying selected data from main worksheet (copy,
paste special, link) and where there were blank cells in original
worksheet, Excel inserted 0's. Is there a quick way to get rid of them.
Blank cells are to be expected in this database of names, info, etc.

My approach was to turn on auto filter and select for 0, highlight
column and delete contents. Hoping there is a less tedious way to do
this.

Thanks,
Trudy
Click to expand...

Hi Trudy

2 Options:

Option 1: Tools > Options > View Tab > Window Options, click in the
zero values box to remove the tick, this will suppress all zeros'
problem with this method is if there is a zero that should be there it
will be suppressed

Option 2: The formula on your sheet you copied to will look something
like this > =Sheet1!A1, you could cahnge the formulas to this
=IF(Sheet1!A2="","",Sheet1!A2), you then do not need to do Option 1
Click to expand...
and any real value zeros will still appear, you can then copy the
formula for other cells you want copied across
 
Paul said:
Hi Trudy

2 Options:

Option 1: Tools > Options > View Tab > Window Options, click in th
zero values box to remove the tick, this will suppress all zeros
problem with this method is if there is a zero that should be there i
will be suppressed

Option 2: The formula on your sheet you copied to will look somethin
like this > =Sheet1!A1, you could cahnge the formulas to this
and any real value zeros will still appear, you can then copy th
formula for other cells you want copied across
Click to expand...

I appreciate having two options. Surely one will work!

I have a question about Option 1. There are no fields on this workshee
where a single zero would be input. Is it the occurrance of a singl
zero that the option addesses, and not cells containing phone number
and addresses that might include zeros?

Thanks,
Trudy :
 
lburg801 said:
I appreciate having two options. Surely one will work!

I have a question about Option 1. There are no fields on this workshee
where a single zero would be input. Is it the occurrance of a singl
zero that the option addesses, and not cells containing phone number
and addresses that might include zeros?

Thanks,
Trudy :)
Click to expand...

Hi Trudy


Option 1 will only supress a single zero, so zero's contained wit
other data are saf
 
Back
Top