In this tutorial, linking your data in Excel will be explored. You will learn how to link information contained in a single workbook or across multiple worksheets/workbooks. The SUM function and using the Shift key across more than one workbook will also be explained. The tutorial concludes with instructions on how to copy and paste information in Excel, while still retaining the link to the original values.
The most important feature of spreadsheets is the ability to reference the contents of cells and their ranges, to be able to create charts and perform the calculations you require.
When you are working with formulas in Excel, you have the ability to use cell references that refer to cells in:
. The same worksheet
. The same workbook but a different worksheet
. A completely different workbook
When you are building your formula and you are using the mouse (not the keyboard) to click on the cells, you are able to navigate around your current worksheet, swap between worksheets, and swap between different workbooks to find the cells you need for your formula. Excel automatically creates your formula for you, using the correct cell references.
When typing a formula, you would normally just include any relevant cell references. However, additions to the cell references can also be included, as shown below:
. Use the name of your current worksheet followed by “!” – tells Excel that you are indicating a different worksheet.
. Use the name of your current workbook with .xls placed at the end in [square brackets] – tells Excel which workbook you require.
Have a look at the examples shown below:
. = A4+C5 – tells Excel to add the contents of cell A4 to cell A5, both in the current worksheet
. =A4+Sheet2!C5 – tells Excel to add A4 in the worksheet you are currently in to C5 on worksheet 2
. =A4+[MyWorkbook.xls]Sheet2!D5 – tells Excel to add A4 in the worksheet you are currently in to D5 in sheet 2 of the workbook that has the name MyWorkbook.xls
Using the SUM function and the Shift key across multiple workbooks
The Shift key can be used to include multiple worksheets in a formula – but only when the cells that you wish to include appear in the same location in each worksheet:
. Place your cursor into the cell where you want the result to be displayed
. Click on the button marked Autosum
. Select the first of the cells/cell ranges that you want to be included in the formula – this can be in any of your worksheets
. Hold down the Shift key and keep it in that position while you select all of the tabs of the worksheets that have data you want to be assimilated
. Press Enter to conclude the formula.
HINT: Calculations created using this method may be copied and pasted to any other cells just like normal formulas (it is not limited to the SUM function).
Copying data and linking to its original values
A useful trick with Excel is the ability to copy data within worksheets, workbooks and across multiple workbooks, whilst also keeping that data linked to the original cells. This is done using the Paste Link feature and this can be found in the Paste Special dialogue box:
. Copy all of the cells that you want to be duplicated – use right-click and select copy
. Select ALL of the cells that will contain the data that has been linked – remember these can be within the same worksheet, workbook or across various workbooks
. On your Home tab, within the Clipboard Group you will see an arrow on the paste button – click on this, then select Paste Link
The information contained in the cells is now linked back to the original. It is definitely worth taking some time to really get to grips with these features within Excel. And remember, it may seem complicated at first but will end up saving you a lot of time later on.