In this lessons you can learn how in easy way create a drop-down list in Excel.

What is a drop-down list?

A drop-down allows you to more easily enter the cell, pre-selected words or numbers. Drop-down list is also useful if you want to impose certain content entering the cell. Sometimes you want all the cells in the column were content YES or NO.

In Excel, drop-down lists can be used to provide a list of options for a specific cell or range of cells, making it easy to enter data in a consistent and error-free manner.

You will create a drop-down list, where you will be able to choose the brand of the car. Here is the table prepared for car brands:

Drop-down list that you create will look like this:

As you can see we have a choice among all brands of cars from the table. How to do it?

How to create a drop-down list?

Start from the table. First, select all brands of cars.

Choose a name for this data. Let it be ‘Cars’. Enter the name to the name field and confirm with Enter button.

Now select the cell (or cells), which has a drop-down list to appear. We go then to the data tab on the ribbon and press the button Excel Data Validation.

A dialog box appears. Select ‘List’ and type the name of our range of cells after the = sign. In our example it is =Cars.

Check the “In-cell dropdown” box if you want to display the drop-down arrow in the cell.

Drop-down list is ready.

You can now test the drop-down list by clicking on the cell with the drop-down arrow and selecting one of the available options.

Input message

An input message is a helpful feature in Excel that allows you to provide users with additional information about the data that should be entered into a particular cell. Here’s how to create an input message for a drop-down list in Excel:

1. In the “Input Message” tab of the “Data Validation” dialog box, check the “Show input message when cell is selected” box.
2. Enter a title and input message in the “Title” and “Input message” fields, respectively. These will be displayed in a small pop-up window when the user selects the cell.
3. Click “OK” to create the drop-down list with the input message.

Now, when the user selects the cell with the drop-down list, a pop-up window will appear displaying the title and input message you entered. This can provide helpful guidance to the user about what data should be entered into the cell, and can reduce the risk of errors or confusion.

Error alert

Creating an error alert for a drop-down list in Excel can help prevent users from entering invalid data into a cell. Here’s how to create an error alert for a drop-down list in Excel:

1. In the “Error Alert” tab of the “Data Validation” dialog box, check the “Show error alert after invalid data is entered” box.
2. Enter a title and error message in the “Title” and “Error message” fields, respectively. These will be displayed in the error alert dialog box if the user enters invalid data into the cell.
3. Select the type of error alert you want to display from the “Style” drop-down list. You can choose from “Stop”, “Warning”, or “Information”.
4. Click “OK” to create the drop-down list with the error alert.

Now, if the user tries to enter data into the cell that is not included in the drop-down list, an error alert dialog box will appear, displaying the title and error message you entered. The user can then correct their input or click “Cancel” to close the dialog box and return to the cell. This helps ensure that only valid data is entered into the cell, reducing the risk of errors and inconsistencies in your Excel worksheet.

VBA code

Creating a drop-down list in Excel using VBA (Visual Basic for Applications) involves using the DataValidation method of the Range object. Here’s an example of how to create a drop-down list using VBA:

Sub CreateDropDownList()

 Dim ws As Worksheet
 Dim rng As Range

 Set ws = ActiveWorkbook.Sheets("Sheet1") ' Change the sheet name as needed
 Set rng = ws.Range("A1") ' Change the cell range as needed

 ' Create a drop-down list with the items "Option 1", "Option 2", and "Option 3"
 With rng.Validation
  .Delete ' Remove any existing data validation
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Operator:=xlBetween, Formula1:="Option 1,Option 2,Option 3"
 .IgnoreBlank = True ' Allow blank cells
 .InCellDropdown = True ' Show the drop-down arrow in the cell
 .InputTitle = "Select an option" ' Set the input message title
 .InputMessage = "Please select an option from the list." ' Set the input message text
 .ErrorTitle = "Invalid input" ' Set the error message title
 .ErrorMessage = "You must select a valid option from the list." ' Set the error message text
 .ShowInput = True ' Show the input message
 .ShowError = True ' Show the error message
 End With

End Sub

This code creates a drop-down list with the items “Option 1”, “Option 2”, and “Option 3” in cell A1 on the “Sheet1” worksheet. The Validation property of the range object is used to create the data validation rule, with the Type parameter set to xlValidateList to indicate that a drop-down list should be used. The Formula1 parameter specifies the list of items to include in the drop-down list, separated by commas.

The other parameters of the Validation property are used to set the input message and error message that will be displayed if the user enters invalid data, and to show the drop-down arrow and input and error messages when the user selects the cell. These parameters can be customized as needed to meet your specific requirements.

Once you have entered the code in a VBA module, you can run the CreateDropDownList subroutine to create the drop-down list with the specified settings.

Related posts:

Getting Started with Excel How to Insert Symbols and Special Characters How to Multiply in Excel