With more than 30 million users worldwide, Excel is the most popular spreadsheet software in the world. Thanks to its variety of analytical and collaborative features, Excel is one of the best tools to store, manage and share large amounts of data both individually and with teams.
Although Excel offers a lot of great features, when it comes to data management, you still have to carry out a lot of these processes manually. This includes transferring your data from one worksheet to another. There are a few ways to do this within Excel, such as copying and pasting or using a simple formula.
However, manually transferring data across different sheets can not only be time-consuming but can also create more room for error. Using an automated alternative that you can still use within Excel may be the best solution to save time and ensure data accuracy.
Let’s explore in more detail how to transfer data from one worksheet to another in Excel, and how the Sheetgo add-in is a great alternative to automate this entire process.
This article will teach you how to transfer data from one spreadsheet to another in Microsoft Excel if your copy and paste function is not working. The two methods presented will use a quick formula that you can apply to data present in the same Microsoft Excel file. This article will show you how to transfer data from one excel worksheet to another automatically.
How to transfer data from one spreadsheet to another? For each example, consider that we have two sheets: Sheet 1 and Sheet 2, and we would like to transfer from cell A1 of Sheet 1 to cell B1 of Sheet 2. Using the + symbol in Excel ... While working with multiple sheets in Excel, it’s very usual to feel the necessity to transfer data from one worksheet to another. There are easy and simple ways to meet this objective. In this article, you’ll learn how you can transfer data from one Excel worksheet to another automatically through 5 suitable methods with short explanations.Table of Contents Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
Automatically Transfer Data Across Worksheets.xlsm
5 Quick Ways to Transfer Data from One Excel Worksheet to Another Automatically
Here, for demonstration purposes, we used the dataset below, where we have various smartphones and their information listed. For avoiding any compatibility issues, use Microsoft Excel 365 edition.
1. Apply Paste Link Option in Excel for Transferring Data Automatically
In the following picture, the Dataset worksheet is representing the specifications of a number of smartphone models.
And here is Paste Link worksheet where three columns from the Dataset sheet have been extracted. The Price column has not been copied yet as we’ll show different methods here to pull out the price list from the first sheet. We must maintain some rules that will auto-update the price column if any change is made in the corresponding column in the first sheet (Dataset).
We can link these two worksheets so that data in one worksheet (Paste Link) will be auto-populated based on another worksheet (Dataset).
1.1 Within Same Workbook
Here, the data are going to be transferred from one worksheet to another worksheet within the same workbook. The data will also be going to be updated whenever the source data changes.
Read More: Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP
1.2 Across Different Workbooks
Until now, we have seen that we can transfer data from one worksheet to another worksheet. But those transfers have been done inside the same workbook. But here, we are going to transfer data from one workbook to another workbook.
2. Transfer Data from One Sheet to Another by Using Worksheet Reference
Now we’ll apply another method where we won’t have to copy and paste anything from one worksheet to another. Rather, we’ll use the cell reference(s) from another worksheet to auto-populate data on other worksheet’s cells.
Read More: How Excel Pull Data From Another Sheet Based on Criteria
3. Insert Plus (+) Symbol to Move Data to Another Worksheet Automatically
In this section, we’ll apply an alternative formula starting with a Plus symbol (+) instead of an Equal sign (=). Let’s go through the following steps.
Read More: Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)
4. Automatically Send Data to Another Worksheet Through Excel VBA
In our last section, we’ll apply VBA codes to transfer data from VBA_1 to VBA_2. In the picture below, VBA_1 contains the values in B4 and C4 respectively. What we’ll do here is type a smartphone model and its price in B5 and C5 first. Then we’ll press a customized button that will transfer the input data from VBA_1 to VBA_2.
And here is the VBA_2 worksheet, where the list of smartphone models and the corresponding prices will be auto-populated from the VBA_1 worksheet.
So, we can move to the necessary steps now that will execute the Macro properly.
Step 1: Insert Command Button
The default button caption needs to be changed to a custom one for better clarity.
Step 2: Assign Macro to the Button
We can now assign a VBA code to the command button.
Step 3: Execute the Code to Transfer Data
We can now move the data from one worksheet to another worksheet by pressing the button.
Read More: How to Transfer Data from One Sheet to another in Excel Using Macros
I hope all these simple methods mentioned above will now help you to apply them in your Excel spreadsheets when you must transfer data from one worksheet to another automatically. For this problem, a workbook is available to download where you can practice these methods. Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
How do I automatically transfer data from one Excel sheet to another?
Select all the data in the worksheet. Keyboard shortcut: Press CTRL+Spacebar, on the keyboard, and then press Shift+Spacebar..
Copy all the data on the sheet by pressing CTRL+C..
Click the plus sign to add a new blank worksheet..
Click the first cell in the new sheet and press CTRL+V to paste the data..
How do I automatically link data from one sheet to another in sheets?
Use the IMPORTRANGE function.
In Sheets, open a spreadsheet..
In an empty cell, enter =IMPORTRANGE..
In parenthesis, add the following specifications in quotation marks and separated by a comma*: The URL of the spreadsheet in Sheets. ... .
Click Allow access to connect the 2 spreadsheets..
How do I autofill the same cell from different sheets to a sheet in Excel?
Click the tab for the first worksheet that you want to reference. Hold down the Shift key then click the tab for the last worksheet that you want to reference. Select the cell or range of cells that you want to reference. Complete the formula, and then press Enter.