How to create a drop-down list in Excel and Google Sheets

If you are creating an interactive spreadsheet, you may need a drop-down list so that users can choose between the options. For that, you can follow this tutorial for create a drop-down list in Microsoft Excel or Google Sheets. You can create a single or nested drop-down menu using this guide.

Like various programming languages, it is also possible to include the if-else statement in an Excel spreadsheet. Suppose you create a spreadsheet for people who need to select different options based on different criteria. At such a time, it is wise to use a drop-down list so that you can offer more choice to people.

How to create drop down list in Excel

To create a drop-down list in Excel, follow these steps:

1. Select a cell in which you want to display the drop-down menu.
2. Go to Data> Data validation.
3. Select the list from the Allow menu.
4. Note your options in the Source area.
5. Save your change.

To get started, you need to select a cell in your spreadsheet where you want to display the drop-down list. After that, go from Home tab to the The data tongue. in the Data tools section click on the Data validation and select the same option again.

Now expand the To allow drop-down list and select listing. Then you should write down all the options one after the other. If you want to display AA, BB and CC as examples, you should write them like this-

AA,BB,CC

No matter how many options you want to provide, you must separate them with a comma. After that click on the OK button. Now you should find a drop-down list like this-

You can also add an error message. It appears when users try to enter a different value from the given options. To do this, go to Error alert tab and write down your message. Follow this tutorial to add error messages in Excel.

How to create a nested drop-down list in Excel

If you want to get data from certain drop-down menus or existing cells and display the options accordingly in a different cell, here is what you can do.

You must open the same Data validation window and select listing in the To allow menu. This time, you must enter a range in the Source box like that-

=$A$1:$A$5

According to this range, the new drop-down list will display the same options as those written in cells A1 to A5.

How to Create a Drop-down List in Google Sheets

To create a drop-down list in Google Sheets, follow these steps:

1. Select a cell and navigate to Data> Data validation.
2. Select the list of items.
3. Write down your items or options.
4. Save your change.

First, select a cell in a spreadsheet and click on the The data in the top navigation bar. After that select the Data validation in the list.

Now expand the criteria drop down menu and select List of objects. Next, you should note any options or items in the empty box.

Finally, click on the save to display the drop-down list in a cell.

Like Excel, Google Sheets displays a warning or error message for entering invalid data. By default, it displays a warning message and allows users to write custom text. If you want to prevent users from entering invalid data, you must choose Reject entry option in the Data validation window.

How to Create a Nested Drop-Down List in Google Sheets

It’s almost the same as Excel, but the name of the option is different. You must select the List from a range option of criteria list and enter a range according to your needs. You can enter a field like this-

=$A$1:$A$5

It will display all the texts in cells A1 to A5 in this drop-down list.

That’s it! I hope this will help you.