« Mr.Obama in India Today | BOOM!!! Excel 2016 is coming soon... » |

## Dynamic Use of 3D Formulas

Formulas, 3D-formulasHi there,

What is 3D formulas?

Where to use 3D formulas?

When to use 3D formulas?

Can we use 3D dynamically?

These are some of the questions which comes to mind when hearing about "3D formulas" for the first time.

Let me go step by step to explain them. **Download Working File.xlsx**

...

**WHAT IS 3D FORMULAS?**

3D formulas or 3D references are something which helps in calculating a cell or range of cells from more than one sheet, of same cell reference.

**WHERE TO USE 3D FORMULAS?**

To calculate the summary of values from Jan'2014 to Dec'2014 where each month values are placed in different sheet but the cell reference is same for all the sheets. This can be achieved using 3D references or formulas.

**WHEN TO USE 3D FORMULAS?**

3D formulas can be used to consolidate all the results from different sheets to a single sheet without complicating the formula/reference. This also saves processing time and saves the file size as well.

**CAN WE USE 3D DYNAMICALLY?**

Let me show you how it works and you can decide whether it can be used dynamically or not.

We have 12 sheets in a workbook with names Jan, Feb, .... Dec. Here to sum the values from all these sheets we can write a formula

=SUM(Jan!C5,Feb!C5,Mar!C5,Apr!C5,May!C5,June!C5,July!C5,August!C5,September!C5,October!C5,November!C5,Dec!C5)

This formula will sum the values listed in all the sheets (12 sheets) in Cell 5.

We can do the same thing using different formula, see below

=SUM(Jan:Dec!C5)

Now, compare and tell me which is easy.

You are right, the second formula is easy right. This is called 3D formula.

Say, we need to sum values only from Jan, Feb,...Dec, except November. How to do this?

Here is a logic involved to do this. Insert two sheets on either of the ends say, before Jan and after Dec and rename them as "start" and "end". Now, move the sheets out of the range for not calculating using 3D formula.

Are you confused!

Move the number sheet after the sheet end, so that the sum function will calculate only from Jan to Dec excluding November because it is not in a range of sheets.

**Note:** Makesure you move other than first sheet and the last sheet. **eg:** should not move Jan or Dec sheet to out of range (after sheet end), because it doesnot make any change in calculations as the sheet name is mentioned in the formula itself.

That's it. Magic Works!!!

Note: This post is written with the inspiration of Mr.Excel Tips, If you like it, thank him as well.

**DO YOU USE 3D FORMULAS:**

Please do teach me how you work on 3D formulas, using below comments section.

If you like this post, please do share it on your social networking sites by clicking on the icons placed around the blog.

If you want to receive such posts to your email, **Leave Your Email** here. We respect information security.

**SOME IMPORTANT LINKS:**

**SUM VALUES BASED ON TWO CONDITIONS**

Thanks,

You must be logged in to see the comments. Log in now!

If you have no account yet, you can register now! (It only takes a few seconds)

## Recent comments