Excel Data Validation With Color: Step-by-Step Guide

To add color to Excel data validation, create a drop-down list with Data > Data Validation, then use Home > Conditional Formatting to color cells according to their selected values. Data Validation controls what users can enter; Conditional Formatting supplies the colors. The drop-down menu itself does not show colored choices, but the cell can change color after a choice is entered.

What You’ll Need

Excel for Microsoft 365 on Windows, with a workbook you can edit.
A range for the entries you want to validate, such as D2:D100.
A short list of permitted values and a color scheme for them. This example uses Not Started, In Progress, and Complete.

You can type the permitted values directly into the validation settings or keep them in worksheet cells. This guide stores them in H2:H4 so that the same list can drive both validation and the color rules. If your real workbook has different columns or labels, substitute those references and labels consistently.

Step-by-Step

Set up the allowed values. In an unused area of the same worksheet, enter Not Started in H2, In Progress in H3, and Complete in H4. These cells form the source list. Keeping the choices in cells makes it easier to update the options later than editing a typed list inside the validation dialog. You can hide column H after setup, but do not delete the source cells.
Select the cells users will fill in. Select D2:D100, or the range that should contain the validated entries. If the column has a heading in D1, leave it outside the selection. Applying validation to a range in advance means users can fill in new rows without you repeating the setup for every cell.
Open Data Validation. On the Windows ribbon, choose Data > Data Validation > Data Validation. In the dialog’s Settings tab, choose List from the Allow menu. Make sure In-cell dropdown is selected so the arrow appears in each validated cell. Leave Ignore blank selected if an empty cell is permitted; clear it if every cell must contain a choice.
Point validation to the list. With the example values in H2:H4 on the same sheet, enter =$H$2:$H$4 in the Source box. The dollar signs keep the source range fixed. Click OK. Select a cell in D2:D100 and confirm that its drop-down arrow offers the three permitted choices.
Choose how Excel handles invalid entries. Open Data Validation again with D2:D100 selected and go to the Error Alert tab. Keep Show error alert after invalid data is entered selected and set Style to Stop if users must choose only from the list. Add a short title and message, such as Choose a listed status and Select Not Started, In Progress, or Complete. A Stop alert blocks many invalid typed entries, though pasted data can still need checking.
Create the first color rule. Keep D2:D100 selected and choose Home > Conditional Formatting > New Rule. Select Use a formula to determine which cells to format. With the validated cells in D2:D100 and the permitted labels in H2:H4, enter =D2="Complete". Click Format, choose a green fill on the Fill tab, and click OK to close the Format Cells dialog. Click OK again to create the rule. The reference D2 is relative, so Excel checks each cell in the selected range against the same label.
Add the remaining status colors. Repeat the New Rule process for the other labels. With D2:D100 as the range being formatted, use =D2="In Progress" for an amber or yellow fill and =D2="Not Started" for a light gray or blue fill. Pick text colors that remain legible against each background. Use distinct fills so the status can be understood at a glance, including by someone who does not rely on color alone.
Optionally flag values outside the list. A Stop alert helps prevent invalid typing, but pasted values may bypass validation. To highlight nonblank entries in D2:D100 that are not present in H2:H4, create one more formula-based conditional formatting rule. With D2:D100 as the range being formatted and H2:H4 as the allowed list, enter =AND(D2<>"",COUNTIF($H$2:$H$4,D2)=0). Choose a red or orange fill that is clearly different from the valid status colors. This rule leaves empty cells uncolored and flags a nonblank value with no exact match in the allowed list.
Check the range and test the result. Choose one value from the drop-down in D2, D3, and D4 and confirm that each cell receives the intended color. Type an unlisted word in another cell to check the error alert. If you added the invalid-entry rule, paste an unlisted value into a test cell to confirm that it is highlighted. Review Home > Conditional Formatting > Manage Rules and check that the rules apply to the intended range, for example =$D$2:$D$100.

To use a list stored on a different worksheet, Excel’s Data Validation Source box generally cannot use a direct reference to another sheet. Create a named range for the source list through Formulas > Define Name, then enter the name preceded by an equals sign in the Source box. For example, if the named range is StatusChoices, use =StatusChoices. Keep the named range pointed at the actual allowed values so the validation and invalid-entry color rule agree.

The menu paths above are for Excel for Microsoft 365 on Windows. Excel for Mac has the same core Data Validation and Conditional Formatting features, though dialog layouts can differ; look under the Data and Home tabs. Excel for the web supports basic list validation and conditional formatting, but some rule-editing options vary. If you cannot create or manage the formula-based rules in the browser, open the workbook in the desktop app.

Common Mistakes

Expecting colors inside the drop-down menu. Conditional Formatting colors the worksheet cell after a value is entered; it does not color each item in the validation menu. Use a separate legend if people need to see the color meanings before making a selection.
Using only a fill color without validating entries. Conditional Formatting changes appearance, not what Excel permits. Set up Data Validation separately if you want a list or an error message to guide data entry.
Applying a rule to the wrong range. A formula using D2 is designed here for a range that starts at D2. If your selected range begins at D5, update the formula reference to D5; otherwise the formatting can appear offset by rows.
Typing a list with inconsistent wording. Extra spaces, misspellings, or different labels can prevent a value from matching the expected status. Copy the labels from the source cells or use the drop-down instead of retyping them.
Assuming validation blocks every route to bad data. Pasting, importing, or filling data can introduce entries that do not follow the validation rule. Keep the invalid-value color rule or periodically check the column after bulk updates.

Tips

Use concise labels. Short choices are easier to scan in a drop-down and less likely to be mistyped. If labels need to change, update H2:H4 rather than rebuilding the validation rule.
Use color as a signal, not the only signal. Keep the status text visible and choose fills with enough contrast. Avoid relying on red and green alone; some readers have difficulty distinguishing them.
Extend the validation range deliberately. If the sheet will grow beyond row 100, apply the rules to a larger sensible range or copy a validated cell to new rows. Check the applies-to range in Manage Rules when the table expands.
Keep the source list easy to maintain. Put the choices together in one place and label that area if it is visible. If the list is on another sheet, a named range makes it reusable and avoids a direct cross-sheet source reference in the validation dialog.
Use the invalid rule only when it helps. Red highlighting is useful for auditing pasted or imported data, but it can make a busy sheet harder to read. If users only enter values from the drop-down, the three valid-status rules may be enough.

Troubleshooting

The drop-down arrow is missing: Select a cell in the intended range and open Data Validation. Confirm that Allow is set to List, In-cell dropdown is selected, and the Source points to the correct cells or named range. Also check whether the worksheet is protected or the cell is part of a merged range, which can affect editing.

The selected value does not change color: Open Conditional Formatting > Manage Rules and select the rule for the cell. Confirm that Applies to includes the cell and that the formula uses the first cell of that range as its relative reference. Check that the rule’s label matches the source list exactly. Also verify that the fill color is visible and that another conditional formatting rule is not taking precedence.

All cells show the same color or colors are shifted: Review the formula reference and the Applies to range together. For a range starting at D2, the formula should refer to D2 without dollar signs on the column or row. An absolute reference such as $D$2 makes every cell test D2 instead of testing its own value.

An invalid entry is accepted: Reopen the Error Alert tab and confirm that the alert is enabled and set to Stop. Existing invalid values are not necessarily removed when you add validation. Use Data > Data Validation > Circle Invalid Data to inspect invalid entries, or rely on the optional red conditional formatting rule to find nonblank values that do not match the source list.

A valid-looking value is flagged as invalid: Compare the cell contents with the source values for extra spaces or different spelling. The validation list and the invalid-value rule must refer to the same allowed labels. If the source list was moved, update both the validation Source and the conditional formatting formula. If the workbook uses copied data, paste values carefully and check for leading or trailing spaces.

Frequently Asked Questions

Can I color the options inside an Excel drop-down list?
Not with standard Data Validation. The drop-down list items do not have individual fill colors. You can color the worksheet cell after a choice is selected by creating Conditional Formatting rules for each value.

Does Conditional Formatting prevent someone from entering a different value?
No. It changes how cells look; it does not restrict input. Use Data Validation with a List and a Stop error alert to guide and block many invalid entries. Add a separate conditional formatting rule if you also want to spot pasted or imported values that are outside the list.

Can I use colors without a drop-down list?
Yes. Apply Conditional Formatting to any range and set rules for the values you want to highlight. A validation list is optional, but it helps standardize entries and makes the color rules more dependable.

Can I use a list from another worksheet?
Yes, but use a named range rather than typing a direct reference to another sheet in the Data Validation Source box. Define the name for the cells containing the choices, then refer to it as =StatusChoices if that is the name you created.

Will the colors remain when I sort or add rows?
Usually, but check the conditional formatting Applies to range after changing the layout. Excel tables often extend formatting to new rows, but a rule applied only to D2:D100 will not automatically cover entries beyond row 100 in every setup.

Can I remove the colors without removing the drop-down?
Yes. Select the affected cells and use Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes the formatting rules for the selection; it does not remove the Data Validation list.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.