Excel 3D Formulas Explained (Sum Across Multiple Sheets)

Learn how to get the sum across multiple Microsoft Excel worksheets, with this feature called 3D referencing. With 3D referencing, you can refer to the same cell in multiple worksheets, and can also use this in formulas such as the SUM or AVERAGE, or COUNT.

Excel 3D formulas can be used to consolidate data from multiple worksheets into a summary sheet. 3D formulas aren't that well known except to advanced Excel users.
If you ever needed to reference the same cell or the same range on multiple Excel worksheets you'll know this can be quite a tedious process. Plus the reference is not dynamic. So, if another Excel sheet gets added in the middle, the formula will not update.

It doesn't have to be this way! In this video I'll show you an alternative approach to gather data from multiple Excel sheets to one sheet. With Excel 3-d reference formulas you can replace this cumbersome process with a short and flexible formula.

In addition, I'll show you a bonus Excel trick I received from Bob Umlas. With this hack we can even add cells or ranges on worksheets that aren't beside one another. This works as long as the sheets have a common name. It's like using wildcards in the 3d reference.

This tutorial is perfect for summing up figures like sales across different product sheets or any other data spread across multiple tabs.

🔑 What You'll Discover:
- The basics of creating 3D-reference formulas in Excel.
- Tips for dynamically summing data across multiple sheets.
- How to customize your formulas for specific data ranges.

🎯 Highlights:
- Simplify data analysis by consolidating information from different tabs.
- Learn how to dynamically update your data with new sheets.

00:00 How to sum data across multiple sheets
00:47 Excel 3D Formula explained
06:21 Bob's Bonus Trick for using 3d formulas in Excel

✉ Subscribe & get my TOP 10 Excel formulas e-book for free

Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[LeilaGharani]

Thank you for your comments and input. As mentioned by some members of our community, you can use the shift key to select the last sheet. So start typing "=SUM(" then click on the cell you want - then hold down the shift key and click on the last sheet.

[mtroanoke]

Thanks for explaining about the quote mark around the name of a tab including a space. It solved an issue I've been having with one of my spreadsheets!

[MrDanielduranc]

The more videos I see the more I fall in love with you. You are the best!

[atsrikumar]

Ma'am, I am very impressed. Your teachings increase my enthusiasm to learn new things. You have changed my inactive mind in to active. God Bless You!!!

[montyearl]

Thanks heaps Leila. You've done a fantastic job sharing your knowledge on YouTube. Today's trick is gonna save me lots of time. Actually, I'm planning to watch your videos on a a daily basis.

[harishkenchugonde]

As the number of sheet increases, solving using "SHIFT KET" Is a difficult task and creates confusion practically.. But what maam has taught is more clear and practically no issues.. Especially Bob's 3d formula😍

[rajanpradeepankarath]

Thanks Leila. I knew the 3D formula. I had been using wildcard filtering in Power Query as well. But never imagined I could combine it. Brilliant!

[mukundjoshi]

Wow... so far ur’s is the only channel that I hv come across on YouTube which shares real cool stuff about excel. U r sharing the things that I had dreamed to find out on my own. Rest all are still sharing 20 shortcuts in excel kinda kids stuff. But I feel lucky to hv come across your channel. My optimism levels with what all I can do with excel and data are growing by each day which helps me in my data Analytics job. Many thanks to you and keep up sharing your knowledge. Truly admired. Thank you 🙏

[alessandrocosta]

Your videos are clear, well planned and very informative. Thank you!

[blackknight]

OMG!! What a bonus is it?, thank you Bob. And thank you leila.

[robertovelicaz]

What a time saver! Thanks Leila and Bob 😃

[wofdigy]

Brilliant. Thank you Leila. Bob, that's fantastic!

[prabhakaransankar]

Wow, awesome. Bob's idea is so nice!

[dejabluek]

Thank you for sharing this super neat trick! I can't wait to share it with my colleagues!

[enriquedominguez]

Thank you Leila and Bob for these tricks.

[dheedoc]

Oh my.. U are the best Excel Teacher ...who knows students requirement. Thanks Leila. U hve saved so much of my time and stress.

[Softwaretrain]

Thanks, First way can be don by holding shift but bonus was amazing.
Love it.

[JeanPierreWhite]

Nice. Consolidation of accounts is the application I was thinking of before you mentioned it.


Accountants used to prefer Multiplan to Excel because 3d formulas were so easy in Multiplan. Nice to see Excel have that functionality.

[lucasvtiradentes]

you're fantastic! thank you for sharing so much knowledge <3

[stevenng]

You are very hardworking and passionate for sharing of your knowledge and skills.