Transfer Data From One Excel Worksheet to Another Automatically

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.

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.

Use Copy and Paste Link to automatically transfer data from one Excel worksheet to another

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. In sheet 1, select a cell and type Ctrl + C / Cmd + C to copy it.

1. 3. In sheet 2, right-click on the equivalent cell and go to the Paste > Link.

1. 4. To prove they’re linked, return to sheet 1 and change the value in the cell you copied.

1. 5. Finally, return to sheet 2 to see that the value has also changed.

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.

1. 2. In sheet 2, double-click on a cell to the right of the dataset and type ‘=’.

1. 3. Go to sheet 1, click any cell from the dataset, and press Enter.

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

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

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

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.

1. 2. Select the criteria column from which you want to filter and go to Data > Filter.

1. 3. Click the small arrow beside the column header to open a drop-down menu.

1. 4. At the bottom of this menu, tick the box of the row(s) that you want to keep and click “Apply”.

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

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.

How to transfer data from one Google Sheets to another?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

Using Layer, you can:

• Share & Collaborate: Automate your data collection and validation through user controls.
• Automate & Schedule: Schedule recurring data collection and distribution tasks.
• Integrate & Sync: Connect to your tech stack and sync all your data in one place.
• Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Sign up today and get free access to Layer, including all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

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.

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.