How to Calculate Excel Formulas With Multiple Pages
By Laurie Marie
While initially it may seem complicated, calculating formulas across multiples pages in Excel is actually quite simple. When you create a formula within a single worksheet you enter cell references in the formula. For example, to get a sum for cells A1 and B1 you enter the formula =SUM(A1:B1). To create a formula that crosses multiple pages you just need to include a worksheet reference in addition to the cell reference. When you refer to the same cell or range of cells across multiple worksheets, Microsoft calls this a “3-D reference.”
3-D Reference: Contiguous Worksheets
Step 1
Create an Excel workbook with four worksheets.
Step 2
Enter a numeric value in cell A1 of Sheet2, Sheet3 and Sheet4. Start by making a simple formula to sum up values across these three worksheets.
Step 3
In any cell on Sheet1 enter an equals sign followed by your function and an opening parenthesis. For our example, enter “=SUM(”.
Step 4
Click on the "Sheet2" tab at the bottom of your workbook and select the cell in Sheet2 that you want to include in your sum. For our example, use cell A1.
Step 5
Hold down the shift key and click on the "Sheet4" tab. The formula updates to include the same cell on Sheet4 that you selected for Sheet2. Complete the formula by entering a closing parenthesis. Your formula will look like this: =SUM(Sheet2:Sheet4!A1). This formula sums up the values in cell A1 on Sheet2, Sheet3 and Sheet4. Notice the format of the resulting formula. The colon between the sheet names indicates that the formula spans all sheets from Sheet2 through Sheet4.
3-D Reference: Selected Worksheets
Step 1
In another cell on Sheet1 enter “=SUM(”. For this example, sum up cell A1 on Sheet2 with cell A1 on Sheet4. This time rather than clicking on cells to select them for the formula, enter the worksheet and cell references directly.
Step 2
Enter the first argument for your sum. The first argument is “Sheet2!A1”. In this case, “Sheet2!” is the worksheet reference and “A1” is the cell reference.
Step 3
Enter a comma followed by the second argument. The second argument is “Sheet4!A1”, where “Sheet4!” is the worksheet reference and “A1” is the cell reference.
Step 4
Enter a closing parenthesis. Your complete formula should look like this: =SUM(Sheet2!A1,Sheet4!A1). This formula will sum up the A1 cells on Sheet2 and Sheet4 and exclude the value in cell A1 on Sheet3. Notice how the format of this formula differs from the previous formula. The references are separated by a comma, indicating that only the stated references are included in the calculation.
References
Tips
- You can create formulas across multiple sheets with any other function including, but not limited to, average, count, max, min and sumif.
- This process works the same in Excel for Windows and Mac.
Warnings
- Formulas must always have matching sets of parentheses to function correctly.
Writer Bio
Laurie Marie has a Bachelor of Science degree from San Francisco State University in business/accounting. She is a payroll industry veteran who assists large corporations as an independent payroll consultant. She specializes in writing payroll, consumer and lifestyle topics. Her articles have been published in the American Payroll Association Paytech magazine and on eHow.