Microsoft Excel working with linked worksheets

Microsoft Excel provides several methods to link values ​​between worksheets or workbooks. The method you choose will depend on your desired end result. This article will review the pros and cons of each method.

Linking by creating a formula. With this method you can create an interactive link between worksheets or workbooks. Linked cells do not need to be in the same location on all worksheets. You can perform any mathematical operation while creating the link. For example, you could take a number from one spreadsheet, multiply it by a number from another spreadsheet, and then subtract a number from a third spreadsheet. You can use this method to link cells between worksheets or workbooks. A disadvantage of this method is that it is time consuming because you work with one cell at a time. The use of named ranges can help with this linking method and make the formulas easier to read once they are created.

Creating a sum in spreadsheets. This is also an interactive link. If a number changes in a worksheet, the sheet with the summary formula will also be updated. If a worksheet is inserted between worksheets in the sum, the sum will update automatically. Some of the disadvantages of this method are that all linked cells must be in the same location on all worksheets. Also, you can only add one cell at a time (although you can copy the formula to other cells in the summary worksheet). Using group mode to create identical worksheets will help when you use the sum function on all worksheets. This method is also limited within a workbook and cannot be performed across workbooks.

Using the consolidation function. You can determine whether or not the link will be interactive when creating the consolidation. This method provides the greatest level of detail in the summary worksheet. You can choose from any function, not just the addition function. For example, you could create an average of multiple divisions in a summary spreadsheet. Some of the disadvantages of this method are that the worksheets must be identical. Although not impossible, it is difficult to use consolidation between workbooks.

Using Paste Link. This also creates an interactive link. Linked cells do not need to be in the same location on all worksheets. You can link between worksheets or workbooks. This is a great way to link totals from one worksheet to another, however it doesn’t have the detail that consolidation provides.