47

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. – mhoran_psprep Jan 9 '12 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 Tuinstra Mar 10 '13 at 19:06
  • 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 Jul 3 '18 at 18:55

11 Answers 11

21

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.

=A1
=A2
=A3
...
=A99

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, – Motti Strom Jan 8 '15 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 Apr 8 '15 at 5:55
  • 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()) – Evil Pigeon Dec 5 '18 at 2:28
29

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:

 =$G$1&Sheet1!B3&Sheet1!A3&$G$2
  • 1
    This didn't work for me. See my alternative below webapps.stackexchange.com/questions/22558/… – Motti Strom Oct 15 '13 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 Dec 20 '14 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 Dec 20 '14 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 Dec 20 '14 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 Apr 11 '17 at 19:17
29

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.

  • 6
    This is the only answer that preserves formulas. – amcnabb Sep 15 '14 at 18:13
  • 3
    Agreed - this is the only correct answer. – stevemidgley Dec 9 '14 at 20:15
  • 1
    Thanks! I was suprised that there is a difference between "Drag" and copy / paste! – rcreswick Jan 23 '18 at 22:27
  • So if you want to cut and paste you just need the select range + drag part, but if you want to copy you need the other extra steps to preserve the original. – Mu Mind Feb 27 '18 at 1:24
  • 2
    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 Jan 7 at 7:00
7

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 Jul 30 '14 at 18:13
  • 12
    Modifying paste with Shift appears to make Google Spreadsheet paste values rather than unmodified formulas. – Matt McClure Aug 26 '14 at 15:30
  • 4
    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. – stevemidgley Dec 9 '14 at 20:14
  • 1
    Doesn't work, -1. – Pacerier Dec 20 '14 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 Feb 16 '16 at 21:17
5

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.

3

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.

0

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

  • 1
    But you would have to repeat this for each cell.... Is there any way to do text copy through multiple cells? – Pacerier Dec 20 '14 at 7:49
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
0

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.

-1

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.

-1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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