How to create YES or NO drop down list with color in Excel?
Let’s say you are creating a survey table in Excel, and you want to add a YES or NO drop down list with color in the table, how could you handle it? In this article, I will introduce the solution for you.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
If you want to create a YES or NO drop down list with color in Excel, please do as follows:
1. Select the list you will fill with the YES or NO drop down list, and click Data > Data Validation > Data Validation. See screenshot:
2. In the Data Validation dialog, under the Settings tab, please select List from the Allow drop down list, type Yes,No in the Source box, and click the OK button. See screenshot:
Now you have added a YES or NO drop down list in the selected list. Please go ahead to add color for the drop down list.
3. Select the table without header row, and click Home > Conditional Formatting > New Rule. See screenshot:
4. In the New Formatting Rule dialog, please (1) click to highlight the Use a formula to determine which cells to format option in the Select a Rule Type list box; (2) type =$C2="Yes" in the Format values where this formula is true box, and (3) click the Format button. See screenshot:
Note: In the formula =$C2="Yes", $C2 is the first cell with YES or NO drop down list, and you can change it as you need.
5. In the Format Cells dialog, please enable the Fill tab, specify a background color, and click the OK button successively to close both dialogs.
Now when you click the cells in the specified list, it will pop out the YES or NO drop down list. And when you select Yes from the drop down list, it will automatically highlight the entire row with the specified color. See screenshot: