When you want to get data from a cell in another workbook, consider a workbook link. A workbook link serves as a pointer that lets you refer to cell content, whether just to see it in a new location or to use it in a formula. Workbook links are quicker and easier to set up than typical data connections, but they only work between workbooks - no other data sources are supported.
Important:
-
Both workbooks must be saved in an online location you can reach with your Microsoft 365 account.
-
Workbook links are considered potentially unsafe and must be the trusted workbook when you open a formula workbook. When you open a formula workbook, click Trust Workbook Links in the Message Bar to allow the workbook links to function. If you don't, Excel keeps the most recent values and disables refreshing the links with values from the data workbook.
Create a workbook link
-
Open both workbooks in a browser window.
-
In the workbook that has data you want to use (the data workbook, also called the source workbook), right-click the cell you want to link to, and then select Copy.
-
In the workbook where you want the link (the formula workbook, also called the destination workbook), right-click the cell where you want the link, and then select Paste Link. You can also click the cell where you want the link, then select Home > Clipboard > Paste > Paste Link.
Manage links in a workbook
Use the Manage Links pane to refresh links, turn automatic refresh on or off, open data workbooks, and break links. When you first open a workbook that contains links, the Message Bar displays a Manage Workbook Links command you can click to open the Workbook Links pane, and you can also open it at any time using the ribbon.
-
Select Data > Connections > Workbook Links to open the Workbook Links pane.
-
In the Workbook Links pane, you can:
Workbook links settings – Trust setting:
-
Select Settings on the Workbook Links pane Toggle on the Always trust workbook links to setup trust to the workbook.
You can use the Always trust workbook links setting to setup trust to the workbook for all the workbook links.
Trusting workbook for the links is a prerequisite for refreshing data from the links.
Set workbook links preferences:
-
Always refresh links: Automatically refreshes all links when the workbook opens.
-
Ask before refreshing links: Asks the user to refresh or not when the workbook opens.
-
Don’t refresh links: Doesn't refresh on open and doesn't ask the user. You will have to user the Refresh button on the Workbook links pane to manually refresh.
These preferences are applied to the workbook and will reflect to all users who use the workbook.
Refresh all links once:
-
Select Refresh All at the top under the Links tab.
Refresh only the links from one data workbook:
-
Select the data workbook with links you want to refresh, and then select Refresh command that appears.
Break all links in the workbook:
-
Select Break all at the top in the Links pane.
Break the links from one source:
-
Select More Options next to the data workbook with links you want to break, then select Break links.
Find the next cell that has a link to a specific data workbook:
-
Select Find next by the data workbook with links you want to find.
-