Don’t forget to share this post

Do you regularly work out of multiple Excel sheets? If so, you should know how to transfer data from one sheet to another automatically. This enormous time-saver doesn’t involve complicated formulas or add-ons, and it’s especially practical if you want to transfer specific data from one worksheet to another for reports. Below, you’ll learn two simple methods to copy data from one Excel sheet to another. These methods also link the sheets so that any changes you make to one sheet’s dataset automatically apply to the other. Finally, you’ll learn how to move data from one Excel sheet to another based on criteria by using the filter feature. 

If you want to transfer data automatically between multiple Excel and Google Sheets files, you can do this easily using Sheetgo. In fact, you can quickly connect your files and select the data you want to transfer based on filters or queries, as well as set up automatic updates to refresh this data. With Sheetgo, you can control access to your files, sharing the whole file or only parts of it with collaborators or stakeholders.

Sign up for Sheetgo today to create connections between your files and start automating your business processes. Let Sheetgo do the boring, repetitive tasks so you can focus on what really matters.

Two methods for automatically transferring data from one Excel worksheet to another

While there are various methods for transferring data from one Excel worksheet to another, the two simplest are the Copy and Paste Link and the Worksheet Reference methods.

While you’re probably familiar with the standard Copy and Paste function, Excel offers multiple paste options. One of these is Link, which allows you to paste a value from another worksheet and link the pasted value to the copied value. In other words, if you change the original value you copied, the pasted value will automatically change as well.

These steps will show you how to use the Copy and Paste Link function:

  1. 1. Open two spreadsheets containing the same simple dataset.
1
2
  1. 2. In sheet 1, select a cell and type Ctrl + C / Cmd + C to copy it.
3
  1. 3. In sheet 2, right-click on the equivalent cell and go to the Paste > Link.
4
  1. 4. To prove they’re linked, return to sheet 1 and change the value in the cell you copied.
5
  1. 5. Finally, return to sheet 2 to see that the value has also changed.
6

How to VLOOKUP in Excel with Two Spreadsheets?

Sometimes our data may be spread out among different Excel sheets or workbooks. Here's how to do VLOOKUP in Excel with two spreadsheets

READ MORE
How to VLOOKUP in Excel with Two Spreadsheets

Use worksheet reference to automatically transfer data from one Excel worksheet to another

This method is more manual than the Copy and Paste method but is equally simple and useful to know, just in case. The following steps will teach you how to use the worksheet reference method to transfer data from one Excel worksheet to another automatically:

  1. 1. Open two spreadsheets containing the same, simple dataset.
7
8
  1. 2. In sheet 2, double-click on a cell to the right of the dataset and type ‘=’.
9
  1. 3. Go to sheet 1, click any cell from the dataset, and press Enter.
10

You should automatically be returned to Sheet 2, where the cell previously containing ‘=’ now contains the value you chose to transfer from Sheet 1.

11
  1. 4. To prove they’re linked, return to Sheet 1 and change the value you chose to transfer.
12

Sheet 2 will automatically have changed the value to your chosen new value.

13
How to Combine Multiple Excel Files Into One
How to Combine Multiple Excel Files Into One

Discover the most popular methods used to manually or automatically combine multiple Excel spreadsheets and data inputs into one master file

READ MORE

Move data from one Excel sheet to another based on criteria using the filter feature

In this example, you will learn how to filter data based on column criteria so that you keep only what you need. Then, you can copy and paste the data into a new sheet, as demonstrated in the above example.

  1. 1. Open a dataset on an Excel worksheet with filterable criteria.
14
  1. 2. Select the criteria column from which you want to filter and go to Data > Filter.
15
  1. 3. Click the small arrow beside the column header to open a drop-down menu.
16
  1. 4. At the bottom of this menu, tick the box of the row(s) that you want to keep and click “Apply”.
17

You should now see only the data you wanted to keep.

18

To move this data to another sheet, follow the steps that show you how to copy and paste to automatically transfer data from one Excel worksheet to another.

Automatically transfer data between Excel files

If you want to quickly and easily transfer data between Excel worksheets and workbooks, Sheetgo provides a flexible and easy-to-use method. You can instantly link your files and merge or split the data however you want. By using queries and filters, you can select exactly what data should be transferred. You can then save this workflow and set up scheduled updates, so you can use your time to focus on what matters to you.

  1. 1. Select the worksheet or workbook with the source data. 
Select source workbook or worksheet with Sheetgo
  1. 2. Choose whether you want to process the data before transferring it.
Data processing options in Sheetgo
  1. 3. Select where you want to send the data.
Select destination file in Sheetgo
  1. 4. Click “Automate” to schedule automatic updates for this connection.
Schedule automatic updates in Sheetgo

Check out this blog post to learn more about how to use Sheetgo to transfer data from one sheet to another in Excel.

Conclusion

So, if you didn’t know before how useful it is to link and transfer data from one Excel worksheet to another automatically, you do now. With the Copy and Paste Link and worksheet reference method, you can transfer data effectively in a matter of minutes. What’s more, you’ll never have to worry about these spreadsheets again - any update in one will be automatically transferred to the other immediately. You can also move data from one Excel sheet to another based on criteria by filtering your data to transfer only what you want. This is great for transferring data for important reports, saving you lots of time from unnecessary copy and pasting, and report editing. 

Using Sheetgo, you can automatically connect not only your worksheets, but also your workbooks, without having to worry about broken formulas or copying and pasting data. If you work with multiple worksheets in Excel and found this article helpful, you should also check out these related posts: How to do VLOOKUP in Excel with two spreadsheets and How to use IMPORTRANGE in Google Sheets.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Feb 3 2022, Updated Sep 11 2023