Office 2007 Excel Paste Special formula only

[JMFleming]

When copying a column that contains data for a formula and the formula to a
new column, the paste special function brings the data values and the formula
to the new location. The past special function should bring the formula only
and set the data to zero. Paste special formula only does not work on this
column copy and past. Anyone have a work around or have the same trouble?

 

[Ken Wright]

The past special function should bring the formula only and set the data

to zero.

No it shouldn't, although that may have been your interpretation of what the
function does. What actually happens if you copy a mixture of values and
formulas and then paste is as follows:

Paste
Standard paste operation pastes everything, that being values as values,
formulas as formulas and formats as well.

Paste Special - Values
This paste operation pastes everything as values only. Values stay values
and formulas become values, but NO formatting is pasted.

Paste Special - Formulas
This paste operation pastes everything as whatever it started as, ie values
stay values and formulas stay formulas, but again, NO formatting is pasted.

For what you want to do, I would simply copy and paste everything using
standard paste operation, then whilst your paste area is still selected, do
Edit / Go To / Special / Constants and thensimply hit delete, or, type 0 and
then hit CTRL+ENTER which will replace all your constants with a 0

In 2007, the above all still holds good, although you have to find the Go To
/ Special function on the right of the Home tab (Its a pair of binoculars)

Regards
Ken.............................