You've got data in one spreadsheet, and you want to use it in another. You could copy it—but then you'd have to manually update the data in each spreadsheet every time the data changed. Who has that kind of time? Not to mention the risk you run of manually inputting incorrect data.
There's a better option: link your spreadsheet cells to keep the data consistent across sheets.
Here's the easiest way to pull data from another sheet or workbook in Excel.
Table of contents:
How to pull data from another sheet in Excel
To pull data from one spreadsheet in Excel to another, you need to input the source sheet's name and the name of the cell being copied into the destination cell.
Open the Excel workbook with the data you want to copy.
Open the spreadsheet that you want to pull the data into.
Click the destination cell (the cell that you want to pull the data into).
Enter
=immediately followed by[the name of your source sheet]![the name of the cell being copied]. For example, to copy the value in cell A2 of the "Roster" spreadsheet into the corresponding cell of the "Names" spreadsheet, I'd enter=Roster!A2.Press Enter.
Here's an even easier way to input the source sheet and cell name into your formula:
Enter
=in the destination cell.Toggle to the source sheet.
Click the cell you want to copy.
Press Enter, and Excel will populate the name of the spreadsheet and the cell with the value you want to copy into your formula.
Whichever method you use, Excel will automatically populate your destination cell with the same data as the source cell. Now, if you change the data in the source cell that was copied, the data will automatically update in every spreadsheet where that cell is referenced.
The same data-linking method works for Google Sheets and other popular spreadsheet apps.
Need to calculate values using the data in your source cell? Simply type the rest of your function as normal. For example, if Names!B3 contains the value 3, and you type =Names!B3*3, Excel will output 9 in your new cell, just as you'd expect.
How to pull data from another workbook in Excel
You can pull data from an entirely different workbook in Excel using the same method as above. But there are a few caveats:
You can only link data from another workbook in the Windows or web version of Excel—not Mac.
If you're using Excel online, both workbooks must be saved in the same online location—for example, OneDrive—and those files must be accessible using the same Microsoft 365 account.
Here's how to link data from another workbook in Excel.
Open both Excel workbooks.
Enter
=in the destination cell.Toggle to the source workbook.
Click the cell you want to copy.
Press Enter.
That's it. Similar to pulling data from another spreadsheet, Excel will automatically populate the name of the workbook, spreadsheet, and reference cell in your formula.
It's worth noting, though, that you need to open both workbooks whenever you make a change to the source cell—otherwise, changes made to the source cell won't appear in the destination cell.
Automatically sync data across spreadsheets
Using formulas to copy data from one spreadsheet to another is good enough if you're working with a really small dataset. But the risk of manual data entry increases exponentially when you're working with anything more than a few rows of simple data.
Avoid the headache altogether. Use Zapier's Microsoft Excel integration to connect your sheets and automatically send data across spreadsheets—no manual data entry or formulas required.
You can also use Zapier to connect Excel to all the other apps you use. Learn more about how to automate Excel, or get started with one of these premade workflows.
Add new Jotform submissions to Excel spreadsheet rows
Add new Jotform submissions to Excel as rows in a table
To get started with a Zap template—what we call our pre-made workflows—just click on the button. It only takes a few minutes to set up. You can read more about setting up Zaps here.
Related reading:
This article was originally published in December 2022 by Matthew Guay. The most recent update was in April 2024.
