How to find hardcoded values in a spreadsheet

A step-by-step run through the add-on, from the empty sidebar to the fix and back again. Ten minutes, and you can do all of it on the free tier except the two steps that say otherwise.

Prefer to watch? The same walkthrough as a video, recorded on the live add-on, with a chapter for each step. Open it on YouTube.

What it looks for

Two things, and only two. Both are ordinary, both are invisible in Sheets, and both quietly stop a spreadsheet telling you the truth.

A value pasted over a formula. A column of formulas with a typed number sitting in it. The sheet still adds up, so nothing looks wrong — and that cell has not recalculated since the day it was pasted.
A constant inside a formula. =E8*0.2 rather than =E8*$B$1. The answer is right until the rate changes, and then it is wrong everywhere the number was repeated.

Sheets has no inconsistent-formula check at all, so neither of these is caught by anything else — and the pasted values at the top or bottom of a run, which is where they usually are, are the hardest to spot by eye.

Before you start

A desktop browser — Chrome™, Edge, Firefox or Safari. Editor add-ons do not run in the Sheets mobile apps.
The add-on installed in Sheets. The support page says how to install it from the Google Workspace™ Marketplace.
A spreadsheet worth checking. If you want to follow along exactly, any sheet with a totals column will do — the screenshots below use a small regional sales summary.
Nothing leaves Google. The scan runs inside the sidebar, on the sheet you have open. The only thing that ever crosses the network is a licence key. The privacy notice says so in full.

Step 1 — Open the sidebar

Open the spreadsheet, then Extensions › Hardcoded Value Detector › Open detector. The sidebar opens down the right-hand side with two buttons and nothing else. It has not read anything yet.

The sidebar, showing the Check this sheet and Check whole spreadsheet buttons and no results. — Nothing happens until you press a button. The add-on never scans on its own.

Step 2 — Check the sheet

Select the sheet you care about and press Check this sheet. It reads the sheet once and lists what it found, worst first.

The sidebar after Check this sheet: eight findings on the Sales sheet, the first a value pasted over the total in F7. — Two findings on this sheet. Checking and every explanation are free, on every sheet, forever.

If it finds nothing it says so plainly. There is no score and no grade — two checks, and what they found.

Step 3 — Read a finding

Each finding is one card, and each card says four things:

A single finding card: title, cell reference, what is wrong, why it matters, and the current value against the suggested formula. — The cell reference at the top right is a link — click it and Sheets selects that cell.

What it is. “Value pasted over a formula”, and the cell.
What is wrong, in one sentence, naming the pattern the rest of the column uses.
Why it matters. The paragraph in grey. Worth reading once per kind of finding.
The comparison. What the cell holds now, and what the formula would be. That block is built from the same instruction the Fix button executes, so it cannot promise one thing and do another.

The colour down the left edge is severity. Red is a value pasted over a formula — a number that has stopped adding up. Amber is a constant inside a formula, which may be perfectly deliberate.

Step 4 — Fix it Pro

On the paid tier each fixable finding carries a button naming exactly what it will do. On the free tier the same card shows the formula and tells you to type it in yourself — the explanation is never withheld.

The same finding on a Pro licence, now with a Restore the formula in F7 button under the comparison. — The button names the cell. It fixes that one finding, not everything it found.

Press it, and the sidebar writes the formula, re-reads the sheet, and shows you what is left.

After the fix: an Undo Restore the formula in F7 button has appeared at the top of the sidebar. — F7 is fixed; the rest are still listed — including the `1.15` in the Target column, which the add-on will not fix for you. Only you know what that number means.

Step 5 — Undo, if you want it back

Undo is free on every tier, always. It appears the moment there is something to go back to, and it survives the sidebar being closed and reopened.

The sidebar after pressing undo: all eight findings are listed again. — Every finding is back, because the sheet is back. Undo restores the cells the fix touched.

Use this button rather than Ctrl+Z. It puts the pasted value back exactly — number, text or date — and it is the reliable route. File › Version history is untouched by anything the add-on does, so that safety net stays as well.

Step 6 — The whole spreadsheet Pro

Check whole spreadsheet does every visible sheet in one pass and lists the findings together, each labelled with the sheet it came from. Hidden sheets are skipped: a finding on a tab you cannot see is a finding you cannot act on.

Findings from two sheets listed together, each labelled with its sheet name. — Three findings across two sheets, in one list.

On the free tier the button is still there, and says why it did nothing:

The free tier response: checking every sheet at once is part of the paid tier. — The active sheet stays free however many times you check it.

Step 7 — Entering a licence key

Open Enter a licence key at the bottom of the sidebar, paste the key from the end of checkout, and press Activate. It takes effect immediately — no restart. The same key works in the Excel add-in.

The licence section expanded, showing the key field and the Activate button. — One key covers three seats, shared across Excel and Google Sheets™.

If the sidebar ever says your subscription ended, it is almost always a card that expired. Checking a sheet keeps working, and so does Undo.

The foot of the sidebar on a lapsed licence: your subscription ended, checking this sheet still works, and Undo always does. — The message sits at the foot of the sidebar, under the findings. A lapsed licence never takes away what was free.

Exporting what it found Pro

Export CSV downloads a row per finding for a spreadsheet or a ticket. Report tab writes the same report into a tab of the spreadsheet itself. Both export the scan you are looking at, not a fresh one, so the file always agrees with the screen.

Where the sidebar sandbox will not let the file download, a Copy the report box appears with the same text in it.

When it will not help

A lone formula. A run needs at least three matching formulas before a gap in it means anything. Two cells are a coincidence.
A column with two different formulas in it. Deliberate variation, and the add-in stays quiet rather than guessing which one you meant.
A number that is simply wrong. This finds numbers in the wrong place, not wrong values. Nothing can tell you 12,400 should have been 14,200.
Hidden sheets, in a whole-spreadsheet check.
A count, position or mode. The 2 in ROUND(x,2) or the 3 in a VLOOKUP is not a rate anyone moves into a labelled cell, so it is not reported. A typed year in DATE is.
Array-formula output. Cells filled by ARRAYFORMULA, FILTER, SEQUENCE and friends hold values but no formulas, exactly like pasted-over cells — so they are deliberately ignored. Flagging them would be wrong, and offering to overwrite them would be worse.
A sheet of more than 2,000,000 cells. The sidebar names the sheet it skipped and checks the rest.

If something looks wrong

Email support@appgarden.co.uk, and say which browser you are on, whether your account is a Gmail™ or a Workspace one, and what the sidebar showed. If a finding looks wrong to you, the spreadsheet itself is the most useful thing you can send — but only send one you are content to share, and read the privacy notice first.