One extremely common request I hear as an Excel consultant is “how do I create a drop-down list to keep my data clean?” (Maybe not those words exactly, but you get the point).
With a little bit of working knowledge on named ranges, you too can have this professional looking feature in your worksheet.
First, make a table of the data you want to appear in the drop-down and apply a named range to it. In this case, we will name this range ’employees’.
Next, select the cell where you want the drop-down list to appear. For this tutorial’s purpose, the cell should be on the same worksheet as the range ’employees’.
Navigate to the data validation section of the menu by following the cues below.
Within the dialog box that pops up, select Allow->List and for Source, write “=employees”.
Ta-Da! You should now have a drop-down list for the cell.
Bonus! – You can copy and paste this cell and the drop-down list is copied right along with it.
Have questions about how to get the most out of Excel?