Excel data validation cannot change a cell’s colour by itself. To colour cells according to a validated value—or flag entries that do not match the allowed choices—use Conditional Formatting alongside Data Validation: set the validation rule under Data > Data Validation, then create a matching rule under Home > Conditional Formatting.
What data validation can and cannot do
Data Validation controls what users can enter in a cell. It can restrict entries to a list, whole numbers, decimals, dates, times, text of a certain length, or values that pass a custom formula. It can also show an input message when someone selects a cell and display an error alert when an entry is rejected. None of these settings assigns a fill colour or font colour to the cell.
That distinction matters because colour is a visual response to a cell’s value, not part of the validation rule. Conditional Formatting supplies that response. The two features can work together, but they are configured separately: validation governs entry, while conditional formatting governs appearance.
For example, a dropdown in a status column might offer “Pending,” “Approved,” and “Declined.” Data Validation creates the dropdown and restricts ordinary typing to those choices. Conditional Formatting can then give each status its own fill colour. A user can also make a cell turn red when it contains a value outside the approved list.
Create the validation rule first
For a dropdown list in Excel for Microsoft 365 on Windows, select the cells to control, such as B2:B100, and choose Data > Data Validation. On the Settings tab, choose List in the Allow box. Enter the choices separated by commas in Source, or select a range containing the choices. Leave In-cell dropdown selected if users should pick from a visible list, then select OK.
A list stored in worksheet cells is easier to maintain than a comma-separated list when choices may change. For instance, put the allowed statuses in E2:E4 and use that range as the list source. In a shared workbook, keep the source list somewhere users will not accidentally overwrite it. For more complex workbooks, a named range can make the source easier to recognize and maintain.
On the Data Validation dialog’s Input Message tab, you can add guidance that appears when a user selects a validated cell. On the Error Alert tab, you can choose whether Excel displays a message after an invalid entry. The Stop style blocks a typed invalid value; Warning and Information let the user proceed after acknowledging the message. These alert styles affect entry behaviour, not cell colour.
Colour valid choices with Conditional Formatting
For a simple status list, select B2:B100, then choose Home > Conditional Formatting > Highlight Cells Rules > Equal To. Enter Approved, choose a formatting option, and select OK. Repeat for Pending and Declined, choosing a different fill for each. The text in each rule must match the dropdown value, including spelling and spaces.
This is a useful setup when the categories are fixed and there are only a few of them. It lets readers scan a table quickly without changing the stored value. Use a restrained palette and keep the status text visible: colour alone may be difficult for some people to distinguish, and a cell might be printed in black and white or viewed with different display settings.
To make a rule depend on a formula, select the target range and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With status values in B2:B100, create one rule per status. For the Approved rule, the formula assumes the first selected cell is B2 and the rule applies to B2:B100: =$B2="Approved". Choose Format, set a fill or font colour, and confirm. The dollar sign fixes the column while the row adjusts for each cell.
Rules can also format an entire row based on a validated cell. If the table spans A2:F100 and the status is in column B, select A2:F100 and use the same formula, =$B2="Approved". Because the column is fixed and the row is relative, Excel checks the status in column B for each row and applies the format across that row. This is often more helpful than colouring only the dropdown cell.
Highlight entries outside the allowed list
Sometimes the goal is not to colour the permitted choices but to find values that do not belong. This can happen when older data predates the validation rule, or when content is pasted or imported into a validated range. Data Validation is not a dependable way to audit every existing value after the fact, so conditional formatting can provide a visible check.
Suppose the allowed values are in E2:E5 and the cells to inspect are B2:B100. Select B2:B100, create a formula-based conditional-formatting rule, and use this formula, which assumes B2 is the first cell in the selected range: =AND(B2<>"",COUNTIF($E$2:$E$5,B2)=0). Set a noticeable fill, such as light red. The formula ignores blank cells and highlights a nonblank value when it appears zero times in the allowed list.
Here the reference to B2 is relative, so Excel evaluates each row’s cell. The dollar signs keep the allowed range E2:E5 fixed as the rule is applied down the column. If the allowed list grows, update the range or use a suitable named range. If blanks are not allowed, adjust the validation rule as well; the example deliberately treats an empty cell as something other than an invalid list entry.
Pasting can produce surprising results. Depending on how content is copied, pasted cells may bring their own formatting or validation settings, and data can end up outside the intended rule setup. Use Paste Special options where appropriate, inspect Data Validation settings on affected cells, and keep the conditional-formatting rule applied to the full range you need to monitor.
Choose a colour rule that fits the task
Use a built-in Highlight Cells Rule when the condition is a simple, exact text or number match. It is quick to set up and easy for another editor to understand. Use a formula rule when formatting must respond to several cells, check membership in a list, ignore blanks, or colour a whole row based on one column.
- Colour each category: Add one rule for each dropdown value. This works well for a short, stable set of statuses.
- Flag unexpected values: Compare entries with the approved list using a formula rule. This is helpful for imported or historical data.
- Colour a complete record: Apply a formula rule to the table range and anchor the status column, so the row’s formatting follows its status.
- Keep entry and display separate: Use validation for allowed content and conditional formatting for visual cues. That makes each behaviour easier to troubleshoot.
Do not rely on fill colour as the only indicator of meaning. Include a readable status label, and consider a text or symbol cue where it improves clarity. Avoid too many similar colours: if users must distinguish many categories, a filter, a clear legend, or a separate status label may work better than an elaborate colour scheme.
Manage rule order and scope
When more than one conditional-formatting rule can apply to the same cell, rule order may affect the result. Choose Home > Conditional Formatting > Manage Rules to review rules, edit them, or change their order. In the dialog, check the Show formatting rules for setting so you are viewing the current selection or the relevant worksheet. A rule that applies to the wrong range will not produce the expected result even if its formula is correct.
For formula rules, the reference must match the upper-left cell of the range selected when the rule is created. If the range begins at B2, a reference to B2 is appropriate; if it begins at B3, adjust the formula accordingly. A misplaced dollar sign can also change how references move. For instance, locking the row when you intended to lock only the column can cause every row to test the same cell.
Review the Applies to field in Manage Rules after inserting rows, extending a table, or copying data. Excel may adjust ranges automatically in many cases, but checking scope is the quickest way to spot a rule that stops before the end of the data. If several rules overlap, simplify them where possible and verify the order and any Stop If True setting.
Common problems and practical fixes
- The cell stays the same colour: Confirm that Conditional Formatting—not Data Validation—is responsible for the colour, and check that the rule’s Applies to range includes the cell.
- The dropdown value is not recognised: Check for extra spaces, inconsistent spelling, or a rule that tests a different value. Compare the exact text in the cell with the value in the rule.
- The wrong rows change colour: Check the formula’s relative and absolute references and ensure it was written for the first cell in the selected range.
- Invalid data is present despite validation: Check older entries and pasted or imported content. Use a conditional-formatting audit rule and review the validation settings on the affected cells.
- A colour disappears after editing: A direct fill colour and a conditional-formatting colour are different. Conditional Formatting can determine the displayed appearance while its condition is true; review the rule rather than relying only on the Fill Color button.
When diagnosing a rule, test one cell with a known value, then inspect the rule and its range before rebuilding it. If a colour is still unclear, temporarily use an unmistakable fill, such as bright yellow, to confirm that the condition is being evaluated. Once the logic is correct, choose a more suitable design.
Windows, Mac, and Excel for the web
The instructions above use Excel for Microsoft 365 on Windows. On Excel for Mac, Data Validation and Conditional Formatting are also available from the Data and Home tabs, although the dialog layout and some controls can differ. Look for the same feature names rather than expecting every window to look identical.
Excel for the web supports common data-validation and conditional-formatting tasks, but some advanced editing options may differ from the desktop application. If a rule is difficult to create or manage in the browser, open the workbook in the desktop version of Excel and check it there. Avoid assuming a colour effect is caused by validation just because both features are applied to the same cells.
Frequently Asked Questions
Can Excel Data Validation change a cell’s colour?
No. Data Validation restricts entries and can show messages or error alerts. Use Conditional Formatting to change fill or font colour based on the cell’s value or a formula.
How do I make a dropdown change colour when I choose an option?
Create the dropdown with Data > Data Validation, then add a Conditional Formatting rule for each option under Home > Conditional Formatting. Match each rule to the exact dropdown text and choose its format.
Can I make the whole row change colour based on a dropdown?
Yes. Select the full table range, create a formula-based conditional-formatting rule, and use a formula that checks the dropdown column with the column fixed and the row relative. For a table in A2:F100 with status in B, the example formula is =$B2="Approved".
Why does Excel allow an invalid value in a validated cell?
A Stop alert can block ordinary invalid typing, but existing values and some pasted or imported content may still need to be checked. Confirm the validation settings and use conditional formatting to flag values that do not match the permitted list.
Can I colour a cell when it is selected?
Data Validation does not provide a selection-colour setting. Conditional Formatting responds to values or formulas, not simply to the active-cell selection. Selection-based behaviour requires a different approach and is not the same as colouring a validated choice.
Bottom line
For an Excel data validation colour change, use two features: Data Validation to control what can be entered, and Conditional Formatting to control how the cell looks. A simple value rule is enough for colouring dropdown choices; a formula rule is more flexible for flagging unexpected entries or formatting whole rows. Keeping the rules separate makes the workbook easier to explain, maintain, and troubleshoot.
Related
- Excel Data Validation: Rules, Lists, and Input Tips
- Edit Data Validation Restrictions in Excel
- Excel Data Validation Restrictions Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets: How to Merge Cells With TextIn Google Sheets, select the cells and choose Format > Merge cells, then pick a merge option—but merging…
- Excel Text to Columns Shortcut Key for Windows and MacIn Excel for Microsoft 365 on Windows, select a cell in the column you want to split, then…
- How to Change the Default Date Format in Google SheetsTo change the default date format in Google Sheets, open the spreadsheet and choose File > Settings, then…
- Google Sheets Filter by Color: Steps and LimitationsYes. In Google Sheets in a web browser, create a filter, open the filter menu on the column…
- Google Sheets Data Validation Dropdown: Setup and TipsTo create a Google Sheets data validation dropdown, select the cells, choose Data > Data validation > Add…
- Conditional Formatting Duplicate Values in ExcelIn Excel for Microsoft 365 on Windows, select the cells to check, then choose Home > Conditional Formatting…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.