Paste formulas without auto filling

**naquer** · 09-28-2009, 03:24 AM

Hello

Is it possible to disable the auto filling, when copy/pasting formulas?

I have a range of formulas, which I want to copy/paste, and I want the pasted formulas to be the exact same as the copied.

Is this possible? I would prefer not having to use $ on all formulas.

Thanks.

**teylyn** · 09-28-2009, 03:33 AM

I would prefer not having to use $ on all formulas.

Why not? This is the Excel syntax to ensure references are not changed when formulas are copied.

Are you aware that you can use the F4 key when editing formulas to quickly toggle between the four possible states, i.e. A1 to $A$1, $A1, A$1

Use this to suit on a complicated formula and copy down and across without a hitch.

**naquer** · 09-28-2009, 03:41 AM

Basically because I have a large sheet, and I'm too lazy to use $ on all formulas ;-)

But if that's the only way, hitting F4 twice, so that rows are fixed seems to solve my problem.

I will mark as answared

**JethroUK** · 09-28-2009, 03:45 AM

If you want to move the formulae somewhere else but keep the data in the dsame place you could copy the formulae

Edit>Paste Special

"Paste Link"

**naquer** · 09-28-2009, 03:59 AM

Thanks ! :D

exatly what I was looking for. If I could copy the formatting along as well it would be perfect.

**teylyn** · 09-28-2009, 04:03 AM

Naquer, you can do that in a second step. First paste the formulas, as Jethro has suggested (brilliant!) and then go Edit - Paste Special - Formats

...

**naquer** · 09-28-2009, 04:07 AM

Thanks guys!

Exatly the solution I needed!

**teylyn** · 09-28-2009, 04:13 AM

just be aware that after pasting (special - Paste Link) the formulas, if you ever change the original formula, the pasted link will update and reflect the changed formula's result.

What you're actually pasting is not the formula itself, but a link to the original cell, thus displaying just the value of that cell.

If that suits your purposes, fine, but be aware of the implications.

cheers