Look Who it Was Random video

Excel - Excel Trick: Noncontiguous Spearing - Analyzing Data Like a Pro! - Episode 974

692 views · Published 19 March 2009 · 2:29 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Excel Trick: Noncontiguous Spearing - Analyzing Data Like a Pro!

Welcome back to the MrExcel netcast! In today's video, I have an amazing trick to share with you - noncontiguous spearing in Excel. This is a game-changing technique that will save you time and effort when analyzing large amounts of data.

As we all know, working with massive amounts of data can be overwhelming. But fear not, because with this trick, you'll be able to easily analyze and summarize your data using pivot tables. So let's dive in and see if you can solve this problem.

First, let's do a quick review. You're probably familiar with spearing formulas, also known as 3D formulas. For example, if you want to add up cell B3 on all the sheets from January to December, you would use the formula =SUM(Jan:Dec!B3). Easy enough, right? But what if your data is not organized in contiguous sheets? That's where noncontiguous spearing comes in.

Let me show you an example. Say we have three salespeople - Andy, Bob, and Charlie - and each of them has three sheets for different products. If we want to add up all the sales for product ABC, we would normally have to manually select each sheet and add them up. But with noncontiguous spearing, we can simply use the formula =SUM('*ABC'!B3) and Excel will automatically grab all the sheets with "ABC" in their name. How cool is that?

But it gets even better. You can also use wildcards in your formula to grab all the sheets with a certain word or phrase in their name. For example, =SUM('Bob*'!B4) will grab all three sheets for Bob. This is a great way to write efficient 3D spearing formulas that don't require contiguous sheets.

I hope you found this trick as amazing as I did. Thanks for stopping by the MrExcel netcast, and be sure to tune in next time for more helpful Excel tips and tricks. Don't forget to like and subscribe for more content like this. See you in the next video!

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/


You may have seen how to create a spearing or 3-D formula such as =SUM(Jan:Dec!B3). In Episode 974, an amazing way to create a 3D reference to non-contiguous sheets.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Table of Contents
(00:00) Spearing 3D Formula in Excel
(00:28) Sum January through December
(00:59) Add All Sheets that end in ABC?
(01:09) Use Wildcard in the formula
(01:38) Actually points to each sheet
(02:06) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Adding up values from multiple worksheets
Advanced Excel formulas for data analysis
Creating dynamic formulas in Excel
How to use 3D formulas in Excel
Maximizing productivity with Excel
Microsoft Excel tips and tricks
MrExcel netcast with Bill Jelen
Solving complex Excel problems
Summing values based on specific criteria in Excel
Techniques for efficient data manipulation in Excel
Using wildcard characters in Excel formulas
YouTube tutorial on analyzing data with pivot tables

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152265/

More from this channel