How to Drag Formula without Changing in Google Sheets

All Excel/Google Sheets users know how powerful these spreadsheet platforms can be. They aren’t just table apps that help you jot things down and showcase them. On the contrary, Google Sheets allows you to make your very own formulas and apply them to specific rows, columns, or cells automatically.

But what if you copy a piece of data to a cell programmed to use a particular formula? What if you want to paste information the regular way? Here’s the info you need.

Copying Google Sheets Formulas without Changing Cell References

When working in Excel or Google Sheets, you’ll note that formulas won’t magically appear alone in other cells and for obvious reasons. Typically, you’ll enter a formula in one cell and copy it to others (usually in the same row/column). This makes sense, as you’re probably performing calculations that pertain to the same thing but in different instances (for example, days, weeks, etc.)

If your formula contains relative cell references, i.e., without the “$” sign, Google Sheets will adjust the cells so that each one operates on the data within its respective column/row. Usually, this is aligned with typical Google Sheets needs. However, in some instances, you might want to copy the exact formula version without changing any cell references.

Copying a Cell along with its References (Exact Formula/Cell Assignments)

The references will automatically change if you select a particular cell and copy and paste it into another. This is how Excel and Google Sheets work. However, there is a way to copy/move a formula from a single cell without changing the cell references.

1. Double-click (not single-click) the chosen cell containing references.
2. In the ‘editing mode’ of the cell, select the cell’s contents by highlighting the formula containing references rather than clicking on the cell.
3. Press “Ctrl + C” to copy the highlighted content. You’ve successfully copied the literal content, not the actual cell.
4. Finally, select the cell you want to paste the formula into and press “Ctrl + V.”

Pro tip: If you want to move a cell instead of copying it, use the “Ctrl + X” (Cut) command.

Copying a Range of Cells as Fixed Formulas/Cell Assignments

Of course, you don’t have to copy/paste cells individually. In most cases, you’ll move a range of cells rather than one. If your aim is moving multiple formulas at once, there are several ways to do it.

Option 1: Use Absolute/Mixed Cell References to Copy Exact Formulas

Sometimes, you want to make exact copies of formulas with ‘relative’ cell references. The best way would be to change those references to ‘absolute’ (adding a “$” sign in front of every formula item). This locks the cell’s contents, making the cell static, regardless of where you move the formula.

You must use ‘mixed cell’ references to lock an entire column or row.

You only need to put the “$” sign before the column letter or row number to change a ‘relative’ reference to a ‘mixed’ reference. No matter where you move the formula, the “column” will now be fixed to the particular column you mark with the dollar sign.

Option 2: Use a Text Editor to Copy and Preserve Cell References

Yes, this might sound a bit “archaic,” but using simple tools such as Notepad is sometimes advisable to copy or move exact formulas without changing them.

Enter the formula view mode by hitting “Ctrl +’ (single quote).” Now, using your keyboard’s “Ctrl” key, select every cell with formulas you wish to move or copy/paste. Once you’ve chosen everything, copy/cut them.

1. Enter the formula view mode by pressing “Ctrl + ` (backtick/grave accent)” The backtick is found on the “Tilde” key (~).
2. Now, using your keyboard’s “Ctrl” key, select every cell showing formulas you wish to move or copy/paste.
3. Once you’ve selected everything, press “Ctrl + C” or right-click on one to copy/cut them.
4. Open your preferred text editor app: Google Docs (all OS’), Notepad (Win), Text Editor/GEdit (Ubuntu), TextEdit (Mac), etc., and paste the formulas into it.
5. You must alter the pasted data in the notepad you chose to transfer the ‘exact’ formulas with their references. Add a “space” after each formula’s “=” sign. Do not add any new characters anywhere!
6. Now, press “Ctrl + A” (Windows/Chromebook/Linux) or “Command Key + A” (macOS) to select all pasted content, and then copy it using “Ctrl + C” or “Command Key + C.”
7. Go back to your Google Sheet, select the upper-left cell (where you want to paste the formulas), and paste the copied content using “Ctrl + V” (Windows/Chromebook/Linux) or “Command Key + V” (macOS).
8. Finally, remove the formula view on Google Sheets by pressing “Ctrl + ` (backtick/grave accent).”

Pro tip: Note that you can only paste the formulas into the worksheet you copied them from. This is because the references include the sheet name. Paste the copied content to any other random sheet, and you’ll have broken formulas.

3. Using the Find and Replace Feature

If you want to copy a range of formulas in Google Sheets but don’t want to change their references, the Find and Replace feature is your best ally. You’ll replace the “=” sign in the formula with a “\” character. After copying/pasting, you’ll return them to normal. Here’s how it’s done.

1. In the Google Sheet, press “Ctrl + H” or click “Edit – > Find and Replace.”
2. Type “=” in the “Find” field, “\” in the “Replace with” area, and then select “Also search within formulas.” This will turn all the formulas inside your sheet into text strings. This prevents Google Sheets from changing the references when you’re copying.
3. Select “Replace All,” then click “Done.”
4. Now, select all the cells with references that you want to copy, then press “Ctrl + C” (Windows, Linux, Chromebook) or “Command + C” (Mac) to copy them to the clipboard.
5. Then, find the top cell in the worksheet to which you want to paste the formulas and press “Ctrl + V” to paste them.
6. Now, change the text strings back to formulas. Press “Ctrl + H” or click “Edit – > Find and Replace.”
7. Type “\” in the “Find” field, “=” in the “Replace with” area, and then select “Also search within formulas.” This will revert things to normal.

Using the steps above, you have successfully changed formulas to text, copied/pasted them to other cells, and changed them back to preserve the original references.

As you can see, multiple ways exist to avoid changing references in Google Sheets when moving or copying/pasting the cells that contain them. Finding the best option depends on your data, layout, and the number of cells in your spreadsheet. Small spreadsheets may work great using simple formula copy/paste functions, while more extensive spreadsheets benefit from the “Find and replace” feature. Choose the method that suits your current needs.