Microsoft Excel Tutorial: Named Ranges and Implicit Intersection - Learn a Useful Excel Trick!

Welcome back to the MrExcel netcast! In this episode, we will be discussing a useful Excel trick involving named ranges and implicit intersection. As I was working on my new books for Excel 2013, I came across this cool trick that I just had to share with you all.

First, we will create some names from selection, a technique that you may have seen Mike Girvin demonstrate before. By selecting the top row and left column and clicking "OK", Excel will automatically define a range of names for us. However, sometimes these names can be too long to fit in the name box, making it difficult to see which range is which. But fear not, I have a solution for you!

Did you know that the little circle in the bottom right corner of the name box is actually a handle? By hovering over it, you can make the name box bigger and the formula bar smaller, allowing you to see those longer names. This is especially helpful when dealing with names like "Des Moines Washington" and "Des Moines Iowa", where the only difference is the state abbreviation.

Now, onto the main trick of this episode - implicit intersection. This is a technique where Excel will automatically return the value of a cell that intersects with a given range. For example, if I type "Cleveland Ohio" in a cell below the range, Excel will return the value from the cell in the same column as the "Cleveland Ohio" range. This also works with formulas, as shown when I use the SUM function to add up values from two different ranges.

But here's where it gets really interesting - you can even use this technique to return values from cells outside of the given range. By typing "=F4" in a cell and copying it down, Excel will return the value from the cell that intersects with the "F4" range. This may seem like a small trick, but it can be incredibly useful in certain situations.

So there you have it, the power of named ranges and implicit intersection in Excel. I hope you found this episode helpful and learned something new. Don't forget to subscribe to the MrExcel netcast for more Excel tips and tricks. Thanks for watching and I'll see you in the next episode!

Buy Bill Jelen's latest Excel book: [ Ссылка ]

You can help my channel by clicking Like or commenting below: [ Ссылка ]


Table of Contents:
(00:00) Name Box and Implicit Intersection
(00:12) Creating Names from Selection
(00:23) Trick for Viewing Long Names in Name Box
(00:59) Two-Way Lookup and Implicit Intersection
(02:01) Implicit Intersection Outside of Range
(02:33) 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:
Creating names from selection
Excel podcast
Handling long names in name box
Implicit intersection
Learn Excel from MrExcel podcast
Making formula bar smaller
Name box expand
SUM function with multiple ranges
Two-way lookup in Excel
Using implicit intersection outside of range
Value error in Excel


Join the MrExcel Message Board discussion about this video at [ Ссылка ]


Name Box Expand is a great way to actually be able to see your Ranges - as you've named them - in the Named Range Box, without trying to guess what you are looking at. But did you know that you can find that name showing in lists when using other Functions of Excel? Bill shows us an interesting discovery and the uses of that discovery in Episode #1579 - this may make your Excel tasks just that much easier and more logical to follow. Today begins a week-long Series on Named Ranges: This is Part I of IV

This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! [ Ссылка ]

"The Learn Excel from MrExcel Podcast Series"

MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!

свернуть