How to Handle Missing, Duplicated, or Inconsistent Rows When AI Analyzes a Spreadsheet

Short answer: Use AI as a detector and triage assistant, not as an invisible replacement for source data. First preserve the original sheet, define what counts as missing, duplicated, or inconsistent, and ask the system to produce a reviewable exception queue. Only apply a correction when a documented rule or a human reviewer can support it; otherwise leave the value unchanged and record why it was not resolved.

That distinction matters because spreadsheet AI tools can generate formulas, transform data, identify outliers, and suggest insights, but their outputs still need checking. Google describes Gemini in Sheets as able to structure and clean data, spot inconsistencies, and highlight values, while Microsoft states that Copilot in Excel can make mistakes or misinterpret information and advises users to review and verify generated content [1] [2]. The workflow below is designed to make that verification visible.

Why imperfect rows need an exception workflow

An imperfect row is not automatically a bad row. A blank phone number may mean that the field was never collected, does not apply, or was intentionally withheld. Two rows with the same name may be duplicate entries—or two different people with a shared name. A date written as 03/04/26 is ambiguous unless the source’s date convention is known. Treating every irregularity as an error invites false corrections.

AI is useful for finding patterns across a large table, proposing candidate matches, and explaining why a row looks unusual. It should not be allowed to invent a missing value, merge records solely because they look similar, or normalize a field without a stated rule. Microsoft’s current Excel guidance explicitly says that AI-generated insights, formulas, and tables can be inaccurate or inappropriate and should be reviewed before reliance [2]. In practice, the safest unit of work is therefore a flag, not an automatic overwrite.

Start with a protected baseline

Make a read-only copy or versioned snapshot before asking an AI assistant to edit anything. Keep the source rows, column names, and original formatting intact. Work in a separate review sheet or workbook that contains the row identifier, the issue type, the original value, the proposed action, the evidence, the reviewer decision, and the review date.

This is operational hygiene rather than a promise that a dataset is complete. It gives you a way to compare before and after states and to undo an over-aggressive transformation. If your tool can edit the workbook directly, use a planning or chat mode first when available, then inspect the proposed changes. Microsoft describes Copilot in Excel as offering edit, plan, and chat modes, with direct workbook changes visible to people who have access to the file [2].

Define the three exception types

Missing

Define missingness at the field level. Decide whether an empty cell, a placeholder such as “N/A,” a whitespace-only value, or a default like zero should be treated as missing. Do not assume that zero, “unknown,” and blank mean the same thing. Ask AI to report counts and row locations for each representation, but keep the original values in the baseline.

Duplicated

Separate exact duplicates from possible duplicates. An exact duplicate might match on every relevant column. A possible duplicate might share a customer ID, email address, invoice number, or a combination of fields while differing in date or amount. Similar names are weak evidence by themselves. Ask the model to show the fields that matched and the fields that differed, rather than returning only a yes/no label.

Inconsistent

Inconsistency means that values conflict with an agreed pattern or with other fields. Examples include mixed date formats, spelling variants, units that change within a column, an end date earlier than a start date, or a category value outside the approved list. Before correcting, write down the expected rule and its source. If no rule exists, the correct status is “needs definition,” not “fixed.”

Use AI for detection, not silent correction

Give the assistant a bounded task and request structured output. A useful prompt names the worksheet, the row key, the columns to inspect, the allowed categories, and the action it must not take. For example:

“Inspect rows in the copied review sheet. Flag blank-like values, exact duplicates, possible duplicates, and values that violate the listed formats. Return row ID, issue type, columns involved, original values, confidence as a descriptive label, and a short reason. Do not delete, merge, fill, reformat, or overwrite any source cell. If evidence is insufficient, mark the row unresolved.”

Then ask for separate checks rather than one broad request. A missing-value scan, duplicate-candidate scan, and rule-validation scan are easier to test independently. Google lists data cleaning, inconsistency spotting, formulas, charts, and other analysis as Gemini in Sheets use cases [1]; those capabilities are most useful when the requested output is an auditable list of candidates.

Build an exception queue

Use a queue with one row per issue, not one row per spreadsheet record. The same source row may have a missing date and an inconsistent category, and those need different decisions. A practical schema is:

Do not treat an AI confidence label as proof. It is a prioritization aid. High-priority cases may deserve earlier review, but every accepted correction should still have a rule, source, or comparison that another person can understand.

Resolve each case with a different rule

For missing values

First check whether the value exists in a trusted source or another version of the record. If it does, copy it with a note that identifies the source and date. If it does not, keep the field missing or use a clearly documented status such as “not provided.” Do not ask AI to guess a person, date, amount, or category merely because nearby rows contain a pattern. A calculated default may be appropriate in a controlled analytical model, but label it as derived and keep the original field separate.

For duplicates

Compare a key identifier and the fields that establish whether two records represent the same event. If the records are exact duplicates, a documented deduplication rule may allow one to be marked as retained and the other as excluded. If they conflict, preserve both until the source of truth is identified. Never merge two records solely because the model says they are similar.

For inconsistencies

Normalize only when the target representation is known. For example, converting text dates to a single display format is different from changing an ambiguous date’s underlying day and month. Keep a before-and-after value, the rule applied, and the rows affected. If the rule is uncertain, flag the issue and ask the data owner to define it.

A simple decision tool

For every flagged issue, walk through these questions:

1. Can I identify the original row and preserve its unedited value?
2. Is the issue defined by an explicit rule, or is it only an AI suspicion?
3. Is there a trusted source or unambiguous evidence for the proposed correction?
4. Would changing the value alter totals, dates, identities, or category assignments?
5. Can another reviewer reproduce the decision from the queue note?

If the answer to the first question is no, stop and restore the baseline. If the second or third answer is no, leave the value unchanged and mark it unresolved. If the fourth answer is yes, use a second-person review or the appropriate subject-matter owner. The fifth question is the release gate: a cleaned copy should not be described as reviewed unless the checks and unresolved cases are documented.

Validate the result before analysis

After approved changes are applied to a new working copy, rerun the detection checks. Compare row counts, key counts, blank-like counts, duplicate candidates, category frequencies, date ranges, and any totals that matter to the stated analysis. Inspect a sample of accepted and rejected cases, including edge cases near the boundaries of your rules.

Also test the AI’s explanations against the cells. A fluent explanation is not evidence. Microsoft recommends reviewing, editing, and verifying anything Copilot creates before relying on it [2]. NIST’s AI Risk Management Framework similarly provides a structure for governing, mapping, measuring, and managing AI risks, including documenting limitations and human oversight [3]. For a spreadsheet workflow, that principle translates into a small but useful record: what the assistant inspected, what it was instructed not to change, what a reviewer accepted, and what remains unresolved.

Beginner readiness checklist

• There is a preserved, unedited baseline.
• Each row has a stable identifier.
• Missing, duplicate, and inconsistent values have separate definitions.
• The AI was asked to flag candidates before making edits.
• An exception queue records evidence and decisions.
• Corrections use a documented rule or trusted source.
• Uncertain cases remain visible rather than being guessed.
• Post-change counts and sample cases were rerun and reviewed.

This method will not eliminate ambiguity, and it is not a certification that a workbook is correct. It is a way to keep uncertainty visible while using AI to accelerate the repetitive parts of inspection. When the spreadsheet supports a regulated, safety-sensitive, employment, health, legal, or financial decision, involve the relevant qualified professional and follow the current primary rules for that context.

Sources and further reading

1. Google Workspace, “Gemini in Google Sheets.” Product capabilities and examples for structuring, cleaning, and analyzing spreadsheet data.
2. Microsoft Support, “Frequently asked questions about Copilot in Excel.” Current capabilities, limitations, verification guidance, and workbook-editing modes.
3. National Institute of Standards and Technology, “AI Risk Management Framework.” Risk-management framework for documenting and managing AI-system risks.