Excel is a widely spread tool used for analytical purposes. The wide range of Excel features is no less than a blessing for its users. With this tool, you can manage to collect, collaborate, store and share smaller to larger files with anyone. Sometimes, you have to work on management functions manually instead of using several formulas. If you need to transfer data from one Excel worksheet to another automatically, you will have to take a deep breath and start following the hacks given in this post.
You often have to work on worksheets manually when you need to automatically transfer data from one sheet to another in Excel. However, Excel offers some easy-to-follow methods including copying and pasting or sometimes a formula can also be used.
Manually you cannot transfer large files because it takes a lot of time as well and there are chances that you make errors. So, let’s get started with our time-saving methods to how to transfer data from one Excel sheet to another automatically.
Use the Paste Link Option to Transfer Data Automatically
Here we will use an example in which a dataset appears with the specifications of a number of smartphone models.
In the Paste Link sheet, you can find out three columns that are taken out from the Dataset sheet. And the Price column is still there without copying. For auto-update, you need to declare some policies for the price column if you see any change in the column given in the first sheet.
By linking these two sheets the data can be transferred automatically into one worksheet.
That’s it!
Use the + Symbol Option in Excel
Using this method needs you to follow the steps given below:
- Select the targeted cells and add the + symbol.
- Right-click on Sheet 1 and then go back to your data values. Now, choose cell A1 and press ENTER key.
- You will see the data is copied in cell B1 by default.
- Once the above steps are performed correctly, you will see the following formula appears in the formula bar by selecting cell A1.
- +Sheet1!A1
Use the +Sheet(X)!((XY) Formula to Transfer Data
In this method, we will be using the +Sheet(X)!(XY) formula. And for this, the following steps are needed to be used.
- Choose the cell in which the data will be transferred.
- Add +Sheet(X)!(XY) in the formula bar.
- You will notice the formula +Sheet2!B21 copies the data from cell B21 of Sheet 2. In the formula, X shows the Sheet label and XY shows the targeted cell boundaries.
Use the Copy and Paste Option to Transfer Data
Each method holds its significance which proves how effective this method is. Using the copy-and-paste method could be a highly easy method for some users when they need to shift data from one sheet to another. You just need to follow these steps:
- Highlight the cells you need to copy.
- Right-click and choose Copy option from the menu.
- Or else you can use the keyboard shortcut key command CTRL + C and for Mac users Command + C.
- Now, move on to the worksheet in which you need to shift the entire data.
- Click on a cell and right-click to choose the Paste option.
- Or else you can use the keyboard shortcut for Paste, which is CTRL + V, and for Mac users Command + V.
Use Worksheet Reference to Transfer Data from One Sheet to Another
Apart from copying and pasting the data, this method uses the cell reference from another sheet to auto-transfer the data to other cells of the sheet.
Follow the steps:
- Choose Cell D5 in the Worksheet Reference sheet.
- Add an Equal = sign.
- Transfer datasheet reference
- Open the Dataset worksheet.
- Choose the cell range D5:D10 in which the prices of each product are mentioned.
- Press ENTER key.
You will notice an array of prices in the worksheet Reference sheet. The array is placed in column D which ranges from D5 to D10.
When any change you see in the Price column in the Dataset sheet, you will see the prices are updated for each item in Worksheet Reference instantly.
Closing Thoughts
That’s it!
Now you are familiar with how to transfer data from one Excel worksheet to another automatically. The above-mentioned method is highly effective and useful. Practice each method and you will become an expert in Excel functions.
You can automatically copy data from one sheet to another in Excel without creating any mess. Happy transferring data!
