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 Excel data automatically with Layer?

Layer is a spreadsheet platform that works on top of Excel and Google Sheets. It allows you to easily manage and automate spreadsheet workflows. Using Layer, you can:

• Upload or connect your existing Excel or Google Sheets-based budget.
• Share different sheets or even cell ranges of your spreadsheet with various stakeholders or departments involved in the budgeting process.
• Automate your communication flows and keep track of your data submissions, contributors, and deadlines.
• Review every single change made and decide which ones to merge with your spreadsheet or discard.
• Eliminate errors in your budget or manually copying and pasting data across files.

Sign up and schedule an onboarding call to get started with Layer right now.

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.