A new spreadsheet rule does not check the old records

Decide what the rule means for existing work

A workbook owner asks for a list of approved department names so future entries are consistent. Before changing the workbook, ask what should happen to the records already there. Some may use an older name intentionally; others may be mistakes. The person maintaining the sheet needs an agreed distinction before making corrections.

Microsoft says you can apply data validation to cells that already contain data, but Excel does not automatically notify you that those existing cells contain invalid values. Adding the rule is therefore not the same task as reviewing the historical records. Our recommendation is to give that review its own owner.

Inspect a controlled copy first

Work on an approved copy with the workbook owner, preserving the original under your normal arrangements. Agree the cells and rule being assessed. Microsoft documents Circle Invalid Data under Data, Data Tools, Data Validation as a way to highlight entries that do not meet validation rules. Use the instructions for your supported Excel version.

For a fictional UK maintenance business, an equipment register contains department names that changed after a reorganisation. The owner reviews the highlighted entries against the date and purpose of each record. They decide which records need current names and which should retain an accurate historical description. No real customer workbook was tested for this example.

Record the proposed treatment of exceptions before making bulk changes. A value outside the new list is a prompt to investigate; it does not, by itself, establish the correct replacement. If nobody can resolve an entry, keep the question visible for the responsible business owner.

Make the message match the intended decision

Microsoft distinguishes the Stop error alert from Warning and Information alerts: the latter allow an invalid entry to be accepted. Choose the behaviour with the workbook owner rather than assuming every warning blocks an entry.

Our suggested test uses invented values in the controlled copy. Ask a colleague to enter an accepted value and an out-of-rule value, then explain what they understand from the message. If a legitimate exception is possible, agree how it should be recorded and reviewed. Do not invite people to remove validation whenever a message interrupts their work.

Check the process that feeds the sheet

Microsoft also warns that validation messages may not appear when data is copied or filled. Include the team’s actual entry method in your test instead of checking only a value typed into one cell. Record the observed result and investigate any route that behaves differently from the owner’s expectation.

Finish with a record of the existing entries reviewed, the unresolved exceptions and who accepted the revised workflow. This is a practical quality check, not proof that every record is accurate or that the workbook meets a regulatory standard. The useful outcome is a rule people understand and an explicit decision about the data it inherited.