Dropdown lists in Google Sheets enhance spreadsheet functionality by allowing users to select from predefined options, streamlining data entry and reducing errors. They are ideal for tasks like budget tracking, project management, or data organization.
1. Benefits of Dropdown Lists in Google Sheets
- Improved Efficiency: Dropdowns eliminate manual typing, saving time and minimizing errors.
- Data Uniformity: Restricting inputs ensures consistent data, crucial for formulas and analysis.
- User-Friendly: Dropdowns simplify data entry by presenting clear, selectable options.
2. Method 1: Create a Dropdown List 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.
Step 2: Go to the Data Tab and Select Data Validation
Go to the Data tab and Select the Data Validation option.
Alternatively, you can right-click and choose the drop-down option.
Step 3: Click on Add Rule
In the side pane that opens, click on the Add Rule option.
Step 4: Select Drop-Down
In the Criteria drop-down, select the drop-down option if not already selected.
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.
To add more items to the list, create another option by clicking on Add another item option.
You can see that now the users can choose the country from this drop-down.
3. Method 2: 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.
Step 2: Go to the Data Tab and Select Data Validation
Go to the Data tab and select the Data Validation option.
Step 3: Click on Add Rule
In the side pane that opens, click on the Add Rule option.
Step 4: Choose Criteria
Choose the Drop-down (from a range) option from the criteria drop-down.
Step 5: Select Data Range
Click the window icon present in the empty box below the Criteria drop-down.
Now, you can add the cell range (where the list of items/data is present) in the dialogue box and press ok.
Alternatively, you can select the data directly from the cells and the range will be populated in the dialog box automatically:
Step 6: Save Changes
Click on the Done option in the side pane to save the changes.
You can see that the values are added to the drop-down.
4. 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.
Step 2: Click on the Pen Icon
Click on the pen icon present at the bottom of the list.
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.
You can see that the drop-down now contains only 5 items.
5. 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:
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.
6. 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.
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.
7. How to Create Dropdown List in Google Sheets with Colors
Assigning colors Sheets to the items in the dropdown lists in Google Sheets and can enhance your Google Sheets. 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.
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.
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.
Repeat the same for all options and close the side pane when you are done.
Here's the Option of the above process,
8. How to Change the Drop-down Display Style in Google Sheet
Change the Drop-down Display Style in Google Sheets look 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.
Step 2: Go to Advanced Options
Click on the advanced options menu from the side pane.
Step 3: Choose the Display Style
You can now choose the desired display style. For this example, let's select the Arrow style.
This is what the drop-down looks like now:
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:
- Select the cell with the dropdown list.
- Use the shortcut Ctrl + C (Cmd + C on Mac) to copy the cell.
- Select the destination cell(s) where you want to copy the dropdown list.
- 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