You can establish links across multiple spreadsheets and workbooks in Microsoft Excel to streamline data management. A link enables a cell in one spreadsheet – the destination spreadsheet – to reference or obtain information from a cell in another spreadsheet – the source spreadsheet.
Microsoft Excel cant map nonadjacent cells from the source spreadsheet to nonadjacent cells in a destination worksheet, however. While it has no problem linking groups of cells, nonadjacent cells must be copied individually.
Ruri Ranbe has been working as a writer since 2008. She received an A.A. in English literature from Valencia College and is completing a B.S. in computer science at the University of Central Florida. Ranbe also has more than six years of professional information-technology experience, specializing in computer architecture, operating systems, networking, server administration, virtualization and Web design.
Excel – Link Data Between Worksheets in a Workbook in Excel
Instances when you might link spreadsheets
When you have a variety of people working with a set of data, linking the spreadsheets they use can be helpful because it allows any updates to synchronize across all linked sets of data. For instance, if a paper company has spreadsheets for its sales staff, accounting department and its warehouse that include the same inventory information, linking the spreadsheets can you ensure all the teams have the most current inventory information when it updates. If you have teams working from different locations using the same information, you can consider linking spreadsheets containing information they all use.
Why link spreadsheets in Excel?
Linking spreadsheets, or workbooks, in Excel can be beneficial because it allows you to reference data from a variety of sources within one main sheet. It also keeps all your information accurate across your documents because changes you make to one sheet automatically update across all documents with which its linked. You can link sheets in the same workbook or sheets from two workbooks on your computer. Linking spreadsheets can reduce errors by keeping all forms consistent, and it can save time because you dont have to update each sheet manually for one change.
How to link spreadsheets in Excel
When linking sheets on different workbooks, you are connecting one sheet, known as the source, to another, which is the destination. This connection allows you to reference data from multiple sources within the destination. You can link individual cells or a group of adjacent cells, such as a table. Here are four methods to use when linking spreadsheets:
Linking spreadsheets by clicking
This method allows you to link two workbooks by clicking on the cells within each. Consider using this method when you want to link only one cell without typing in a function. The steps for linking two spreadsheets by clicking are:
Excel has now created the formula that links these two spreadsheets.
Linking spreadsheets by copying and pasting
When you copy and paste information from one spreadsheet into another with standard functions, the values in one dont automatically update if you change them in the other. This method allows you to copy and paste data from the source into the destination, while also ensuring any changes made to the source data replicate in the destination. With this method, you can copy multiple adjacent cells, so this can be a useful technique when copying a table. Here are the steps for linking by copying and pasting:
When you do this, Excel links your selected cell or cells from the source to the destination. To ensure the correct cells link, you can view the variables within the formula bar.
Linking spreadsheets by using a formula
If you are more comfortable typing a formula yourself instead of having Excel populate it for you, consider this method. This can be effective when linking one cell or multiple nonadjacent cells. When linking multiple cells with this method, you can copy the formula and paste it, changing only the source cell location. Here are the steps for linking spreadsheets by using the formula:
When typing the formula, “SheetName” refers to the file name of the source document, and “CellReference” refers to the cell in the source that you are linking. For example, if you wish to link cell B8 in a spreadsheet labeled “Sales” to a cell in the same workbook, the formula would look like this: “=Sales!B8.” If you are linking two spreadsheets that are not in the same workbook, you also include the workbooks name in the formula. If youre linking to a book named “2020Data,” that same formula would look like “=[2020Data]Sales!B8.”
Linking spreadsheets with hyperlinks
Hyperlinking between spreadsheets is another way to connect the two documents, but this method doesnt transfer data. Hyperlinking is useful when you want a quick and easy way to navigate to the source of data within a table, or if you simply want to locate a specific worksheet quickly. Here are the steps for hyperlinking Excel spreadsheets.
The hyperlink is now active. Consider clicking on the link to ensure it leads to the correct location.
Tips for using linked spreadsheets
Here are some practices to consider when using linked spreadsheets:
Double-check that automatic calculation is on
When your data updates, it is important for any calculations occurring within the spreadsheet to update with the new information. To ensure this happens automatically, navigate to the “Calculation” section of the Excel ribbon. Click on the arrow next to “Calculation Options” and select “Automatic.” Alternatively, you can click “File,” “Excel Options” then “Formulas.” Under the “Formulas” tab, ensure youve selected “Automatically” under “Calculation Options.”
Back up your data in an unlinked spreadsheet
Because your destination sheet updates automatically when the source sheet changes, consider backing up both sheets regularly. By doing so, you create a secondary option in case someone accidentally deletes or corrupts the data in either sheet. You can do this by going to “File,” clicking “Save As” and creating a new document with a different name.
How to unlink your spreadsheets
If you no longer want to have the source and destination linked, unlinking them can be simple. To do this, click the “Edit Links” button on either sheet. You can find this in the “Queries & Connections” section of the “Data” tab in the top ribbon. In the dialog box that pops up, you can see any links to or from that sheet. Click the “Break Links” button and confirm which link you wish to disable.
Please note that none of the companies mentioned in this article are affiliated with Indeed.
How do I link sheets in different spreadsheets?
- 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. …
- Press Enter.
- Click Allow access to connect the 2 spreadsheets.
How do I automatically link data from one sheet to another in Excel?