Dropping it low in Excel is easy (and easier on the knees) thanks to drop down lists. These are great tools to use if you’re entering values that repeat in numerous cells or if you want to restrict a user to only entering a certain range of values. Check out our table above as an example: we’ve got a list of projects and need to fill out a status for each one to indicate if the project is complete, in progress, or not started. Instead of typing out each status each time in each cell, we can use a drop down list.
To create a drop down list, the first thing we’ll need to do is open the Data Validation dialog box by pressing ALT A V V, or you can navigate to the data tab and select “Data Validation” in the data tools group. Once the dialog box is open, we need to select “List” from the “Allow” permissions drop down, because we want our cells to contain a value from a predetermined list.
Next, we’ll need to enter the values that we want to appear in our drop down list in the Source box. If we’ve already got those values present somewhere in our worksheet, we can select the range of cells that contain status data (that’s what I do in the example above), or we could type in each status manually separated by a comma. Once we’ve entered the drop down list’s values, all we need to do is hit “OK” and we’ll have successfully created our first drop down list!
Now go ahead and fill the drop down list down the column using the autofill handle, and, voilà! We’re ready to quickly select each project’s status from our new drop down list (and, bonus, nobody will be able to enter a misspelled value).
