Data Validation error

Question

How to Edit All Formulas in a Column and not get the Data validation error?

Answer 1

Data validation errors usually appear when editing formulas because the new result no longer meets the validation rule, or because validation is configured to block changes.

To edit all formulas in a column and avoid constant validation errors, use these approaches:

1. Temporarily turn off or relax validation, then restore it
   1. Select the validated cells in the column.
   2. On the Data tab, select Data Validation.
   3. On the Error Alert tab, either:
      • Clear Show error alert after invalid data is entered, or
      • Change Style from Stop to Warning or Information so edits are allowed.
   4. Edit or fill the formulas in the column.
   5. When finished, turn the error alert back on or restore Stop style so invalid data is blocked again.
2. Make sure formulas do not produce errors Data validation is ignored if the cell’s formula returns an error such as #REF! or #DIV/0!. Before or after bulk editing formulas:
   • Correct any formula errors in validated cells (for example, fix broken references or divide-by-zero issues).
   • Ensure referenced cells are correct so formulas calculate valid results.
3. Avoid breaking validation by copy/fill When copying or filling formulas, users can bypass validation messages. If the goal is to keep validation effective for manual entry but still allow formula edits:
   • Keep validation in place.
   • Use copy/fill to update formulas, then run checks (e.g., filters or conditional formatting) to find any results that violate the validation logic.
4. If validation settings must change for the whole column If the validation rule itself needs to be adjusted to match the new formulas:
   1. Select one validated cell.
   2. Data > Data Validation > adjust the rule on the Settings tab.
   3. Check Apply these changes to all other cells with the same settings so the entire column updates at once.

If validation still blocks edits, confirm the sheet/workbook is not protected or shared, as that prevents changing validation settings.

---

References:

• More on data validation
• Apply data validation to cells
• Detect formula errors in Excel

AI-generated content may be incorrect. Read our transparency notes for more information.