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.

How To Drag Formula Without Changing In Google Sheets

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.

How To Drag Formula Without Changing In Google Sheets

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.

How To Drag Formula Without Changing In Google Sheets

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.

How To Drag Formula Without Changing In Google Sheets

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 + `.

How To Drag Formula Without Changing In Google Sheets

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.

How To Drag Formula Without Changing In Google Sheets

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.

Sign up and earn $1000 a day ⋙

Driver Booster 12 Free: Keep 9.5M+ Drivers Up-to-Date in 1-Click

Driver Booster 12 Free: Keep 9.5M+ Drivers Up-to-Date in 1-Click

Driver Booster 12 Free is an effective tool that will keep your computers drivers up to date, which will make the system run faster and more reliably. This driver updater from IObit keeps your PC running at its best by checking for lost, out-of-date, or broken drivers immediately.

Advanced SystemCare 17 Free Review 2024 - Your Best PC Optimizer

Advanced SystemCare 17 Free Review 2024 - Your Best PC Optimizer

In an era where digital efficiency is paramount, Advanced SystemCare 17 Free emerges as a beacon for those seeking to enhance their PC's performance.

Summary of Movies & TV application shortcuts on Windows 10

Summary of Movies & TV application shortcuts on Windows 10

Summary of Movies & TV application shortcuts on Windows 10, Summary of Movies & TV application shortcuts on Windows 10 to bring you a great experience. Maybe

How to fix Messages Failed to Load error on Discord for Windows

How to fix Messages Failed to Load error on Discord for Windows

How to fix Messages Failed to Load error on Discord for Windows, Discord isn't fun if you can't read what other people write. Here's how to fix Messages error

How to display the This PC icon on the Windows 11 desktop

How to display the This PC icon on the Windows 11 desktop

How to display the This PC icon on the Windows 11 desktop, During the process of using Windows 11, many users need to access This PC (management).

How to find information in the Windows Registry quickly

How to find information in the Windows Registry quickly

How to find information in the Windows Registry quickly, Do you find it difficult to find information in the Windows Registry? So below are quick ways to find the registry

How to limit the number of failed login attempts on Windows 10

How to limit the number of failed login attempts on Windows 10

How to limit the number of failed login attempts on Windows 10. Limiting the number of failed password login attempts on Windows 10 helps increase computer security. Here's how

How to create fake error messages in Windows

How to create fake error messages in Windows

How to create fake error messages in Windows, Windows can come up with some pretty creative error messages but why don't you try creating your own content for them to make fun of?

Ways to open Windows Tools in Windows 11

Ways to open Windows Tools in Windows 11

Ways to open Windows Tools in Windows 11, Windows Administrative Tools or Windows Tools are still useful on Windows 11. Here's how to find Windows Tools in Windows 11.

How to fix Windows Quick Assist not working error

How to fix Windows Quick Assist not working error

How to fix Windows Quick Assist not working error, Windows Quick Assist helps you connect to a remote PC easily. However, sometimes it also generates errors. But,

How to pin Word, Excel and PowerPoint files to the corresponding app icon on the Windows 11 taskbar

How to pin Word, Excel and PowerPoint files to the corresponding app icon on the Windows 11 taskbar

How to pin Word, Excel and PowerPoint files to the corresponding app icon on the Windows 11 taskbar, How to pin Office files to the taskbar icon on Windows 11? Invite

How to fix the error of not being able to install software on Windows

How to fix the error of not being able to install software on Windows

How to fix the error of not being able to install software on Windows, Why can't you install apps or software on Windows 10/11? Here's everything you need to know about how to fix it

Instructions for deleting or changing PIN on Windows 11

Instructions for deleting or changing PIN on Windows 11

Instructions for deleting or changing the PIN code on Windows 11, In Windows 11, the PIN code is a very useful and convenient security tool for users. However some people

How to fix There Are Currently No Power Options Available error in Windows 10

How to fix There Are Currently No Power Options Available error in Windows 10

How to fix There Are Currently No Power Options Available error in Windows 10, Can't select power mode in Windows 10, what should I do? Here's how to fix the error

The simplest way to fix Photos application errors on Windows 10

The simplest way to fix Photos application errors on Windows 10

The simplest way to fix Photos app errors on Windows 10, what should I do if Microsoft Photos doesn't work? Don't worry about ways to fix Photos app errors on Windows

Instructions for installing keyboard shortcuts to switch input languages ​​on Windows 11

Instructions for installing keyboard shortcuts to switch input languages ​​on Windows 11

Instructions for installing shortcuts to switch input languages ​​on Windows 11. During the process of using Windows, users will often have to switch between methods.

How to check supported power status on Windows 11

How to check supported power status on Windows 11

How to check power status is supported on Windows 11, Windows 11 can handle many different power states. Here's how to check the power status

How to switch from 2.4GHz to 5GHz in Windows 10

How to switch from 2.4GHz to 5GHz in Windows 10

How to switch from 2.4GHz to 5GHz in Windows 10, If you want to find a quick and simple way to speed up the Internet, changing the WiFi band from 2.4GHz to 5GHz may help.

How to fix Not Enough Memory to Run Microsoft Excel error on Windows

How to fix Not Enough Memory to Run Microsoft Excel error on Windows

How to fix Not Enough Memory to Run Microsoft Excel error on Windows, Are you having an error of not enough memory to run Microsoft Excel? So, how to fix Not Enough Memory error

Ways to find all video files on Windows

Ways to find all video files on Windows

Ways to find all video files on Windows, Can't find recorded and saved videos on PC. Below are ways to help you find all clips on Windows.