Create Default Value for Drop-Down List in Excel

This tutorial will demonstrate how to create a default value for a drop-down list in Excel and Google Sheets.

 

 

When you create a drop-down list using Data Validation in Excel, the default value of the cell where you have placed the list is usually blank. It might be useful to pre-populate this cell with either a default value, or a message to the user such as “Select…”

Creating a Drop-Down List Using Data Validation

1. In a separate location in the worksheet from where the drop-down list will go, set up the data for the list. This can be on the same worksheet as the drop-down list, or on a different worksheet in the file.

 

 

2. Then click where you want the drop-down list to go, and in the Ribbon, select Data > Data Tools > Data Validation.

 

 

3. In the Settings tab, select List from the Allow field, and ensure that Ignore blank and In-cell dropdown are checked.

4. Click in the Source box, and type or select the range of cells that hold the data for the drop-down list.
5. Click on the Error Alert tab and make sure that the Show error alert after invalid data is entered is unchecked. This allows you to enter information into the cell that is not in the drop-down list of data.

 

 

6. Click OK to create the drop-down list.

 

 

Creating a Default Value for the Drop-Down List

Now that you’ve created the drop-down list, you can create a default value to show before the drop-down list is first used.

1. In the cell that contains the drop-down list, type the following formula.

=IF(D2="", "Select...")

 

 

Make sure that the cell you’re referring to (e.g., D2) is not going to be used for anything else and will remain blank.

2. Now switch the Error Alert back on for the Data Validation. Make sure you are in the cell where the drop down is located, and then in the Ribbon, select Data > Data Tools > Data Validation again.
3. Click on the Error Alert tab and make sure that the Show error alert after invalid data is entered is now checked.

 

 

Now when you select the drop-down list, you’ll see that the top option says “Select….” above the list of available options. If you try to type something else that is not on the list, you will get an error.

 

 

As soon as you click Cancel, the “Select…” option will reappear as the default option in the list.

 

 

However, once you select an option from the list, the formula will disappear, so this default value is only available the first time the drop-down list is used and therefore only useful in a worksheet where a drop-down list is designed to only be used once.