Fix Data Validation in Excel: Lists, Rules, and Errors

To fix data validation in Excel, select an affected cell, choose Data > Data Validation, check the Settings tab, and correct the allowed values, source range, or rule. Test the cell afterward with both an allowed and a disallowed entry. These steps are for Excel for Microsoft 365 on Windows; the Data Validation controls are also available in Excel for Mac, though ribbon layout can differ.

What You’ll Need

The workbook with the cells that have the problem, preferably opened in desktop Excel.
The range of cells that should use the rule, plus a clear idea of which entries should be accepted.
If the rule is a dropdown list, the list of permitted items or the worksheet range where those items are stored.

Save a copy before changing a rule used by a large or shared workbook. If you are unsure what the intended rule is, check with the workbook owner before replacing it.

Step-by-Step

Identify the affected cells. Click a cell where the dropdown is missing, an entry is rejected, or an invalid value was accepted. Note its address, then check nearby cells that should behave the same way. Validation is attached to individual cells, so one cell may have a different rule from the rest of its column. If only some rows are affected, do not select the entire column until you understand the difference; the range may contain headings, formulas, or exceptions.
Open the validation settings. With a problem cell selected, go to Data > Data Validation. In the dialog, inspect the Settings tab first. The Allow field identifies the kind of rule, such as Whole number, Decimal, List, Date, Time, Text length, or Custom. If you selected multiple cells, Excel may not show a single usable rule when the cells have different settings. Select and inspect a representative cell individually before making changes to the whole range.
Repair the rule type and conditions. Choose the correct option in Allow, then review the conditions below it. For a numeric or date rule, check the comparison type and both boundary values. A rule set to “between” needs a minimum and maximum; a rule set to “greater than” needs the correct cutoff. Also review Ignore blank: when selected, Excel allows an empty cell, which may be intended or may explain why a required value is not being enforced. Click OK to apply the change, or continue through the tabs before confirming.
Fix a dropdown list source. For a List rule, make sure In-cell dropdown is selected if users should see an arrow in the cell. Inspect the Source box. It should refer to the intended items, not a blank range, a deleted sheet, or the wrong column. A source can be a cell range, a named range, or a short list typed directly into the box. For example, a source range might be =$H$2:$H$6; the dollar signs keep the reference fixed when the rule is applied elsewhere. Check that the source cells contain the expected choices and do not include unwanted blanks. For a short typed list, Excel uses the list separator set for the computer, so a source that works on one computer may need a different separator under another regional setting. Using a worksheet range is usually easier to maintain.
Check a custom formula carefully. If Allow is set to Custom, the formula must return TRUE for entries that should be accepted. Make sure its cell reference corresponds to the first cell in the selected range. For example, with the intended values in B2:B50 and B2 as the active first cell, this rule permits whole-number-like values from 1 through 100, including decimals unless you add a whole-number condition: =AND(B2>=1,B2<=100). In B2, an entry of 25 makes the rule TRUE and is accepted; 120 makes it FALSE and is rejected. If you apply the same rule starting at B3 but leave the reference as B2, Excel checks the wrong row. Keep references relative where they need to move by row, and use dollar signs only for references that should stay fixed.
Review the messages and error behavior. Open Input Message to show an instruction when a user selects the cell, such as “Choose a department from the list.” On Error Alert, confirm that Show error alert after invalid data is entered is checked if you want Excel to warn or block invalid typed entries. Choose Stop to prevent the entry, Warning to let the user continue after confirmation, or Information to display a message while allowing the value. Check the title and message for clarity, then click OK.
Apply the corrected rule to the right cells. If the same rule should cover a continuous range, select the cells and open Data Validation. Set the rule, confirm it, and verify a few cells at the top, middle, and bottom. If one correctly configured cell already has the intended validation, you can copy it, select the destination cells, then use Home > Paste > Paste Special > Validation to copy only its validation settings. Avoid copying the entire cell when it contains a value or formatting you do not want to duplicate. For cells that need different rules, repair each group separately.
Find existing invalid entries and test the result. Excel does not automatically remove or correct values that were already in cells when you added or changed a rule. To identify them, choose Data > Data Validation and use Circle Invalid Data from the Data Validation menu. Review each circled cell and correct it or confirm that it is an intentional exception. Then test a permitted value and a value that should fail. For a dropdown, test the arrow as well as typing; a missing arrow and a rejected typed value can have different causes. Use Clear Validation Circles from the same menu when you finish reviewing.

In Excel for Mac, select the cells and use the Data tab’s Data Validation control; dialog labels are generally similar, but their placement may differ. Excel for the web has validation controls too, but if a setting or menu command is unavailable, open the workbook in the desktop app to make and verify the full change.

Common Mistakes

Editing the wrong range: Fixing one cell does not necessarily repair the rest of a column. Conversely, applying a new rule to an entire column can overwrite intentional differences or affect a heading. Select only the cells that should share the rule.

Assuming a rule fixes old values: Validation generally checks new entries; it does not clean up existing data. Use Circle Invalid Data and review the flagged cells separately.

Trusting every pasted value: Copying and pasting, filling cells, importing data, or using some automated processes can put values into a cell without the usual validation prompt, or replace its validation. Check the rule after large pastes and inspect the resulting data rather than treating validation as a substitute for review.

Breaking a list reference: A dropdown may appear empty if its source points to blank cells or a range that was moved or deleted. Check the source itself and the cells it refers to; changing the list items alone may not repair a broken reference.

Using the wrong formula reference: In a Custom rule, a relative reference is evaluated in relation to the first cell in the selected range. A formula that refers to the wrong row can seem inconsistent even when the formula syntax is valid.

Tips

Keep dropdown choices together in a clearly labeled range and avoid leaving gaps in the middle of the list. If other people maintain the workbook, put the source list on a dedicated worksheet and document what it controls. A named range can make a source easier to recognize, but confirm that the name exists and refers to the current list before using it.

Write error messages that tell people what to do, not just that something went wrong. “Enter a date from 1 Jan through 31 Dec” is more useful than “Invalid entry.” Use an input message for guidance before entry and an error alert for a value that breaks the rule.

After changes, test realistic edge cases: the smallest and largest permitted values, a blank if blanks matter, a value just outside the limit, and a choice near the end of a dropdown. If the workbook is shared, ask another user to try the entry in the version and app they normally use. This can reveal a broken source range or a paste workflow that bypasses the prompt.

For rules that rely on a separate list, keep the source cells intact when sorting or reorganizing the workbook. If you move a list, revisit the validation Source field afterward. If the workbook uses protected sheets, check whether users are allowed to select and edit the cells; protection can make a working rule appear unresponsive.

Troubleshooting

The dropdown arrow is missing. Select the cell and open Data Validation. Confirm Allow is set to List and In-cell dropdown is selected. Check that the Source points to populated cells. If the cell is protected or the sheet is protected, test on an editable cell or review the sheet’s protection settings with the workbook owner.

Excel accepts an invalid value. Open the Error Alert tab and make sure the alert is enabled. A Warning or Information alert allows users to proceed, so change the style to Stop if entries must be blocked. Remember that pasted, imported, or filled data may bypass the normal typed-entry check; inspect the range and use Circle Invalid Data to find values that do not meet the rule.

Excel rejects a value that should be allowed. Recheck the boundary values, comparison type, and whether the entry is stored as text rather than as a number or date. For a list, compare the entered value with the source item, including spaces and spelling. If a custom formula is involved, confirm the first-cell reference and test the formula’s logic with a known acceptable value.

The Data Validation command is unavailable or changes do not stick. The worksheet may be protected, the workbook may be in a restricted state, or the selected cells may include a situation Excel cannot edit as a group. Try one affected cell at a time, check whether the sheet is protected, and save a working copy before changing workbook structure. If you are in Excel for the web and cannot access the needed option, open the file in desktop Excel.

Only some cells have the repaired rule. Compare the settings of a working cell and a failing cell individually. If needed, copy only the validation from the working cell with Paste Special, then recheck that the source references still point to the intended list or row. Retest after pasting, because relative references may adjust when the rule is placed elsewhere.

Frequently Asked Questions

Why does my Excel dropdown list show no items?
Usually its Source refers to blank cells, an incorrect range, or a broken named range. Open Data Validation for that cell, inspect Source, and verify that the referenced cells contain the choices.

Can I add validation to cells that already contain data?
Yes. Applying a rule does not usually erase existing contents, but it also does not automatically make those contents valid. Use Circle Invalid Data to find entries that need checking.

Why can someone enter a value that is not in the list?
The Error Alert may be disabled or set to Warning or Information, or the value may have been pasted or imported. Enable a Stop alert when typed entries must be blocked, then check existing values separately.

How do I remove data validation without deleting cell contents?
Select the affected cells, choose Data > Data Validation, and click Clear All in the dialog, then confirm. This removes the validation settings; it does not clear the values already in the cells. Check the selected range before using it.

Can validation rules be copied to another range?
Yes. Copy a cell with the intended rule, select the destination range, and choose Home > Paste > Paste Special > Validation. Check the destination afterward, especially if the rule uses a relative formula or a source reference.

Does data validation stop every incorrect value?
No. It helps guide or restrict normal cell entry, but pasting and other data-transfer methods may bypass its alerts or replace the rule. Combine validation with review, and check imported or pasted data for errors.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.