How To Drag Formula Without Changing In Google Sheets

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

But what if you want to simply copy a piece of data to a cell that has been programmed to use a certain formula? What if you want to paste information the regular way?

Copying Google Sheets Formulas Without Changing References

When you’re working in Excel or Google Sheets, you’ll note that the formulas won’t occur alone for the most part. Typically, you’ll enter a formula in a cell and then copy the same formula to other cells (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 has relative cell references, i.e., without the “$” sign, Google Sheets will adjust the cells. It will change them so that each of the formulas 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 version of the formula, without changing any cell references.

Copying a Cell

If you select a particular cell, copy it and paste it to other cell(s), the references will change. This is just how Excel and Google Sheets work. However, there is a way to copy/move a formula from a single cell without changing the references.

If you select the cell, press Ctrl + C, select another cell, and then paste using Ctrl + V, the references may change. However, if you copy the exact values of a cell, you’re doing just that – copying exact values, rather than the references. Here’s how it’s done.

Select a cell, only this time, double-click it. This will display the editing mode of a cell. Now, select the cell’s content by left-clicking and dragging the pointer across the selection (as you would select any text in Google Docs). Then, press Ctrl + C to copy the contents. Now, you’ve successfully copied the literal content of the cell in question. Finally, simply select the cell you want to paste the content to, 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 Formulas

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

1. Absolute/Mixed Cell References

Let’s say that you want to make exact copies of formulas that have relative cell references. The best way to go here would be changing the references to absolute references (adding a “$” sign in front of every formula item). This will essentially fix the reference in the cell in question. This means that the cell will remain static, regardless of where you move the formula.

To lock a column or a row, you’re going to need to resort to mixed cell references. This will lock an entire column/row.

To change a relative reference to a mixed reference, all you need to do is put the “$” sign in front of the column letter or row number. Now, no matter where you move the formula, the column will be fixed to the particular column that you mark with the dollar sign.

2. Using a Text Editor

Yes, this might sound a bit “archaic”, but resorting to simple tools such as Notepad is sometimes advisable. Start by entering the formula view mode by hitting Cntrl + `. Now, using the Ctrl key on your keyboard, select every single cell with formulas that you wish to move or copy/paste. Once you’ve selected everything, copy/cut them.

Open your preferred text editor app and paste the formulas to it. Make sure to do some change on the formula, can be as simple as adding a space somewhere. Do not put any other character on it. Now, use the Ctrl + A command to highlight the entire pasted content, and then copy it using Ctrl + C or by right-clicking and selecting Copy. You can also cut the content.

Go back to your Google Sheet. Select the upper-left cell (where you want to paste the formulas), and paste the copied content. Finally, remove the formula view by hitting again Cntrl + `.

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 end up with broken formulas.

3. Using the Find and Replace Feature

If you want to copy an entire range of formulas in Google Sheets but don’t want to change their references, the Find and Replace feature is your best ally here.

To enter the feature, either press Ctrl + H, or navigate to the Edit entry in the upper menu and navigate to Find and replace.

Now, in the Find field, enter “=”. In the Replace with field, enter “\”. 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. Select Replace All.

Now, select all cells that you want to copy without changing the references, and use the Ctrl + C command to copy them to the clipboard. Then, find the top cell in the worksheet that you want to paste the formulas to, and press Ctrl + V to paste them.

Don’t worry about the weird-looking formulas in your original spreadsheet. Using the Find and replace function, place “\” in the Find field and enter “=” into the Replace with field. This will revert things to normal.

Moving the Formula in Google Sheets Without Changing the References

As you can see, there are multiple ways of avoiding changing references in Google Sheets. Choose the method that suits your current needs to move those formulas without changing their references. This falls under essential knowledge for working in Google Sheets.

Was this guide helpful? Have you managed to do what you wanted? If you have any other questions about moving/copying formulas without changing the references, let us know in the comments section below.