How to Make a Drop Down List With Colors in Microsoft Excel 2003
Creating drop-down lists in Excel requires using a few different functions, but Excel 2003 makes it easy to create and modify your drop-down lists, then apply them to one or more cells in your workbook. To create a drop-down list of colors that users may select from, it is useful to decide the order in which you want the colors to appear before creating the list, such as most commonly selected first, alphabetical order or the order in which they appear on the color wheel.
Open the Excel 2003 workbook in which you would like to insert the drop-down list.
Click on the tab of an unused worksheet to open that sheet. If you do not have an unused worksheet, right-click on the tab of an existing worksheet and select "Insert," the "Worksheet" and then click "OK."
List your colors in a single column in the new worksheet, using the order you determined. For example, you might have "Red" in cell A1, "Orange" in cell A2, "Yellow" in cell A3 and so on if you are using color wheel order.
Highlight all of the cells in your column that have colors in them by clicking the top color and dragging your mouse down to the bottom color before releasing the mouse button.
Enter the word "Colors" (without quotes) in the name box, which is the blank field at the top of your spreadsheet to the left of the formula bar. If you can't find this box, single-click in any cell. The name of that cell, such as "B17," will appear in the name box. Once you have located the box, re-highlight your color column and type "Colors" into the box.
Click on the tab of the worksheet where you want the drop-down list to appear, then click in the cell where you want the list placed.
Click on "Data" in the toolbar, then "Validation." Click on the "Settings" tab if it is not the tab currently open.
Select "List" from the field next to "Allow."
Type "=Colors" (without quotes) in the box labeled "Source," then click "OK." This applies the drop-down list to that cell. To access the list, click in the cell, then click the down arrow that appears to the right of the cell, then click on the color of your choice.
Click in the cell with your drop-down list and select "Format," then "Conditional formatting."
Select "equal to" from the second drop-down box in the conditional formatting dialog, then type the name of your first color into the third field. Make sure the first box says "Cell value is," then click on the "Format" button. To make your font color match the word, select your color from the drop-down list under "Color." To change the background color, click the "Patterns" tab and select your color from the options provided.
Click "OK," then click "Add" and continue to set conditional formatting for each color. For example, select for a cell value "Is equal to" and "red" in the second and third boxes, select formatting of red font or background from the "Format" dialog box. When all of your colors are entered and formats selected, click "OK."
Click the arrow next to your drop-down list and select a color from the list. The cell font or background color will change to the selected color.
Tips & Warnings
- Only one-word cell range names are allowed; the name must be typed into the source box exactly as it was entered in the name box, including capital and lower case letters.