Software

Copy an Excel formula without changing its cell references

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.

57 comments
lgomez
lgomez

need help. I have a value in one cell and I want to subtract individually the value in  50+ other cells. How can I keep the one cell in the formula for everyone without typing it individually? When I copy the formula down it changes the cell I want to keep constant. Can anyone help me?

shanawas04
shanawas04

click "show formula" in formula bar, then select the entire range u want to copy, then copy it,,,, open a word document and paste it,,,,, it will be pasted as formulas... now u can easily copy it from word and can paste it in excel for how many times u needed..... no replacements are needed.... :-)

gosto
gosto

Easiest way to copy multiple cells without changing cell references is to copy the whole sheet to a new sheet (right-click on sheet tab, click on Move or Copy) (be sure to select Copy!). Then cut and paste the range you want from the new sheet to the original sheet, and delete the new sheet.

naumene
naumene

Select all the cells where the formulae extst, click on show formulae on the "Formulas" ribbon and then copy using CRTL C. Now, Paste them into MS Word. Copy them back into the desired new excel workbook. It will work.

pappasa
pappasa

Yes, but how do you do the same for many cells? I do not want to copy just one cell at a time, but a whole region.

deepsand
deepsand

When copying a formula, one could do so in a manner so that VC would, as each Cell reference was encountered, ask if such was to be copied as Absolute or Relative. Sometimes older is better.

michaelc
michaelc

Never had I tried that / I have been looking for a way to Copy excel formulas and move them with out the formula changing. thank you again very much!

brucelloyd
brucelloyd

The thrust of this article is about copying an Excel formula without changing its cell references. This can also be useful if you don't want to copy cell formatting, especially if you paste into another application such as an e-mail, IM program, search field in an Internet database, etc. Many times the paste won't work if it contains anything other than text. The F2 key invokes edit mode just like a double click in the cell or selecting the string of text in the formula bar or inside the cell. Instead of pressing Enter at step 5, you can also press Esc to exit Edit mode. Now you can right-click in the destination cell or field and select paste. Unfortunately, Ctrl+A does not work in edit mode. However, after pressing F2, you can press Shift+Home to select the entire cell contents vice dragging over the cell contents with your mouse. Unfortunately, Paste Special doesn't have the desired option. However, it does have a lot of options that can be used in a variety of scenarios.

The Bobinator
The Bobinator

If you're going to click & drag to copy the formula, why not just click and drag to copy from the Formula Bar at the top of the screen instead, and skip pressing F2 altogether. Not much different but eliminates a useless keyboard step.

Realvdude
Realvdude

If your copying the formula for presentation reasons, why not reference the result instead. This way if you adjust the formula, you only adjust it in one place.

rlasker
rlasker

You can avoid typing the "$" by selecting the cell reference in the formula bar and toggling the F4 key to create an absolute cell reference. You can create $A$4 or $A4 or A$4 just by toggling the selected A4 reference.

karansoin
karansoin

After having written the formula for instance like =A1*B1/D1 To achieve the result of copying and pasting so that the cell references do not change all you need to do is to modify the formula like this =$A$1*$B$1/$D$1 now the cell references are absolute references and will not change

gbramley
gbramley

If the formula cell references stay the same why not make them absolute references by just pressing F4 with the cursor with the reference

sashy
sashy

Easiest way in my experience is: 1. Select the range of cells you need to copy the formula to 2. While cells are highlighted start typing the formula 3. Once done do Ctrl+Enter 4. Whatever you typed will be copied in all the cells Note: If you have a long formula and do not want to type then before you do step 1 copy the formula without "=", and then in Step 2 type = then do Ctrl+V and then Ctrl+Enter.

naumene
naumene

Select all the cells where the formulae extst, click on show formulae on the "Formulas" ribbon and then copy using CRTL C. Now, Paste them into MS Word. Copy them back into the desired new excel workbook. It will work.

DBlayney
DBlayney

Brucelloyd is absolutely right, the article is about copying a formula. However, I don't often need a _copy_ of the same formula, more often what I need is to _move_ the cell containing the formula without the references changing. The simplest solution in many cases is simply to highlight the cells or cells that contain the formula(s) that you want to relocate and then drag and drop into the new position. By the way - the magic F4 key has another great use: it does the same as CTRL+Y (repeat last editing action) but in a more comfortably on the keyboard.

JamieM
JamieM

Thanks, I've been using Excel for many years and was never aware of the F4 toggle. Everybody keep sharing your great tips and tricks. The simplest ones may be something very useful. Even to us "old hats".

Doug Lowry
Doug Lowry

Thanks, rlasker, for this tip. I had forgotten that you could toggle the F4 key to move through the absolute reference options. Good Tip! Doug

Level
Level

Mary Ann's posting about about F2 and rlasker's explanation of F4 are both useful. Thanks!!

Only if you ask nicely
Only if you ask nicely

The whole idea is to copy the formula or part of a formula to another cell while keeping it dynamic. This tip is for people who didnt know that you could copy the formula text as is and could be beneficial in some cases...

MikeDrummond
MikeDrummond

The F2 - F4 style tricks are OK, but eventually I got fed up and wrote a macro that pastes without changing the references. I use the keyboard a lot so Ctrl-C and Ctrl-E in the destination cell is all I need to do. Ctrl-C is standard Excel copy. Ctrl-E runs the macro which reads the Clipboard and pastes into the destination. If anyone wants the macro let me know.

Fidel
Fidel

Thank you for the excellent resource in your post. I am attempting to transfer formula results from one cell to another cell or another document cell (Open Office) as raw data. But when transferring the data, I am only getting the formula. How can I solve this? FORMULA =A12&B12 A12 CELL -- AC- B12 CELL -- 1214 RESULT -- AC-1214 I essentially want to transfer the data "RESULT" to another the same document or another document without the formula and having to rely upon formula.

Absolutely
Absolutely

Manually adding '$' to the formula also allows you to specify that only columns, or only rows are allowed to vary, giving flexibility that can be very useful for certain formulas. I don't know any function key that can duplicate this, but I'm not sure there isn't one, either. Anybody?

Richard Noel
Richard Noel

To do this quickly all you need to do is highlight the formula an press F4.

lami50
lami50

That style also works well if you want to reference a single (or more) cell in multiple formulas. For instance if you want to mutiply cell A1 times eack cell in column B, type your formula to read =$A$1*B1 in column C get your + sign on the bottom right corner of the formula cell and drag it down column C. Cell C2 will read =$A$1*C1 and so on down the column.

ernst_berger
ernst_berger

I had a similar problem and solved it the following way. I needed to create an exact copy of a complete column containing references to multiple columns. I solved this by first moving the column to the desired place (where the copy should go) and then copying this column to a new worksheet. I then moved the column back to its original place and copied the column from the new worksheet into the original worksheet at the desired place. It gave me a duplicate of the column at the desired place.

ram_viswa2004
ram_viswa2004

1. Click in your formula cell 2. Drag respective paste cells 3. press Enter that's it, very easy way to copy formula to another cell Its only applicale for the dynamic change in the formula cell but the respective cell formula activated it's right one

sms2
sms2

There could be an occasion wherby relatively copying the formula from the new location is required.

david.turpin
david.turpin

Why not use a named range in the formula instead. This will make it absolute automatically.

medler1
medler1

ASAP utilities is a free add-in which lets you do almost anything to cells, sheets and formulae. (Google for it) For instance you can select any of the 4 reference modes from a drop down. No remembering, no fuss. Only drawback is that with 300 extra commands, you may well find yourself spending more time exploring than actually working when you first get hooked!

OrangeBull
OrangeBull

If you want to copy a range of formulas without changing the references, you can use "Replace With", CNTRL H. Simply highlight your range, use CNTRL H to replace the = with A= (A can be any text character) and copy the range to where you need it. Then use CNTRL H to replace the A= with =. You will then have all of your formulas back.

sashy
sashy

Sorry no it wont work with Ctrl+Enter.

The Bobinator
The Bobinator

I never said the article wasn't about copying a formula. What I said is that F2 is a useless step if you are going to drag the mouse anyway. The only difference is you are copying the formula out of the cell instead of the formula bar. If you are going to use the mouse anyway, skip the F2 and paste from the formula bar Man Law

Daiquiria
Daiquiria

Thanks, rlasker, for that tip! I've been using Excel for centuries and have been typing in the absolute sign ($) on each reference cell. This makes it a lot easier!

zylstra
zylstra

Another way to keep the formula dynamic, but not changing the cell addresses is to add a ' at the beginning of the formula before the =. Copy and paste. Now remove the '. The formula is the same.

peternp
peternp

I just click in the formula bar, drag over the formula, press Ctrl-C, Escape, right-click on the destination cell and select Paste. It copies the formula just as it is, without any changed references. No need to make cells absolute; only if you have to.

stemerdink
stemerdink

Hi Mike, You wrote in 2009 that you wrote a macro to "Fast Copy without changing references in Excel". I've been looking for this for a while, I have a strong need for this. Would you be so kind to send me the script for this ? The F2-F4 tricks are not good enough if you do a lot of building in Excel. Thanks a lot for your help, Wouter Stemerdink, Amsterdam wouter.stemerdink@zonnet.nl

jdclyde
jdclyde

if there was anyone on TR that knew how to do an ABSOLUTE cell reference, I was hoping it would be you..... ;\

dhc
dhc

When I use CNTL H my curson goes to the home cell. All of the methods in this string will work but all are work arounds to fix something that Excel should have fixed a long time ago. If you need to do this repeatedly all the work arounds are tedious and time consuming. The only fast and simple way I have found to do this is to use the Excel Add in ASAP. If you install it, it has a "Range" - Copy formulae command that works just fine. I hope MS fixes this since visicalc had it 20 yrs ago!

Kathryn Crowley
Kathryn Crowley

Best advice I've had in a long time. Once range of formula's were text I could then COPY, REPLACE sheet names, and change back to formulas. Fantastic.

lishalinski
lishalinski

this has been driving me mad for ages. I was faced with a series of different spreadsheets just so I could calculate some stuff that was essentially the same table for each - because I couldn't cut and paste the same table several times in the sheet. Now I can!! thanks so much, such a ridiculously simple solution means it's easier to remember too!

matthew.tyler.za
matthew.tyler.za

Excellent thread, probably the most logical and efficient solution. The only solution that answered the original question

erikjohnson
erikjohnson

I think this tip is better and more useful than the one in the orignal article.

pixelsyl
pixelsyl

What a neat trick! Thanks !

spjeff
spjeff

Is there an easier way to do this for an end user, who is not proficient with Find/Replace? She needs to copy multiple cells without changing the formula.

gio
gio

I never thought of using the "replace with" that way! Thanks again.

michaelvdnest
michaelvdnest

I prefer using the keyboard. Its faster for me. F2 Shift + Home Ctrl + C Esc Arrow keys (Move to cell) Ctrl V

fractalzoom
fractalzoom

I've used this trick a lot, too...sometimes it's just quicker and easier to add an apostrophe to the front of the forumla, copy and paste the resulting text string, and then remove the apostrophe to revert it back to its formula self.

spjeff
spjeff

How about doing this for multiple cells? How do you do that?

ttocsmij
ttocsmij

peternp's method replaced steps 2 thru 5 nice job sir!

Absolutely
Absolutely

Once you know where the $ goes to keep the column and/or row absolute, it's faster to type the $ directly than to toggle with F4 until the right combination appears. You're welcome to teach the remedial students if you wish.

dhc
dhc

There is also Kutools which competes with ASAP. I just tried it and it does have a simple "excact copy" command that will copy formulae ranges absolutely and without altering the formuae or F4'ing them. Cost: $19/yr.