Skip to content Skip to main navigation Skip to footer

How to create a drop down list in Excel

03

Data entry can be more quicker and efficient if you use a drop-down list in Microsoft Excel. You can help people pick data from a list that you create. The chances of wrong entry can be reduced in this way. Today, in this content I will share 4 possible ways to create a drop-down list in Excel.

The drop-down list is the most popular data validation tool in Microsoft Excel.

How to create a drop-down list in Excel

You can create a drop-down list in four ways. They are

  • Using manual data entry process
  • By selecting a range of cells

Read More: How to add the second line in Excel cell

Create a drop-down list by manual data entry process

When the volume of your data is less you can use this method. It is the most common method to create a  drop-down list in excel.

  • Select a cell where you want to create the drop-down list.
  • Go to the Data tab, and click Data Validation from the Data Tools group.

How to create a drop-down list in Excel_01.1

  • Click Data Validation under the Data Validation menu. Under the Settings tab of Data Validation choose validation criteria from the list. Choose List.

How to create a drop-down list in Excel_02.1

  • Type the list data separated with a comma in the source text box.

How to create a drop-down list in Excel_03

  • You can set input messages and error alerts as well.

How to create a drop-down list in Excel_04

How to create a drop-down list in Excel_05.1

  • Press OK to complete your data validation. Your drop-down list will look as below.

How to create a drop-down list in Excel_06

Create a drop-down list by selecting a range of cells

  • Go to another worksheet and prepare the list that you want to show in your dropdown list.

How to create a drop-down list in Excel_07

  • Select a cell where you want to create the drop-down list.
  • Go to the Data tab, and click Data Validation from the Data Tools group.
  • Click Data Validation under the Data Validation menu. Under the Settings tab of Data Validation choose validation criteria from the list. Choose List.
  • Click the Source box and select the above list range (B1:B4) and hit Enter.

How to create a drop-down list in Excel_08

  • You can set input messages and error alerts as well.
  • press OK to complete your data validation. Your drop-down list will look as below.

How to create a drop-down list in Excel_09

  • If you want to add more data to your drop-down list, it is easy and simple. Add more cells in the selection and type what you add to the list.

How to create a drop-down list in Excel_14

How to protect your drop-down list

The range of data in your drop-down list is not protected anyone can delete or modify your data. So, you have to lock these cells to protect them. To protect them you can follow the below instructions.

  • Go to the column where you have typed your drop-down list, then select them all. Go to the Home tab> Alignment group and click the right bottom corner.

How to create a drop-down list in Excel_10.1

  • Go to the Protection tab, you can change the cell properties locked and hidden by clicking those checkboxes. Click OK to complete the task.
  • Go to the Review tab, and click Protect Sheet or Protect Workbook. Make any adjustments if required. Click OK to complete the task.

How to create a drop-down list in Excel_12

  • Your drop-down data list is now protected.

How to create a drop-down list in Excel_13

Dynamic drop-down list

Dynamic drop-down list updates drop-down list when you add any item to your list. That is why it is called a dynamic drop-down list. To create your drop-down list dynamic follow the below instructions.

  • Select a cell where you want to create the drop-down list.
  • Go to the Data tab, and click Data Validation from the Data Tools group.

How to create a drop-down list in Excel_01.1

  • Click Data Validation under the Data Validation menu. Under the Settings tab of Data Validation choose validation criteria from the list. Choose List.

How to create a drop-down list in Excel_02.1

  • Clcik the Source box and enter the formula: =OFFSET(Sheet2!$B$1,0,0,COUNTA(Sheet2!$B:$B),1) Click OK to complete.

How to create a drop-down list in Excel_16

Understanding the formula

Here we have used the OFFSET function, which takes 5 arguments.

  • Reference: Sheet2!$B$1 
  • Row to offset: 0
  • Column to offset: 0
  • Height: COUNTA(Sheet2!$B:$B)
  • Width: 1

COUNTA(Sheet2!$B:$B) counts the number of values in column B on Sheet2 that are not empty. When you add an item to the list on Sheet2, COUNTA(Sheet2!$B:$B) increases. Thus, the range returned by the OFFSET function expands and the drop-down list will be updated as well. See the result below.

How to create a drop-down list in Excel_17

How to Clear data validation from a cell

If you want to clear data validation from a cell follow the below instructions.

  • Select the cell that you want to clear data validation.
  • Go to the Data tab> Data Validation> Select Data Validation.
  • Select the Settings tab of Data Validation.
  • Click Clear All, and OK to complete the action.

Benefits of the drop-down list

  • Why should you use a drop-down list?
  • It displays a range of choices to the user.
  • It helps clean up a busy layout of your datasheet.
  • The user cannot change the data.
  • No chance of spelling mistakes by the user.

Dear learners, I hope this is helpful to you. You have learned how to create a drop-down list in Excel already. Stay tuned with us. Thank You.

0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *