If we were to record our sales for each month by providing a separate sheet for each product, we would probably create something that looks like the following.
For ease of reading, we have used the same cells for each product’s months. In fact, the only thing that is different between the sheets (besides the sales values) is the product heading at the top of the sheet.
If all the January sales for each product were to be added together and placed on a summary sheet (“All_Products”), the formula could look like the following:
='Prod Game'!B3 + 'Prod Utility'!B3 + 'Prod Productivity'!B3With only 3 products in this example, we are already growing weary of selecting each cell for each sheet. Imagine a workbook with hundreds of sheets, or when you wish to add a new product sheet. The formula is not dynamic; it would need to be updated. This strategy breaks down quickly.
Enter the World of 3-D Formulas
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…
=SUM(A1:A10)…we can use the same colon symbol to say “start sheet thought end sheet”; or “Sheet1 through Sheet10” as in the below example.
Sheet1:Sheet10The 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
=SUM(’Prod Game’!B3NOTE: 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:
=SUM(Prod_Game:Prod_Productivity!B3)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.
Getting the Grand Total with a Single 3-D Formula
It’s not necessary to create the individual month sales formulas to act as feeder formulas for the grand total formula.
If we are only interested in the grand total, we can write the following formula to arrive at our result:
=SUM(‘Prod Game:Prod Productivity’!B3:B8)The referenced cells return an array of values from each sheet which is then ‘stacked’ atop one another to then be added together by SUM.
Adding New Product Sheets
One of the previously stated advantages to 3-D formulas is that they are dynamic. This means that if we create a new sheet for a new product, we only need to add the sheet to the workbook and our 3-D formulas update automatically.
There is one tiny, little catch to this. The newly added sheet must be placed between the previously referenced “first” and “last” sheets. This is because the 3-D formula read every sheet between the stated sheets (inclusively).
If a new sheet is added outside the range of defined “start” and “end” sheets, the 3-D formula will be unable to see the new sheet.
This can also be an issue when a user accidentally places a sheet between the “start” and “end” sheet that is not supposed to be included in the 3-D formula.
The 3-D formula will absorb the newly-added sheet into its logic.
BONUS TRICK
We want to thank Bob Umlas for sending in this amazing trick.
Bob is one of our favorite contributors here at XelPlus. With 20-plus years as an Excel MVP, he has a few tricks up his sleeve.
The issue is that we need to reference multiple worksheets for a 3-D formula, but the sheets are not grouped together.
The trick is to add a common prefix to each sheet and then add all sheets with that prefix.
If we were to update all our sheet names with the prefix “Prod”, we will then be able to leverage that to our advantage when selecting sheets.
By using wildcard characters (i.e. asterisk “*” for “anything/any amount” and question mark “?” for “anything/1 character”), we can craft a more generic reference to the qualifying sheets.
Starting from the beginning, if we select cell B3 for “January” sales, we can write the following formula:
=SUM(‘*Prod*’!B3)This formula says that anything can come before or after the text “Prod”. We are including the single-quotes in case any of the discovered sheets contain spaces. If you knew the sheet names would never have spaces, you can omit the single-quotes.
If the text “Prod” always begins the sheet names, the formula would be written as follows:
=SUM(‘Prod*’!B3)Or, if “Prod” was always at the end of the sheet names…
=SUM(‘*Prod’!B3)When we hit ENTER, look at what Excel turns the formula into.
=SUM('Prod Game:Prod Health'!B3,'Prod Utility:Prod Productivity'!B3)Excel has created two separate range references, one for all sheets between “Prod Game” and Prod Health”…
=SUM('Prod Game:Prod Health'!B3,'Prod Utility:Prod Productivity'!B3)… and one for all sheets between “Prod Utility” and Prod Productivity”.
=SUM('Prod Game:Prod Health'!B3,'Prod Utility:Prod Productivity'!B3)BEWARE
Remember to be mindful of positioning new sheets in the workbook. If they are to be included in the 3-D formula, you need to add them between a set of referenced “start/end” sheets.
If you need to exclude the sheet from the 3-D formula, make certain the new sheet(s) are placed outside the referenced sheets.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
