Copy an Excel formula without changing its cell references

by Mary Ann Richardson | Oct 31, 2006 8:00:00 AM

Tags: Microsoft Office, Mary Ann Richardson, Microsoft Excel, cell reference, Microsoft Office Suite Excel Tips Newsletter

Takeaway: If you copy a formula from one place to another, Excel will want to readjust the references to reflect the change. However, if you want the formulas to stay the same, Mary Ann Richardson can help end the confusion.

When working with a spreadsheet, when you copy a formula to another location, Excel automatically adjusts the cell references in that formula to the new location. If you do not want to have Excel adjust the cell references, you would have to make them absolute before copying them. Or, you could copy the formula to the clipboard as text before pasting it to its new location. Follow these steps:

1. Click on the cell containing the formula you want to copy.

2. Press [F2].
3. Click and drag to select the entire formula.
4. Click the Copy button in the Standard toolbar.
5. Press [Enter].
6. Select the cell into which you want to paste the formula.
7. Click the Paste button in the Standard toolbar.

When you copy an Excel formula in this manner, the formula copies as text and will not adjust its cell reference. You can also copy part of a formula this way. For example, you may want to include the formula as part of an If statement in another part of the worksheet.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Print/View all Posts Comments on this article

Absolute referencesgbramley@...  | 11/01/06

Range of FormulasOrangeBull  | 11/01/06

Now that is interesting...Only if you ask nicely  | 11/01/06

Thanks!gio@...  | 11/01/06

How to teach an end user?spjeff@...  | 02/07/07

Range of Formulasmary.stranghoener@...  | 06/09/08

Awesome!pixelsyl@...  | 09/26/08

ASAP utilities have this and 300 other functions..Free!!medler1@...  | 11/01/06

Named Rangesdavid.turpin@...  | 11/01/06

Absolute referencessms2@...  | 11/01/06

It is a easy Stepram_viswa2004@...  | 12/22/06

Another way to do thisernst_berger@...  | 02/22/07

Copy an Excel formula without changing its cell referenceskaransoin  | 11/01/06

Referencing a single cell in mutiple formulas.lami50@...  | 11/01/06

A Good ShortcutRichard Noel  | 11/01/06

=$a1+b$2Absolutely  | 12/28/06

f4 will put the $ sign injdclyde  | 12/28/06

F4 is for beginnersAbsolutely  | 01/03/07

Transfer Formula Results To As Raw DataFidel@...  | 05/11/07

Use F4 to create absolute referencerlasker@...  | 11/01/06

You are missing the point...Only if you ask nicely  | 11/01/06

Copying formulas without changing cell referencespeternp  | 11/22/06

here herettocsmij  | 11/28/06

What about multiple cellsspjeff@...  | 02/07/07

Replace trick for copying references as text in multiple cellsDiaz.RobertRichard@...  | 09/11/08

F4 and F2 -- two new tricksLevel  | 11/01/06

F4 Toggle: Good Tip!Doug Lowry  | 11/01/06

Ditto: Thanks for the Great TipJamieM  | 11/01/06

Good tip.inalt@...  | 11/01/06

converting to text.zylstra@...  | 11/01/06

Converting to text works well!fractalzoom  | 11/01/06

F4 toggle keyDaiquiria  | 11/01/06

Formula or resulttechr@...  | 11/01/06

Why Press F2?The Bobinator  | 11/01/06

Copying in Excelbrucelloyd@...  | 11/06/06

Simple drag and dropDBlayney  | 11/22/06

We need a man lawThe Bobinator  | 11/22/06

I like the keyboardmichaelvdnest  | 12/20/06

Very helpfulmichaelc@...  | 11/24/06

VisiCalc was quite flexible in this regard.deepsand  | 12/21/06