Note with named ranges you cannot use spaces. If you need to, use an underscore.
Next, select the cell where you want the drop down list to be. Under the Data tab in Excel select Data Validation. Under the allow section, select List. Then in the source section reference your named range with an = sign before it. See example below:
If you didn’t assign a named range to your options, you have to specify the cells here. Click ok and you’re done.
Now when you select that cell you will see the drop down options available. If you want to copy the drop downs to be available to adjacent cells use flash fill by dragging the bottom right corner of the cell with data validation and drag over to which cells you want it to apply to.
Drop Down Options Based on Previous Selections
What you can also do is make your drop down options dependent on what you selected in a prior drop down selection. This requires using the INDIRECT function.
In the first drop down option what I will do is create a named range for all the categories,Category1-Category5. Once a user selects an option, the next drop down will be based on the products relating to the category that they have selected. The first part is the same as the process mentioned above, only now it is relating to the selection of a category group. The key here is making sure that the categories are the same as the named ranges I have created for them. For example, if a user selects Category1, in order for my other drop down to work I need to have a named range assigned to Category1. If I do, then I can utilize the indirect formula. Below is the data validation I would use for the second drop down option:
My category selection is in cell B7. The indirect function looks at cell B7 to see what range I am referencing. If I select Category1 in cell B7, then the data validation will pull the named range for Category1. If I haven’t set up a named range for the selection, the drop down list will be empty. Otherwise, my second drop down will now reference the products in the named range belonging to Category1.