How to Add/Create Drop Down List in Google Sheets

In this article, we will learn how to add to drop down in google sheets. If you want to let someone, choose from a given list, then you can easily add dropdowns that have multiple options to select. Dropdowns are very helpful in the user interface and they make a better user experience. Dropdowns can be of many types; we will start with the basics.

Basics of Drop Down List in Google Sheets

A basic dropdown can be a gender dropdown from which a user can select male or female. some dropdowns allow multiple options to pick such as a dropdown that has some colors and ask for the favorite color then a user can pick more than one option. It depends on the creator of that dropdown. Now you can make your own, so let us get started.

We can solve many problems with dropdown menus, they are very highly used in the modern user interface, Websites, games, mobile apps, and everywhere on the web. You will see them everywhere and enjoy them to avoid difficulties as a user. In google sheets, we have a lot of data big companies with tons of big data use google sheets as their primary record-keeping resource. They use dropdowns with several data types and make it easy for their users and themselves to manage the data easily and quickly.

Dropdowns are very essential when working on complex data. Similarly, the filters that help us manipulate large data easily and quickly. The dropdown can be used with filters and other functions as well.

In google sheets, we can have multiple dropdowns and can be treated differently according to their datatype. There is nothing wrong with using hundreds of dropdowns on a particular spreadsheet file. They are fast and do not consume memory. Dropdowns original data column can be hidden for users and accessed by the data manager at any time.

Let’s get into the dropdowns practically.

You have a dataset that keeps the city, age, and job of some people. You can make a dropdown for all three columns and allow your user to pick their city, age, and job from the given drop-down list items. Let’s do it, it’s pretty simple.

How to Add/Create a Drop Down in Google Sheets

Step 1

Firstly, we will write down the column names on the headers.

Step 2

Write the data of these columns separately in some other columns so that we can hide that data for a better user experience.

Step 3

Select the first cell below your header > Go to data > then click data validation.

Step 4

A data validation window will open, in the criteria section go to Enter a range or formula and select your data for that column. Also, make sure to check on the Show drop-down list in the cell.

Step 5

Click the “Save” button and you’re done.

Step 6

Now you can hide the columns that contain data, this is only for a better user experience

Select a column or multiple columns from their headers and right-click on header > then click hide column(s).

That’s it, now you can use the drop down arrows to select from the drop down list in your google sheets.

For example, City drop down list will look like the below picture.

When City is selected, Age and Job list will be automatically updated as below.

You can also click on the side navigation buttons to unhide the columns.

How to Add/Create a Drop Down List in Google Sheets

What is the difference between a drop-down menu and a drop-down list? In the drop-down above we select the data range from our spreadsheet, in a drop-down list we can define unique data directly in the data validation window.

Let’s practically see what it means.

Step 1

Select the cell where you want to add your drop-down list.

Step 2

Go to Data > data validation.

Step 3

In Criteria drop-down select the second option “List of Items” instead of the first option “List from a range”.

Step 4

In the next input box, enter all options in CSV, comma-separated values format.

Example:

1,2,3,4,5,6

Step 5

Check on show drop-down list in the cell

Step 6

In appearance, check on “Show validation help text” > then add a custom text to help the user avoid invalid input.

Step 7

Click on Save and you’re done.

Drop down list is created where you have selected the cell earlier.

That’s it, Now you can expand the drop down list by clicking on the cell and select any item from the drop down list.

How to Add Yes/No Drop Down in Google Sheets

Yes / No drop-downs in google sheets are very useful and provide a great user experience. Users love to pick an option instead of writing Yes or No from their keyboard. So, in this section, we will see how to add the Yes / No dropdown in google sheets.

Step 1

Select the cell or range where you want to add the Yes / No drop down list.

Step 2

Go to Data > Data validation

Step 3

Select the second option “List of Items” from the Criteria

Step 4

Write Yes, No in CSV in the text input box

Step 5

You can check on “Show validation help text”, but it’s not required.

Step 6

Click on save and here you go. Yes / No dropdown is added.

Now the Yes/No drop down list is added for each of the row items.

You can select Yes/No by clicking on the drop down list.

How to Add Drop Down Values in Google Sheets

In this section, we will see how to add dropdown values in google sheets, it is easy and simple to add dropdown values. Let’s do it practically to understand it better.

Step 1

Select your dropdown cell.

Step 2

Go to Data > Data Validation

Step 3

If you have used a cell range, then add data values into that range and include them in the range to include in the dropdown values, else if you have used a list of items, then add your new values in CVS format in the second input box under criteria section.

Now select a data range.

Step 4

Change the cell ranges, and the values will be added to the dropdown.

How to Add Drop Down in Google Sheets with Color

Colors are very commonly used to make the data more user-friendly and improve user experience. In the google sheets drop down, we use colors based on conditional formatting, and the color against the selected option will appear with the value.

Example

A drop-down of fav_Color.

In this list, we have 10 colors, and the user can pick one color which is then the user’s favorite color.

So, after picking that color the call color will turn to the same color. It increases the user-friendliness and improves user experience. Let’s see how we can do it in a few steps.

Step 1

Select the cell where you want to add a drop-down list.

Step 2

Enter the data (all colors names) in another column (because we will hide those colors)

Step 3

Create a simple dropdown for the list of colors as we have done it many times above.

Step 4

Select the cell where you have added the dropdown and go to Format > Conditional formatting.

Step 5

Select the cell in the conditional formatting window > apply to the range section

Step 6

Format Rules section, select a condition/rule from the given dropdown list (“text contains”)

Step 7

Enter the color name in the below input box

Step 8

Now select the same color as you named above from the below formatting style box.

(You can also use other formatting options)

Step 9

Repeat step 8 for all colors in your list, and you’re done. Now the selection of the user will appear with the same color as the cell.

Repeat this process for all colors from Add another rule option.

Now you can test your drop down list with colors as below picture shows Yellow color cell when selected yellow from drop down list.

How to Add Date Drop Down in Google Sheets

Most of the time we want the user to select a date, now we cannot add every date in the drop-down menu it will become messy and too annoying for the user to find the date, and also for us to inter so many dates. So, we have a solution in google sheets.

In this section, we will see how to add a date drop-down in google sheets. It is also called date picker, so don’t get confused with this term.

Let’s create it to understand it better.

Step 1

Select the cell where you want to add a date dropdown.

Step 2

Go to Data > Data validation

Step 3

In the Criteria section dropdown > select Date.

Step 4

Select “Is a valid Date” from the next dropdown within the Criteria section

Step 5

Click save and the date picker is ready. Double click on the cell to access the date picker.

Tutorial: How to Add Drop Down List in Google Sheets

Notes to Remember when working with Drop Down List

Frequently Asked Questions

Can I Change an Existing Drop Down List in Google Sheets?

Yes, you can change an existing drop-down list in Google Sheets by customizing the dropdown menu. The customizing dropdown menu feature allows you to add or remove items, reorder them, or even modify the options in the list. This functionality gives you flexibility to update and personalize your drop-down lists to suit your specific needs.

Can I Use the Same Method to Add a Date Picker in Google Sheets?

Yes, you can use the same method to add a date picker in Google Sheets. There are various add-ons available that offer this feature, such as date picker for google sheets. Simply install the add-on and follow the instructions to easily insert and use a date picker in your spreadsheets.

Conclusion

Concluding the entire dropdown topic in the google sheets, so you have got the idea of how dropdown works, and why they are useful in data manipulation. We learned several types of dropdowns and used several data types to make you fully understand google sheets drop-downs. How to add a simple dropdown? how to add a dropdown with colors? how to change the values of a dropdown? what are the benefits of using dropdowns? what is a date picker? how do we use conditional formatting in dropdowns? what are the basic requirements for adding a dropdown? how can we use a list of items in a dropdown? and many more questions are answered for example in this particular article.

I hope you enjoyed the article, if yes then kindly share it with your social networks, and don’t forget to subscribe to our Office Demy Blog for future updates.