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
Start by selecting the target cell (in our case B1 of Sheet 2) and typing in the + symbol.
Next, right-click on the Sheet 1 label button to go back 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 would like to swap the data and type
+Sheet(X)!(XY)into the formula bar.
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.
Need more help with Excel? Check out our forum!
You can use the Excel Advanced Filter function to pull data from one sheet into another sheet based on criteria. The following step-by-step example shows how to use this function in practice.
Step 1: Enter Data into the First Sheet
First, let’s enter the following dataset into the first sheet of an Excel workbook:
Step 2: Define Criteria
Now suppose we would like to pull each of the rows that contain “Mavs” in the Team column into Sheet2.
To do so, we can define this criteria in Sheet2:
Step 3: Pull the Data from the First Sheet
Next, we can click the Data tab and then click the Advanced Filter button:
In the new window that appears, click Copy to another location and then fill in the following information:
Once you click OK, the rows from Sheet1 where the Team column is equal to “Mavs” will automatically be pulled into Sheet2:
Notice that all three rows where the Team column is equal to “Mavs” have been pulled from Sheet1 into Sheet2.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel Advanced Filter: How to Use “Contains”
Excel Advanced
Filter: How to Use “Does Not Contain”
Excel Advanced Filter: Display Rows with Non-Blank Values