Edit Data Validation in Excel: Lists, Rules, and Fixes

How-to basics · Updated November 18, 2026

To edit data validation in Excel for Microsoft 365 on Windows, select a cell that uses the rule, then choose Data > Data Validation > Data Validation. Change the settings in the dialog and select OK; for a drop-down list, update its Source on the Settings tab. You can also change the input message or error alert without changing the rule itself.

What data validation controls

Data validation sets limits on what can be entered into selected cells. It can require a value from a list, restrict numbers or dates to a range, limit text length, or check an entry with a custom formula. A rule can also show a message when someone selects the cell and an error alert when an entry does not meet the rule.

For example, a department column might offer a list of approved names, while a quantity column allows only whole numbers from 1 to 500. Changing the rule changes what Excel permits during ordinary typing or selection. It does not automatically inspect and repair every existing value in the cells, and pasted data can sometimes get around the rule.

Validation belongs to the cells, not just to the visible drop-down arrow. A cell may have a valid rule even if its arrow is not currently displayed; the list arrow typically appears when the cell is selected. Conversely, a cell that looks like it belongs to a validated column may not have a rule if its validation was never applied or was removed.

Edit a rule in the Data Validation dialog

  1. Select a cell with the validation you want to change. If you are unsure which cells have rules, select a likely cell and open the dialog to inspect its settings.
  2. Choose Data > Data Validation > Data Validation. The dialog has Settings, Input Message, and Error Alert tabs.
  3. On Settings, change the Allow type or its conditions. For a list rule, replace or revise the Source. Check or clear In-cell dropdown as needed.
  4. On Input Message, edit the optional prompt shown when the cell is selected. On Error Alert, edit the warning shown when someone enters an invalid value, or change whether Excel blocks the entry.
  5. Select OK, then try a permitted value and an invalid value to confirm the result.

For a whole-number rule, for instance, you can change the minimum from 1 to 0 while keeping the maximum unchanged. For a date rule, you can adjust the start or end date. The available boxes depend on the Allow choice: selecting Decimal, Date, Time, Text length, or Custom displays different options.

If the selected cell has no validation, the dialog will not show a rule to edit. If the workbook has different rules in different cells, editing one cell does not automatically update the others. Select the intended target cells deliberately before applying a replacement rule; applying one rule to a mixed selection can replace different existing rules with a single setting.

Change a drop-down list’s choices

A list rule’s Source can be typed choices or a cell range. For example, a small list might use the source Open,In progress,Closed. For a longer or frequently updated list, a worksheet range is usually easier to maintain: select the validated cell, open Data Validation, and change Source to the new list range. Keep the entries together and avoid including blank cells if you do not want an empty-looking choice.

When the choices are stored on another worksheet, Excel may reject a direct cross-sheet range typed into the Source box. A common approach is to define a name for the source range, then use that name as the list source. To define one, select the cells containing the choices, choose Formulas > Define Name, enter a clear name such as Departments, and confirm. In the validation Source box, refer to it as =Departments. Names cannot contain spaces; underscores are a readable substitute.

If the list grows over time, plan for that growth instead of repeatedly editing a fixed range. You can use a named range that refers to a suitably sized area, or maintain the list in an Excel table and use a defined name that refers to the table’s relevant column. The table’s structured reference is not always accepted directly in the Data Validation Source box, so test the named-range approach in the workbook and verify that newly added items appear in the drop-down.

For a short fixed list, typing comma-separated choices is convenient. However, the list is then stored in the validation rule, so changing a choice means editing the rule itself. A worksheet-based source is generally easier to audit, reuse, and update, especially when several columns or sheets need the same approved choices.

Change rule types, prompts, and error alerts

Use the Settings tab when the permitted entries need to change. The Allow menu includes options such as Whole number, Decimal, List, Date, Time, Text length, and Custom. The Data condition controls whether a value must equal, fall between, be greater than, or meet another condition. For a custom rule, Excel evaluates a formula that returns TRUE for an allowed entry and FALSE for a disallowed one.

For example, to prevent duplicate IDs in A2:A100, select A2:A100 before opening Data Validation, choose Custom, and use the following formula. The formula assumes IDs are entered in A2:A100 and the active cell when the rule is created is A2:

=COUNTIF($A$2:$A$100,A2)=1

The first ID entered in a cell is accepted when it appears just once in the range; if the same ID is entered in another cell, the count becomes 2 and the rule rejects it. Ensure the relative reference A2 matches the top-left cell of the selected range. The dollar signs keep the checked range fixed as the rule applies down the column. If blank cells should remain acceptable, leave Ignore blank selected and verify the behavior with the workbook’s existing entries.

On Input Message, you can provide brief instructions such as “Choose a department from the list.” On Error Alert, Stop blocks an invalid typed entry, while Warning and Information allow the user to proceed after a message. Clear the Show error alert after invalid data is entered option if you want no blocking alert. These controls influence normal data entry, but they are not a substitute for reviewing imported or pasted data.

Apply an edited rule to other cells

To update a group of cells with the same rule, select the target range before opening the Data Validation dialog and configure the rule once. This is useful when creating a new consistent rule across a column. Be cautious if some selected cells have different rules: the new settings can replace those differences.

If you have edited one cell and want to copy only its validation elsewhere, copy the cell, select the destination cells, then choose Home > Paste > Paste Special > Validation. This transfers the validation rule without copying the source cell’s displayed value or formatting. On Windows, Ctrl+C copies; Ctrl+V opens the standard paste action, but use the Paste Special menu to choose Validation specifically. On Mac, use Command+C to copy and the Paste Special command from the Home tab or context menu to select Validation.

Copying rules can also copy references that behave differently in the destination, particularly for custom formulas with relative cell references. After using Paste Special, test a cell near the beginning and another farther down the range. If the rule relies on a particular column or row, check whether its references should be relative or fixed with dollar signs.

Remove validation or inspect existing entries

To remove a rule, select the relevant cells, choose Data > Data Validation > Data Validation, and select Clear All in the dialog, then select OK. Clear All removes the validation settings for the selection, including its input message and error alert; it does not delete the cell’s contents. Check the selection before confirming so you do not remove rules from unrelated cells.

To locate invalid values already present, select the cells and open Data Validation. Excel’s Circle Invalid Data command, available from the Data Validation menu, can mark entries that fail the current rules. This is useful after importing data or changing a rule. It does not correct the entries; review each marked value, then remove the circles with Clear Validation Circles when the review is complete.

There is an important distinction between a rule and the data already in the cells. Editing a rule usually governs future entry; it should not be treated as an automatic cleanup of old values. For example, narrowing an allowed date range does not guarantee that every earlier date outside the new range has been replaced. Use the invalid-data check and review the affected records separately.

Common problems and practical checks

  • The drop-down arrow is missing: Select the cell and reopen Data Validation. Confirm that Allow is set to List and In-cell dropdown is checked. Also check that the selected cell really has the rule.
  • A new list item does not appear: Inspect the Source range or its defined name. The added item may sit outside the referenced cells, or the name may point to a different range.
  • Invalid values still get in: Check whether the error alert is enabled and set to Stop. Pasting, filling, or importing data can bypass validation, so check for invalid entries after bulk changes.
  • A rule seems to vanish after copying: Normal paste can overwrite destination content, formatting, and validation. Use Paste Special > Validation when you want to copy only the rule.
  • A custom rule rejects a value that should work: Check the formula’s reference to the active top-left cell, the intended fixed ranges, and whether existing values affect the result.

Also consider workbook protection. A protected worksheet may prevent changes to cells or validation settings, depending on how protection was configured. If the dialog is unavailable or changes cannot be saved, check Review > Unprotect Sheet and ask the workbook owner for the password or appropriate access. Do not remove protection unless you are authorized to do so.

Excel for the web and Mac differences

In Excel for the web, select the cell and use Data > Data Validation. Depending on the current interface, settings may appear in a side pane rather than the Windows dialog. The same basic task applies: choose the rule type, revise its conditions or list source, and save the change. Some advanced options and workflows can differ from desktop Excel, so open the workbook in the desktop app if a setting you need is unavailable.

On Excel for Mac, the Data Validation command is also on the Data tab, although dialog layout and some menu wording can vary by release. Windows shortcuts do not always match: Ctrl+C and Ctrl+V are commonly Command+C and Command+V on Mac. If your workbook is shared, make sure you are editing the intended file and that your changes have synced before closing it.

Frequently Asked Questions

Can I edit data validation for multiple cells at once?
Select the cells first, then open Data > Data Validation and set the rule. This is safest when the cells should all share identical settings. If they currently use different rules, a single edit may replace those variations.

How do I change the items in an Excel drop-down list?
Select a cell with the list, open Data > Data Validation, and change Source on the Settings tab. Edit the typed choices or update the referenced range or defined name, then select OK and check the drop-down.

Why can someone paste a value that violates validation?
Data validation is designed to guide entry, but some paste and import operations can bypass its checks. Keep an error alert enabled for normal entry and inspect the range for invalid data after bulk updates.

How do I remove a drop-down list without deleting the cell’s value?
Select the cell or range, open Data Validation, choose Clear All, and confirm. This removes the rule and its messages while leaving the existing cell contents in place.

Can I edit a list stored on another sheet?
Yes. A reliable method is to define a name for the source cells using Formulas > Define Name, then enter that name with an equals sign in the validation Source box. Confirm the range behind the name includes every desired choice.

Make the rule easy to maintain

For a durable workbook, keep approved choices in a clearly labeled source area, use descriptive defined names, and provide concise input instructions where they will prevent mistakes. After editing, test both allowed and disallowed entries, confirm the rule covers the intended cells, and check existing data if the permitted range changed. These small checks help ensure the validation is useful rather than merely present.

Related

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.