Often we need to summarise information from one (or many) Excel files into another. Doing this is very easy; to begin, you will need both of your files open; the file that you want to retrieve the information from, and the file you want the information to go to. If you open each workbook side by side on your screen it makes this process the easier. Select the data on the sheet you want to copy from, copy it, then in your destination workbook select where you want your linked data to go, right-click with your mouse and select the “Paste Link” icon:
You will notice if you select a cell in your destination workbook that it now contains a formula that refers to a corresponding cell in your source data workbook. Here you can see that cell F2 is referring to Workbook 1.xlsm, Sheet1, cell F2:
If you make any changes to your source data workbook, they will be automatically changed on your destination workbook. Changes will even be made if one of the workbooks is closed. I.e. if changes are made to the source workbook whilst the destination workbook is closed, the destination workbook will update the next time it is opened, even if the source workbook is closed. For this to work, it is important you do not rename your source workbook, or move its location on your computer or network.
There is another way to link between the two files, and this is better suited for linking one cell at a time. This time, you need to start by selecting the cell in the destination workbook where you will want the information to go. Type an equals symbol, and then before typing anything else, click on the cell in the source workbook where you want to retrieve the information from, then press Enter. Again, this will enter the cell reference into your cell automatically. If you intend to drag this reference down, you will need to remove the dollar signs from the cell reference.
This can be taken one step further, and instead of taking the data from a single cell, you could include a range of cells as part of a formula. For example, if you wanted to sum the cells in Workbook 1 into Workbook 2, you would start on Workbook 2 by beginning the formula:
And when you get to the open bracket, before you type anything else, click onto Workbook 2 and select the range you want to sum, close the bracket and press Enter:
If you’ve found this useful, then please sign up for our blog updates; you will be regularly updated with useful tutorials on Microsoft Excel and Google Sheets like this one. This tutorial is also available on our YouTube channel: