How to Create a Drop-Down List in Google Sheets (with Colors)

You can work collaboratively with Google Sheets, but there are chances of others would put data you don’t want. Here’s how to create a drop-down list in Google Sheets so others only add data you’ve predetermined.

A drop-down list is basically a multiple choice box that allows users to input data that have been predetermined and that shows on the list. The list is by default hidden but can be shown after a single click on the cell where the list is available. Users then can select one option from the drop-down list only.

You can also make it a table and customize the color and styling of the cell when users select a specific option from the list, which will also be explained in this post.

How to create a drop-down list in Google Sheets

First, simply open the Google Sheets spreadsheet you want to add a drop-down list in. Then select the cell where you want to put the list.

RELATED: How to Count Checkboxes in Google Sheets

Now, click the Data menu then choose Data validation. Data validation is a feature that allows you to validate the data inputted by others, and one of the types of data can be inputted by selecting an option from a drop-down list.

Here, you can select the cell range. If you’ve already chosen the cell, it’ll be there. The criteria are the way the data can be inputted.

In this example, I created a survey spreadsheet containing some predetermined options (on the criteria) with a drop-down menu. Select List of items or List from a range to create a drop-down list from those criteria (multiple choices).

You can add anything other than the drop-down list, like a checkbox, as well. But for a drop-down menu, you can select one of the those two options here.

The difference between a list of items and a list from a range is that the items are predetermined in this Data validation dialog box you’re seeing now. From a range, the items will be drawn from a range of cells already inputted on the spreadsheet.

Don’t forget to checkmark the Show the drop-down list in cell to make the cell shows the drop-down list after a single click on it. Not check-marking it will hide the drop-down list.

On Invalid data, you can select to show a warning or reject input (a dialog box showing an error message). A warning message box will pop out on the right side of the cell whenever users input anything other than predetermined multiple choices, like this.

If you choose to show the rejection error message, it will do exactly like this. The error message can be changed on your own by check-marking Show validation help text and inputting the message.

You can finally save it so that users can now use the drop-down list you’ve created.

Using the drop-down list in Google Sheets

Using a drop-down list is easy. If you choose show validation help text, the message will show when someone hovers the cursor on the drop-down list button.

After a single click on the cell, the drop-down menu will show.

One thing that might be a problem in Google Sheets is that anyone can enter any data other than the predetermined drop-down list options, including a random text or number. That’s why you need to show an invalid data warning so they don’t input unexpected value.

Removing the drop-down list from the cell

If you don’t want to use the drop-down list anymore on the cell, you can remove it any time you want by going to the Data > Data validation menu. Then, click Remove validation.

Showing different colors on the cell after users entering a specific value on the drop-down list

You might really want to add conditional formatting which will stylize the cell when users inputting a specific value you’ve predetermined. You can normally do this on regular cells but it can be applied with a drop-down list cell as well.

This can be a great tool if you want the cell to become red when they don’t put anything from the drop-down list options, showing green background color when they choose the right option, and so on.

To do that, simply click the cell.

Choose Format, and choose Conditional formatting.

Here, choose the format rules and how the formatting works.

In “Format cells if”, there are conditions that can be happened to the cell: it is empty, it contains a specific text, doesn’t contain a specific text, and so on.

On the green color (the default color on my spreadsheet) that shows “Default” text, click it to change the color and other stylings that are available.

If you want to create a custom color, simply select it from the options below, which contains the text color and the background color of the cell.

If you wish to add another custom rule of conditional formatting, click Add another rule at the bottom. This will apply to the same cell, so you can have multiple format rules at the same time.

In addition, if you wish to add an advanced color scale formatting, Google Sheets provide it for you here. This is useful if you want to stylize really specific value, for instance, a different percentage value.

That’s it. Click Done after the conditional formatting customization is done. Now users will see different colors and styles when entering some specific values from the drop-down list.

---

A drop-down list is a great tool for anyone who wants to predetermine the value other people would input, and amazingly, you can do that for free in Google Sheets and collaborate with others in real-time working in the same spreadsheet.