Quick, clean, and to the point

Shortcuts for named ranges

In this video, we'll look at shortcuts for creating and applying named ranges.

Here we have a set of sales data that spans 2 years, broken down by month.

Using named ranges, I'm going to add several formulas to fill in the summary information, based on a name in I8.

Generally, the fastest way to create a named range is to use the name box.

Just make a selection and type a valid name.

To see a list of existing names, use Control + F3. On a Mac, add the function key.

Names also appear in the name box drop down.

To create names automatically using row or column labels, use Control + Shift + F3, with the function key if you're on a Mac.

When I select all sales data, I can use this shortcut to create a named range for each column in the table. In this case, the names come from the Top Row.

Now we have 6 names.

As you enter formulas names will match automatically as you type. Confirm a name with Tab.

In Windows, you can also paste a name into a formula using F3.

You can also just point and click, which works on both platforms.

Excel doesn't have a MAXIF or MINIF function, so to get that information, I'll need to use an array formula.

Array formulas must be entered with Control + Shift + Enter.

Now when I change the name, the summary is fully dynamic.

I'll make one more change to make choosing a name more convenient - I'll add a dropdown for names using Data Validation.

First, I'll get a full set of names from the table, paste, and remove duplicates.

With the Ribbon, remove duplicates is: Alt A + M.

Now I can select all unique values + the header, and define a new named range called "Names" 

Finally, I need to apply data validation to the input cell. With the Ribbon, data validation is:

Alt A, then VV.

For the List, I'll paste in "Names".

Now we have a working drop down.

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas