Linking workbooks in Excel

Photo by Luke Chesser on Unsplash

So today we are going to learn how to link workbooks in Excel.

Dataset used

We are going to be using 4 datasets which have 4 sheets in each of them

Each workbook has a sheet called Chris,Anna, Stella and total.

different sheet titles

Chris,Anna and Stella’s sheets contain the profit they have made in different products from Q1-Q3.

dataset overview

Steps

• To begin the linking process, we open the workbook that we want our answers to be (In our case we want to sum up the totals across branches)

sheet we are working on

• Then we open up the other workbooks.
• Now we have all 4 workbooks open but we can only see one workbook at a time. So to solve this, we go to the view tab and click on arrange all

arrange all tab

• A dialog box appears. Tick tiled and press ok.

arrange all dialog box

• And just like that we can see the 4 work all at the same time.

four pane view

• Now we want to carry out our calculations
• So we go to the workbook, then input the equal to sign and click on each cell you want to add

adding different workbooks

• But you’ll notice it applies absolute referencing by default eg $A$4
• In order to be able to copy the formulas we have to remove the Dollar signs

dollar sign removed

• After that you press enter

after pressing enter

• Then drag the formula across

dragging formula across

formula dragged down

• So that’s it. The workbooks are linked

Additional steps

• If you would like to remove (break) a link, you go to the Data tab and check the connections group

data tab

• Click on edit links
• Then you’ll see the links in a dialog box

edit links dialog box

• You can choose to break links or change the source of a workbook