Copying links to other excel workbooks always looks at same cell

A common query we have relates to linking cells between WORKBOOKS and why links to other excel workbooks always looks at same cell.

The issue is with how Excel sets up the link.

Default Relative vs Absolute ($ signs in formula)

As shown below, when you link to another SHEET i.e. you:

  • Click in a cell and type =
  • Go to another SHEET and click on the cell

Excel creates the link and makes it relative (note no $ signs in the link) by default. In this case, if you now copy the formula across it will correctly change where it looks and look at the correct matching cell.

links to other excel workbooks always looks at same cell

Linking to another WORKBOOK

However, if you do the exact same thing but link to a different WORKBOOK, Excel’s default action is different.

Below note that we are linking Workbook (1) to workbook (2). Notice that the formula now is using absolute referencing ($ signs in front of the letter and number).

If you leave it like this, when you copy it across, all the months will look at January instead of looking at the matching month.

links to other excel workbooks always looks at same cell

This is just the way Excel does the links. If you link to a different WORKBOOK, make sure you remove the $ signs if that is what you want. There are some shortcuts to working with the dollar signs. You can see them on the free Excel Fundamentals course we offer which explains the $ signs and how to get them to do what you want.

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.