Are you working regularly on multiple Excel sheets? If your answer is yes then you should know how to Transfer Data from One Excel Worksheet to another automatically. This huge time-saver doesn’t consist of dense formulas or add-ons, and it’s specifically practical if you want to transfer specific data from one worksheet to another for reports. Here, you will learn two simple techniques to copy data from one Excel sheet to another. These ways also link the sheets so that any modifications you make to one sheet’s dataset automatically apply to the other. In the end, you will learn how to transport data from one Excel sheet to another centered on criteria by using the filter feature.
How to Transfer Data from One Excel Worksheet
We can link worksheets and update data automatically. A link is a forceful method that pulls data from a cell of one worksheet and automatically keeps posting that data to another worksheet. These concerning worksheets can be in the same workbook or in another workbook. The major worksheet is called the source worksheet, from where this link pulls the data automatically, and the other worksheet is called the destination worksheet which keeps that link formula and where data is updated automatically. You should remember one thing the formatting of cells of the source worksheet and destination worksheet should be the same if not the result could be noticed differently and can lead to mistake.
Two methods for automatically transferring data from one Excel worksheet to another
There are several approaches for shifting 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 are undoubtedly acquainted with the typical Copy and Paste function, Excel offers multiple paste options. One of these is Link, which permits you to paste a value from another worksheet and link the pasted value to the copied value. In other words, if you make an amendment to the original value you copied, the inserted value will automatically change as well.
The steps which are given here will show you how to use the Copy and Paste Link function:
- Firstly, open two spreadsheets containing the same, simple dataset.
- Then In sheet 1, select a cell and type Ctrl + C / Cmd + C to copy it.
- After that In sheet 2, right-click on the equivalent cell and go to the Paste > Link.
- Now To prove they’re linked, return to sheet 1 and change the value in the cell you copied.
- At last 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 process is more labor-intensive than the Copy and Paste method but is similarly simple and worthwhile to know, just in case. These steps will teach you how to use the worksheet reference method to transfer data from one Excel worksheet to another automatically:
- First of all, open two spreadsheets containing the same, simple dataset.
- Secondly, in sheet 2, double-click on a cell to the right of the dataset and type ‘=’.
- Once again go to sheet 1, click any cell from the dataset, and press Enter.
- You should automatically be back to Sheet 2, where the cell previously having ‘=’ now holds the value you chose to transfer from Sheet 1.
- Finally, To prove they’re linked, return to Sheet 1 and change the value you chose to transfer.
Here you notice that 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 model, you will learn how to filter data based on column standards so that you keep only what you need. Then, you can copy and paste the data into a new sheet, as revealed in the above illustration.
- Earlier, open a dataset on an Excel worksheet with filterable criteria.
- Next, select the criteria column from which you want to filter and go to Data > Filter.
- Afterward, click the small arrow beside the column header to open a drop-down menu.
- In the end, At the bottom of this menu, tick the box of the row(s) that you want to keep and click ‘Apply’.
Now You have only the data that you wanted to keep.
Hence, if you did not know in the past how convenient 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 successfully in no time. What’s more, you will never have to burden with these spreadsheets yet again – any update in one will be automatically transferred to the other straightaway. You can also transfer data from one Excel sheet to another based on criteria by filtering your data to move only what you want. This is a great way for transferring data for essential reports, saving you lots of time from avoidable copy and pasting, and report editing.