Creating dropdown menus and named ranges in Excel is a great way to speed up your workflow and streamline your dashboards. This example will show how to create dropdown menus to select specific athletes and exercises.
Creating the Named Ranges
Named ranges allow Excel to identify specific cells within your sheet, making them easy to reference. To create a named range, we first need to list the data we want it to include. Create a header "athlete name" and simply list their names below. Then create a new list with the header "exercises" and repeat the process of listing the exercises you wish to track. Now that you have your lists, you need to create named ranges for them. We will do athlete names first.
Select the number of rows that contain your names (if you will be adding more, include more blank cells
Go to Formulas > define name
Enter "AthleteName" with no spaces
Click "OK"
Repeat this for the exercise list
Creating the dropdown menu
Select the cell that you want to use as your dropdown menu for selecting the athlete's name. To create the dropdown menu itself:
Select Data > Data Validation
Choose "list"
Type "=" and the name of the range you just created. e.g., =AthleteName
Press "Ok"
You can see a dropdown has appeared and now you have all of the athletes available. If you want to add an athlete, simply go back to my lists and add them to the bottom of the list. Repeat the process for your exercises and you are done. You now have named ranges and dropdown menus for your athletes' names and exercises.
If you want to learn more about Excel, using named ranges and dropdown menus, check out our tutorial for creating a Multi-Athlete Lift Logbook.
Comments