How To Link Between Spreadsheets In Excel

Want to know how to use an Excel formula to link between spreadsheets? Then read this article to find out more. You can link worksheets and workbooks using simple formula. This article describes how to create simple formula to do this.

Ever wondered how you can create a formula in Excel which uses data from different worksheets or from different Excel files? This article shows you how to do this in easy steps. We'll first look at a formula linking one or more worksheets.

Suppose you want to add data in three worksheets into a fourth worksheet, all within the same workbook. We'll create an example with four worksheets called Jan, Feb, March and Summary. Here's how to do it.

Launch Excel and open a new workbook. The default worksheet only shows three sheets, so to add a fourth sheet you need to do the following. In Excel 2003 choose Insert, Worksheet. In Excel 2007 click on the Insert Worksheet icon to the immediate right of the existing three sheet tabs at the bottom of the screen. Excel then adds a fourth sheet.

Next rename Sheet1 Jan, Sheet2 Feb, Sheet3 March and Sheet 4 Summary. To rename a worksheet, move the cursor to the bottom of the sheet and double click on the sheet tab. The tab name will become bold. Then type in the new name (then press Enter in Excel 2003). So now the sheets should be called Jan, Feb, March and Summary.

We need some data to work with, so we'll create the same small single column table in each of the sheets Jan, Feb and March, and add the results into Summary.

To create the Jan table, ensure the Jan sheet is selected, and in cell D4 type in the word Sales and press enter. Then type in these numbers, pressing Enter between each, to give the four numbers in successive cells under the heading: 10 in cell D5; 20 in cell D6; 30 in cell D7; 40 in cell D8. Don't forget to press Enter after the last number is entered.

Next we'll copy this table and paste it into sheets Feb and March. To do this highlight cells D4 to D8 in the Jan sheet, right click in the highlight and choose Copy. Then select the Feb sheet, click into cell D4 and choose Paste. Then choose the March sheet, select cell D4 and again choose Paste. This will give you the same table in Jan, Feb and March. To keep life simple, we'll assume the sales were the same in each month, but you can of course change the values in Feb and March if you wish.

Now we want to build the summary table. So choose the Summary sheet, select cell D4 and type in Summary Sales and press Enter. So now we'll build the first formula linking the three sheets.

Ensure the cursor is still in cell D5 in the Summary sheet. Type an equals symbol = and then carefully switch to the Jan sheet by clicking its tab on the lower part of the screen. Then left click once into the Jan cell D5. If you look at the formula bar above the spreadsheet you'll see =Jan!D5 has appeared. Excel adds the sheet name followed by an exclamation mark before the cell reference D5. Then type a plus symbol +.

Then carefully switch to the Feb sheet by clicking its tab at the bottom of the screen. Now left click once into the Feb cell D5. Look at the formula bar and you'll see the formula has become =Jan!D5+Feb!D5. Now type another plus symbol + and we've one more month to include. Again carefully switch to the March sheet by clicking its tab name, and left click once into the March D5 cell. Now in the formula bar you'll see =Jan!D5+Feb!D5+March!D5 and we're nearly there.

Now just press Enter and the formula is complete and Excel returns to where the formula was started, the Summary sheet. Please remember that after you have selected the March cell, to complete the formula you just press Enter - you do not switch to the Summary sheet. Excel will return there anyway because the formula is created there.

If you move the cursor back up one cell in Summary to cell D5 you'll see the finished formula in the formula bar, showing = Jan!D5+Feb!D5+March!D5 and the cell will show the value 30.

You can now fill this cell down three cells to complete the summary for all four cells. To do this ensure cell D5 in Summary is still selected. Then hover over the small black square at the bottom right of the cell, press and hold the mouse left button and drag down three cells. Let the mouse go and you'll see all the cells added.

Using a formula to link cells in separate workbooks is done in much the same way. If you want to include data from another Excel file in your current formula, you need to have the other Excel file open as well.

Then when you switch sheets as you build your formula, just switch files and select the required sheet and cell in the other file. Excel will add the filename in square brackets, before the sheet name and exclamation mark, before the cell reference in the resulting formula.

If you do include cells from another file in your current formula there are two points to be careful about.

Firstly, Excel adds dollar symbols in the cell reference for cells in another file. If you intend to fill the resulting formula you'll need to remove the dollar symbols first.

Secondly, including cells from another file in you current file links the files together. This means that when you open your existing file again in the future, Excel will prompt you to be allowed to update the data from the other file if it has changed. So please don't go renaming, deleting or moving the other file, or you'll give Excel a headache.

To avoid Excel headaches generally, why not consider attending one of the many Excel training courses available, and learn more about Excel formulas and its many features.