Data Linking in MS Excel

Although this is written showing Excel 2010, the same principles can be used in 2007 & 2013.

Data linking can be used to prevent holding many copies of the same data. For example maintaining one master price list and linking any other spreadsheet that needs that same information. It helps maintain data accuracy as only one spreadsheet needs to be updated for price changes. It is an excellent method of showing a summary of up to date data.

There are two methods:

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.

Capture2

If you use this method the cell is entered as an absolute cell reference

Capture3

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.

Share on LinkedInShare on FacebookTweet about this on TwitterShare on Google+Email this to someone

Related courses