Linking Data in MS Excel

Linking Data allows you to input data from one worksheet into another worksheet in such a way that it will change if the original data is changed.

This can be used to prevent your spreadsheet from needing 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.

There are two methods to link data with.

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

Shows the initial data sorce

If you want your data to be better delineated, you can follow our guide on creating borders here.

 

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:

Shows the reference with method 1

 

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 data link option doesn’t appear in it.

Shows the specific paste link option

If you use this method, the cell is entered as an absolute cell reference. You can learn all about all types of cell references with this comprehensive guide.

 

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 spend on communications for three years has been shown on the summary sheet:

Shows the multiple cell linking ref

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.

Microsoft Excel offers lots of different ways to validate and track your data in larger, more complex spreadsheets; you can become an expert in Excel with our wide variety of course offerings.