How To Create An Excel Formula Using Data From Several Worksheets

Sat 23rd April 2011

As you develop your skills using Excel you may need to create a formula to combine data from more than one worksheet. This article describes several ways to do this and summarises advantages and disadvantages of each. The ways are to use arithmetic operators such as +,-,* or /, or use a function such as SUM(range) or use consolidation.

As you develop your skills using Excel you may need to create a formula to combine data from more than one worksheet. This article describes several ways to do this and summarises advantages and disadvantages of each. The ways are to use arithmetic operators such as +,-,* or /, or use a function such as SUM(range) or use consolidation.

Using arithmetic operators

If you want to add, subtract, multiply or divide data from a few sheets, then you can use the appropriate symbol and build up the calculation in the same as adding data within one sheet.

Suppose for example we want to add data from three worksheets called Q1, Q2 and Q3 into a fourth worksheet called Total. In each worksheet the sales numbers are in cells D1 to D3. We want to start by creating a formula in sheet Total in cell D1. The formula will add the data in Q1 sheet cell D1 and in Q2 sheet cell D1 and in Q3 sheet cell D1. So we begin in sheet Total and select cell D1. We'll type in the complete formula and then press the Enter key to complete.

So type an equals symbol = and then carefully change sheets and select Q1. In Q1 click once into cell D1. The cell will show the selection marquee, and if you look into the formula bar you'll see =Q1!D1. Next press the add symbol + and carefully switch to sheet Q2 and select cell D1. You see the formula has become =Q1!D1+Q2!D1. Then carefully switch to sheet Q3 and select cell D1. Look at the formula bar again where you'll see =Q1!D1+Q2!D1+Q31D1 press the Enter key to finish. You'll see the correct value in the Total sheet in cell D1 with the three values added.

Although you could repeat this exercise by adding the cells D2 in sheets Q1, Q2 and Q3 to D2 in the Total sheet, and then repeat this for cell D3, you can also use the fill handle to achieve the same. So ensure you are still in the Total sheet with cell D1 selected. Now use the fill handle to fill the formula down two cells, and all three cells will be completed.

Using a function

If you want to add cells over several worksheets you can use the SUM function, provided the data is in the same cell position in each sheet. This is easier to use than selecting multiple sheets and cells, as we only need to select the first and last sheets. We'll start with the same example described above with the Total cells D1 to D3 all empty. As before, ensure you start by selecting cell D1 in the Total sheet.

Then type =SUM( as you would normally do when starting to use a function. Now carefully select sheet Q1, then press and hold down the shift key, and select sheet Q3. Notice that all the sheets are selected. Then click once into cellD1, type a close bracket and press the Enter key, so you're returned to the Total sheet. Ensure D1 is selected and look at the formula in the formula bar =SUM('Q1:Q3'!D1) which contains extra symbols to indicate the sheet range first, then the cell.

Using Consolidation

You can also combine data from more than one worksheet using Excel's consolidation feature. The end result is the same in that the cells in the destination sheet show the combined values, but the difference is that there are no formula. Excel will do the calculation once and drop the results into the cells as values, not formula. We'll start the above example one more time, ensuring cells D1 to D3 in the Total sheet are empty.

Now select all three cells. Then choose Consolidation (In Excel 97-2003, choose Tools, Consolidate. In Excel 2007/2010 choose the Data tab, Consolidate). In the Consolidate panel we select each source block of cells in turn. So ensure the cursor is in the white box Reference, then switch to sheet Q1 and select all three cells D1 to D3. Then in the Consolidate panel click Add. Repeat this for the data in Q2 and then Q3. To finish click OK and Excel will add the blocks of cells together in the target block.

If you link the worksheets either using arithmetic operators or a function, as we did in the first two examples, then any change in the source data will change the data in Total. However if you use Consolidation, and change in the source data will not affect the result, as consolidation is a one off action.

Interested in learning more about Excel features? A really effective way is to attend a training course. There are many available and the best ones give you lots of hands on practice with follow-up support.

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

Original article appears here:
https://www.stl-training.co.uk/article-1622-create-excel-formula-using-data-from-several-worksheets.html