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

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.