How To Create A Formula In Excel To Link Data In Different Sheets

Sat 24th March 2012

Boost your Excel skills by learning how to create Excel formulas to link cells between different worksheets. This technique can save you valuable time by delegating the work to Excel to complete it for you!

Have you ever wanted to create a formula in Excel which links data in different worksheets? This article describes how to do this in easy to follow steps. You can also link data in separate Excel files in similar ways. By linking data over a number of worksheets, you can save time by letting Excel compile the information for you.

Let's look first at how to link data in different sheets in the same workbook. (This and much more can be learnt in our Excel intermediate training courses.)

Suppose you have data in three separate sheets within the same workbook. For example, sheet 1 might contain data for January, sheet 2 has data for February and sheet 3 has the data for March.

You create a fourth sheet where you want to add the data from the other sheets. First, you rename each sheet to a more meaningful name. This is not absolutely necessary but helps in identifying data. So you rename sheet 1 as Jan, sheet 2 as Feb, sheet 3 as Mar and sheet 4 as Total. In this example we'll create the same table layout in each of the sheets, but each with different data.

So we create a two column table with the titles Product and Sales in cells D3 and E3 in the Jan sheet. Then you enter these three product names in successive cells under the Product title, Gold, Silver and Bronze. In the cell under Bronze you type in the word Total. Then even though the Sales cells are empty, in cell E7 you type in the formula =SUM(E4:E6) and press the Enter key.

Then you copy this table from the Jan sheet to each of the other sheets, so you end up with four tables, one in each sheet. Now you can type in the sales data in each of the Jan, Feb and March sheets. Then you select the Total sheet. Here we want to create formula to add the data in each of the other sheets and put the totals here.

To begin, in the Total sheet select the cell for the gold sales - this should be cell E4. We will now type in the formula to add the data from the corresponding gold sales cell in each of the other sheets. So type and equals symbol, and then carefully change the selected sheet to the Jan sheet. Now select the Jan gold sales cell E4 with a single left click. Look in the formula bar and you'll see the developing formula. Then carefully change the selected sheet to the Feb sheet.

Select the Feb gold sales cell E4 with a single left click. Look in the formula bar to see how the formula has changed further. Then one more time, carefully change the selected sheet to the Mar sheet. Then select the Mar gold sales cell E4 with a single left click. You are almost there. Don't make any more switches. Just stay in the Mar sheet. Now press the Enter key, and you're taken back to the Total sheet, where you started. You should now see the total gold sales figure in cell E4. And that's the job done for the gold cells.

Still in the Total sheet, re select cell E4 and look at the formula in the formula bar. It should look like =Jan!E4+Feb!E4+Mar!E4 and you can see how Excel adds the sheet name followed by an exclamation mark, then the cell reference to show a cells in different sheets. Then you can use the Fill handle in the Totals sheet to fill down the other sales figures.

In a similar way you can link cells in separate Excel files as well as in separate worksheets. You need to ensure that the separate file is open. Then build the formula in a similar way, but you can switch files as well as sheets to select target cells. Just take note that if you do link cells from different files, then Excel adds the external cell references as absolute and adds the dollar symbols in the cells references in the formula. If you intend to then use the fill handle, you should manually remove the dollar references as appropriate.

Interested in learning more about Excel's many useful formula features? Why not consider attending one of the many courses available. That way you can learn a great deal in a short time.

Author is a freelance copywriter. For more information on advanced excel training, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-2062-how-create-formula-in-excel-link-data-in-different-sheets.html