How to modify or remove data validation in Excel

Data validation in Excel restricts what someone can type into a cell—for example, only whole numbers between 1 and 100, or dates after today. If you set up validation and now need to change the rule, remove it entirely, or explore a different restriction, you do that through the same Data Validation dialog where you created it the first time.

The process is straightforward: select the cell or range, open Data Validation, and either edit the existing rule or clear it. You can change what type of data is allowed, adjust the limits, modify the error message that appears when someone enters invalid data, or delete the validation completely.

Key Takeaways

  • Select the cell or range with validation, then go to the Data tab and choose Data Validation to open the dialog where you can edit or remove the rule.
  • You can change the validation type (whole number, decimal, date, text length, list), adjust the minimum and maximum values, or swap out the error message without starting over.
  • To remove validation entirely, select the cells and click Clear All in the Data Validation dialog.
  • If you copy cells with validation to a new location, the validation rule copies with them unless you use Paste Special and uncheck the Validation option.

Selecting the cells you want to change

Start by clicking on a single cell that has validation, or drag to select a range of cells. If the cells are not next to each other, hold Ctrl (or Cmd on Mac) and click each one individually. You can also click the column or row header to select an entire column or row, though this is less common for validation changes.

If you are not sure which cells have validation, you can use Find & Replace to locate them. Press Ctrl+H, click Options, then click Find All. This does not find validation directly, but you can visually scan the sheet. A faster method is to select all cells (Ctrl+A), then open Data Validation—if multiple different rules exist, the dialog will show blank fields, telling you the selection is mixed.

Opening the Data Validation dialog and editing the rule

With your cells selected, go to the Data tab at the top of the ribbon. In the Data Tools group, click Data Validation (in some older versions of Excel, this is called Validity). A dialog box opens showing the current rule.

The dialog has three tabs: Settings, Input Message, and Error Alert. The Settings tab shows what type of validation is in place—for example, "Whole number" with a minimum of 1 and maximum of 100. To change the rule, click the dropdown next to "Allow" and pick a new type: Whole Number, Decimal, Date, Time, Text Length, List, or Custom. Then adjust the operator (between, equal to, greater than, and so on) and the values that go with it.

If you want to keep the same validation type but change only the numbers or dates, edit the Minimum and Maximum fields directly. If the validation is a list, you can edit the list of allowed values in the Source field.

Changing the error message users see

When someone enters data that breaks the validation rule, Excel shows an error message. By default it says something generic like "The value you entered is not valid." You can customize this message to be more helpful.

Click the Error Alert tab in the Data Validation dialog. Make sure "Show error alert when invalid data is entered" is checked. In the Title field, type a short heading (for example, "Invalid Entry"). In the Error message field, type what you want the user to see (for example, "Please enter a number between 1 and 100"). You can also choose the Style—Stop prevents the invalid entry, Warning allows it but asks for confirmation, and Information just notifies the user.

If you want to add a hint before someone enters data, use the Input Message tab instead. Type a Title and Message, and that text will appear as a small popup when the cell is selected.

Removing validation from cells

To delete validation entirely, select the cells that have it, open the Data Validation dialog, and click Clear All at the bottom left. The dialog closes and the validation is gone. The next time someone clicks that cell, no restriction or message will appear.

If you want to remove validation from only some cells in a range, select just those cells before opening Data Validation and clicking Clear All. Cells you did not select keep their rules.

Copying cells with validation to a new location

When you copy a cell that has validation and paste it elsewhere, the validation rule copies too. This is usually what you want—if you have a validated list in column A and copy it to column B, column B now has the same list rule.

If you want to copy the data but not the validation, use Paste Special. Copy the cells normally (Ctrl+C), then right-click where you want to paste and choose Paste Special. In the dialog, uncheck the Validation box, and click OK. The values paste but the rule does not.

Validation that references other cells or formulas

Some validation rules use formulas or cell references instead of fixed numbers. For example, a rule might say "allow only dates after the date in cell D1" or "allow only numbers less than the sum of cells E2 through E10." If you need to change what cell the rule refers to, open Data Validation, find the formula in the Minimum, Maximum, or Source field, and edit the cell reference.

If the cell you are referencing moves or is deleted, the validation rule breaks and shows an error. To fix it, open Data Validation and update the cell reference to point to the correct location.

Frequently Asked Questions

Can I change validation on multiple different cells at once if they have different rules?

No. If you select cells with different validation rules and open Data Validation, the dialog shows blank fields because there is no single rule to display. You must select cells with the same rule, or select them one at a time and change each one separately.

What happens if I change the validation rule but someone already entered invalid data?

The invalid data stays in the cell. Validation only prevents new entries that break the rule—it does not check or remove data that was already there. If you want to clean up old invalid entries, you have to find and fix them manually or use Find & Replace.

If I change a list validation, do I have to retype the whole list?

No. Open Data Validation, click in the Source field where the list is, and edit it directly. You can add new items, remove old ones, or rearrange them. If the list is in cells on the sheet, you can change the cell range instead of retyping.

Can I copy a validation rule from one cell to many cells without copying the data?

Yes. Select the cell with the rule you want to copy, copy it (Ctrl+C), then select the range where you want the rule. Use Paste Special, uncheck everything except Validation, and click OK. Only the rule pastes, not the cell contents.

What if the validation rule is not showing in the Data Validation dialog?

Make sure you selected the correct cell. Click it once to select it, then open Data Validation. If the dialog is blank or shows "All" in the Allow field, that cell does not have validation. Also check that you are on the Data tab in the ribbon—the button location varies slightly between Excel versions.