Open In App

How to Create Dropdown List in Google Sheets

Last Updated : 19 Jun, 2024
Comments
Improve
Suggest changes
Like Article
Like
Report

Adding a dropdown list in Google Sheets can make your spreadsheets more dynamic and user-friendly. This feature allows you to create a list of options that users can select from, making data entry quicker and reducing errors. Whether you're managing a budget, tracking projects, or organizing data, dropdown lists can enhance your workflow.

In this article, we will make you learn how to create a dropdown in Google Sheets using Google Sheets data validation. We'll cover everything from adding a simple Google Sheets dropdown list to more advanced options like using a Google Sheets list from a range. By the end of this article, you'll be able to easily add a dropdown list in Google Sheets, customize Google Sheets dropdown options, understand the benefits of using a Google Sheets dropdown and learn how to make your spreadsheets more efficient.

How-to-Add-Dropdown-List-in-Google-Sheets-(1)

Dropdown in Google Sheets

A drop-down list is a list of items from which a user can choose an option that gets populated in a cell in a Google Sheet. Naturally, this is a great tool for the following reasons:

  • Improve efficiency: Since the user won't have to type the data manually, using a drop-down saves a ton of time. Not only this, but it also helps in minimizing errors and thus, improves efficiency.
  • Maintain Uniformity: Using a drop-down restricts the options that a user can enter in the cells. This makes sure that the data is uniform. This becomes very important to avoid errors when the data is used with formulas or conditions later on.
  • User-friendlyUser-friendly: A drop-down is user-friendly and enables people to input data with ease. All you have to do is select an option from the given list and you are good to go. This also removes any confusion regarding the data that should be entered in a cell since all the allowed options are present before the user.

How to Create Drop-down in Google Sheets with Preset Values

When you have to add a drop-down with a few options in it, then you can use this simplest and quickest method:

Step 1: Select Cells

Select the cell or cells where you want to add the drop-down.

How to Add Dropdown List in Google Sheets
select the cells

Step 2: Go to the Data Tab and Select Data Validation

Go to the Data tab and Select the Data Validation option.

How to Add Dropdown List in Google Sheets
data validation

Alternatively, you can right-click and choose the drop-down option.

How to Add Dropdown List in Google Sheets
drop-down option

Step 3: Click on Add Rule

In the side pane that opens, click on the Add Rule option.

How to Add Dropdown List in Google Sheets
add rule

Step 4: Select Drop-Down

In the Criteria drop-down, select the drop-down option if not already selected.

How to Add Dropdown List in Google Sheets
choose the drop-down option

Step 5: Add options

Now type the desired options in the boxes titled option 1 and option 2 and click Done to save the changes.

How to Add Dropdown List in Google Sheets
add items

To add more items to the list, create another option by clicking on Add another item option.

How to Add Dropdown List in Google Sheets
add more items

You can see that now the users can choose the country from this drop-down.

How to Add Dropdown List in Google Sheets
result

How to Add a Drop-down List in Google Sheets with Existing Data

Note that in the previous section, we added a few items in the drop-down. Hence, it was feasible to manually type in the options while creating the drop-down. But if you have a long list of items that you would like to add to the drop-down list, then you must follow this method:

Step 1: Select the Cells

Select the cell or cells where you would like to add the drop-down.

How to Add Dropdown List in Google Sheets
select cells

Step 2: Go to the Data Tab and Select Data Validation

Go to the Data tab and select the Data Validation option.

How to Add Dropdown List in Google Sheets
data validation

Step 3: Click on Add Rule

In the side pane that opens, click on the Add Rule option.

How to Add Dropdown List in Google Sheets
add rule

Step 4: Choose Criteria

Choose the Drop-down (from a range) option from the criteria drop-down.

How to Add Dropdown List in Google Sheets
drop-down from range

Step 5: Select Data Range

Click the window icon present in the empty box below the Criteria drop-down.

How to Add Dropdown List in Google Sheets
click on the window icon

Now, you can add the cell range (where the list of items/data is present) in the dialogue box and press ok.

How to Add Dropdown List in Google Sheets
add cell range

Alternatively, you can select the data directly from the cells and the range will be populated in the dialog box automatically:

How to Add Dropdown List in Google Sheets
directly select items from the sheet

Step 6: Save Changes

Click on the Done option in the side pane to save the changes.

How to Add Dropdown List in Google Sheets
press done

You can see that the values are added to the drop-down.

How to Add Dropdown List in Google Sheets
result

How to Edit or Remove a Drop-Down List in Google Sheets

It is an easy process to edit or remove a Drop-Down List from Google Sheets. If you want to edit a drop-down list in Google Sheets, simply follow the steps given below for your reference:

Step 1: Select the Drop-Down List

Select the drop-down list that you want to edit by simply clicking on it.

How to Add Dropdown List in Google Sheets
select the drop-down

Step 2: Click on the Pen Icon

Click on the pen icon present at the bottom of the list.

How to Add Dropdown List in Google Sheets
click on the pen icon

Step 3: Edit as Required

Now you can make changes in the Select the data range box. For the sake of this example, let's change A8 to A5. This will remove the last three values from the list.

If you wish to remove the entire drop-down, then simply click on the Remove rule option. Once you are satisfied with the changes, press Done to save the changes.

How to Add Dropdown List in Google Sheets
edit

You can see that the drop-down now contains only 5 items.

How to Add Dropdown List in Google Sheets
resultant drop-down

How to Use Your New Drop-Down List

Using a drop-down list in Google Sheets is very simple. All you have to do is select an option from the drop-down and the data will be populated in the cell.

You can see in the image below that the chosen option is added to the cell:

How to Add Dropdown List in Google Sheets
choose an option

Further, you can also use a drop-down with an entire row or column without having to create one for each cell. Simply copy the cell with the drop-down and paste it into the cells where you want to add the drop-down.

copy-and-paste
copy and paste

Additional Tips for Drop-down Lists in Google Sheets

Move Items in a Drop-Down List

If you want to shift items up and down in a drop-down list, then follow these steps:

Step 1: Click the Pen Icon

Select the drop-down by simply clicking on it and then select the pen icon present at the bottom.

How to Add Dropdown List in Google Sheets
pen icon

Step 2: Drag and Drop

Hover over the 6 dots in front of the option you wish to shift. When the cursor changes into a four-arrow icon, drag and shift the option up or down. Close the side pane once you are done.

How to Add Dropdown List in Google Sheets
drag and drop

How to Create Dropdown List in Google Sheets with Colors

Assigning colorsSheets to the items in the dropdown lists in Google Sheets andand can enhance your Google SheetsSheets. You can assign different colors to the options in the drop-down as follows:

Step 1: Click the Pen Icon

Select the drop-down by simply clicking on it and then select the pen icon present at the bottom.

How to Add Dropdown List in Google Sheets
pen icon

Step 2: Click the Colour Drop-Down

Click on the drop-down present to the right of the grey-coloured circle. This will open a panel of colors.

How to Add Dropdown List in Google Sheets
color drop-down

Step 3: Select the Colour

Select the desired color from the panel. You can preview how the drop-down will look from the bottom of the panel.

How to Add Dropdown List in Google Sheets
select the color

Repeat the same for all options and close the side pane when you are done.

How to Add Dropdown List in Google Sheets
repeat for all options

Here's the Option of the above process,

How to Add Dropdown List in Google Sheets
result

How to Change the Drop-down Display Style in Google Sheet

Change the Drop-down Display Style in Google Sheetslook to make your Sheets looks more presentable. You can change how a drop-down appears in the sheet. This is how to do it:

Step 1: Click the Pen Icon

Select the drop-down by simply clicking on it and then select the pen icon present at the bottom.

How to Add Dropdown List in Google Sheets
pen icon

Step 2: Go to Advanced Options

Click on the advanced options menu from the side pane.

How to Add Dropdown List in Google Sheets
advanced options

Step 3: Choose the Display Style

You can now choose the desired display style. For this example, let's select the Arrow style.

How to Add Dropdown List in Google Sheets
arrow display style

This is what the drop-down looks like now:

How to Add Dropdown List in Google Sheets
drop-down in arrow style

Conclusion

In this post, we saw how we can add a drop-down list in Google Sheets. Drop-downs are a great tool that one must use, especially while working with a team. It not only enhances the user experience but also makes the overall task much more efficient. Google Sheets comes packed with many such great tools and thus, is a great skill to add to your profile.

Can multiple items be selected in a drop-down list?

Open Data Validation dialog box, click on Settings tab. In the Allow drop-down list, select List In the "Source" box, enter the range of cells for your list and Click on Ok button.

How do I add a drop-down list to an entire column in Google Sheets?

For applying a drop down list to an entire column drag the bottom-right corner of the cell with your drop-down menu to the cells you want have the same list, or copy and paste the contents of the cell into other cells.

What is the shortcut for drop down list in Google Sheets?

There is no direct keyboard shortcut to create a dropdown list in Google Sheets. You need to use the "Data validation" feature to add a dropdown list.

How do I copy a drop down list in Google Sheets?

How to Copy a Drop Down List in Google Sheets:

  1. Select the cell with the dropdown list.
  2. Use the shortcut Ctrl + C (Cmd + C on Mac) to copy the cell.
  3. Select the destination cell(s) where you want to copy the dropdown list.
  4. Use the shortcut Ctrl + V (Cmd + V on Mac) to paste the dropdown list.


How to Create a Dependent Drop-Down List in Google Sheets?

Step 1: Prepare your data

Step 2: Create Named Ranges

Step 3: Create the Primary Drop Down List

Step 4: Create the Dependent Drop down List

Step 5: Test your drop down List


Similar Reads