Excel Data Validation Colour Change: Rules and Fixes

Excel data validation cannot directly change a cell’s colour when someone chooses a value. To make the cell change colour, create a dropdown with Data Validation, then add a Conditional Formatting rule for each value you want to highlight.

What data validation controls—and what it does not

In Microsoft Excel for Microsoft 365 on Windows, data validation controls what users can enter in a cell. It can restrict entries to a list, a whole number range, a date range, a time, a text length, or a custom formula. It can also show an input message and an error alert. It does not set cell formatting, and it does not add colours to individual options in a dropdown menu.

That distinction explains a common point of confusion: choosing a different item from a validated dropdown does not, by itself, recolour the cell. The usual solution is to make the cell’s displayed colour respond to its value with Conditional Formatting. The validation rule and the colour rule are separate, even though they work together in the same worksheet.

For example, a status column might allow only “Not started,” “In progress,” and “Complete.” Validation helps keep those entries consistent. Conditional Formatting can then give each status its own fill colour. The colour is a visual cue; it does not determine whether the entry is valid.

Change colour based on a dropdown choice

First create or select the cells that need a dropdown. On the Windows ribbon, select the range, then choose Data > Data Validation. In the Settings tab, choose List under Allow. Enter the permitted choices in Source, separated by commas, or point Source to a range containing the choices. Leave In-cell dropdown selected if users should choose from a list in the cell, then select OK.

Next, add a conditional formatting rule for each value that needs a different appearance:

Select the cells that contain the dropdowns, such as B2:B100.
Choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains if a straightforward text match is sufficient. Enter the status and choose a formatting style.
Repeat for the other statuses, choosing a distinct fill colour for each.
Test the rules by selecting each dropdown value in a few cells.

For a more controlled rule, use Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With status values in B2:B100 and the rule applied to that same range, use the formula =$B2="Complete" for the Complete colour. Set the desired fill using Format. The dollar sign fixes the column while the row number adjusts for each cell, so Excel checks each row’s own status. Repeat with the other status text and a different fill.

To colour an entire row according to a status in column B, select the rows or table area to format, such as A2:F100, and use the same formula, =$B2="Complete". The column is fixed so every cell in a row checks column B; the row remains relative so the next row checks B3, then B4, and so on. Confirm that the rule’s Applies to range matches the area you selected.

Set up and manage conditional formatting

Conditional Formatting changes how cells look when their contents meet a rule. The stored value does not change, and the formatting is not the same as manually applying a fill colour. When the condition becomes true, Excel displays the rule’s format; when it becomes false, that conditional format no longer applies. A cell can also have ordinary formatting underneath the conditional rule.

For multiple dropdown choices, create one rule per choice. Use clear, consistent labels and make sure the text in each rule matches the list exactly. If the list says “In progress” but the rule checks for “In Progress,” a case difference alone will not usually prevent a match, but extra spaces or different wording can. A formula rule is useful when the condition needs to be explicit, when you want to colour a larger area, or when a simple text rule gives an unexpected result.

To inspect or adjust rules, select a cell in the affected range and choose Home > Conditional Formatting > Manage Rules. In the rules manager, check which worksheet or selection the rules apply to, their order, and their formatting. If two rules can be true at once, order can matter; options such as Stop If True can affect whether later rules are evaluated. To remove rules without deleting cell contents, select the range and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

Highlight entries that are not on the allowed list

Sometimes the goal is not to colour approved dropdown choices, but to flag values that do not appear in the permitted list. This is useful when a workbook already contains pasted or imported data. Data Validation can prevent some invalid entries typed directly into cells, but pasted data can bypass the normal warning behaviour, so a separate visual check can help find problems.

Suppose the permitted values are in H2:H5 and the entries to check are in B2:B100. Select B2:B100 and create a formula-based conditional formatting rule. Use =AND(B2<>"",COUNTIF($H$2:$H$5,B2)=0), then choose a warning fill. This highlights nonblank values that do not match any item in the allowed range. The formula leaves empty cells uncoloured; without the first condition, blanks could be flagged as not appearing in the list.

Data Validation’s Error Alert is a different safeguard. In Data > Data Validation, use the Error Alert tab to decide whether Excel should show a message when someone enters an invalid value. A Stop alert blocks an invalid entry typed into the cell; Warning and Information alerts let the user choose whether to continue. These alerts do not assign a colour, and they are not a substitute for reviewing pasted data or values changed by other processes.

Colour changes for numbers, dates, and blanks

The same approach works when a cell’s value is numeric or a date rather than a dropdown choice. For simple thresholds, select the range and use Home > Conditional Formatting > Highlight Cells Rules, then choose an appropriate comparison such as Greater Than, Less Than, or Between. This is suitable for cases such as colouring overdue dates or highlighting quantities below a minimum.

For more specific logic, create a formula rule. With dates in C2:C100, a rule can compare each date with the current date; choose a format to distinguish dates before today. If blank cells should not be highlighted, include a nonblank test in the rule. Likewise, if a numeric threshold should apply only when a related status is “Open,” a formula rule can test both cells. Set the rule’s Applies to range carefully: a relative reference should follow the cell being evaluated, while a dollar sign should fix a row or column only when the rule needs it.

Be aware that a date in Excel is stored as a serial number and displayed according to its number format. A cell that looks like a date may actually contain text, in which case comparisons may not behave as expected. Check the underlying value and formatting if a date rule appears to miss entries. Also decide explicitly how blanks should behave: an empty cell, a formula returning an empty string, and a cell containing a space are not necessarily treated alike by every rule.

Why a colour rule may not work

If a cell does not change colour, check the rule before rebuilding the validation list. Many failures come from a rule applying to the wrong range, a formula reference that shifts incorrectly, or a value that does not match the text the rule expects.

Check Applies to. In Manage Rules, confirm that the range includes the cells users edit. A rule written for B2:B100 will not colour a value in B101.
Check the first-row reference. If the rule applies from row 2, a formula that tests row 1 may be looking at a heading instead of the current entry.
Look for extra spaces or different labels. Pasted text may contain spaces or slightly different wording. Compare it with the list source.
Check competing rules. Another rule may apply a different format or take priority. Review the order and Stop If True setting in Manage Rules.
Distinguish conditional and manual fills. A rule may be working while an ordinary fill colour makes the result confusing. Clear or adjust the underlying fill to make the conditional result easier to see.
Check how the value got there. Existing invalid data, pasted values, and formulas may need separate checks. A dropdown alone does not guarantee that every value already in a workbook is permitted.

For a quick diagnosis, select a cell that should be coloured, open Manage Rules, and inspect the formula and range. Then test with a known exact value. If the rule works for that value, investigate the actual cell contents; if it does not, investigate the rule’s references and formatting.

Choose colours that make the sheet easier to use

Use colour to support information, not to carry it alone. A status should remain understandable from its text even if a reader cannot distinguish the fill, prints in grayscale, or uses a screen reader. Prefer a small, consistent palette and ensure the text remains readable against the chosen fill. Avoid using several similar shades to communicate states that need to be distinguished quickly.

Think about the workbook’s purpose before adding rules. A status tracker may benefit from a clear colour for completion and a contrasting colour for blocked work. A data-entry sheet may need only one warning colour for values that need attention. Too many rules can make a sheet noisy, slow down troubleshooting, and make exceptions harder to notice. Use descriptive labels and, where useful, a written legend near the table.

Also consider whether the colour should respond to the value or to an action. Conditional Formatting reacts to the current cell contents; it does not record that a user changed a value, and it cannot reliably serve as an audit trail. If the workbook must track who changed a status or when, use a suitable change-tracking process rather than treating colour as a history record.

Excel versions and other approaches

The instructions above use Excel for Microsoft 365 on Windows. Excel for Mac also supports data validation and conditional formatting, though the ribbon layout and dialog details can differ. Excel for the web supports these features too, but some editing controls may be arranged differently or have fewer options than the desktop app. If a workbook is shared, check the rules in the version people will use.

For an alternative to colour, use a clear text status, an adjacent warning column, or a summary count of exceptions. These approaches can be easier to filter, print, and interpret than colour alone. A table filter can help users focus on a status, while a separate validation check can identify values outside the approved list. The right choice depends on whether the main need is quick visual scanning, reliable checking, or a record that can be reported and audited.

Frequently Asked Questions

Can data validation itself change a cell’s colour?
No. Data Validation restricts or guides entries and can show input or error messages. Use Conditional Formatting to change a cell’s displayed colour based on its value.

Can each option in an Excel dropdown have its own colour?
No. Excel does not assign separate colours to individual items inside the in-cell validation dropdown. You can colour the cell after a choice is made by creating a conditional formatting rule for each value.

Will conditional formatting colour an invalid value pasted into a cell?
It can, if the rule checks whether the value is allowed and applies to that cell. A formula rule comparing entries with the allowed list is a useful way to flag pasted values that a validation dropdown might not have prevented.

Why does my dropdown choice not trigger the colour?
Check that the rule applies to the cell, that its formula refers to the correct row, and that the selected text matches the rule’s expected value. Also check for another conditional formatting rule that may affect the displayed format.

Can I colour a whole row based on a validated cell?
Yes. Apply a formula-based conditional formatting rule to the full row range and use a fixed column reference for the status cell, such as =$B2="Complete" when the status is in column B and the first data row is row 2.

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.