Others

How to Fix the Excel Data Validation Multiple Rules Error

Tauseeq Magsi Sep 30, 2026 874 views

Question details

The user needs to resolve an error preventing them from modifying data validation settings across multiple cells with conflicting rules.

How to Fix the Excel Data Validation Multiple Rules Error

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Attempting to apply a single data validation rule to a range of selected cells that already contain mixed or different validation settings.
Observed behavior: The software displays a prompt stating "The selection contains more than one type of validation" and interrupts the user from applying the new rule.

Before you start

Verify the exact range of cells you wish to apply the new data validation rule to, and ensure that standardizing these rules won't negatively impact how data is currently entered in other dependent cells.

Solution 1Recommended

Clear Existing Validation Rules and Reapply

The most effective way to resolve conflicting validation rules is to allow the software to clear the old rules from the selected range so you can apply a single, unified rule.

When you select a range containing different validation rules (like a mix of text length limits and drop-down lists), the system cannot determine which existing rule to prioritize. Clearing them allows you to start fresh across the entire selection.

Select the target cells

Use your mouse to highlight all the cells in the column or row that should use the exact same data validation rule or drop-down list.

Open Data Validation settings

Navigate to the "Data" tab on the top ribbon menu and click on the "Data Validation" button.

Clear existing rules

When the warning prompt "The selection contains more than one type of validation. Erase current settings and continue?" appears, click "OK" to clear the conflicting rules.

Apply the new rule

In the Data Validation dialog box, configure your desired validation criteria (such as setting Allow to 'List'), input your source data, and click "OK" to apply it to the entire selected range.

Clear Existing Validation Rules and Reapply

Data Remains Unchanged: Clearing the validation settings only removes the background rules. It does not delete or alter any of the existing text, numbers, or values already typed into those cells.

Manage Data Easily with WPS Spreadsheet

Effortlessly Manage Data Validation with WPS Office

WPS Spreadsheet provides a smooth, highly compatible experience for managing complex data validation rules, dropdown lists, and formulas. You can fix validation conflicts using the exact same intuitive steps.

1. Select Cells: Open your workbook in WPS Spreadsheet and select the range of cells you want to modify.
2. Access Data Validation: Go to the Data tab on the main ribbon and click the Validation icon.
3. Clear Conflicts: If prompted about multiple types of validation, click OK to erase the old conflicting rules.
4. Set New Rules: Define your new validation conditions in the settings dialog and click OK to finalize.

Easily clear and apply unified data validation rules to multiple cells.Fully compatible with Microsoft Excel (.xlsx) formats and features.Lightweight software with a familiar, user-friendly interface that requires zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Will clearing data validation delete my existing cell data?

No, clearing data validation rules only removes the input restrictions and drop-down menus from the cells. The actual text or numbers currently entered in your worksheet will remain completely unchanged.

How can I quickly find all cells with data validation in my worksheet?

You can locate them by pressing Ctrl+G to open the Go To dialog, clicking "Special", and then selecting "Data Validation". This will automatically highlight all cells in your current sheet that have validation rules applied.

Can I apply data validation to an entire column at once?

Yes, you can click the column letter at the top of the spreadsheet to select the entire column, then go to Data > Data Validation to apply your rule uniformly to all cells within that specific column.

Office Quick Solutions