Connecting Excel workbooks

Cells of Excel spreadsheets can be linked across a single worksheet, between different worksheets in the same file, different files on the same computer and even between files on different computers. Today we look at how to make connections outside of the file you are working on. This way, work carried out in one area will update work elsewhere (immediately, if both files are open).

The easiest way of linking two Excel files is with them both open on your screen. Simply:

• Click on a cell in your original file
• Type the “=” sign
• Click the relevant cell in the second file
• Hit return

You should now see the value of the selected cell in your second file, in your first file. You should also see, in the formula bar, something daunting like:

='[OriginalExcelFile_Unit 14_ConnectingWkbksB.xlsx]Sheet1′!$A$1

What’s this?

• [OriginalExcelFile_Unit 14_ConnectingWkbksB.xlsx] is the name of the file you are linking to
•  Sheet1 is the name of the worksheet you are linking to
•  ! denotes where the sheet name ends
•  $A$1 is the cell you are linking to. The dollar signs indicate an absolute or fixed reference (essentially that if you copy this formula elsewhere in your sheet, it will link to cell E12)

There is a more advanced way of linking the data in two workbooks.

To do so, use the Data Connection Wizard found on the Ribbon: DATA > Connections > Connections to launch the Workbook Connections dialogue box:

Click Add, then choose Browse For More at the bottom and click on the workbook you want to link to. You can then follow the steps of the wizard to connect the workbooks.

To learn more about connecting workbooks in Excel, visit our course:

Section 3 ‘Administration’ > Unit 3 ‘Connecting Workbooks’ > Module 1 ‘Basic Techniques’

Excel online training

Filtered is an online course that only teaches the features of Excel that are useful to you, saving you precious time and providing a truly tailored learning experience.

Read more about Filtered training and take a look at the Excel course syllabus.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.