Linking cells in different sheets and workbooks

As you’ve seen, you can tell one cell in Excel to look at another cell to get some information. So if, in cell A1 you type =A2,

• Excel will go to cell A2,
• see what is there and
• put it in cell A1.

You can use the same technique to get Excel to link to other sheets within the workbook or even totally different spreadsheets (workbooks).

To follow along go to 6.1.1 & 6.1.2

If we want to link one cell to another we type = and then you can either click on the cell you want to link to, or type in the cell name.

Linking to another sheet or spreadsheet is the same. You can either type in the name or click on the cell. Typing in the name of the cell can be difficult though because the moment you want to link outside the current sheet, Excel needs to know the sheet name and it needs to be typed in, in a special way. So to avoid having to learn this, just click on the cell you want

Link to a cell in another sheet

To link to another cell within the same spreadsheet but on a different sheet:

• click into a cell and type =
• Click on the sheet you want to go to
• Click on the cell you want to link to
• Click enter (very important- use enter to finish your formula- don’t click on another cell or use your arrow keys)

Note below that instead of just now having the cell name (B5) it has the full sheet name surrounded by ‘ and with an ! before the cell. So you can type it, but you need to get it exactly right. Rather just click on the cell and let Excel do the work for you.

Including the external link in a formula

Above we just linked to the cell, but you can have the linking done within a formula. Below, instead of just pulling through the price in cell C5, in cell D5 we have created a formula which firstly looks at B5 on this sheet and then multiplies it by cell B5 in the other sheet. To enter this all you need to do is:

• In cell D5 type =
• Click on cell B5 in this sheet
• type multiply (*)
• Go to the other sheet and click on the correct cell
• Hit enter (important that you hit enter here).

Using $ signs on these external links

You can also use $ on these links in the same way as you used them on the normal calculations (and the F4 shortcut works here as well). So if you know that you want to use the link again somewhere else but you need it to be absolute, then just put the $ signs on the letter and number as appropriate.

The steps would be:

• click into a cell and type =
• Click on the sheet you want to go to
• Click on the cell you want to link to
• Type in a $ in front of the letter and number (or use F4)
• Click enter (very important)

Linking to a different workbook

Now that you have linked to different sheets within the same workbook, lets link to cells on another workbook (totally different spreadsheet). The process is exactly the same except you now need to go to a different spreadsheet. So:

• click into a cell and type =
• Click on the other spreadsheet you want to go to (it needs to be open)
• Click on the cell you want to link to
• Click enter (very important)

As shown below, the formula now is slightly longer in that it records the spreadsheet name (Book2 below), the sheet name (6.1.2 Linking2) and the cell reference ($B$5).

The major difference between sheet and spreadsheet linking

Although the process is the same there is one difference between the 2 links. If you link between sheets within a spreadsheet, the cell reference has NO $ signs initially. If you link between 2 workbooks, by default $ signs are put onto the letter and number. You can still change the $ signs but you just need to realise that they are on or off. You don’t even need to remember it. Just look at it before you click enter!

If you don’t notice this, you may have problems with copying the cell to other cells. It may not act as you expect.