Drop down list in Excel with Conditional formatting
How to Create a Drop-Down Box With Color
By Tricia Goss Related
One way to streamline data entry is to create custom lists in Excel and use them to create drop-down lists in specified cells. When you use the program's Data Validation tool, along with its Conditional Formatting feature, you can make a drop-down list that is color-coded, enabling you to determine data at a glance. You can customize the list colors to coordinate with your logo, topic or other data. Show
1Create a list in an unused or hidden column of your spreadsheet. Enter the items into consecutive cells in a single column, one list item per cell. Leave no blank cells in-between. 2Click on the cell or select the range of cells in which you want to add the drop-down list. Select the "Data" tab, click "Data Validation" and choose "Data Validation." Choose "List" in the Allow section. Click inside the "Source" box and select the cells containing the list you created. Click "OK." 3Make sure the cell or the range of cells in which you want to add the drop-down list is still selected and click on the "Home" tab. Select "Conditional Formatting" in the Styles group. 4Point to "Highlight Cells Rules" and select "More Rules." The New Formatting Rule dialog opens. 5Choose "Format Only Cells that Contain" in the Select a Rule Type list. Choose "Specific Text" in the Format Only Cells With list. Enter the first list item in the third box and click the "Format" button. 6Select the "Fill" tab of the Format Cells box. Choose the color with which you want to fill the cell, if the first list item is selected. For instance, if your list consists of the options "High," "Average" and "Low," you might want to shade cells with the word "High" in green, "Average" in yellow and "Low" in red. 7Click "OK" to apply the formatting. Format the cells again with the second list item and continue until you have added a rule and a color for each item on the list. Save the worksheet before closing Excel. When a user chooses an option from the drop-down list, it will automatically fill the cell with the appropriate color. References
Warnings
Writer Bio Tricia Goss' credits include Fitness Plus, Good News Tucson and Layover Magazine. She is certified in Microsoft application and served as the newsletter editor for OfficeUsers.org. She has also contributed to The Dollar Stretcher, Life Tips and Childcare Magazine. Related ArticlesHow to Fill Colors in a List in ExcelHow to Identify Duplicate Items in ExcelHow to Insert a Combo Box in Excel 2010How to Make a Drop Down Box to Allow Multiple Selections in ExcelHow to Chart the Frequency of a Data Set on ExcelHow to Use Auto Complete in a Combo Box in Excel 2003How to Make a Regression Table in ExcelHow to Set a Low Inventory Alert in ExcelHow to Duplicate Words in ExcelHow to Add Outer Glow Effects in GIMPHow to Create a Drop-Down List From a Range of Cells on Another WorksheetHow to Skip Printing Rows That Have a Cell Value of Zero in MS Excel 2003Most Popular
|