If you find yourself copying and pasting figures from one Excel sheet to another, there is a better way. Linking cells means the data updates automatically — change a number in one place and it updates everywhere it is referenced. This guide shows you how to link data between sheets in the same workbook and between entirely separate Excel files.
Why Linking Is Better Than Copy-Pasting
When you copy a value and paste it somewhere else, you get a static snapshot. If the original changes, your paste does not. Linking creates a live connection instead. This matters whenever:
- You have a summary sheet that pulls totals from multiple department sheets
- You have a master price list that feeds into several quote templates
- You want a dashboard that reflects live data without manual updates
Linked cells update instantly when the source data changes, provided the source file is open. If the source is a different workbook and it is closed, Excel updates the links the next time you open the destination file and choose to update.
Simple Sheet References Within the Same Workbook
To reference a cell on another sheet in the same workbook, the syntax is:
=SheetName!A1
For example, if you have a sheet called Sales and you want to pull the value from cell B3 into your summary sheet, you would type:
=Sales!B3
The exclamation mark separates the sheet name from the cell reference. You can type this directly, or — which is easier — type the equals sign, then click the tab of the sheet you want, then click the cell. Excel writes the formula for you.
Sheet Names With Spaces
If the sheet name contains a space, Excel automatically wraps it in single quotes. You will see:
='January Sales'!B3
You do not need to type the quotes yourself — Excel adds them automatically if you use the point-and-click method. If you type the formula manually and the sheet name has spaces, make sure to include the single quotes, otherwise you will get an error.
3D References Across Multiple Sheets
If you have data arranged identically across several sheets — for example, one sheet per month — you can sum the same cell across all of them with a single formula called a 3D reference:
=SUM(January:December!B3)
This adds up cell B3 from every sheet between January and December (inclusive). The sheet names act as a range with a colon between them. The sheets must be in a contiguous sequence in the workbook, and the cell reference must be in the same position on each sheet.
3D references work with SUM, AVERAGE, COUNT, MIN, MAX and a handful of other functions. They are particularly useful for monthly budget workbooks where each month has the same layout.
Linking to Another Workbook
You can also link to cells in a completely separate Excel file. The syntax looks like this:
=[Workbook.xlsx]Sheet1!A1
Again, you rarely need to type this manually. Open both workbooks, type the equals sign in the destination cell, then switch to the other workbook and click the cell you want. Excel writes the full reference including the file name and path.
When the source workbook is open, the reference looks like the example above. When it is closed, Excel stores the full file path instead:
='C:\Users\YourName\Documents\[Workbook.xlsx]Sheet1'!A1
What Happens When the Source File Is Moved
This is where external links can cause problems. If the source workbook is renamed or moved to a different folder, Excel cannot find it and the links break. You will see a message asking whether to update links when you open the destination file, and if Excel cannot locate the source, the cells will show the last known values or display an error.
To fix a broken link, go to Data > Edit Links. This shows all external links in the workbook. Select the broken link and click Change Source to point it at the file’s new location.
Updating External Links
When you open a workbook that contains external links to a closed file, Excel will usually ask whether you want to update the links. Click Update to pull in the latest data from the source file. If you click Don’t Update, the workbook will show whatever values were there the last time links were refreshed.
You can also update links manually at any time via Data > Edit Links > Update Values.
Breaking Links
If you want to remove the link and keep the current values as static data, go to Data > Edit Links, select the link and click Break Link. This replaces every formula referencing that external workbook with the current values. The process is permanent — once broken, you cannot re-establish the link from within Excel (you would have to re-create the formulas).
Breaking links is useful when you are sending a workbook to someone else and do not want them to get prompts about external files they cannot access.
Named Ranges Make Formulas Clearer
Instead of using cell references like =Sales!B3, you can give a cell or range a name and use that instead. Named ranges make formulas much easier to read and maintain.
To create a named range:
- Select the cell or range.
- Click the Name Box (the box showing the cell address, top-left above the grid).
- Type a name (no spaces — use underscores, e.g.
Total_Sales) and press Enter.
Now instead of =Sales!B3 you can write =Total_Sales in any formula. Named ranges work across sheets by default. You can manage them via Formulas > Name Manager.
If you are building a workbook that other people will use or that you will need to maintain months later, named ranges are worth the small extra effort. A formula like =Revenue - Total_Costs is far easier to understand at a glance than =Summary!B12 - Costs!F44.
