Best Practices for Linking Excel Spreadsheets

In this article, you will learn about best practices for linking Excel spreadsheets. This is very easy to understand; a basic understanding of Excel will be enough. This article uses the following features: name range, linking Excel workbooks, and the VLOOKUP function. All of these features are available in Excel 2003 and later. So, there is no chance of facing any compatibility issues.

Download Practice Workbook

You can download the Excel file from the following link.

Problem Overview

Our Excel file has two sheets. The first sheet named “Problem” has some problems when linking spreadsheets. Then, the last sheet, “Solutions,” solved those problems. There will be five best practices for linking Excel spreadsheets. Most of these deal with the visibility of the spreadsheet. The simpler the source spreadsheet, the better. The following image shows the “Problem” sheet.

The five best practices are as follows:

• Employ Short File Name: This will help with readability when linking Excel spreadsheets. Moreover, the formula will be shorter too. The following image shows a simple file. We have shortened the file name.

• Use Meaningful Name Range: It is preferable to give a name range a significant name when using one. That way, further modifications to the linked spreadsheet will be simpler.

• Enable Automatic Calculation: You need to make sure that Automatic Calculation is turned on. To do so,
  • Firstly, you need to go to the Formulas tab.
  • Then, select the Calculation Option from the Calculation section.
  • After that, make sure Automatic is selected.

• Backup Your Data: Make sure you backup your data before linking to other spreadsheets. It is preferable to use the cloud (i.e., Google Drive, OneDrive, Dropbox, etc.) to backup your data.
• Show Snapshot of Linked Range: You can embed an image from the linked spreadsheet to improve clarity. For example, if you are using a VLOOKUP function output, then it is better for the user to see the source table, from where this data comes.

Finally, this is the output after applying all these practices to the Excel file.

Conclusion

Thank you for reading this article. We hope you have learned about some best practices for linking Excel spreadsheets. You can find more articles similar to this on the ExcelDemy website. If you have any questions or suggestions, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Keep excelling!