Google Sheets has a feature where if you have a reference to a data cell in a formula, and you cut the data from that cell and paste it in a new location, the reference in the formula is updated to point to the new cell location.

I'd like to be able to cut and paste my data without my formulas changing.

I've done some hideous things in the past involving the INDIRECT() function to work round this feature, but it seems like there's got to be an easier way. Any suggestions?

  • 2
    funny...I never thought of this feature as a bug. It has worked this was in Excel for decades. Commented Jan 9, 2012 at 11:02
  • 1
    Did you find the time to check up on the answers given? Perhaps you can mark one of them as the best answer.
    – Jacob Jan
    Commented Mar 10, 2013 at 19:06
  • 2
    Many different questions are answered below. Can the question be cleaned up? I think an example of your problem would be this: Cell A2 contains a formula such as "=B1 + 5". B1 contains "42". A2 displays "47", as expected. But you've realized that B1 shouldn't have "42" in it, C1 should have "42", and B1 should have "58". You click on B1, "cut", click on C1, paste. B1 is now blank (expected), C1 is now "42" (expected) but A2 still displays "47", because A2 has automatically updated to "=C1 + 5", to follow the cut and paste (not expected). What you wanted was A2 to stay "=B1 + 5". Am I right?
    – cesoid
    Commented Jul 3, 2018 at 18:55

20 Answers 20


The =$A$1 notation can also be automatically moved if the value in (say) A1 is changed.

The only surefire solution is to enclose all your references in INDIRECT, e.g. =INDIRECT("$A$1") or even just =INDIRECT("A1").

The difficulty with either of these solutions is where you have many of the same formula, e.g.


Normally, you would fill in the first =A1 and then copy-and-paste (or drag down using autofill) to fill out the consecutive formula automatically. If, however the first formula is =$A$1 or =INDIRECT("A1") then this trick won't work (in fact, this is what $ is for - to prevent it being automatically changed).

If you have many consecutive formula, one solution is:

  1. Create the formula as you normally (use plain =A1 and autofill)
  2. Use the replace dialog (Ctrl+H) and use "search in formulas" to wrap all =A1, =A2 ... =A99 in the =INDIRECT("A1") etc. Note you have the option to use regular expressions when doing a find-and-replace.
  • Updated answer to suggest use of better find-and-replace in New Google Spreadsheets, which can do a replace in formulas and use regexps, Commented Jan 8, 2015 at 18:33
  • 1
    Can you give an example of what the search/replace parameters would be to wrap all cell references in a formula with INDIRECT?
    – Michael
    Commented Apr 8, 2015 at 5:55
  • 2
    You can use INDIRECT in combination with ROW() to have a formula which operates on the current row. For example: =A1 + B1 becomes =INDIRECT("A" & ROW()) + INDIRECT("B" & ROW()) Commented Dec 5, 2018 at 2:28
  • 2
    I had to get the value of the same cell in a different sheet, used your suggestions to construct this formula: "=INDIRECT("Sheet1!R" & ROW() & "C" & COLUMN(), FALSE)
    – Tudmotu
    Commented Jul 12, 2021 at 12:02

When you hover over selected cells and the cursor turns to a "hand", you can then drag the cells and preserve their references. Detailed instructions below:

From the Google Docs forum:

Select the range of cells. Then use Ctrl+C (copy; this should put the cells and their formulas in the paste buffer).

Now drag (hover over the edge of the selection until the mouse cursor changes into a hand; now you can drag) the selected cells to the new position.

Go back to the original top-left position of the copied cells and use Ctrl+V (paste).

Now you should have two sets of formulas both referencing the same cells.

  • 14
    This is the only answer that preserves formulas.
    – amcnabb
    Commented Sep 15, 2014 at 18:13
  • 5
    Agreed - this is the only correct answer. Commented Dec 9, 2014 at 20:15
  • 1
    Thanks! I was suprised that there is a difference between "Drag" and copy / paste!
    – rcreswick
    Commented Jan 23, 2018 at 22:27
  • 5
    WARNING: This will break references. It's only OK to do when there are no references to the cells you're dragging. Otherwise you have to manually fix all references.
    – ADTC
    Commented Jan 7, 2019 at 6:52
  • 8
    A better method: Duplicate the sheet, cut from the sheet's clone, paste into original sheet in new location where you need it. When you're done, delete away the sheet's clone. This way, the references remain unaffected.
    – ADTC
    Commented Jan 7, 2019 at 7:00

Came across this looking for a similar problem and ended up finding a solution for Excel which seems to work perfectly in the Google Spreadsheets.

For the cell references you don't want to change on paste simply add $ before each part.

So G12 would become $G$12

A cell from a spreadsheet I am using that does what I need, looks something like this:

  • 2
    This didn't work for me. See my alternative below webapps.stackexchange.com/questions/22558/… Commented Oct 15, 2013 at 23:38
  • @Leonard, Wow the syntax is horrible. It's much more intuitive if it's the other way round, i.e. relative cells use $ while absolute cells are default.
    – Pacerier
    Commented Dec 20, 2014 at 7:58
  • @Pacerier I agree with you completely, though it works, it is horrible and hard to read. I don't use spreadsheets often enough to understand what you're suggesting though. Could you elaborate?
    – Leonard
    Commented Dec 20, 2014 at 14:40
  • @Leonard, I meant the default behavior is different, so instead of $G$1&Sheet1!B3&Sheet1!A3&$G$2, we do G1&Sheet1!$B$3&Sheet1!$A$3&G2.
    – Pacerier
    Commented Dec 20, 2014 at 15:15
  • 1
    @grahamparks: Then downvote and possibly leave a comment why it's not a solution. Don't attempt to add your own commentary into the answer.
    – ale
    Commented Apr 11, 2017 at 19:17

In Google Spreadsheets, to cut and paste cells without auto-shifting their formulas, you use Ctrl + X to cut (or Ctrl + C to copy), and Ctrl + Shift + V to paste.

The Shift tells Google Sheets to leave your formulas alone.

  • fyi, if you are using apps like Flycut (mac) with CTRL+SHIFT+V shortcut already active, then it will conflict with the spreadsheet shortcut and formula locking won't work. Disable Flycut app's shortcut, or just quit it first before doing CTRL+SHIFT+V.
    – Hlung
    Commented Jul 30, 2014 at 18:13
  • 20
    Modifying paste with Shift appears to make Google Spreadsheet paste values rather than unmodified formulas. Commented Aug 26, 2014 at 15:30
  • 7
    This is incorrect - ctrl-shift-v will simply paste the "values" of your formulas. This will prevent transposing your data, but the resulting cells will contain static values not formulas. Commented Dec 9, 2014 at 20:14
  • 2
    Doesn't work, -1.
    – Pacerier
    Commented Dec 20, 2014 at 7:48
  • 1
    I never cuts. When you paste with ctrl+shift+v, it always copies the values, even if you did ctrl+x on the source cells.
    – sparebytes
    Commented Feb 16, 2016 at 21:17

Work around this the same way as in Excel:

COPY and paste the data, and then go back and delete it from the original place.


Here's another option:

  1. Switch to "Show formulas" mode (under the View menu)
  2. copy the desired area
  3. Paste in some external text editor
  4. Make some change - can be as simple as adding a space somewhere
  5. Copy the formulas from the external text editor
  6. Paste in the target area in the sheet
  7. Turn Show formulas off

While this seems like a long procedure, in practice it's merely a few key strokes. Also, step #4 is mandatory in my setup - without it Sheets will still update the cells locations as if it were a direct copy and paste. YMMV.

  • I can't believe it is so tricky, I don't understand why it must be so hard, but this is the only meaningful answer. It worked, thank you Commented Apr 3, 2020 at 12:30
  • This solutions is the only one really working for me. Good for bulk copy and paste of many formulae cells also.
    – Kurt UXD
    Commented Mar 14, 2021 at 16:14
  • About point 4: I had to close and reopen the sheet in order to make google sheet forgot the references.
    – Kurt UXD
    Commented Mar 14, 2021 at 16:30

Cut (instead of Copy) seems to work for me, probably for the same reason dragging selected cells works - i.e. it's the same operation. This is also the only way I know of to paste to another page without the formula changing.

If you're trying to do a Copy instead of a Move, either use the drag method (copy before dragging), or copy it somewhere else on the page first.


To move a range of cells to a new location in Google Sheets:

1. Select the range of cells you want to move
2. Move your mouse to any edge of the selection until the cursor changes into a hand
3. Drag your cells to the new location

This does what it should - it moves them - so all cell information will remain exactly as they were in their original location.

ps. Google has also instructed this here but they don't say you can drag from any edge, just the top one, and that is why my instruction is better! :) Also it's good to note that the cells you select need to be adjacent in order for this to work.

  • Very nice catch! Commented Oct 10, 2019 at 10:09
  • The same method is mentioned here as well. Still. This one is better researched and explained. Commented Oct 10, 2019 at 10:14

Using INDIRECT() works great for preserving the absolute position of references after the targets have been copied and pasted, but the problem is that it also preserves the absolute position when the formula is copied, meaning that you can't easily extend a formula to cover a large range when using it.

The solution is to combine INDIRECT() with ROW(), COLUMN(), and ADDRESS() to programmatically generate the position of the target cell based on the formula's cell.

In the simplest case, such as when the target cell has a fixed column and always stays in the same row as the formula, this can be done as follows:


In order to introduce dynamic offsets from the formula cell, you can use ADDRESS():


In the below screenshot, the formulae in the B1:E1 were extended to the 16 rows beneath, and then the number sequence in A7:A10 was cut and pasted 6 cells down. As you can see, the simplest formulae were automatically adjusted and desynchronized, while the naive use of INDIRECT() did not extrapolate across all rows properly, but the two formulae that use INDIRECT() along with programmatic retrieval of row and column locations were able to maintain their references:

Demonstration Picture

  • This should be the accepted answer. This allows you to copy the formula over, and it's not affected by data in cells being moved away (cut+paste for example). Indirect alone is not enough, as you'd now have a fixed reference which is probably not what you want.
    – ZeeCoder
    Commented Mar 25, 2022 at 9:38

I just Find and Replace all instances of '=' with '' (or as much of the formulas I'm trying to copy as I can) to make all the formulas into plain text. I copy and paste the cells, then add the '=' back.

Note: this probably won't work for huge sheets, as it can have unintended consequences by editing other cells, but it usually works for my purposes.


Do a text copy of the formula instead of a cell copy: Use the mouse to select the formula text and press CTRL+C. Then select the destination field and press CTRL+V. This preserve the formula

  • 2
    But you would have to repeat this for each cell.... Is there any way to do text copy through multiple cells?
    – Pacerier
    Commented Dec 20, 2014 at 7:49

Easiest way is to just select all of the cells you want to move and drag/drop to where you want them. If you have formulas in other cells that were referencing back to the original location of the cells you moved, the reference formulas will automatically update to the new location of the cells you moved. Hope this helps! (Assuming this works in Excel, but I've only done it in Google Sheets).


There are lots of solutions here already, but if your column-headers are stable you can use index() with row() and match(). Say the value you want will always be in a column with a [1st-row] heading of 'last month', then to find:

  • the column # you would: match("last month",$1:$1,0)
  • the row you would use (without any arguments): row()

So then your formula to get the value is (e.g. with 150 rows):

=index($1:$150, row(), match("last month",$1:$1,0))
  1. Select cells with mouse
  2. Copy co clipboard
  3. Move the selected cells to a new location with your mouse via capturing the edge of the group
  4. Paste from clipboard into the empty space you just moved everything away from

The best way I've been able to solve this problem is by creating a duplicate of the sheet on Google Sheets.

From the duplicate, I cut the cells and then paste them to the original sheet. I then delete the duplicated sheet.

Seems tacky, but saves me a lot of time.


Here is a bit of hack, as previously noted, Ctrl+X for cut and then paste preserves the references (equations changed to refer to original cells from new location), it's copy and paste that updates according to the rules changing which cells are referred to). Using $s à la $A$1 version works within a sheet, but not between sheets.


  1. Use Ctrl+X and Ctrl+V to cut and paste it to a new location (the equations will be modified to refer to the original locations). The problem is that then the original is gone.
  2. Copy (Ctrl+C) the new position
  3. Undo to restore the old (removing the new)
  4. Then Ctrl+V at the new location to put back the copy

Result table copied and all the equations are modified to refer to the same original cells, even on different sheets.


Simplest way:

  1. Show formulas -> View / Check Show Formulas
  2. Copy formulas (or cut) -> Ctrl + C | Ctrl + X
  3. Paste values -> Ctrl + Shift + V
  4. Hide Formulas -> View / Uncheck Show Formulas

This "bug" seems to be a standard feature in all spreadsheets applications, and I know of no option to disable it, (though there are a few workarounds).

It's most irritating when you have an accounting ledger with a column of subtotals. When you cut and copy and item/amount row, the subtotal formulas get mixed up. Even if you "protect" the formula cells, they still get changed when you do this! It's very counter intuitive.

The trick of selecting and mouse-dragging doesn't work for me in Libre Calc.

What I always do by habit, as a safeguard, is Ctrl+C to cut my item/amount rows, then immediately hit Delete to remove them. Now there is no longer any reference between the amount cell and adjacent formula cell, and I can past the row in another location with issue.


I think I found a super simple way. Instead of copying the cell. Click on the cell you want to copy and go to the fX bar and copy straight from there. The Cell references do not change.


You can hit F4 to cycle through the various absolute referencing options when the cursor is on the cell or range. This works on Mac and Windows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.