01 / 14The Three Cells That Stopped Moving

Formula AuditingExcelData Quality

Find Hardcoded Values in Excel: Audit a Formula Column

The Service Charge Under-Recovered £4,703.20 and the Certificate Had Already Been Issued, Because Three Line Totals Were Numbers Somebody Had Typed

22 Oct 202614 min read

UsesSUMPRODUCTCOUNT, COUNTAROUNDSUM

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 linesOn the certificateAt 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 formula9 of 1212 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.

ISFORMULA and FORMULATEXT arrived in Excel 2013. The array forms of MAX and MATCH below 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.

ABCDEF
1
Service line
Qty
Rate
Certified
At the rates
What happened
2
Cleaning, common parts
1842
18.6
34261.2
34261.2
The biggest line on the schedule and one of the nine that calculated all year. The hours rose from 1,790 to 1,842 when the logs were trued up in September, and the total rose with them without anybody touching it.
3
Window cleaning
418
27.4
9590
11453.2
Typed in July during the call: 350 drops at £27.40. The logs show 418 drops, the cell still shows the July figure, and the £1,863.20 between them is the largest single error on the schedule.
4
Entrance mat hire
86
64
5504
5504
Eighty-six weeks of hire across two entrances, calculated from its own row. Nothing has ever gone wrong with this line, which is why it is useful: it is what the other eleven are supposed to look like.
5
Waste collection
2262
12.75
28840.5
28840.5
The number is right and the formula is not: D5 holds =B5*12.75, with the rate inside the formula rather than in C5. Nothing is wrong today, and nothing will change in column D the day the rate is renegotiated.
6
Consumables
1840
4.95
7920
9108
The second cell typed in July, at 1,600 units. The true count is 1,840, so the schedule under-recovered £1,188.00 on soap, towels and bin liners, and the cell looked identical to the ones around it.
7
Planted displays
984
8.4
8265.6
8265.6
Calculated from its own row, start to finish. Quantities moved twice during the year and the total moved with them both times.
8
Pest control
310
21.5
6665
6665
Calculated. A quiet line that nobody has ever had a reason to type over, which is the only thing keeping it right.
9
Gutter clearance
1
7450
7450
7450
A lump sum, and the third shape of the problem: D9 holds =7450. ISFORMULA returns TRUE because there is an equals sign, FORMULATEXT shows the constant, and the cell calculates nothing at all.
10
Lift maintenance
64
118
5900
7552
The third cell typed in July, at 50 visits of £118.00. Sixty-four visits were logged, the cell never moved, and £1,652.00 of a line that the tenants would have paid without query went unrecovered.
11
External sweeping
2262
3.2
7238.4
7238.4
Calculated from the same area figure as the waste line, and one of the nine that followed the September true-up without anybody noticing it had.
12
Site supervision
18
395
7110
7110
Eighteen days at the agreed daily rate, calculated. One of the three lines the surveyor queried in July, and the only one of the three that was left as a formula.
13
Fire alarm testing
140
36.5
5110
5110
Calculated. The last line of the schedule, and the one most likely to be typed over next: it sits directly above the total, where people experiment.
14
Certificate as issued
12 lines
133854.7
138557.9
What =SUM(D2:D13) added up on the day the certificate was issued, against what the same twelve lines are worth at the quantities and rates printed beside them. Three cells hold the whole of the difference.
15
The three typed cells
3 of 12
23410
28113.2
The three line totals that are numbers rather than formulas, added up on their own: £23,410.00 as typed against £28,113.20 at the schedule's own rates, and £4,703.20 of service charge between them.

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 grid

03ISFORMULA 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.

QuestionFormulaOn 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)+13

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.

Try it in the grid

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 grid

05The 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 grid

06The 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 happensWhat is left behind
Typing a figure to check somethingThe number stays, the formula does not
Paste Special ▸ Values over a live cellA whole block of answers, frozen
Dragging a constant down the columnThe same figure in every row
Pasting from an email or a PDFText and numbers, never formulas
Replacing #REF! with the last good figureA 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 grid

07FORMULATEXT 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*C2Calculated from its own row
D3#N/AA typed number
D5=B5*12.75The rate is inside the formula
D9=7450A 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.

Try it in the grid

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 grid

09The 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.

ControlFormulaThis 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 grid

10Make 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 grid

11Six Checks Before the Certificate Goes Out

  1. Count the typed cells. =SUMPRODUCT(--NOT(ISFORMULA(D2:D13))) should return 0.
  2. Foot the column against the arithmetic. =ROUND(SUM(D2:D13)-SUMPRODUCT(B2:B13,C2:C13),2) should return 0.
  3. Look at the formulas. Ctrl and the backtick, then read the calculated column down.
  4. Select the constants. F5 ▸ Special ▸ Constants ▸ Numbers should select nothing inside D2:D13.
  5. Hunt the literals. =COUNTIF(F2:F13,"*.*") over a FORMULATEXT column should return 0.
  6. Bring the triangles back. File ▸ Options ▸ Formulas ▸ Reset Ignored Errors, then look for green corners.

12Eight Traps

  1. Typing a figure "for now". There is no later: the cell looks finished the moment you press Enter.
  2. Paste Special ▸ Values over the master. It freezes a block of answers and leaves the sheet looking exactly as it did.
  3. Ignore Error. One click makes Excel's own warning permanent history, for that cell and for everybody who opens the file.
  4. A rate inside a formula. =B5*12.75 ignores column C for good, and ISFORMULA says the cell is fine.
  5. =7450. An equals sign in front of a constant passes every formula count and calculates nothing.
  6. An audit column that gets tidied away. Keep the control total instead: one cell, above the data, hard to delete by accident.
  7. Dragging a constant down the column. The fill handle copies whatever is in the cell, and a number copies as a number.
  8. 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

  1. Reproduce it. Build twelve rows of =B2*C2, type over three of them, and confirm =SUM(D2:D13) reports the typed figures without complaint.
  2. Count them. Put =SUMPRODUCT(--NOT(ISFORMULA(D2:D13))) above the column and check it returns 3.
  3. Find the first. =MATCH(FALSE,ISFORMULA(D2:D13),0)+1 should name the first broken row before you go looking for it.
  4. Trap the literal. Replace one formula with =B5*12.75, change C5, and watch the control total move while the cell itself looks untouched.
  5. 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.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge