CODEWITHSUNDEEP
excelexcel-formula
Trevor D
Trevor D

Reputation: 743

Any way to copy/paste formulas in a range of cells but keep absolute?

I have a matrix of data and each cell is a formula. All cell formulas are relative, so when I copy and paste just below, the formulas are now off.

Here's what I know works:

I don't want to go and F4 each part of every formula for dozens of cells. Is there a way to copy paste a large batch but tell Excel to keep the exact formulas as written?

Upvotes: 3

Views: 1881

Answers (2)

BruceWayne
BruceWayne

Reputation: 23283

When pasting, right click and choose "Paste Special" and use "Paste Formulas" (the fx icon).

Edit: Ah, I realize now this only works for 'fully anchored' ($A$1) cell references.

Upvotes: 0

XOR LX
XOR LX

Reputation: 7762

I personally do a bulk Find and Replace on the original range, replacing all "=" signs with a random text string which is guaranteed to not occur anywhere within those formulas, my usual choice being "##".

This converts all the formulas to text strings, after which you can paste the range before then performing the reverse Find and Replace, i.e. replacing "##" with "=", which coerces Excel into treating the strings as actual formulas once more.

Of course, if we're talking about a very large amount of formulas, i.e. several thousand, then this operation can take a while to perform, since not only is Excel required to carry out the Find and Replace on all strings, but also to calculate all the newly-created formulas.

Regards

Upvotes: 3

Related Questions