3-D formulas have several advantages:
- 3-D formulas are short
- 3-D formulas are dynamic; they recognize newly added sheets
- 3-D formulas are easy to create
- 3-D formulas are easy to understand
3-D formulas have the following disadvantages:
- Hmmm…, I can’t really think of any at the moment, but I’ll let you know if I do
Our dataset below has 3 sheets, one for each product, where the sales are recorded by month.
The data for each month’s sales is in the same cell locations from sheet to sheet (i.e. January sales are in cell B3 for each product.)
We have a summary sheet named “All_Products” that will sum all the January sales for each of the 3 sheets and place the result in cell B3 of the summary sheet.
NOTE: When placing the results on the summary sheet, it is not required that the results rest in the same cell address location as the values being summarized. In this example, it is done for the sake of clarity.
When defining a range of cells on a single sheet, we use a colon “:” to symbolize the word “through”; as in “A1 through A10”. Where a SUM function would add all the cells from cell A1 to cell A10 using the below formula syntax…
…we can use the same colon symbol to say “start sheet thought end sheet”; or “Sheet1 through Sheet10” as in the below example.
The steps for creating a 3-D formula for January sales is as follows:
- Select the “All_Products” sheet and click in cell B3
- In cell B3, type an equals sign
- Type SUM(
- Select the first product’s sheet (ex: “Prod Game”)
- Click in cell B3 of the “Prod Game” sheet
The formula bar should display the following partial formula
NOTE: The single-quotes around the sheet name are there due to the presence of a space between “Prod” and “Game”. If your sheet name does not contain spaces, you don’t need the single-quotes in the reference.
Rookie Mistake: It is assumed by some that we will complete the formula as follows if we want to SUM cells B3 on each of the 3 sheets in our example:
=SUM(’Prod Game’!B3:’Prod Productivity’!B3)
What makes this incorrect is that Excel only requires a single set of single-quotes for the sheet names and a single exclamation mark to separate the sheet names from the cell address. The correct way to write this formula is as follows:
=SUM(’Prod Game:Prod Productivity’!B3)
If we didn’t have spaces in our sheet names (perhaps we have underscores, instead), the formula would appear as follows:
This may be a compelling reason not to use spaces in sheet names, as 3-D formulas will have less complexity which translates to fewer errors when typed.
Once the formula for January sales is complete, we can fill the formula down to the remaining months.
Because we have a monthly total at the bottom of each product sheet, we can even create a 3-D grand total. If we didn’t possess totals on the separate product sheets, we could easily sum the results of the “All_Products” sheet to get the grand total.