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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
> ------------------------------------------------------------------------
> lburg801's Profile:
> http://www.excelforum.com/member.php...o&userid=28338
> View this thread: http://www.excelforum.com/showthread...hreadid=480045
>

[lburg801]

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

I appreciate having two options. Surely one will work!

I have a question about Option 1. There are no fields on this worksheet where a single zero would be input. Is it the occurrance of a single zero that the option addesses, and not cells containing phone numbers 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 worksheet where a single zero would be input. Is it the occurrance of a single zero that the option addesses, and not cells containing phone numbers and addresses that might include zeros?

Thanks,
Trudy

Hi Trudy


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

[lburg801]

Thanks so much!

Trudy