Excel Data Validation: How to Color Cells by Value

No—Excel Data Validation cannot directly set a cell’s fill color. To color cells based on whether their contents meet a validation rule, use Conditional Formatting: create the validation rule under Data > Data Validation, then add a matching rule under Home > Conditional Formatting.

What Data Validation Can and Cannot Do

Data Validation controls what people can enter in a cell. It can restrict entries to whole numbers in a range, dates within limits, a list of choices, text of a specified length, or values that satisfy a custom formula. It can also show an input message and an error alert when someone enters a value that violates the rule.

It does not provide an option to change the cell’s fill, font, or border when a value is valid or invalid. The error alert’s Stop, Warning, and Information styles affect the message shown during entry; they do not color the worksheet cell. This distinction matters because a validation rule governs entry, while Conditional Formatting changes how a cell looks in response to its value.

For a useful visual cue, set up both features: use Data Validation to guide or restrict new entries, and Conditional Formatting to show the status of values already in the sheet. The formatting rule should reflect the same criteria as the validation rule, or the two features can give conflicting signals.

Set Up a Dropdown and Flag Values Outside It

Suppose a worksheet tracks requests in A2:A100, and the permitted statuses—Approved, Pending, and Rejected—are listed in H2:H4 on the same worksheet. A dropdown helps users choose an allowed status, while conditional formatting can flag nonblank entries that do not match any item in the list.

Enter the allowed status names in H2:H4. Keeping the list in cells makes it easier to update later than typing each item into the validation dialog.
Select A2:A100, then choose Data > Data Validation. In the Settings tab, set Allow to List and set Source to =$H$2:$H$4. Leave In-cell dropdown selected if users should be able to choose from a dropdown, then select OK.
With A2:A100 selected, choose Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format.
With the allowed list in H2:H4 and the data cells in A2:A100, enter this formula: =AND(A2<>"",COUNTIF($H$2:$H$4,A2)=0).
Select Format, choose a fill color such as light red, and select OK. Confirm that the Applies to range covers A2:A100, then finish creating the rule.

The formula leaves empty cells uncolored and highlights a nonblank entry when COUNTIF finds no exact match in H2:H4. For example, Approved is not highlighted, while a mistyped value such as Approvd is. COUNTIF is not case-sensitive, so approved will match Approved; if capitalization must be enforced, the rule needs a case-sensitive approach.

The first cell reference in a conditional-formatting formula is A2 because that is the top-left cell of the selected range. Excel adjusts that relative reference for each row. The dollar signs lock the allowed-list range so it stays H2:H4 as the rule is evaluated down the column.

Color Valid Choices Differently

You can also color valid values by category, rather than only flagging invalid ones. For example, a request tracker might show Approved in green, Pending in yellow, and Rejected in gray. Keep the dropdown validation rule, then create one conditional-formatting rule for each status.

Select A2:A100 and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the status cells in A2:A100, a rule for the Approved status is =$A2="Approved". Choose a green fill for that rule. Make corresponding rules using =$A2="Pending" and =$A2="Rejected", assigning each a distinct fill.

In each formula, the dollar sign fixes the column at A while leaving the row relative. That lets the rule evaluate A2 for the first row, A3 for the next, and so on. Make sure each rule applies to the intended range. If the status is in a different column, change the column reference accordingly. A cell containing a value outside the dropdown list will not match any of these three rules, so you can add the invalid-entry rule as well if you want unexpected values to stand out.

Match the Formatting Rule to the Validation Rule

For reliable results, decide what the visual formatting is meant to communicate. A green fill might mean an entry is allowed, a particular workflow status, or simply that a field is complete. Those are different conditions. If green means valid, the conditional-formatting test should check the actual validation criteria. If green means Approved, it should check the status value only.

For a numeric example, imagine that B2:B50 must contain whole numbers from 1 through 100. Set Data Validation to Allow: Whole number, Data: between, Minimum: 1, and Maximum: 100. To flag nonblank numbers outside that range, select B2:B50 and create a formula-based conditional-formatting rule. With B2:B50 as the target range, use =AND(B2<>"",OR(B2<1,B2>100)) and choose a warning fill.

This formula is suited to the stated numeric example; it does not fully reproduce every possible validation test. If pasted text or other nonnumeric values are possible, design and check a rule that explicitly handles those cases instead of assuming that a simple numeric comparison catches every invalid entry. More generally, custom validation formulas and conditional-formatting formulas can behave differently if they test different conditions. Treat them as separate rules that need to be kept in sync.

Find and Correct Existing Invalid Data

Adding Data Validation to a range does not automatically repair or remove values already in those cells. It also does not guarantee that all later changes will be blocked: copying, filling, or importing data can introduce entries that bypass the normal validation alert. That is one reason a conditional-formatting check is useful even when the dropdown or restriction is in place.

Excel includes a separate way to locate existing invalid entries. Select the relevant cells and choose Data > Data Validation > Circle Invalid Data. Excel marks cells that do not meet their validation rules with circles. This is a temporary review aid, not a fill-color rule; it does not replace Conditional Formatting. After correcting the entries, use Data > Data Validation > Clear Validation Circles to remove the circles.

If an invalid value is not being flagged, check the validation rule, the conditional-formatting formula, and the Applies to range. Also inspect the value itself: a cell may contain a leading or trailing space, an unexpected character, or a number stored as text. A dropdown list can reduce typing mistakes, but it cannot guarantee that existing or pasted content exactly matches the permitted values.

Manage Rules and Avoid Common Problems

When several rules apply to the same cells, the order and settings in the Conditional Formatting Rules Manager can affect what appears. Choose Home > Conditional Formatting > Manage Rules to inspect rules, their ranges, and their order. If a rule does not work, confirm that its Applies to range is correct and that the formula is written relative to the top-left cell of that range. A formula designed for A2:A100 may give the wrong result if its range instead begins at A1 or B2.

No color appears: Check that the rule has a fill selected, that the target range includes the cell, and that the formula returns TRUE for the value you expect to format.
Every cell is colored: Check for a missing blank check, an incorrect relative reference, or a range reference that shifted unexpectedly.
Valid cells are flagged: Compare the allowed-list values with the entries. Look for spaces, different punctuation, or a formatting rule that points to the wrong list range.
Typing is blocked but pasting is not: This can happen because validation alerts are not a dependable audit of pasted or filled data. Keep a review rule or check the values after imports.
Colors conflict: Open Manage Rules, review overlapping ranges, and remove or adjust rules that duplicate or contradict one another.

For large workbooks, avoid creating a separate rule for every individual cell when one formula-based rule can cover a range. A smaller, clearly named set of rules is easier to audit and update when the permitted values change.

Excel for Mac and Excel for the Web

In Excel for Microsoft 365 on Windows, the main paths are Data > Data Validation for entry rules and Home > Conditional Formatting for visual rules. Excel for Mac has the same core features, though the ribbon layout and dialog details can differ slightly by release. Look for Data Validation on the Data tab and Conditional Formatting on the Home tab; the formula logic and relative-reference principles are the same.

Excel for the web also supports common data-validation and conditional-formatting tasks, but some dialog options and rule-management capabilities may differ from the Windows desktop app. If a workbook uses a complex custom rule or you cannot find a particular setting in the browser, open it in the desktop version of Excel to inspect or edit the rule. Avoid assuming that a visually similar menu contains every desktop option.

Frequently Asked Questions

Can Excel Data Validation change a cell’s background color?
No. Data Validation controls permitted entries and can show an input message or error alert, but it does not apply cell formatting. Use Conditional Formatting to change the fill based on the cell’s value.

Can I color invalid entries without preventing them?
Yes. Apply a Conditional Formatting rule that identifies values outside your criteria. You can use this as a warning or review aid even when you do not use Data Validation to block entries.

Can a dropdown choice automatically get its own color?
Yes. Keep the dropdown list and create a separate conditional-formatting rule for each choice you want to color. For a status in column A, a formula such as =$A2="Approved" can format matching cells in the selected range.

Why did an invalid value get into a validated cell?
Validation alerts are mainly intended to guide direct entry. Pasting, filling, or importing can introduce values that do not follow the rule. Use conditional formatting or another review process to find unexpected entries.

Does changing the dropdown list update the colors?
Only if the formatting rules use the list as their criteria, or if you also update the rules. A conditional-formatting rule that checks for the literal word Approved will not automatically change when you replace that status with a different word.

Can I color blank cells that still need an entry?
Yes. Add a conditional-formatting rule for blanks, separately from the rule that flags invalid nonblank values. This makes missing information visually distinct from an incorrect entry.

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.