A Quick Way to Name Ranges and use Named Ranges in Formulas

Last updated on November 17, 2011 By

In this 6 minute video I show you how to quickly name cell ranges with a powerful keyboard shortcut, and also show you how to use three math functions – SUMIF(), COUNTIF() and AVERAGEIF(). These are good building blocks that you’ll use in spreadsheet calculations and building spreadsheet models – I covered their friends SUM(), COUNT() and AVERAGEIF() in an earlier post that you can read by clicking here.


If you have any trouble watching the Youtube video embedded above, you can view the video on YouTube by clicking here.


Video Highlights

0:58 – Named range tricks (Ctrl + F3 to bring up name manager, Ctrl + Shift + F3 to create named range from selection)
1:52 – Create unique list of names (using Excel’s Remove duplicates feature, available in Excel 2007+)
2:30 – Set up data validation drop down list, sourced from a defined named range
3:35 – SUMIF() formula (total lifetime sales)
4:21 – COUNTIF() formula (number of months with sales)
5:04 – AVERAGEIF() formulas (average monthly sales)
5:49 – Using drop down list to select new sales person, formulas automatically recalculate


Named ranges explained

Named ranges are important to understand and use because they make it easier to read formulas. Which would you prefer to read?


= SUMIF ( sales_person, criteria_1, sales )


OR


= SUMIF ( $A$15:$A$170, $B$3, $D$15:$D$170 )


In the first formula, “sales_person”, “criteria_1” and “sales” are named ranges that point to cells on the worksheet. The nice thing about named ranges is that you can customize them and call them something that’s memorable and easy to read. You can’t use spaces in named ranges so I used an underscore when creating the named range “sales_person” – Excel doesn’t accept “sales person” as a named range.

Named ranges make it easier to read formulas.

You need to watch out though, because when you define a static named range like I did in the video you might forget to expand the range when there’s new data. There are ways to make dynamic named ranges that expand and contract when you have new data, but I’ll probably cover these in future posts. So when you see a named range it’s a good idea to check that it points to the correct cells and includes all the data that should be included.


I know that some people prefer hard cell references (like $A$15:$A$170) because they can see exactly where the cells are. With named ranges you need to use the Name Manager to figure this out.


Named range keyboard shortcuts

You can bring up the Name Manager in Excel by pressing Ctrl + F3. This lists the names used in your current workbook, and you can also define new names, edit existing names or delete names from the Name Manager.


The named range trick I introduced in the video is very handy if you want to define several named ranges using data that’s arranged in neat tables. Excel creates named ranges from your selection and uses your data headings as the new names – follow these steps:

  • make sure your data has headings (top row, left column, bottom row or right column) as these will turn into the names of your named ranges
  • select the data including headings
  • press Ctrl + Shift + F3
  • in the dialog box select where your headings are (top row, left column, bottom row or right column)
  • click OK

Pressing Ctrl + Shift + F3 brings up the "Create Names from Selection"

dialog box

When you’ve done this it’s good practice to check your named ranges were correctly defined – use the Name Manager by pressing Ctrl + F3 to check.

Bring up the Name Manager by pressing Ctrl + F3 to check that your named

ranges were correctly defined


Mind Map: Excel Formula Basics (Sumif, Countif, Averageif)

Click on this Mind Map for larger higher-res version


Learn more Excel Formulas – fast!

I based this video on material found in the Excel Formula 1 e-book, and if you are interested in picking up a copy of this e-book click on this link.


Bear in mind that I will get a commission from Chandoo.org if you purchase a copy of the Formula-1 e-book but the only reason I’m promoting it is that I think it can offer good value to Excel users who are just starting out and want a fast introduction to common Excel formulas.


The author Chandoo is a well-known Excel expert who was awarded Most Valued Professional (MVP) status by Microsoft for the help he has provided to the Excel community online, and he has written the e-book in a friendly informal style that makes it accessible if you don’t want to wade through more technical explanations.

To find out more about the Excel Formula-1 e-book from Chandoo, click on the

picture

Related Posts:


Connect on YouTube, LinkedIn, Twitter.

Hey, I'm Victor Chan

Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?

Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.

That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.

Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!


Recommended Posts

Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.

Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.

Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!

Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.

Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.

RECOMMENDED READING

Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.

Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!

Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.