How do you add a dropdown list to Excel?
If you want to add a dropdown to an Excel sheet with a list of values for a user to choose from, then you are in luck because it’s a fairly easy process.
Example of adding a dropdown list to Excel
So, suppose that we have a spreadsheet with a column for a list of car companies, and we want to have another column right next to it with a list of countries where those cars are made. But, we want each cell in the “Country” column to basically be a list of countries from which the user can select – this way we restrict the user so that he/she can only select from a dropdown. With that goal in mind, here’s a screenshot of the Excel sheet that we have so far – it’s just 2 columns – Car Company and Country:
Where to put the list of permissible values
Since we want to have a list of values that will show up in the dropdown, we will need that list to be stored somewhere. We can start by finding an area of the spreadsheet which is not being used (and will not be used in the future), and we can put our list there.
We arbitrarily chose to put the list in the G column as you can see here:
Now, in order to actually create the dropdown list in the Country column, we first highlight the cells for which we want the dropdown – we just selected the entire “Country” column as you can see here:
Then, we go to Data->Validation, and a popup box that says “Data Validation” will appear. It looks like this:
Note that we use red numbers to label the different pieces of the Data Validation window. Here is what each of those items mean – explained by the numerical label given to it:
Understanding the data validation popup
-
1. This is the type of value allowed. You can say whether to only allow whole numbers, decimals, lists, date, time,
text lengths and custom. For most of these you can then make a further specification – e.g. a range of numbers or dates. -
2. For whole numbers, dates, decimals, times and text lengths, you can then further specify
a permissible ranges (less than, between, etc.). - 3. In this tab you can specify a message to appear every time a cell in the range is selected.
-
4. In this tab you can specify what should happen if a user enters an unpermitted value. Choose
from (in decreasing order of severity): a stop notice (default), a warning, information
notice or nothing at all. You can also write a custom message
For our simple example, the next step is to simply select “list” under the “allow” dropdown. Then, under “Source”, we just select the values that we created in column G – so the cells from G5 to G9. Note that you can actually use the cursor to select the values you want to show up in the dropdown – in order to select multiple values simply hold down “SHIFT” and drag down until you select all the cells you want.
For our example, this is what the data validation popup would look like once we input the appropriate settings:
What the dropdown looks like
And now we are done! The final product looks like this – note the actual dropdown in this image:
Also note that as soon as we click in any cell in the B column (we are in cell B2), we are presented with a list of countries to choose from.