News & tips
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:
If you want your data to be better delineated, you can follow our guide on creating borders here.
The formula bar will show where the information has been linked to, the sheet name, an exclamation mark and the cell number:
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.
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:
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.