By Jake Sheridan
Have you ever wanted to add a dropdown list in Google Sheets, but also wanted to add colors to better organize your information? In this article, I’ll show you how to create a dropdown list with colors in Google Sheets to make your spreadsheet even more practical and enjoyable to use.
Why Use a Dropdown List with Colors?
Google Sheets is primarily known for its calculation capabilities, but it is also frequently used for other purposes, such as tracking status or inventory. In many cases, simply entering a number in a cell will not be enough to convey the necessary information. However, if users are allowed to manually enter data in cells, typos will often occur, and formulas that depend on these inputs may no longer work.
The best way to prevent this type of input error is to create a dropdown list that allows users to enter only the exact values that the spreadsheet can handle. By using this method, it is also possible to set up conditional formatting to color different values in the dropdown list.
How to Create a Dropdown List in Google Sheets with Colors
Dropdown lists are essentially menus located in spreadsheet cells. Users can click on the cell to open the menu and easily select an option. Here’s how to add them to your spreadsheet:
Step 1
Select a cell or range where you want to add dropdown lists, then open the “Data” menu and select “Data validation.”
Step 2
In the “Data validation” popup window, choose the data validation criteria you want to use. The two options that will appear as dropdown lists are “List from a range” and “List of items.”
Step 3
To use the “List of items” option, type the desired list items in the text field next to it, separating them with commas.
Step 4
To use the “List from a range” option, type the range reference in the text field next to the criteria dropdown menu. You can also use the “Select data range” button in the text field to choose the range. Remember to include dollar signs ($) to make the reference absolute.
Step 5
Make sure the “Show dropdown list in cell” box is checked. Choose whether you want to allow users to enter values not in the list (it is recommended to reject invalid entries unless you have a reason not to). Then, click “Save.”
Step 6
The selected cells will now display a downward-pointing triangle to show that they contain dropdown lists. Click on the cell to open the list and select an option.
How to Add Colors to a Dropdown List
Once you have set up your dropdown lists, you can add color codes to the list items by following these steps:
Step 1
Select the same range where you added the dropdown lists, then open the “Format” menu and choose “Conditional formatting.”
Step 2
In the conditional formatting sidebar, under “Apply to range if…”, select the option “Text is exactly” and type the exact text used for the first item in the dropdown list.
Step 3
Choose the formatting options from the “Formatting style” section, then click “Done” to apply the rule.
Step 4
To add more rules for each item in the list, click “Add another rule” and repeat steps 2-3 as needed.
Step 5
Now, when you select items from the dropdown list, the cells will automatically format based on the conditional formatting rules.
By following these simple steps, you will be able to create dropdown lists with colors in Google Sheets, allowing you to better organize your data and make it easier to visualize. Feel free to try this feature and incorporate it into your spreadsheets to make your work more efficient and enjoyable.
For more tips and tricks on Google Sheets, visit our website. You can also check out our example spreadsheet to further familiarize yourself with this functionality.
We hope this article has been helpful, and that you can use this knowledge to optimize your use of Google Sheets. Don’t hesitate to share your experiences and comments in the section below. Happy creating colorful dropdown lists!
