Using Google Sheets is one of the most systematic ways of ensuring that you and your friends remain on the same page while entering data.
If you are working on something with a group of people on Google Sheets, chances are that there might be some discrepancies while entering data. Others might insert incorrect or invalid data. Alternatively, they can enter some unexpected data that might mess with the inserted formula. Fortunately, you can counter this problem by creating a dropdown validation list for everyone sharing the Google Sheets.
In this article, we’ll provide a fool-proof guide on how to add drop down list in Google Sheets and other related things.
What is a DropDown List in Google Sheets?
Google Sheets allows you to add a dropdown list manually or with the help of a predefined cluster of items in a range of cells.
A drop-down list simply operates like a normal menu through which users of Google Sheets can choose a particular option from a pre-defined set.
The drop-down list in Google Sheets promotes data accuracy as it enables the users to insert only the available data into different cells.
Using a drop-down list is the most effective method to prevent ambiguity while entering data. It prevents users from entering inaccurate data, typos, or redundant values into a cell.
To help you optimize your Google Sheets experience, we’ll talk you through all the aspects of using drop-down lists, from how to use and edit drop down list in Google Sheets.
How to Add Drop Down List in Google Sheets?
Creating a drop-down list in Google Sheets is fairly simple. The useful function allows the owner of the Sheets to regulate the kind of data entered into different rows and columns.
Put simply, there is one major method through which you can easily create a drop-down list in Google Sheets. With this method, the user would be required to create a drop-down list by using different values in a range of cells.
Steps to Create a Drop Down List with a Range of Cells
As we had mentioned, Google Sheets gives us the option to curate our drop-down list using a range of cells. So in this method, a user can create their drop-down list with the help of a range of cells containing some specific items.
For this method, a user will have to work on a specific range of cells with some sort of values or records entered into it. So if you are just starting your Sheet, you’ll have to enter some items within the desired range of cells.
The next step involves selecting the appropriate range of cells over which you want to create the drop-down menu. Users also have the option to select cells from a different spreadsheet, making it possible for you to create a Google Sheets drop down list from another sheet.
Then, the users need to look at the bar on the top of the spreadsheet and click on the “Data” option. This will open a drop-down menu where the user will need to locate and click on the “Data Validation” option.
Alternatively, users can even right-click after selecting the appropriate range of cells and then clicking on the “Data Validation” option directly.
Once the earlier process is done, you will see a data validation dialog box. Here, the user will get plenty of options to create and customize their drop-down lists as per their requirements.
Here are the different settings or options that you will encounter –
1. Cell Range
This is the first and the simples setting of the dialogue box. This setting is used to determine the range of cells where the drop-down list will operate. But the setting will already be filled as we have already selected the desired range of cells.
The criteria section of the dialog box comes with a list of options. But for this article, the users would only need to be concerned with the first two options. The other options won’t be compatible with the drop-down menu feature.
Users will be required to choose the “list from a range” option. Then on the adjoining field, users will need to insert the cell range over which the drop-down list needs to be created. You will also have the option to automate the process by using the ‘select data range’ icon.
Next, you’ll be faced with a pop-up titled “select a data range”. Now again you will have to select the cell range that consists of the relevant items’ list. Now, click on OK to proceed.
3. Show Dropdown List in the Cell
For this setting, users will first need to ensure that they have enabled the “Show dropdown list in cell” checkbox.
4. On Invalid Data
This option allows users to determine what would happen in case the users enter invalid data.
The appearance setting will allow you to convey to the users about how they can operate the drop-down list, or what kind of values they can insert within the specified cell range.
The above process is basically how to create a drop down list in Google Sheets with multiple selections.
How to Use the DropDown List in Google Sheets?
Once the users have created their drop-down list, they’ll need to know how to use it as well. Once you’re done, click on the drop-down arrows next to any of the cells and then select a relevant value from the given set.
Users will be able to see the specified text if they had used the “show validation help text” setting earlier. Also, if the user enters an unacceptable value then Google Sheets will display a red warning mark in the cell. Alternatively, if you use the “reject input” option, users will get a predetermined error message in such a case.
How to Edit the DropDown Lists in Google Sheets?
Working in Google Sheets is a dynamic experience. You might want to redo or undo the work that you’ve done, or the data that you’ve entered. Similarly, you might want to add new items or modify the existing ones in your drop-down list. For this purpose, users need to know how to edit their freshly created drop-down lists in Google sheets.
For editing, users will need to go to the specified cell range in which they have created the drop-down list. Here, they can make all the necessary changes. Users have the freedom to add new items, modify existing items, and even remove some items from the selected cells.
In this article, we covered every detail about working with drop-down lists in Google Sheets – from how to create drop down lists in Google sheets with color to figuring out how to make modifications once our drop-down list has been made.