Excel 2007 Training Video: Multi worksheet formulas

Learn how to create cross worksheet formulas in Excel. A transcript is available for this video.

Multi worksheet formulas in Excel 2007

Excel allows you to create formulas using data on more than one worksheet.

You are able to provide totals on a range of values held on different worksheets.

We now want to calculate the values of all of the Monday sales from week 1 to week 4 and have the answer displayed in C3 of the totals worksheet. The best way to do this is to use a function.

Now initially when we perform a function we went to Home and insert a function this way. But you'll notice on the ribbon, we have a formulas tab. When you click on the formulas tab, all of the formulas available in Excel become available in different categories such as financial, logical and we are going to look at AutoSum.

From the dropdown of auto-sum, I'm going to click on sum, and again we have our standard function =Function name and then range of cells that we are going to perform our function on.

We need to select the first range of cells, which are on a different worksheet. And to navigate to that worksheet, click on the worksheet tab, Week 1, select the cell that you wish to use in your calculation. Once you have done that, hold down the shift key. Once you have done that, hold down the shift key. With the shift key held down, if you move the cursor to click on Week 4, it will select each of these sheets.

I've now clicked on week 4, I will let go of the shift key, and you can see Week 1, Week 2, Week 3, Week 4 are selected. And in the formula bar we have =SUM and in brackets 'Week1:Week4'This designates the worksheets. And after that it has !C3. It's telling us that it's going to perform a sum of the Cell value of C3 in Sheets Week 1 to Week 4. Click enter or the enter tick from the formula bar, and now in the Totals worksheet, We have the total sum for Monday which is 221.

Because we are working with tables, the calculations have automatically been created for the remaining days of the week. So here we have selected C7, and in C7 the value of the formula is =SUM('Week1:Week4'!C7) A really quick way to add totals for values in different worksheets.

Firstly selecting the cell, clicking on AutoSum, clicking on the first cell you wish to calculate in, the actual AutoSum, holding down the shift key, and clicking in the last worksheet for the range. In the total field it will create the function =SUM('Week1:Week4'!C3)