How to Add Drop-down List in Google Sheets

By

Creating a drop-down list in Google Sheets might seem very complicated at the first look, but when you understand the process of adding drop-down list, you will know that it’s a very simple process.

google-sheets-logo

Before I share with you the process of adding drop-down list in Google Sheets, let me give you an overview.

What is a Drop-down List in Google Sheets?

If you already know what is a Drop-down List in Google Sheets, you may skip this part.

In Google Sheets, a Drop-down list is one of the data-validation function. The user can select an option from the list of items available in the drop-down list. For example, if you have a list of 10 CEOs, i.e. Bill, Mark, Jeff, Jack, Steve, Tim, Larry, Satya, Sundar and Michael, you can create a drop-down list to select any one CEO against the CEO Name field. In the image below, you can see that I created a list of CEO Name in the first two columns and then, created a drop-down list based on those 10 CEO Names in the fourth column.

You can create Drop-Down List in Google Sheets using two methods:

  1. Create a Drop-Down List based on the data already available in the sheet using Cell Range option.
  2. Create a Drop-Down List by adding the list of items manually.

Benefits of Drop-down List in Google Sheets

The drop-down list can applied in Google Sheets in variety of ways depending on your requirement. Yet, we rarely see people using drop-down list in Google Sheets. You can use it in To-do Lists, Forms, Data-Entry Work, Calculation-Sheets and more.

The primary benefits of Google Sheets can be listed as under:

  1. User-friendly: The user has all the possible inputs pre-filled in the Drop-down List. So, he/she has to select only the correct input from the available inputs. This feature makes Drop-down lists very user-friendly.
  2. No typing: The user has to select the data from the available inputs in the drop-down list using your mouse. So, there is no typing involved.
  3. No spelling mistakes: Since you don’t have to type anything while using drop-down listed, you will not be making any spelling mistakes.

How to create a Drop-Down List based on Cell Range

In this method, you can create drop-down list from the data already available in any of your Google Sheets. The process is as follows:

  1. Open the Google Sheets file in which you want to create the Drop-down list.
  2. Select the cell on which you want the drop-down list to appear. You can select any cell as you wish.
  3. Identify the data which you want in the list. The data should be either in the same sheet or any other Google Sheets file.
  4. Now, in the sheet where you want the drop-down list, click on “Data” from the Menu Options.
  5. From the available options in the Data menu, click on “Data Validation”. A dialog box will open.
  6. In the Cell Range option, you will see the Cell Location where you will create the Drop-Down List. You should check if the given location is correct. However, if you want to change the Cell Location, you can change it by editing it as per your requirement.
  7. In the Criteria option, you will have to select “List from a Range” and enter the data range which you want as your Drop-Down List. You can click on the Cell Symbol in the Criteria field to select the range manually.
  8. Tick the Check-box against text “Show dropdown list in cell”.
  9. You have filled up all the required information. Now, click on “Save” and you are done. The drop-down list is created in the Cell Range you provided and you can see the drop-down list by clicking on the Drop-down symbol visible in the cell.

How to create a Drop-Down List by adding items manually

This method is the opposite of what the first method was. In this method, you will add the list of items manually by typing instead of sourcing the data from any sheet.

  1. Open the Google Sheets in which you want to create the Drop-down list.
  2. Select the cell on which you want the drop-down list to appear. You can select any cell as you wish.
  3. Click on “Data” from the Menu Options.
  4. From the available options in the Data menu, click on “Data Validation”. A dialog box will open.
  5. In the Cell Range option, you will see the Cell Location which you selected to create the Drop-Down List. However, if you want to change the Cell Location, you can change it by editing it as per your requirement.
  6. In the Criteria option, you have to select “List of Items”.
  7. On selecting “List of Items”, a blank box will appear in which you have to enter the name of the items (it can be number also) separated by comma.
  8. Tick the Check-box against text “Show dropdown list in cell”.
  9. You have filled up all the required information. Now, click on “Save” and you are done. The drop-down list is created in the Cell Range you provided and you can see the drop-down list by clicking on the Drop-down symbol visible in the cell.

How to use the Drop-Down List in Google Sheets

I shared the process of creating a drop-down list using cell-range and by adding items manually. Now, I will show you how you can create & use the drop-down list by creating a To-Do List in Google Sheets.

I will show you how to create a To-Do List of 10 Tasks. The status of the task will be updated using Drop-Down List. The drop-down list will include three status types – DONE (for completed task), WIP (for tasks-in-progress) and PLANNED (for tasks yet to start).

So, let’s begin.

1.You should create a table in Google Sheets with the following content:

to-do-list-google-sheets

2. You have to create drop-down list of Task Status for Project A to Project J. So, you should select the cells from C4 to C13 where you will create the drop-down list. After selecting the C4:C13 cells, click on “Data” from the Menu and select “Data Validation” to open the Data Validation dialog box.

to-do-list-google-sheets

3. Now, you have to provide the required inputs in the Data Validation dialog box to create the drop-down list.

to-do-list-google-sheets
  • Cell Range: You won’t need to change the Cell Range as you already select the 10 cells (C4:C13) in the previous step.
  • Criteria: You can provide the criteria for a Drop-down List in two ways as I explained earlier – List from a Range OR List of Items. First, we will create a Drop-down list using List from Range. You have to select “List from a range” option from the drop-down and click on the Cell Symbol to enable you to select the Cell Range. Now, you have to select Cell Number A19 to A21 which contains the list of Task Status to be included in the Drop-Down List. After selecting the Cell Range, click on “OK” button.
  • Show dropdown list in cell: You have to tick the Check-box against this field. Otherwise, the drop-down list will not appear on the sheet.
to-do-list-google-sheets

What if you want to add the list of items manually instead of using the cell range. In that case, you have to select “List of Items” from the drop-down and a Text-Box will show up. You have to type DONE, WIP, PLANNED in the text-box (all items must be separated by comma).

to-do-list-google-sheets

4. Click on “Save” button. Your drop-down list is now ready for use in the sheet.

to-do-list-google-sheets

5. The user can enter the Task Status from the drop-down list after clicking on the down arrow symbol in the cell.

to-do-list-google-sheets

How to remove the Drop-Down List in Google Sheets

You can easily remove the Drop-Down List from any cell using the steps below:

to-do-list-google-sheets
  1. Select the cell in which you want to remove the drop-down list.
  2. Go to “Data” in the Menu and select “Data Validation”.
  3. Click on “Remove Validation” button.
  4. That’s it. You have successfully remove the Drop-down List from the cell.

If you still have questions about the drop-down list in Google Sheets, you can share the same in the Comments section.

Previous

How to Freeze Rows or Columns in Google Sheets

How to Become a Satellite Technician

Next

Leave a Comment

error

Enjoy this blog? Please spread the word :)