Excel inserted 0's in cells linked to blank cells

[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

 

[Paul Sheppard]

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

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

and any real value zeros will still appear, you can then copy the
formula for other cells you want copied across

 

[Alan]

Try 'Tools' > 'Options' , on the 'View' tab, uncheck 'Zero Values' > OK
Regards,
Alan.

 

[lburg801]

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

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 :

 

[Paul Sheppard]

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

Hi Trudy


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

 

[lburg801]

Thanks so much!

Trudy :