Copy data from one Excel sheet to another: automatically
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. This article will show you three methods of 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
- Start by selecting the target cell (in our case B1 of Sheet 2) and typing in the + symbol.
- Next, right-click the Sheet 1 label button to return to your data. Select cell A1 and then press Enter. Your data will be automatically copied into cell B1.
- If you performed the operation correctly, then upon selecting cell A1, you should have the following formula displayed in the formula bar: +Sheet1!A1.
-
Using the +Sheet(X)!((XY) formula
The second method will make use of the +Sheet(X)!(XY) formula.
- Select the cell in which you want to swap the data and type
+Sheet(X)!(XY)
into the formula bar.
2. Using the conditions above, the formula +Sheet2!B21 will copy data from cell B21 of Sheet 2.
N.B. "X" stands for "sheet label;" and "XY" stands for the targeted cell coordinates.
-
Using worksheet reference method
1. Open two spreadsheets containing the same data.
2. On sheet 2, double-click the cell to the right of the data set and type: =.
3. On sheet 1, click on any cell in the data set and press Enter.
The program will automatically take you to sheet 2, where the cell previously containing = now contains the value that you wanted to transfer from sheet 1.