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.