Select Distinct Logo Clear Background

Business Analytics Blog

Dropdown Lists in Excel

Drop Down Lists in Excel

Dropdown lists in Excel are a useful feature that allow users to select an item from a list of options.

They are commonly used in data entry to control what data is entered. And to ensure that users do not enter incorrect data, preventing some errors

Creating a drop down list in Excel

Step by step guide to creating  dropdown lists in Excel

We will use an example of creating a master list of stores for a fictional retailer, we want to control three key inputs

Nearest City, we want to have no misspellings, no abbreviations and consistent names

Location Type, we want to limit to High Street, Shopping Centre or Retail Park

Brand, select the brand from a list, avoiding typo’s

 

Step 1, create the control lists

 

Excel drop down list validation data

 

We list the allowed values in a worksheet as simple columns. It is also a good idea to create these as Excel tables

Why you should be using tables in Excel

To make these into tables select the range of data and press Ctrl+T

Step 2 – set up data validation

Next, go to the page where you want to add the drop down list and select the cell or range

A destination table for a drop down list in Excel

Then on the ribbon, go to Data, Data Validation and select the data validation option

Data Validation in Excel

The data validation dialogue box appears.

 

Step 3 – Define the Source Data

 

In the Allow section, scroll down to select list. Make sure to tick the option for in-cell dropdown, this is what enables the dropdown list

For the source, select the range of cells that you want to appear in the dropdown list

than click OK

Excel drop down list settings

 

 

 

Creating a dynamic drop down list in Excel

So far, we have used a fixed range to define the allowed data entry. But you can build some future expansion in by allowing the lists to grow

We will now do this for the brand

The steps are the same as the fixed lists, but with one key difference

Using a table as data validation for dropdown lists in excel

=INDIRECT(“Brands[Brand]”)

This command tells the system to look for values within the Brand column, in the Brands table

This is better than a fixed list because if we add a new Brand to the list it is automatically included in the validation range

 

How to edit a drop down list in Excel

To edit a dropdown list, simply select one of the cells with the validation, and select data validation on the data ribbon

You are then presented with the data validation dialogue box. From there you can edit the settings or other controls as you need to

Add an input message to a drop down list

To make things easier to understand for your users, you have the option to add an input message. This message can pop up to prompt the user to select a value from the drop down list

You can enter your own text in this message

To do this, select the cell with the validation, then when the data validation box appears, select the Input message tab

Adding an input message to a dropdown list in Excel

When a cell with an input message is selected this message is displayed

Input message display for a dropdown list in excel

Add an error alert to a drop down list

It is also a good idea to add an error message to guide users when data validation is enabled.

To add an error message, select the cell with the validation settings. Click data validation on the ribbon and then go to the Error Alert tab

Error alerts in Excel for dropdown lists

There are three styles, by default the stop option prevents any values being entered that are not in the list

The other two are warning and information, these both allow data to be entered that is not in the list. But it does notify the user for a more flexible solution

We will leave it as a Stop option to prevent inconsistencies

Conclusion

Using a drop down list in excel is a really simple yet powerful way to begin to control data quality, preventing errors at source whilst giving you control over what users see

 

 

Subscribe to our channel to see more tips and timesavers

Select Distinct YouTube Channel

Or find other useful SQL, Power BI or other business analytics timesavers in our Blog

Our Business Analytics Timesavers are selected from our day to day analytics consultancy work.  They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.

Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!

Business Analytics Blog

By Simon Harrison

Simon Harrison Founder of Select Distinct Limited and a business intelligence expert