Microsoft Excel Tutorial: Mastering 3D References in Excel: Save Time and Effort with Spearing Formulas | Excel In Depth.
Welcome to another episode of the Mr.Excel netcast! In this chapter of Excel In Depth, we will be diving into the world of 3D references.
As we all know, Excel is full of cool formula tricks, and 3D references are no exception. Also known as Spearing Formulas, 3D references allow you to easily total up data from multiple sheets. In this podcast, we will be discussing how to use this feature to save time and effort.
Let's say we have sheets for each month of the year and we want to total up the data from all of them. Instead of manually writing a formula for each sheet, we can use a 3D reference to quickly add up all the numbers. By using the SUM function and specifying the range of sheets we want to include, we can easily get the total for all the months.
But that's not all, we can also use this feature to copy the formula to other cells that need to add up data from multiple sheets. By using the Paste Special Formulas option, we can quickly replicate the formula to other cells, saving us even more time and effort.
So there you have it, a quick and efficient way to use 3D references in Excel. Thank you for tuning in to this episode of the Mr.Excel netcast. Don't forget to subscribe and stay tuned for more Excel tips and tricks. See you next time!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/
Table of Contents:
(00:00) 3D References
(00:14) Topic: Spearing Formula or 3D Reference
(00:24) Setting up sheets for January-December
(00:38) Using Ctrl key to drag and create new sheet
(00:52) Using formula to add up January-December
(01:05) Alternative method using "sum" function
(01:21) Copying formula to other cells using Paste Special
(01:35) Clicking Like really helps the algorithm
This video answers these common search terms:
3D References
Adding up sheets
Chapter 17
Copying format and structure
Excel In Depth
Excel podcast
January to December sheets
Paste Special Formulas
Spearing Formula
Sum formula with sheet names
Totaling sheets
A 3-D Reference (also known as a 'Spearing Formula') is when you need to shoot through a series of worksheets to add up the same cell on many worksheets. Episode #1253 shows you how to create these references in just a few clicks!
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel. For all of your Microsoft Excel needs visit MrExcel.com - Your One Stop for Excel Tips and Solutions.
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152934/
