If you’re wondering how to create a drop down list in Google Sheets with color, this is the guide for you! There are various times when you might want to create a drop down and then color code it based on the selection.
For example, maybe after finding duplicates in two columns, you'll want to categorize the data with a drop down list. Another use case for this might be if you are analyzing historical stock data and want to use repeated notes from a list. You could even add drop downs to your spreadsheet after syncing two sheets into one master sheet.
So, how do you create a color coded drop down list in Google Sheets? To create a drop down list in Google Sheets, you’re first going to use data validation to create the drop down. Next, you’ll want to set up conditional formatting to make the drop down selection color coded. The combination of using data validation and conditional formatting will give you the desired result!
Here are some step by step instructions for creating a dropdown list with color in Google Sheets.
Creating a Drop Down List With Data Validation
We want to make a drop down list from the data contained in our list.
First, we select the cell where we want the drop down to appear.
1. In the Ribbon, select Data > Data Tools > Data Validation.
2. Select List.
3. Select the range of cells required as the Source.
4. Click OK to see the drop down list in the workbook.
Sample Google Sheet File
In this tutorial we will be using the below Google Sheet Document to work. Feel free to create a copy for yourself.
Add color to Dropdown List
Creating a Dropdown Lists
To those who are new to the concept of Data Validation and Dropdown Lists you can get visit below links.
- How to Create a Dropdown Lists
- How to Edit a Dropdown List
Or, a quick recap below would also be useful to juggle the memory.
Creating Dropdown Sheet using List of Items Option
Creating Dropdown Cells using Cell Range Option
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 theDatamenu then chooseData 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. SelectList of items orList 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 theShow 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.
OnInvalid 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-markingShow 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.
Video liên quan