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:
The formula bar will show were the information has been linked to, the sheet name and the cell number:
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:
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.