How to Unlock the Power of 3 Dimensional Formulas in Excel 365: A Game-Changer for Quick Reporting
Looking to streamline your data analysis across multiple worksheets in Microsoft Excel 365?
Then you MUST know about 3 Dimensional (3D) formulas!
3 Dimensional formulas allow the same cells to be referenced across multiple worksheets and is great for adding monthly data together with the result appearing on a summary sheet, reducing the risk of errors and helping you work more efficiently.
In this post, I’ll share what 3D formulas are, how to use them, and why they are a game changer in Excel, particularly if you are working on consistent data added up over months, weeks, teams or departments.
Prefer to watch rather than read?
It’s OK, check out the video below
What Is a 3 Dimensional Formula in Excel?
Put simply a 3 Dimensional, or 3D formula refers to a formula that works across multiple worksheets that generally have the same formatting – think column and row headings the same. Commonly used for adding data across multiple worksheets into a summary worksheet, 3 Dimensional formulas save time as they are a single formula that spans across multiple worksheets in the one file.
For example:
=SUM(Sheet1:Sheet5!A1)
This formula adds (hence the use of the function SUM) cell A1 from Sheet1 to Sheet5 inclusively.
Extra Note : Whenever you see an ! in a formula it means whatever is to the left of it is the name of a worksheet tab in the current file.
Want more Microsoft 365 tips for business.
Subscribe here.
Why Should You Use A 3 Dimensional Formula?
Here are some reasons why 3 Dimensional Formulas, or 3D formulas are a game changer.
- Efficiency – Instead of having to create a long formula adding multiple cells together, using a 3D formula simplifies the process
- Risk Management – Because you are creating the formula once and referencing multiple sheets, as long as your first formula is correct, it is easy to fill data in your report with the formula.
- Automatic Update – If a value changes in one of the worksheets referenced, the cell containing the 3 Dimensional Formula automatically updates!
How to Create a 3 Dimensional Formula in Microsoft Excel 365
- Open your workbook with multiple sheets.
- Click into the cell where you want the result.
- Type a formula like:
=SUM(January:December!B2)
This will add the value in cell B2 across all monthly sheets that are named January through to December and place the result in the selected cell.
Want a step by step walkthrough of 3 Dimensional Formulas?
Check out this video I put together
Extra Tip – Here’s Some “Best Practices” for Using 3 Dimensional (3D) Formulas
- Keep it Simple – make sure you use consistent sheet names and spreadsheet structure – as shown in the example
- Avoid referencing cells that are merged in the referenced data as your 3D formulas may return an error.
- Check Twice, Create Once Double- check your formula references the correct worksheet and cell before coping the formula across the summary worksheet
Donna’s Final Thoughts
Consolidating data cross multiple worksheets in Excel 365 using 3 dimensional formauls is an amazing way to combine data.
Regardless of whether you are tracking sales, managing a budget or adding staff information, this feature can save you hours of unnecessary stress and frustration.
Want more Microsoft 365 help and support?
Check out my Productivity Hub here.
