Excel adjusts references when you copy a formula: =C2*C3 copied three columns right becomes =F2*F3. Here are five ways to keep the formula exactly as it is, from a quick cut-and-paste to moving a whole block of formulas.
One cellCopy from the formula bar
Cell belowCtrl + '
A blockFind and Replace = with a placeholder
Table of Contents
Why references change
References are relative by default. Copy =C2*C3 from C4 to F7 and both references move with it, becoming =F5*F6.
Quick ways
| Method | How | Best for |
|---|---|---|
| Cut instead of copy | Ctrl + X, then Ctrl + V | Moving formulas: references never change |
| Copy from the formula bar | Click in the formula bar, select the text, Ctrl + C, Esc, then paste into the new cell | One formula |
| Copy the cell above | In the cell below, press Ctrl + ' (apostrophe), then Enter | Repeating the formula just above |
| Lock references | Press F4 while editing to add $: =$C$2*$C$3 |
Formulas you'll copy often |
Copy a block: Find and Replace
- Select the formula cells → Ctrl + H.
- Find
=, replace with§=(or any character not used in your formulas) → Replace All. The formulas become text. - Copy and paste them to the new place.
- Select both the original and pasted cells, and replace
§=back with=.
The original post used #. Avoid it if your formulas contain spill references (A2#) or text like #N/A.
Copy a block: Show Formulas and Notepad
- Press Ctrl + ` to show formulas.
- Copy the cells and paste into Notepad.
- Copy from Notepad and paste into Excel at the new place.
- Press Ctrl + ` again.
Things to know
- Formulas that point to another sheet keep that sheet name; check them after pasting into a different workbook.
- Excel Table formulas use column names (
[@Qty]), which don't shift when copied within the table.