Dropdowns are useful features that simplify data entry and enforce data validations in spreadsheet software. Creating a dropdown list is easy. And you might have done that already in Excel or Google Sheets. But did you know, you can also assign a background color to your dropdown list items? A colored dropdown makes your data easier to read and makes user selections easier to identify. In this post, we will show how to create a dropdown list with color in Microsoft Excel and Google Sheets.
If you use Microsoft Excel as your preferred analytic tool, you might already be familiar with the concept called conditional formatting. Conditional formatting, as the name suggests, is used to format the content of a cell based on a certain condition. For example, you may use conditional formatting to highlight duplicate cell values. In a similar manner, you may use conditional formatting to assign colors to items in a dropdown list.
Similarly, if you use Google Sheets on a frequent basis, you may already know how to apply data validation rules to cell values. These rules can be used to create a dropdown list, as well as to assign colors to the dropdown list items.
We have previously covered how to create a dropdown list in Microsoft Excel and Google Sheets. In the following sections, we will see how to color code dropdown lists in Microsoft Excel and Google Sheets.
How to create a dropdown list with color in Excel
To create a color-coded dropdown list in Microsoft Excel, you first need to create a dropdown list and then you can move ahead to add colors to the list items.
Let us say we have a sample spreadsheet as shown in the above image wherein we have a list of tasks that needs to be marked as ‘New’, ‘In Progress’, ‘Done’, or ‘Not Done’. To take user input, we will first create the dropdown list as follows:
- Select cell B2.
- Go to the Data tab.
- Select Data Validation from the Data Tools section.
- Select List from the Allow dropdown.
- Type ‘New,In Progress,Done,Not Done’ in the Source field.
- Click on the OK button.
The above steps will create a dropdown list next to the first task in the spreadsheet. Next, we will add colors to the dropdown list items as follows:
- Select cell B2.
- Go to the Home tab.
- Click on Conditional Formatting in the Styles section.
- Select New Rule from the dropdown that appears.
- Select Format only cells that contain under Select a Rule Type.
- Under Format only cells with, select (and type) Specific Text > containing > ‘New’, where ‘New’ refers to the item in the list.
- Click on the Format button.
- In the Format Cells window, switch to the Fill tab.
- Select the color that should be associated with the dropdown list item ‘New’. In this example, we are applying a shade of yellow to the new tasks assigned.
- Click on the Ok button.
- Click on the Ok button again in the next window. So far, we have associated color with the list item ‘New’.
- Repeat the process (steps 1 to 11) for other list items – ‘In Progress’, ‘Done’, and ‘Not Done’, while applying a different color to each of them. We have applied a shade of blue, a shade of green, and a shade of red to these items in this example.
- Go to Home > Conditional Formatting > Manage Rules to open the Conditional Formatting Rules Manager.
- Preview and verify all the rules that you’ve applied to the dropdown list items and click on OK. Now you have a color-coded dropdown list in cell B2.
- Take the cursor to the bottom-right corner of cell B2.
- As the cursor turns into a plus (+) symbol, click and drag the cursor till cell B6. This action will copy the cell content and the corresponding formatting rules of cell B2 to the cell range B3:B6 (where we need to have the dropdown list).
How to create a dropdown list with color in Google Sheets
Just like Microsoft Excel, Google Sheets allows you to create a dropdown list with color-coded values. However, creating a colorized dropdown list is much easier in Google Sheets than in Excel. This is because Google Sheets has added a new feature to assign background colors to items while creating a dropdown list (this was earlier accomplished using conditional formatting, just like in Excel).
Let us see how to create the same dropdown list (as explained in the above section) in Google Sheets.
- Place your cursor in cell B2.
- Go to Data > Data Validation. The Data validation rules pane will open on the right side of the spreadsheet.
- Click on the Add rule button.
- Select the value Dropdown under Criteria. You will see 2 options. Rename Option 1 as ‘New’ and assign yellow color to the option using the Color dropdown.
- Rename Option 2 as ‘In Progress’ and assign blue color to the option.
- Click on the Add another item button twice to add 2 more list options.
- Rename the list items as ‘Done’ and ‘Not Done’ and change their background colors to green and red respectively.
- Click on the Done button to save the rule. You now have a color-coded dropdown list in cell B2.
- Take the mouse pointer to the bottom-right corner of the cell and as turns into a plus symbol, click and drag the cursor till cell B6. This will copy the data and data validation rule of cell B2 in cells B3 through B6.
This is how you can create a dropdown list with color-coded data in Excel and Google Sheets. I hope you find this post useful.
Read: How to connect Google Sheets with Microsoft Excel.
How to create yes or no Dropdown list with color in Google Sheets?
Place the cursor on the cell where the dropdown list should appear. Select Data > Data validation. Click on the Add rule button in the right side. Select Dropdown in ‘Criteria’. Rename ‘Option 1’ as Yes. Rename ‘Option 2’ as No. Assign colors to the options to give them an eye-catchy look. Click on the Done button.
How do I change the color of a selected value in a dropdown?
In Microsoft Excel, select the cell where the dropdown is placed. Go to Home > Conditional Formatting > Manage Rules. Double-click on the desired color. Click on the Format button in the next window. Choose a different color and click on OK. In Google Sheets, select the dropdown and click on the edit (pencil) button at the bottom of the item list. Select the desired color using the color options available in the right panel and click on the Done button.
Read Next: How to add a Tooltip in Excel and Google Sheets.
