Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Google Docs 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?

share|improve this question
1  
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
    
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

7 Answers 7

Work around this the same way as in Excel (I guess someone, somewhere thought this was a useful feature):

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

share|improve this answer

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
share|improve this answer
    
This didn't work for me. See my alternative below webapps.stackexchange.com/questions/22558/… –  Motti Strom Oct 15 '13 at 23:38

On mac you can hit function F4 to cycle through the various absolute referencing options when the cursor is on the cell or range.

share|improve this answer

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

share|improve this answer

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 consequutive 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 consequative formula, one solution is:

  1. Create the formula as you normally (use plain =A1 and autofill)
  2. Import "Search And Replace Formulas" Google App Script
  3. Make judicious use of find-and-replace to automatically wrap all =A1, =A2 ... =A99 in the =INDIRECT call. (I used a custom version of this script with regular expressions).
share|improve this answer

For those still looking for an answer...

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.

share|improve this answer
    
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 at 18:13
    
Modifying paste with Shift appears to make Google Spreadsheet paste values rather than unmodified formulas. –  Matt McClure 19 hours ago

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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