Pentland Estates certified the year's service charge for Dalegate House at £133,854.70. The quantities and rates on the same schedule price those twelve lines at £138,557.90. Nothing was broken and nothing was red.
Three cells in the total column were not formulas. They were typed in July, during a call with the tenants' surveyor, and for three months they sat there looking exactly like the nine cells still calculating beside them.
A typed number and a calculated one are identical on screen. Same font, same alignment, same number format, and both of them add up. The only difference is that one of them never changes again.
| Twelve service lines | On the certificate | At the rates beside them |
|---|---|---|
| Total recovered | £133,854.70 | £138,557.90 |
| The three typed cells | £23,410.00 | £28,113.20 |
| Cells holding a formula | 9 of 12 | 12 of 12 |
| Under-recovered | £4,703.20 | £0.00 |
Scope.
ISFORMULA,FORMULATEXT, Go To Special, Show Formulas and the error-checking rules, in Excel for Microsoft 365, 2021, 2019 and Excel for Mac.
ISFORMULAandFORMULATEXTarrived in Excel 2013. The array forms ofMAXandMATCHbelow need Microsoft 365 or Excel 2021, or Ctrl+Shift+Enter on anything older.Throughout, column B is the quantity, column C the rate and column D the line total, and the twelve charged lines run from row 2 to row 13.
01The Three Cells That Stopped Moving
In July the tenants' surveyor queried the draft and the managing agent was on the phone. Three figures were agreed out loud, so he typed them straight into the total column to hold them while everything else was re-costed.
They never moved again. In September the contractors' logs were trued up, the quantities in column B went up, and nine of the twelve line totals went up with them.
The window cleaning line is the clearest. Column B says 418 drops, column C says £27.40, and column D says £9,590.00 — which is 350 drops at £27.40, the figure agreed in July.
Nothing on the schedule records any of that. The cell holds a plausible number, in the right format, in the right column, under a heading that says Total.
Twelve Service Lines, Three of Them Frozen in July
Dalegate House's service charge schedule as it was certified, in A1:F15. Column B is the quantity from the contractors' logs after September's true-up, column C the agreed rate, and column D the total as the certificate carried it. Column E is what the same row's quantity and rate come to — the column the schedule did not have. The three rows where D and E disagree are the three cells somebody typed in July; the two rows at the bottom are the certificate as issued against the certificate as the rates price it.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
02A Typed Number Looks Exactly Like an Answer
Excel draws the result of =B2*C2 and the digits somebody typed in exactly the same way. Both are right-aligned, both take the column's number format, and both are included in =SUM(D2:D13).
Clicking the cell and reading the formula bar is the only ordinary way to tell, and nobody clicks twelve cells before issuing a certificate.
Ctrl and the backtick key — the one above Tab — switches the whole sheet to Show Formulas. Every formula appears as its own text, and every typed cell stands out as a bare number in a column of =B2*C2.
Show Formulas widens the columns while it is on and changes nothing in the file. The same keys put the sheet back, and so does Formulas ▸ Show Formulas.
Scenario: Open your own charging schedule and press Ctrl and the backtick once. Read the calculated column from top to bottom: anything that is not a formula is a number somebody typed. Press the keys again to put the sheet back.
Try it in the grid03ISFORMULA Counts What Should Not Be There
=ISFORMULA(D2) returns TRUE when the cell holds a formula and FALSE when it holds a value. One column of it reads the whole schedule at once.
The count is the number worth keeping on the sheet. =SUMPRODUCT(--NOT(ISFORMULA(D2:D13))) returns 3, and on a column that is meant to calculate all the way down, the only acceptable answer is 0.
| Question | Formula | On this schedule |
|---|---|---|
| Does D2 calculate? | =ISFORMULA(D2) | FALSE |
| How many cells are typed? | =SUMPRODUCT(--NOT(ISFORMULA(D2:D13))) | 3 |
| How many calculate? | =SUMPRODUCT(--ISFORMULA(D2:D13)) | 9 |
| Which row is the first? | =MATCH(FALSE,ISFORMULA(D2:D13),0)+1 | 3 |
That last one returns 3, and row 3 is the window cleaning line. On Excel 2019 and earlier it needs Ctrl+Shift+Enter; everywhere else it is an ordinary formula.
Scenario: Put =SUMPRODUCT(--NOT(ISFORMULA(D2:D13))) in a cell above your own calculated column and leave it there. Any answer but 0 means a cell has been typed over since the last time you looked.
04Go To Special Finds Them in One Keystroke
Select the column, press F5 ▸ Special ▸ Constants, and Excel selects every cell in it that is not a formula. No helper column, no formula to write, no version requirement.
The reverse works too. F5 ▸ Special ▸ Formulas selects the calculating cells, which is how you fill the gaps in one go once you know where they are.
Column differences is the sharper cousin. With the correct cell active, Ctrl and the backslash key — or Home ▸ Find & Select ▸ Go To Special ▸ Column differences — selects only the cells that break the column's pattern, allowing for references that ought to change row by row.
Colour the selection while it is still selected. A fill on the typed cells is a map that survives the selection going away, and Home ▸ Clear ▸ Clear Formats takes it off again afterwards.
Constants means text and dates too. On a mixed block it selects headings and labels as well as figures, so select the one column you are auditing rather than the whole sheet.
The four tick boxes under Constants — Numbers, Text, Logicals, Errors — narrow it. Numbers on its own is the one that answers "which of these totals were typed".
Scenario: Select D2:D13 on your own sheet, press F5 ▸ Special ▸ Constants with Numbers ticked, and fill the selection amber. Every amber cell is a figure that will not move again until a person moves it.
Try it in the grid05The Warning Excel Had Already Shown
Excel noticed in July. A cell that breaks the pattern of the cells around it gets a green triangle in its corner and the message Inconsistent Formula or Formula Omits Adjacent Cells.
That triangle is dismissed in one click, and Ignore Error is permanent for that cell. It never shows again, in that workbook, for anybody who opens it.
The rules live in File ▸ Options ▸ Formulas, under Error checking rules. The two that matter here are Formulas inconsistent with other formulas in the region and Formulas which omit cells in a region, and either can be off for the whole workbook.
Reset Ignored Errors is the button to press. It sits in the same dialog, it takes a second, and it brings back every triangle that anybody has ever dismissed in that file.
Scenario: Open File ▸ Options ▸ Formulas on your own workbook, confirm the two consistency rules are ticked, and press Reset Ignored Errors. Then look down the calculated column for green corners.
Try it in the grid06The Five Ways a Formula Becomes a Number
Nobody deletes a formula on purpose. Five ordinary actions do it instead, and four of them leave no trace at all.
| How it happens | What is left behind |
|---|---|
| Typing a figure to check something | The number stays, the formula does not |
| Paste Special ▸ Values over a live cell | A whole block of answers, frozen |
| Dragging a constant down the column | The same figure in every row |
| Pasting from an email or a PDF | Text and numbers, never formulas |
Replacing #REF! with the last good figure | A plausible number in a broken column |
Paste Values is the biggest of the five, because it is what everybody does to tidy a sheet before sending it out. That is the right thing to do to a copy and the wrong thing to do to the master.
Scenario: Copy a block of calculated cells on your own sheet, paste it back over itself with Ctrl+Alt+V ▸ Values, then press F5 ▸ Special ▸ Constants to see what you just made. Undo, and note how little changed on screen in between.
Try it in the grid07FORMULATEXT Puts the Formula Beside the Answer
=FORMULATEXT(D2) returns the formula in D2 as readable text: =B2*C2. On a cell that holds a typed number it returns #N/A, which is the loudest flag in this article.
A column of it next to the totals is an audit anybody can read. Twelve rows that should say =B2*C2 down to =B13*C13, and three #N/As where the formula should be.
| Cell | =FORMULATEXT(D…) | What it means |
|---|---|---|
| D2 | =B2*C2 | Calculated from its own row |
| D3 | #N/A | A typed number |
| D5 | =B5*12.75 | The rate is inside the formula |
| D9 | =7450 | A formula that is only a constant |
Wrap it so one column reads straight through: =IF(ISFORMULA(D2),FORMULATEXT(D2),"TYPED: "&TEXT(D2,"#,##0.00")) names the fault instead of showing an error.
Scenario: Put =FORMULATEXT(D2) in a spare column on your own sheet and fill it down beside the totals. Read the column once, then move it to a hidden Audit sheet, where it cannot be tidied away by somebody formatting the schedule.
08A Formula Can Still Be a Typed Number
ISFORMULA answers "is there an equals sign", not "is this cell calculated from the sheet". Two cells on this schedule pass that test and are typed numbers anyway.
D9 holds =7450. It is the gutter clearance lump sum, it is correct today, and it is a constant with an equals sign in front of it. Every audit that counts formulas counts it as healthy.
D5 holds =B5*12.75. The waste rate is in the formula instead of in C5, so column C can be renegotiated all afternoon and column D will not move.
A literal inside a formula is a typed number with better camouflage. =COUNTIF(F2:F13,"*.*") over the FORMULATEXT column from section 7 counts the formulas carrying a decimal point, which is where rates hide.
Scenario: Press Ctrl+F on your own sheet, type a rate you know by heart, set Look in to Formulas and press Find All. Every hit is a formula carrying a number that should have been a reference to the rate column.
Try it in the grid09The Control Total That Fails Loudly
Audit columns get deleted. A control total is one cell, it sits above the data, and it reports a number that should be 0.
=ROUND(SUM(D2:D13)-SUMPRODUCT(B2:B13,C2:C13),2) returns 4,703.20 on this schedule. Where every line total is its own row's arithmetic it returns 0, and the ROUND stops a stray penny of floating point from crying wolf.
It does not care how the cell went wrong. A typed figure, a stale paste, a rate buried in a formula, a reference that slipped a row — all of them come out in the same number.
| Control | Formula | This schedule |
|---|---|---|
| The column foots | =ROUND(SUM(D2:D13)-SUMPRODUCT(B2:B13,C2:C13),2) | 4,703.20 |
| Every line calculates | =SUMPRODUCT(--NOT(ISFORMULA(D2:D13))) | 3 |
| Nothing is missing | =COUNTA(D2:D13) | 12 |
| The worst single line | =MAX(ABS(D2:D13-B2:B13*C2:C13)) | 1,863.20 |
Give it a word rather than a number and nobody has to interpret it: =IF(ROUND(SUM(D2:D13)-SUMPRODUCT(B2:B13,C2:C13),2)=0,"Agrees","CHECK").
Scenario: Put the control cell above your own total, add a conditional format that turns it red when it is not 0, and make it the last thing you look at before the file leaves. Issue nothing while it reads CHECK.
Try it in the grid10Make the Column Hard to Type Over
Auditing is what you do when prevention did not happen. Three things keep a calculated column calculated.
Ctrl+T turns the range into a Table, and a Table column fills one formula down every row, including the rows added later. Type a number into one cell and Excel offers to restore the calculated column: a prompt rather than a prohibition, but a loud one.
Protection makes it a refusal. Select the input cells in columns B and C, untick Locked in Format Cells ▸ Protection, then Review ▸ Protect Sheet. The total column now rejects the keystroke outright.
Give the typed figures a home. Agreed sums and manual overrides belong in their own column, with a heading that says so, so that a number which has to be typed is typed where typed numbers are expected.
Scenario: Convert your charging range with Ctrl+T, then type a number into the middle of the calculated column and read what Excel offers you. Undo, unlock columns B and C, and protect the sheet.
Try it in the grid11Six Checks Before the Certificate Goes Out
- Count the typed cells.
=SUMPRODUCT(--NOT(ISFORMULA(D2:D13)))should return 0. - Foot the column against the arithmetic.
=ROUND(SUM(D2:D13)-SUMPRODUCT(B2:B13,C2:C13),2)should return 0. - Look at the formulas. Ctrl and the backtick, then read the calculated column down.
- Select the constants. F5 ▸ Special ▸ Constants ▸ Numbers should select nothing inside D2:D13.
- Hunt the literals.
=COUNTIF(F2:F13,"*.*")over aFORMULATEXTcolumn should return 0. - Bring the triangles back. File ▸ Options ▸ Formulas ▸ Reset Ignored Errors, then look for green corners.
12Eight Traps
- Typing a figure "for now". There is no later: the cell looks finished the moment you press Enter.
- Paste Special ▸ Values over the master. It freezes a block of answers and leaves the sheet looking exactly as it did.
- Ignore Error. One click makes Excel's own warning permanent history, for that cell and for everybody who opens the file.
- A rate inside a formula.
=B5*12.75ignores column C for good, andISFORMULAsays the cell is fine. =7450. An equals sign in front of a constant passes every formula count and calculates nothing.- An audit column that gets tidied away. Keep the control total instead: one cell, above the data, hard to delete by accident.
- Dragging a constant down the column. The fill handle copies whatever is in the cell, and a number copies as a number.
- Trusting the total because it foots. Typed cells are included in
=SUM(D2:D13), so a total that adds up is not a total that is right.
13Five Exercises
- Reproduce it. Build twelve rows of
=B2*C2, type over three of them, and confirm=SUM(D2:D13)reports the typed figures without complaint. - Count them. Put
=SUMPRODUCT(--NOT(ISFORMULA(D2:D13)))above the column and check it returns 3. - Find the first.
=MATCH(FALSE,ISFORMULA(D2:D13),0)+1should name the first broken row before you go looking for it. - Trap the literal. Replace one formula with
=B5*12.75, change C5, and watch the control total move while the cell itself looks untouched. - Protect it. Unlock columns B and C, protect the sheet, and try to type into the total column.
What to Take Away
The schedule was not broken. Twelve lines, twelve plausible figures, a column that footed, and a total nobody had any reason to doubt.
What was missing was the arithmetic in three cells. Somebody typed three agreed figures in July, the quantities were trued up in September, and those three cells carried on reporting the summer.
A calculated column has to be checked as a column, not read as a list of numbers. One count, one control total, and both of them say 0 on a schedule that is still doing its own sums.
Six seconds of checking, against £4,703.20 the landlord cannot re-bill once the certificate is issued.