Excel 2019 Bible

637

C H A P T E R

28

Linking and Consolidating

Worksheets

IN THIS CHAPTER

Using various methods to link workbooks

Consolidating multiple worksheets

I

n this chapter, we discuss two procedures for using data from other workbooks or worksheets:

linking and consolidating. Linking is the process of using references to cells in external work-

books to get data into your worksheet. Consolidating involves combining or summarizing infor-

mation from two or more worksheets (which can be in multiple workbooks).

Linking Workbooks

Excel allows you to create formulas that contain references to other workbook files. With such for-

mulas, the workbooks are linked in such a way that one depends on the other. The workbook that

contains the external reference formulas is the dependent workbook (because it contains formulas

that depend on another workbook). The workbook that contains the information used in the exter-

nal reference formula is the source workbook (because it’s the source of the information).

When you consider linking workbooks, you may ask yourself the following question: if Workbook

A needs to access data that’s in Workbook B, why not just enter the data into Workbook A in the

first place? In some cases, you can. But the real value of linking becomes apparent when the source

workbook is being continually updated by another person or group. Creating a link in Workbook A

to Workbook B means that in Workbook A, you always have access to the most recent information in

Workbook B because Workbook A is updated whenever Workbook B changes.

Linking workbooks also can be helpful if you need to consolidate different files. For example, each

regional sales manager may store data in a separate workbook. You can create a summary workbook

that first uses link formulas to retrieve specific data from each manager’s workbook and then calcu-

lates totals across all regions.

Linking also is useful as a way to break up a large workbook into smaller files. You can create

smaller workbooks that are linked with a few key external references.

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.