Article 17 – 3D Formulas – Hidden Wonder of Excel

3D formulas are one of the hidden wonders of Excel and not many of us know about the secret of this. The purpose of this article is to unravel the mystery of 3D formulas in Excel.

Suppose you have 4 worksheets called Quarter1, Quarter2, Quarter3, Quarter4 as below. And you want to find the total of Quarter1 to Quarter4 revenue. This is quite simple a task, just put the formula =Quarter1!B6+Quarter!B6+Quarter3!B6+Quarter4!B6 and you are through. But what happens when number of sheets are large say 12, 50 or 100. This becomes quite cumbersome to enter. And that is where the magic of 3D formulas comes into picture.

The Excel file related to this article can be downloaded from 3D Formulas

What is a 3D Formula?
A reference that refers to the same cell or range on multiple sheets is called a 3-D reference. A 3-D reference is useful and convenient way to reference several worksheets that follow the same pattern and cells on each worksheet contain the same type of data. (Create a 3-D reference to the same cell range on multiple worksheets)

So, a 3D formula is basically a formula which operates on more than one sheet and has reference to range therein.

What Functions can be used in a 3D Formula?
3D formulas can be used with all Excel functions. They can be used with only limited functions and are used to perform mathematical calculations only. Following functions only can be used in a 3D formula as per Create a 3-D reference to the same cell range on multiple worksheets

• SUM
• AVERAGE
• AVERAGEA
• COUNT
• COUNTA
• MAX
• MAXA
• MIN
• MINA
• PRODUCT
• STDEV
• STDEVA
• STDEVP
• STDEVPA
• VAR
• VARA
• VARP
• VARPA

But, while I was researching for this article, I cam across following link http://www.bettersolutions.com/excel/EED883/QN620422111.htm and this listed down following additional functions which I tested and found working in a 3D formula –

• AND
• AVEDEV
• DEVSQ
• LARGE
• MEDIAN
• OR
• PERCENTILE
• QUARTILE
• RANK
• SKEW
• SMALL
• SUMSQ

How to Create 3D Formulas?
The syntax followed by a 3D formula is (SUM is used as an illustration only. Any function specified above can be used)

=SUM(Sheet1:SheetN!Range) — Hence, only two worksheets can be specified and a range can be specified. Please take note of this.

Note – In case, there is a space in sheet name, write this in following way (as an example)

=SUM('Jan 2019:Dec 2019'!C2)

Now, I want to know the total revenue for Quarter1 and Quarter2. Hence, I can write following 3D formula utilizing the syntax

=SUM(Quarter1:Quarter2!B6)

The beauty of 3D formula is that you just need to specify start sheet and end sheet and it will refer to all sheets lying in between these 2 sheets including these 2 sheets.

Hence, If Quarter1 to Quarter4 worksheets are sequential, then we can just write

=SUM(Quarter1:Quarter4!B6)
OR
=SUM(Quarter1:Quarter4!B2:B4)

It will add B6 of Quarter1, Quarter2, Quarter3 and Quater4 and answer would be 291000

A best practice is to have a sheet named Start and End which would be blanks and put all sheets between them. Hence, your 3D formula would simply be

=SUM(Start:End!B6)
OR
=SUM(Start:End!B2:B4)

Another best practice is to hide Start and End Sheets so that nobody tampers with Start and End.

Note – If you sheet names contain blanks also like Quarter 1, Quarter 2, then sheet names will have to be enclosed in apostrophes. Hence, formulas in this case would become (This is not because of 3D formula but is a general Excel Fundamental way to enter sheet name)

=SUM('Quarter 1:Quarter 4'!B6)

Another Way to Enter 3D Formulas (If you don't want to write full formula)
1. Write =SUM(
2. Click B6 in Quarter1 (or Start)
3. Hold SHIFT and click Quarter4 tab (or End)
4. Put ) and ENTER
OR
1. Write =SUM(
2. Click Quarter1 tab (or Start)
3. Hold SHIFT and click B6 in Quarter4 tab (or End)
4. Put ) and ENTER

You Can Create Named Range Also for 3D Formulas
You can use powerful named range also in 3D formulas. For example, I created a named range Quarters where Quarters is referring to =Quarter1:Quarter4!$B$6 and I can simply say =SUM(Quarters) which is equivalent to =SUM(Quarter1:Quarter4!B6)

To create a named range –
1. Press CTRL+F3 or Name Manager (or Define Name) in Formulas tab
2. Put Quarters (or any other name) in Name: box
3. Put =Quarter1:Quarter4!$B$6 in Refers to: box

Caution – When I was creating named range it was automatically putting double quotes around =Quarter1:Quarter4!$B$6 and thus =SUM(Quarters) was giving error. Hence, you need to remove these double quotes from around the formula in Refers to box.

Another example named range is Whole where I put =Start:End!$B$2:$B$4 in Refers to: box and then the formula =SUM(Whole) becomes equivalent to =SUM(Start:End!B2:B4)

How 3D Reference Changes when You Insert, Delete or Move a Worksheet

Inserting a New Worksheet between Start and End Worksheets – The new worksheet automatically becomes part of range between Start and End worksheets.

Deleting a Worksheet between Start and End Worksheets – The deleted worksheet automatically stops becoming a part of range between Start and End worksheets.

Moving a Worksheet out of between Start and End Worksheets – The moved worksheet automatically stop becoming a part of range between Start and End worksheets.

Moving Start / End Worksheet – The formula updates itself to include worksheets between Start and End only.

Deleting Start / End Worksheet – If Start sheet is deleted, the immediate right worksheet to Start becomes new Start sheet. If End worksheet is deleted, the immediate left worksheet to End becomes new End worksheet.

The Real Power of 3D formulas are realized when Wildcards are used
3D formulas become an even greater ally when the magic of wildcards are used. This is a little known feature of wildcards and its one of the greatest asset. (This trick I learned from John Walkenbach's book – Excel 2010 Tips and Tricks)

Suppose you want to create a 3D formula for all sheets starting with Qua, then you can simply write
=SUM('Qua*'!B2:B4)

You can use all wildcard rules in this related to * and ?.

And another magical trick is
=SUM('*'!B2:B4)
This means that sum the range B2:B4 of all sheets except the sheet where this formula is kept.

=SUM('???'!B2:B4)
This means that sum the range B2:B4 of all sheets whose name are having only 3 characters and exclude the sheet where this formula is kept.

Note – When you use wildcards to create 3D formulas, Excel will automatically convert this to a formula like =SUM(Start:End!B2:B4). Hence, no need to panic. Excel just takes the wildcard formulas and converts them to regular 3D formulas.

Question
Q1 – I want to create a 3D formula for only Quarter1, Quarter2 and Quarter4, hence leaving out Quarter3. How this can be done?
A1 – You must have noticed that the syntax of a 3D formula is such that it includes all sheets including Start and End sheets. Hence, it means that the sheets will have to be sequential for a 3D formula creation or wildcards will have to be used. So, you will have to use some sort of wildcard trick to do this. For example, I can rename these tabs as Quarter1X, Quarter2X & Quarter4X. Then, we can use following 3D formula =SUM('*X'!B2:B4)

Another alternative way to write would be =SUM(Quarter1:Quarter2!B6,Quarter4!B6)

Q2 – Can I use SUMIF function in 3D formulas?
A2 – No. You can use only functions listed in "What Functions can be used in a 3D Formula?". But this limitation can be overcome by helper columns in all sheets if this is acceptable to you.