What Is Workbook Referencing in Excel?
Workbook referencing in Excel refers to Excel's capability to link data from one workbook to another. The term "workbook" refers to an Excel file, including all the worksheets and data contained in it. Thus, workbook referencing allows you to share data between multiple Excel files on your computer or network without having to manually copy and maintain the information.
Other People Are Reading
-
Advantages
-
Spreadsheets often require information that changes over time. If multiple spreadsheets use the same data, then any changes to that data will affect each spreadsheet. Without workbook referencing, it would be necessary to find and edit every affected spreadsheet in turn. This is not only time consuming and tedious, but it also leaves room for mistakes.
With workbook referencing, a master spreadsheet containing the common information can be maintained. All workbooks requiring that information can link to the master spreadsheet. When the common information changes, only the master spreadsheet will need to be modified. All other spreadsheets will update automatically when they are opened.
Network References
-
It is possible to reference workbooks stored on other computers on your network. This makes it possible for multiple people to use copies of the same spreadsheet on different computers while being kept current on common information from a master file. It also enables users to share information about their own work with each other.
The drawback to referencing workbooks over a network is that the source workbook's availability becomes less reliable. The computer hosting the file may be turned off, or another user may move the file without warning. This will prevent the destination workbook from automatically updating when it is opened.
-
Indirect References
-
Indirect references in Excel return data from a location that is determined dynamically. This allows a cell in a spreadsheet to call upon different data as it is needed, instead of referring to the same data all the time. According to Microsoft Support, it is possible to use indirect references across different workbooks, but it is necessary to have all indirectly referenced workbooks open in Excel during reference updates.
Performance Impact
-
The data in an open spreadsheet is normally stored in computer memory, making reference updates quick and responsive. In comparison, updating references to other workbooks can take noticeably longer. In extreme cases, such as on slow networks, the delay in updates may add up to several minutes. Additionally, Microsoft warns that creating two workbooks that contain links to each other can slow down both workbooks.
Other Considerations
-
According to Microsoft, it is important to have automatic calculation enabled in the source workbook (this is enabled by default). You should also be careful when sharing Excel spreadsheets with others to whom you also provide access to any workbooks the spreadsheets link to. Spreadsheets with workbook references will try to find linked workbooks at the same file path each time, so tell others where to place linked workbooks when sharing your spreadsheets with them.
-
