How to Add a Drop-Down List in Google Sheets?

How to Add a Drop-Down List in Google Sheets
Share this

Have you heard the term “how to add a drop-down list in google sheets”? Your answer is 

No? 

or 

Probably Yes, but do not have any idea about it? 

No worries, I am here to explain. 

Let us know how I came to know about this query. 

One day, my sister was working from home and was figuring out some data. But, in a couple of minutes, she just started tearing her hair out by looking at the screen. 

When I asked her about the problem, I understood that she was dealing with inconsistent/vague/ unexpected data. And she had to sort it out manually as per her expectations. 

Are you facing the same problem? 

Then, Don’t worry your pretty little head about it. The solution is easy to start with and will save time while working. 

So many of you’ve heard about Google Sheets. Some of you might be using it. There are some tips, tricks, and benefits that you need to have handy while dealing with Google Sheets. 

Google Sheets is perhaps the best tool you can use to store information, track client data, and make account reports. 

Since it has limitless qualities and serves endless purposes, it tends to be the most flexible tool that data-centric people can use. 

In this section, we will bend our minds toward the advantages of Google Sheets.

Advantages of Google Sheets

1. The primary reason is that it is a cloud-based application that anybody can access, save, and maintain the data from anywhere.

2. And google sheet is my personal favorite as it is free. There are no hidden charges to use any of the features.

3. One of the eye-catching features is it is accessible from any device, whether you use pc, laptop, tablet, or smartphone.

4. Many participants can work on a single sheet at the same time. For instance, you can edit your data with your colleagues working simultaneously and save the process from being cumbersome due to their feature of multiple colorful pointers for every single user.

5. You can deal with big data efficiently by using different formulas such as SUM(), CONCATENATE(), TEXTJOIN(), COUNT(), etc.

add a drop-down list in Google Sheetsadd a drop-down list in Google Sheets - functions

You can see it by yourself, the variety of functions that are ready to use.

6. Creating charts and collaborating with other Google applications is one of the best features I have ever seen.

chart - add drop down list

Here, you can see the fuss-free chart Google Sheets made for my entered data. 

7. Shoo away your fear of losing the data in case of any crash with Google sheets in your pocket.

8. We do not have to keep an eye on the changes and the name of the person who made the change. Yes, you heard it right. Google Sheets saves every detail instantly for us.

9. Getting data from external sources like a website or any other application is an added advantage. It conserves the time and effort of typing the data.

10. Google sheet provides numerous plugins to handle different tasks from one platform. Suppose you have to add customer details as soon as the customer fills the google form, then you can use Google’s free form tool that sorts your work forever.

So from the above points, we got a crystal clear idea about the versatility of Google sheets. 

Now I will focus on the cause of today’s blog- How to add a drop-down list in Google Sheets in 2 minutes. 

Before moving towards the actual method of adding a drop-down list in google sheets, let us see the benefits of doing the same.

google workspace marketplace

Why Add a Drop-Down List in Google Sheets?

  • The foremost reason is it allows you to deal with consistent data. Moreover, it saves you from the possibility of getting unpredictable data from the user. 

Let us understand this cause by taking an example 

Suppose the user wants to enter the country, let’s say UAE. Then, the user may enter, The United Arab Emirates, or United Arab Emirates, or UAE, or Emirates. But in this same situation, if you provide a drop-down list, the user choices will get restricted.

drop downex
  • Secondly, it saves time for both the user and the data retriever. 
  • Another most valid reason is it allows you to give multiple choices to the user with the advantage of saving an ample amount of space. 
  • Lastly, it avoids mistyping, misspelling, or misunderstanding the input. 

So, why mess with everyone’s head? If we have the advantage of using the tool so efficiently. Without doing further ado, let us begin our step-by-step guide.

How to Add a Drop-Down List In Google Sheets in a Few Minutes?

Take a situation you want the input of the document the user will be submitting as proof of birthdate. 

Here are the steps:- 

  • Initially, you will have to select the cell that requires a drop-down list. You can do this in two ways:- 
  1. By dragging the cursor over the range of cells, you want to choose. 
  2. By manually entering the cell range in the data validation pop-up box. 
  • Now Click on Data-> Data Validation.
data validation

Once you click on Data Validation, a pop-up box will appear. 

data-validation

1. You can enter the range of cells:-

  • For 1 cell, Cell Range- Sheet1!B1 
  • For multiple cells, Cell Range- Sheet1!B1:B6

2. Next is Criteria, in which you have choices according to the type of data you want the user to enter. So let us see each type one by one in detail.

  • List from a range- You can use this option to take the values from other cells in the same or another sheet. 

See the figure below, mention countries in cell A and will use the values from A1:A5.

data validation list

See the figure below, mention countries in cell A and will use the values from A1:A5. As a result, it will appear in the drop-down list.

add a drop-down list in Google Sheetsadd a drop-down list in Google Sheets
  • List of Items –You can enter your options in the text box beside, separating all options by a comma.
data validation - list of items
  • Number – If the data type you are entering is in the form of numbers, then this option is helpful. We have alternates of selecting the range of numbers too, such as between 1 to 100, below 50, etc.
number input - data validation
  • Text – As the name suggests, the user can enter text here, and we can validate it according to the type.
text

In the above situation, since I have entered only my name and not my email id, the warning appears.

  • Date – The most fantastic feature when the data is in the form of a date.
date insert
  • Custom formula – This attribute can calculate any value by using the entered value by the user. 

For instance, calculate provident funds using salary as an input in the formula.

remove - data validation
  • Checkbox-Useful for a yes/no or true/false type of input.
checkbox - add drop down list in Google sheet

And the results are here;

text insert

3. Enable Show drop-down list in the cell, which shows the user that the options are available. 

4. You have to select the method of informing the user about their invalid data entry. If you choose, Show Warning results are:-

5. The last step is to enable Show validation help text, which informs the user when they enter any wrong input manually without seeing the drop-down list. 

For Example:- I have given the options Aadhaar Card, PAN Card, Driving License, Birth Certificate, and School Leaving Certificate. But if the user enters the initial other than these, the warning will appear. 

Also, you can customize the warning in the below-given textbox. According to the type of data you are dealing with, such as Enter digits only, if you ask a user to enter the age range.

6. You can remove the validation of a single cell or range of cells, by clicking the remove validation button.

Summing Up

Google Sheets benefits increase the number of hours any marketer or data retriever enjoys with these tools, which leads to massive productivity, helping both the individual and the association. 

So if you are the person who is holding back using the google sheet due to a lack of knowledge, I highly recommend starting using it from now onwards.

I will be back with more informative tutorials similar to How to add a drop-down list in Google Sheets.

Leave a Reply

Your email address will not be published.