The inconsistent formula in the middle of a row

Established onExcel 365 and 2016 and later: inconsistency flag, row differencesGoogle Sheets, web: neither featureBoth: formula view, and FORMULATEXT

A column of a thousand identical formulas with one that differs is the most common defect that survives every other check, because a formula is not displayed in its cell.

How they appear

Somebody corrects one row by typing the answer. Somebody pastes a block from another sheet into the middle. A formula is dragged over a row that had been edited. Somebody adds a plus one to a single row for a reason that made sense that afternoon.

All four are ordinary acts, and none of them leaves a visible mark.

What the software offers

Excel's error checking includes a rule for a formula inconsistent with others in its region, which marks the cell with a small triangle in the corner. It is on by default, it catches a good share, and it is silently ignored by most users because the same marker is used for several less important warnings.

There is also a selection command that highlights every cell in a row or a column differing from the active one, which answers the question directly for a selected block.

Google Sheets has neither. This is the largest auditing gap between the two products and is worth weighing when the choice is open.

The two-minute audit

Switch to formula view and look down each calculated column. A cell that reads differently from its neighbours is visible immediately, and a typed constant among formulas is unmistakable because it has no equals sign.

This works in both products, needs no feature, and finds most of what this entry is about.

The one-cell version

Both products have a function returning a formula as text. A helper column applying it to every row of a calculated column, and a count of how many distinct values that column contains, produces a number that should be one.

Any other number names the problem. It is the only check here that keeps working after everybody has stopped looking.

The notation that makes it textual

In the alternative reference style described in the second part, a correctly filled column shows exactly the same text in every cell, so an inconsistency is a line that reads differently rather than a formula that must be compared mentally.

Switching for ten seconds is a complete audit of a column.

Why the defect is worse than it looks

A short range is wrong by a known amount and a typed-over cell is wrong for ever, in a way that does not respond to any input changing. So a model can be corrected, re-run and re-checked, and that one row continues to produce the same number through all of it.

Which is also why it survives testing: the cell is right on the day it was typed, and only becomes wrong as the inputs move away from that day.

Preventing it

Protecting the calculated regions, which the fourth part describes, makes typing over a formula a deliberate act requiring an unlock. That converts the whole class from an accident into a decision, and decisions can be documented.

An override column, also described there, gives people the legitimate thing they were trying to do.

What to do when you find one

Do not simply restore the formula. Find out why somebody typed over it, because in perhaps half of cases the number is right and the formula was wrong, and replacing the number reintroduces the original error.

That is why the check reports rather than repairs.

What we cannot verify

The inconsistency rule, the row and column difference selection, the formula-as-text function and the alternative notation are documented by the vendor and were confirmed on a current build. The absence of the first two in Google Sheets was established by testing. The claim about how often the typed value is the correct one is our experience and not a measurement.

The related defect in a row

Everything above concerns a column of identical formulas, and the same thing happens across a row: a summary line where one period's formula covers a different span than its neighbours.

It is harder to spot because rows are read left to right and formulas are compared vertically by eye more naturally than horizontally. The selection command that highlights differences works in both directions and is the answer for both.

Why this entry is late in the site

Because the arrangement recommended in the fourth part removes most of it in advance. A table object with a calculated column propagates one formula to every row automatically and flags any cell that stops matching, which turns this from a search into a notification.

The check that belongs in every model

One cell per calculated column, counting how many distinct formulas it contains, sitting in the block of checks described two entries earlier. The expected answer is one, it costs a formula, and it is the only protection here that keeps working when nobody is looking.

For further context, see Microsoft guidance on data validation.