Linking Data in MS Excel – 2 Methods
Contents
What if all your data could be linked across from one sheet to another?
You would never need multiple copies of the same data.
You’d also minimize the risk of incorrect data due to forgetting to update the information across sheets!
We cover it on our introduction to Excel course as it makes collaboration so much quicker and easier.
Before we start, here is what a sheet of the source data in Excel looks like:
Method 1 – Cell Formula Writing + Clicking
First, click the cell in the worksheet you want the data to appear in:
- Type in the formula bar “=”
- Click on the information in the source worksheet you want to carry across
- Press enter
The formula bar will show where the information has been linked to, the sheet name, an exclamation mark and the cell number:
You can use this same method to reference named ranges from other sheets too.
Method 2 – Copy And Paste
Start on the source worksheet and copy the information you want to link
- Right-click on the cell in the destination sheet and go to ‘Paste special’
- Click on the ‘Data link’ icon. Interestingly, if you are used to opening up the paste special box, the link data option doesn’t appear in it.
If you use this method, the cell is entered as an absolute cell reference.
Linking to Multiple Cells
You can also create a link to multiple sheets and cells to a single cell with a function.
In the example below, the total spent on communications for three years has been shown on the summary sheet:
To do this, you will use method 1 above but after clicking on the first total in 2010, press ‘+’ then click the next piece of data and so on.
