Data Linking in MS Excel

Although this is written showing Excel 2010, the same principles can be used in 2007, 2013, 2016 and Office 365.

Data linking can be used to prevent your spreadsheet need to hold multiple copies of the same data.

For example, maintaining one master price list and linking any other spreadsheet that needs that same information to it.

This helps maintain data accuracy as only one spreadsheet needs to be updated for price changes. It is an excellent way to make it very simple to create a summary of up to date data. This becomes especially important when multiple people are working on the same spreadsheet. If the data inputs are not logically presented and organised it is impossible for someone else to update the spreadsheet accurately. Excel offers lots of different ways to validate and track your data in larger more complex spreadsheets which we cover in more advanced training courses.

There are two methods to do this:

Before we start, here is what a sheet of the source data looks like:

Source data example

Source data example

Method 1

  • First click in the cell in the destination worksheet you want the data to appear
  • Insert =
  • then click on the information in the source worksheet you want to carry across
  • hit enter

The formula bar will show were the information has been linked to, the sheet name and the cell number:

Data linking in Excel

Data linking in Excel

Method 2

  • Start on the source information sheet and copy the information you wish to link
  • Right click on the cell in the destination sheet adn go to ‘Paste special’
  • Click on the ‘Data link’ icon. Interestingly, if you are used to opening up the paste special box, the data link 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 link to multiple sheets and cells to a single cell with a function, in the example below the total spend on communications for three years has been show on the summary sheet:

Linking multiple sheets

Linking multiple sheets

To do this you will use method one above but after clicking on the first communication total in 2010 press ‘+’ then move to the 2009 sheet and click on the communication total and press ‘+’, click on 2008 communication total and press enter.

If you would like to cover this in more detail why not attend our Excel Intermediate training.

Want to learn more on adding borders to a worksheet? Follow this article to find out more.

Want to learn more about relative cell references? Follow this article to find out more.