See all How-To Articles This tutorial will demonstrate how to add a drop-down list with color formatting in Excel and Google Sheets.
To create a drop-down list where the background color depends on the text selected, start with Data Validation in Excel, then use Conditional Formatting to amend the background color.
Create a Drop-Down List With Data Validation
To make a drop-down list from the data contained in a range of cells, start by selecting the cell where you want the drop down to appear.
- In the Ribbon, select Data > Data Tools > Data Validation.
- Select List.
- Select the range of cells with items as the Source.
- Click OK to see the drop-down list in the workbook.
Amend the Background Color With Conditional Formatting
- Select the cell that contains the drop-down list, and then in the Ribbon, select Home > Styles > Conditional Formatting.
- Select New Rule, and then select Format only cells that contain.
- Click on the Format… button to set the format. Select the Fill tab and select the color [in this case, red].
- Click OK to return to the New Rule screen and then OK to show the Rules Manager.
- Select the drop-down list and change the value to Red to see the result of the Conditional Formatting.
- Create rules for Yellow, Green, and Blue by following the same steps. The Conditional Formatting Rules Manager should end up having four rules all applying to the cell containing the drop-down list [e.g., D2].
Add a Drop-Down List With Color Formatting in Google Sheets
The process to add a drop-down list with color formatting is much the same in Google Sheets as it is in Excel.
- In the Menu, select Data > Data validation.
- Make sure the Cell range is where the drop-down list should go, and then select the Criteria range [e.g., B3:B6].
- Tick Show dropdown list in cell if it not already ticked and then click Save.
- With the cell that contains the drop-down list selected, select Format > Conditional formatting from the Menu.
- Click Add another rule to add the rest of the Conditional Format rules to the drop-down list.
Select a different color from the drop-down list to see the result.
Can you color code the drop down in Google Sheets?
You can select the cell or column you want to assign colors to and go to Format > Conditional Formatting. It will open up a side panel with options. Under “Format cells if…” select from a number of options [we recommend “text is exactly”]. Type in the text that you'd like to auto-change color for.
How do I change the color of a cell in a drop down list?
1] Click on cell with drop down list..
select the cells which shall be be affected by the drop down list ..
home -> conditional formating -> new rule ..
format only cells that contain ..
in format only cells with ... ... .
format -> fill -> select color -> ok..
How do I format a drop down list in Google Sheets?
Create a drop-down list.
Open a spreadsheet in Google Sheets..
Select the cell or cells where you want to create a drop-down list..
Click Data. ... .
Next to "Criteria," choose an option: ... .
The cells will have a Down arrow. ... .
If you enter data in a cell that doesn't match an item on the list, you'll see a warning. ... .
Click Save..