4 Ways To Create Drop-Down List In Excel

Summary:

This blog summarizes the complete detail on how to create drop list in Excel using different tricks. Also learn how to add/remove item from the drop down list or steps to delete the drop-down list.

What Are Data Validation Lists?

Creating the drop-down list is the best way to keep the data entries in an uniform and errors free structure. You can also restrict the values entry which you don’t want to put on your worksheet.

Due to this reason only it is also known as data validation lists. So only the valid data will get into the cell after passing the conditions that you have applied to it.

Adding drop-down list in excel sheet is very helpful when several users are entering data on same Excel sheet. Whereas on the hand, you wants to assign limited options to the list of values or items that are already approved by you.

Apart from this, one can also uses the drop-down list in excel to create financial models and interactive reports where result automatically gets changed when value of cell is changed.

One of the most useful features of data validation is the ability to create a dropdown list that let users select a value from a predefined list.

To recover Excel file data, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

  1. Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
  2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
  3. Preview the repaired files and click Save File to save the files at desired location.

How To Create Drop List In Excel?

Method 1# Create Simple Drop-Down List

1. In your new Excel worksheet, you have to type the data you want to show in the drop-down list.

It’s better to have list of item in the Excel table but if you don’t have such then convert the list to table by making selection of the cell ranges and pressing the Ctrl+T tab.

Create Drop List In Excel

Notes:

  • Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. You don’t need to do anything else.
  • Now is a good time to Sort data in a range or table in your drop-down list.

2. Choose the worksheet cell where you need to insert the drop-down list.

3. Now hit the Data> Data Validation tab from the Excel Ribbon.

excel-data-validation-ribbon-686-183

Note:

If you are unable to hit the Data Validation tab then this means that your worksheet is either protected or shared.

  • So immediately, unlock the particular section of your protected workbook or just disable the sharing of worksheet.
  • After then only try the remaining steps.
  • From the Settings tab you have to hit the Allow box, and then choose the List option.
  • Go to the Source box and choose the list range.

In the shown example we are putting our data on the sheet named as cities in the A2:A9.

Create a drop-down list 1

Remember: we have left the header row just because we need not to include this in the selection area:

6. If you don’t want that empty cell create any kind of issue then make a check across the Ignore blank Also tick the In-cell dropdown option.

simple-drop-down-list

7. Hit the Input Message

    • To make a message box to pop-up when you make a tap over the cell, in that case you need to choose the “Show input message when cell is selected”
    • Now assign the title and message which you want to show in the dialog box i.e. up to 255 characters.

In case, you don’t need to show any message then leave the option “show input message when cell is selected” unselected.

8. Go to the “Error Alert”.

  • In case to pop-up a message box when someone put anything wrong that is not present in your list, then make a check across the option “Show error alert after invalid data is entered” .

Create a drop-down list 3

  • After that pick an option from the Style box: Information, Warning and stop. After that type the title and error message. If you don’t wants to display any pop-up message then remove the text from the error message section and uncheck the “show error alert after invalid data is entered”.

Disable Error Alerts

  1. If you are confused about the options to choose from the Style box. Then read this out:

Information or warning: choosing this option will show the message but doesn’t prevent users from entering unavailable data of the drop-down list.

Stop: this option will stop users from inputting data which is not present in the drop-down list.

Note:

If you forgot to add the title or error message then by default the title will be considered as Microsoft Excel and message is taken as “The value you entered is not valid. A user has restricted values that can be entered into this cell.”

Method 2# Create Drop-Down List  That Allow Other Entries

You can easily create drop-down list in Excel which allows other entries. Here are the steps that you need to perform:

  • Enter the value which is not available in the drop-down list after this Excel will display the error alert.

In this case, for allowing other entries, perform the following steps.

  • Go to the Data tab after that from Data Tools group, hit the Data Validation icon.

excel-data-validation-ribbon-686-183

This will open the dialog box of ‘Data Validation’.

  • Now you have to move onto the Error Alert tab and untick the ‘Show error alert after invalid data is entered’ option.
  • Hit the OK button.
  • Now you can very easily enter the value which is not present in the drop-down list.

Disable Error Alerts

Method 3# Dynamic Drop-Down List

Another very interesting way is to create dynamic drop–down list in Excel so that whenever any item is add or deleted from the list it automatically gets updated.

  • From your first sheet choose the cell B1.

Dynamic Drop-down List 1

  • Go to the Data tab and then from the data Tools group hit the Data Validation icon.

Dynamic Drop-down List 2

This will automatically open the dialog box of  ‘Data Validation’

  • From the Allow box, hit the List option.

Dynamic Drop-down List 3

  • Tap on the Source box and after that enter this formula: =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)

Dynamic Drop-down List 4

Explanation: here the Excel OFFSET function uses the 5 arguments. Reference: Sheet2!$A$1, rows to offset: 0, columns to offset: 0, height: COUNTA(Sheet2!$A:$A) and width: 1. COUNTA(Sheet2!$A:$A) counts the number of values in column A on Sheet2 that are not empty. When you add an item to the list on Sheet2, COUNTA(Sheet2!$A:$A) increases.

Dynamic Drop-down List 5

This will give you the result that range returned from the OFFSET function gets expanded and it will update the drop-down list also.

  • Hit the OK button.
  • Go to the second sheet, and then add any new item at the list end.

Result:

Method 4#Dependent Drop-down Lists

Let’s know how you can create dependent drop-down list in Excel. Here is an example to show you how to create dependent drop-down lists in Excel.

From one drop down list when the user chooses the pizza.

Dependent Drop-down Lists 1

Then in the second drop-down list variants of pizza starts appearing. Perform the following steps to create dependent drop-down lists in Excel.

Dependent Drop-down Lists 2

  • Go to the second sheet, for creating up the following named ranges.
Name Range Address
Food A1:A3
Pizza B1:B4
Pancakes C1:C2
Chinese D1:D3
  • From your first sheet just choose the cell B1.

Dependent Drop-down Lists 3

  • Go to the Data tab and after that from the Data Tools group, hit the Data Validation icon.

Dependent Drop-down Lists 4

This will open the dialog box of ‘Data Validation’.

  • Now on the setting tab go to the Allow box make tap over the List option
  • In the Source box enter the text type =Food.

Dependent Drop-down Lists 5

  • Hit the OK button.

Result:

Dependent Drop-down Lists 6

  • Now choose the cell E1.
  • From the opened data validation dialog box go to the settings tab and then from the Allow box make tap over the List option.
  • In the Source box enter the text type =INDIRECT($B$1).

Dependent Drop-down Lists 7

  • Press OK button.

Result:

Dependent Drop-down Lists 8

Explanation: here INDIRECT function will give you the reference mainly specified by the text string. Suppose if user selects the Chinese food category then from your first drop-down list. =INDIRECT($B$1). This will give you the Chinese item references. Due to this in the second drop-down lists having the Chinese items.

How To Add/Remove Items From Drop-Down List?

Even without using the ‘Data Validation’ option and making changes in the range reference, you can easily add/delete items from the drop-down list in Excel.

Let’s know how…!

  • For adding an item in the drop-down list, choose the item first which you wants to add.

Add or Remove Items 1

  • Make a right click, and hit the Insert option.

Add or Remove Items 2

  • Choose the “Shift cells down” and hit the OK button.

Add or Remove Items 3

Result:

Add or Remove Items 4

Note:

You will notice that automatically the range reference is get shifted from Sheet2!$A$1:$A$3 to Sheet2!$A$1:$A$4. For checking this out, open the dialog box of ‘Data Validation’.

  • Enter any new item that you want to add.

Add or Remove Items 5

Here is the Result:

Add or Remove Items 6

  • For removing any item from the drop-down list, got o step 2 and hit the Delete option. Choose the “Shift cells up” option and hit the OK tab.

How To Remove A Drop-Down List In Excel?

Here are the steps that you need to perform for removing drop-down list in excel.

  • Choose the cell having the drop-down list.
  • Go to the Data tab and then from the Data Tools group choose the Data Validation icon.

excel-data-validation-ribbon-686-183

After this you will see that a dialog box of ‘Data Validation’ starts appearing on your screen.

  • Hit the Clear All button.

Clear all to remove data validation completely

Note:

For removing entire drop-down lists : on the opened data validation dialog box check the “Apply these changes to all other cells with the same settings” option right before selecting the Clear All button.

  • Hit the OK button.

How To Recover Corrupt/Lost/Deleted Excel File Data?

Have you ever thought what initiative you will take if all of sudden your Excel file data lost or gets deleted somehow. Well to deal with such situation i have a simple solution i.e to use Excel Recovery Tool, This software helps you to recover deleted, corrupted, lost Excel file even without any backup. Not only this it is also capable to repair corrupt, damaged Excel (XLS and XLSX) files and fix Excel errors as well.

With this, you can recover chart, chart sheet, table, cell comment, image, cell comment, formula, and sort & filter and all data components from corrupt Excel file.

* Free version of the product only previews recoverable data.
  • Quickly recover and repair both .xls and .xlsx file.
  • Easily restore multiple corrupted Excel file simultaneously.
  • Restore everything including worksheets, cell comments, worksheet properties, charts and other data.
  • Too easy to use so that even a novice user can also use it.
  • Supports both Windows as well as Mac operating system.

Conclusion:

Adopting the dependent drop down list idea is really very cool and quiet easy to make.

You can make numbers of dependent drop-down lists in Excel worksheet. But as the number of such type of drop-down list increases this will complex the whole data. so it’s better to use it in less complex state.

I hope the above post will help you to create drop-down list in Excel.

So without wasting any more time create Dropdown lists in Excel worksheet that let users to make selection for the value from the pre-defined list.



Margret Arthur is an entrepreneur & content marketing expert. She writes tech blogs and expertise on MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in easy to understand language is very impressive. When not writing, she loves unplanned travels.