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.
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
Quick, clean, and to the point.