How to set up a dropdown list in Excel to ensure correct answers - Photo by Donald Giannatti on UnsplashI am sure we have all had the tasks of sifting through data where people have filled in a form and have been allowed to use their own words. This makes the job of analysing the data from the form when it comes back.

If we give the user a chance to write whatever they want, we can get a very wide range of answers that could be interpreted in different ways. Therefore, it is best practice to give a user a predetermined choice of answers. In this tip I am using the scenario of multiple people filling in the data on a shared spreadsheet. I therefore need the same spelling and word choice from each person.

In the past I have discussed the Data Validation function. We are going to use it here but not quite in the same way.

Firstly, you need to create a full list of choices. Create this on a separate sheet in the file you are using. This list can be in any order you wish. It does not have to be in alphabetical order, giving you the chance to select the most common choice for the top place in the list.

Here is a sample list.List of three

Once created, select and name the range of items you have typed in each column. Giving it a sensible but short name for each set of answers.

I have named these lists as “AnswerList_1”, “AnswerList_2” and “AnswerList_3”.

select one

Remember the rules on naming cells, you will find that in my previous tip on naming ranges in Excel.

How to set the dropdown list to a cell or a range of cells

Now that you have your named list on a separate sheet you can apply this to a cell or a range of cells by using the Data Validation Function.

Data Validation

  • Select the area where you wish the user to add the correct word. I find it useful to highlight the cell in a light colour so that the user knows that they will have a selection to choose from in that cell.
  • From the Data Tab in the Data Tools section Select the Data Validation Tool. Data Validation Tool

This dialog box appears.Validation Settings

  • From the “Allow” list select: “List”
  • Click in the Source line, which has now appeared, to activate it.
  • Press the F3 key to access the list of named ranges.Named ranges List
  • Select the name for this area in the spreadsheet. Then click on Ok.

Your sheet will look something like this.Questions

  • Keep building your questionnaire in this same way. I have also coloured the cells were the dropdown lists occur to make it easier on the user the know there is a list to choose from.
  • Click on any of the cells in that light blue area and you will see the list of choices.
  • Select one from the dropdown arrow. Dropdown arrow list

The added beauty of this named list is that you can edit it in the future changing some of the entries or adding to it (inserting within the named range of course not at the bottom of the list, as it will not be part of the original named range).

Drop down lists make your work a little easier or someone else’s, fun to work with.

Tips – using range lookup in the Vlookup function in Excel.

Tips – How to create the count functions in Excel

Tips – Count functions in Excel Continued

Using the Average Function in Excel


Please enter your comment!
Please enter your name here