How to Add/Create Drop Down List in Google Sheets

google sheets drop down list 21

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.

See also  How to Apply Conditional Formatting Strikethrough in Google Sheets

How to Add/Create a Drop Down in Google Sheets

Step 1

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

google sheets drop down list 1

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.

google sheets drop down list 2

Step 3

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

google sheets drop down list 3

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.

google sheets drop down list 4

Step 5

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

google sheets drop down list 5

Step 6

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

google sheets drop down list 6.0

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

google sheets drop down list 6.1

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

google sheets drop down list 7

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

google sheets drop down list 8

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

google sheets drop down list 9

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

google sheets drop down list 10

See also  How to Use Conditional Formatting in Google Sheets [Complete Guide]

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.

google sheets drop down list 11

Step 2

Go to Data > data validation.

google sheets drop down list 12

Step 3

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

google sheets drop down list 13

Step 4

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

Example:

1,2,3,4,5,6

google sheets drop down list 14

Step 5

Check on show drop-down list in the cell

google sheets drop down list 15

Step 6

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

google sheets drop down list 16

Step 7

Click on Save and you’re done.

google sheets drop down list 17

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

google sheets drop down list 18

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.

google sheets drop down list 19

See also  How to Change Text Color Using Conditional Formatting in Google Sheets

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.

google sheets drop down list 20

Step 2

Go to Data > Data validation

google sheets drop down list 21

Step 3

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

google sheets drop down list 22

Step 4

Write Yes, No in CSV in the text input box

google sheets drop down list 23

Step 5

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

google sheets drop down list 24

Step 6

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

google sheets drop down list 25

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

google sheets drop down list 26

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

google sheets drop down list 27

See also  How to Add Calculated Fields in Google Sheets (Ultimate Guide)

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.

google sheets drop down list 28

Step 2

Go to Data > Data Validation

google sheets drop down list 30

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.

google sheets drop down list 29

google sheets drop down list 31

Now select a data range.
google sheets drop down list 32

Step 4

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

google sheets drop down list 33

See also  How to Apply Conditional Formatting Lowest and Highest Value in Google Sheets

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.

google sheets drop down list 34

Step 2

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

google sheets drop down list 35

Step 3

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

google sheets drop down list 36

google sheets drop down list 37

Step 4

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

google sheets drop down list 38

Step 5

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

google sheets drop down list 39

Step 6

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

google sheets drop down list 40

Step 7

Enter the color name in the below input box

google sheets drop down list 41

Step 8

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

(You can also use other formatting options)

google sheets drop down list 42

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.

google sheets drop down list 43

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

google sheets drop down list 44

google sheets drop down list 45

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

google sheets drop down list 46

See also  How to Use Macros in Google Sheets [Complete Guide]

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.

google sheets drop down list 47

Step 2

Go to Data > Data validation

google sheets drop down list 48

Step 3

In the Criteria section dropdown > select Date.

google sheets drop down list 49

Step 4

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

google sheets drop down list 50

Step 5

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

google sheets drop down list 51

See also  How to Apply a Formula to an Entire Column or Row in Google Sheets

Tutorial: How to Add Drop Down List in Google Sheets

google sheets drop down list

See also  Conditional Formatting based on Another Cell in Google Sheets

Notes to Remember when working with Drop Down List

  • To add a dropdown, the first thing is to understand why do we need it? We need for better user experience and quick manipulation of data
  • We can use various conditional formatting methods, as we saw colors above in our dropdowns to make them attractive and more engaging
  • In date dropdown, you don’t have any dropdown button in the cell, because it is more likely a date picker with the same functionality.
  • You cannot add a dropdown in the cell having any text. It will work but will show a validation error.
  • The dependent dropdown can be also be created using the same techniques
  • Dropdowns cannot be targeted by cell address.
  • Dropdowns don’t have the index
  • Dropdowns can only be formatted from the cell address where they originated.
  • We created a Yes/No dropdown, similarly, you can make it with many options. Male / Female, Day / Night, Before / After, etc. Also, you can add the number of options you want. Example: Summer/ Winter/ Autumn / Spring.

See also  How to Search in Google Sheets (Complete Guide)

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general