In Google Sheets, a drop-down list that lets users choose a value from a predetermined list rather than manually inputting data can increase data accuracy and productivity. This guarantees consistency in the data and safeguards against misspellings and inaccurate data entry. It can also make data entering simpler and reduce the likelihood of mistakes. In this article, I’ll demonstrate 2 easy ways to insert a drop-down list in Google Sheets. Here is an overview of what we will archive:
A Sample of Practice Spreadsheet
You can download the free Google Sheet from here and practice independently.
2 Easy Ways to Insert a Drop-Down List in Google Sheets
We will use the dataset below to demonstrate 2 easy ways to insert a drop-down list in Google Sheets. The dataset contains a list of items, a list of sizes and colors for each item of a particular shop. Now, we will insert a drop-down list with this dataset.
1. Using Data Validation
With the help of the Data Validation tool in Google Sheets, you can define a set of guidelines for the types of information that may be submitted into a certain range of cells. This lowers the possibility of mistakes by ensuring that the data entered into the cells is correct and consistent. Using this tool, you can create a drop-down list with predetermined choices.
1.1 Inserting Items Manually
We can create a drop-down list by manually inserting predetermined choices. This is useful when you don’t have predetermined choices in the dataset or you want to create a drop-down list with few entities. In that case, you can manually insert choices into the drop-down list.
- Firstly, select a cell where you want to insert a drop-down list. In that case, we selected Cell B12. Next, go to the Data tab from the top menu bar and select Data validation.
- Or, you can go to the Insert tab from the top menu bar and select Dropdown.
- As a result, it will open a dialog box titled Data validation rules. Next, click on Add rule.
- Now, in the Options section, enter your predetermined choices.
- In our case, we entered all the items from that particular shop. After that, click Done.
- Thus, it will create a drop-down list of the items in the shop in Cell B12.
Read More: How to Insert Yes or No Box in Google Sheets (2 Easy Ways)
1.2 Inserting Items from Cell Range
A drop-down list may also be made by inputting predetermined choices from a cell range. This technique is useful when you need to quickly generate a drop-down list and the dataset contains predetermined choices.
- Firstly, choose a cell where you’re going to insert a drop-down list. In our case, we chose Cell C12. Next, from the top menu bar, go to the Data tab and click Data validation.
- As a result, a dialog box titled Data validation rules will show up. Next, from the Criteria region, choose Dropdown (from a range). After that, click on Select data range.
- Then, select the data range according to your demand. In our case, we wanted to list all the sizes in the drop-down list. So we selected Cell C5:C9. Next, click OK.
- Thus, it will create a drop-down list with all the sizes listed.
- How to Insert Multiple Rows in Google Sheets (4 Ways)
- Insert Rows Between Other Rows in Google Sheets (4 Easy Ways)
- How to Insert a Textbox in Google Sheets (An Easy Guide)
- Insert Page Break in Google Sheets (An Easy Guide)
- How to Have More than 26 Columns in Google Sheets
2. Copying from Another Sheet
Another way to insert a drop-down list in Google Sheets is to copy it from another sheet and paste it into the current sheet. But if you paste by pressing the Ctrl+V button, it won’t paste the drop-down list. For this, you have to use the Paste special command.
- First, go to the sheet and select the cell from which you want to copy the drop-down list. In our case, we selected Cell B12.
- Then, press the Ctrl+C button to copy the cell.
- Next, go to the current sheet and select the cell where you want to paste it.
- After that, right-click on the selected cell, go to the Paste special command and select Data validation only.
- As a result, you will obtain the same drop-down list in your current sheet.
How to Edit a Drop-Down List in Google Sheets
It may be beneficial to edit a drop-down list to update or modify the list items, including additional choices in the list, taking the list’s old or pointless items out, changing the display text for each choice, and rearranging the items in the list for better organization. In our example, we want to change the data range for the drop-down list because instead of colors there are remaining sizes. Here, I will show you how to do that.
- First, click on the drop-down list and select the Edit button icon.
- As a result, it will open the Data validation rules dialog box. Now, click on the Select data range icon to select the new data range.
- Then, choose the data range based on your requirements. In this situation, we needed the drop-down list to include all the colors. Therefore, we chose Cell D5:D9. Click OK after that.
- As a result, it will generate a drop-down list that has a list of all the colors.
How to Remove a Drop-Down List in Google Sheets
Occasionally we might want to get rid of a drop-down list. In contrast, the drop-down list won’t be removed if we pick the cell and delete it. The cell will still have the drop-down list in it. To delete the drop-down list in Google Sheets, follow the instructions listed below.
1. Removing Single Drop-Down List
You can remove a single data validation rule from your dataset using this approach. We’ll remove it from Cell D12.
- Firstly, go to the drop-down list and click on the Edit button icon.
- As a result, a dialog box titled Data validation rules will pop up on your screen, where you will see the data validation rule for the cell. Now, click on the Remove rule option to remove the data validation rule.
- Thus, it will remove the drop-down list from Cell D12.
Read More: How to Insert Button in Google Sheets (5 Quick Steps)
2. Removing All Drop-Down Lists
We have three dropdown lists in Cell B12, C12 and D12. We can remove all the drop-down lists from the current sheet by following this way.
- First, go to the Data tab from the top menu bar and click on Data validation.
- As a result, a dialog box titled Data validation rules will pop up on your screen. Now, click on Remove all to remove all the drop-down lists from the current sheet.
- Thus, it will remove all the drop-down lists from your current sheet.
This concludes our article. In this article, I’ve covered 2 easy ways to insert a drop-down list in Google Sheets. I’ve also covered how to edit and remove the drop-down list. I hope this will meet your requirements. Please feel free to any queries and suggestions in the comment section below. To explore more of these informative articles on Google Sheets, visit our website Officewheel.com.
- How to Insert Superscript in Google Sheets (2 Simple Ways)
- Insert Video in Google Sheets (2 Easy Ways)
- How to Insert a Legend in Google Sheets (With Easy Steps)
- Insert Signature in Google Sheets (3 Easy Ways)
- How to Insert a Calendar in Google Sheets (2 Effective Ways)
- Paste and Insert Rows in Google Sheets (3 Easy Ways)
- How to Insert a Header in Google Sheets (2 Simple Scenarios)