How to replace ctrl+~ show formulas with a permanent colour-coded excel audit map
- Step 1Open the tool on a Pro account — The highlighter is Pro-minimum (
Ctrl+~is free in Excel, but the permanent colour map is a Pro feature here). Pro handles 50 MB / 100,000 rows. - Step 2Upload the .xlsx workbook — Only the
.xlsxfamily is accepted; CSV is rejected (a CSV has no formulas, soCtrl+~wouldn't show anything either). One file per run. - Step 3Run the highlighter — no settings — There are no options. Unlike configuring a custom view, you just run it: ExcelJS colours formula cells blue and all other non-empty cells amber across every sheet.
- Step 4Check the counts — Two findings appear: formula cell count and hardcoded cell count. It's the quick numeric summary
Ctrl+~never gives you. - Step 5Download formula-highlighted.xlsx — The output is a binary XLSX (download prompt, not a preview). Save the coloured copy — your original is untouched.
- Step 6Open in normal view — no toggling needed — The colours are there in normal view immediately and stay through save and reopen. To go back to a plain workbook later, clear the fills (Home → Fill Color → No Fill) or just keep using the original.
Ctrl+~ / Show Formulas vs the permanent colour map
The built-in features are temporary, single-sheet, text-only overlays. The highlighter is a permanent, all-sheet, colour-coded copy.
| Aspect | Ctrl+~ / Show Formulas | JAD Formula Highlighter |
|---|---|---|
| Persistence | Resets when the file closes | Permanent — saved in the file |
| Formula vs hardcoded distinction | None — both look plain | Blue vs amber fill |
| What it displays | Formula TEXT in place of values | A colour; values still show normally |
| Scope | Active sheet only | Every sheet in one pass |
| Survives sort / filter | n/a (view toggle) | Yes — fills move with cells |
| Provides a count | No | Yes — formula + hardcoded counts |
| Modifies the file | No | Yes — produces a coloured copy |
How cells are coloured
Classification is by cell.value: a formula object → blue; any other non-empty value → amber; empty cells are skipped.
| Cell | Ctrl+~ shows | Highlighter applies |
|---|---|---|
=A1+A2 | =A1+A2 as text | Blue FFBDD7EE |
Typed 500 | 500 (unchanged) | Amber FFFFEB9C |
Typed text Total | Total (unchanged) | Amber FFFFEB9C |
| Typed date | the date (unchanged) | Amber FFFFEB9C |
| Empty cell | blank | No fill (skipped) |
Tier limits (excel family)
Pro-minimum. Pick the tier that fits the workbook.
| Tier | Max file size | Max rows | Batch files |
|---|---|---|---|
| Free | Not available (Pro required) | — | — |
| Pro | 50 MB | 100,000 | 5 |
| Pro-media | 200 MB | 500,000 | 20 |
| Developer | 500 MB | Unlimited | Unlimited |
Cookbook
Side-by-side of what Ctrl+~ reveals versus what the permanent colour map gives you. Each shows the cell and the two outcomes.
The same sheet: Ctrl+~ vs colour map
Ctrl+~ floods the sheet with formula text but makes typed and calculated cells look equally plain. The highlighter keeps values visible and colours the difference.
Cells: B2 100 C2 =B2*1.1 B3 200 C3 =B3*1.1 Ctrl+~ view: B2 100 C2 =B2*1.1 B3 200 C3 =B3*1.1 (you read formula text, but B and C look the same kind) Highlighter copy: B2 amber C2 blue B3 amber C3 blue (formula vs hardcoded is obvious; values still readable)
Persistence across save and reopen
Ctrl+~ is gone the next time you open the file. The highlighter's fills are saved formatting, so the audit map is there every time.
With Ctrl+~: toggle on -> close -> reopen = normal view (toggle lost) With the highlighter: download formula-highlighted.xlsx -> close -> reopen = blue/amber still applied (it's real formatting)
All sheets at once vs per-sheet toggling
Ctrl+~ only affects the sheet you're on. The highlighter colours every tab in a single run.
Workbook with 6 tabs: Ctrl+~ : press it on each tab, 6 times, every session Highlighter : one run colours all 6 tabs permanently
Surfacing a hardcode that Ctrl+~ won't flag
Under Ctrl+~ a typed number in a formula column just shows as a number among formula text — easy to miss. As amber in a blue column, it jumps out.
Formula column with one typed override:
Ctrl+~ : =SUM(..) =SUM(..) 9999 =SUM(..)
(9999 is easy to overlook among the text)
Highlighter : blue blue AMBER blue
(the override is unmissable)Filtering with the colours intact
Because the fills are real formatting, you can even filter or sort by colour in Excel after running — something a Ctrl+~ overlay can never support.
After downloading the coloured copy: Data -> Filter -> Filter by Color -> amber = isolates every hardcoded cell in the column. Ctrl+~ has no equivalent; it's a view toggle, not data.
Edge cases and what actually happens
Shared-formula followers may show amber, while Ctrl+~ shows them as formulas
By design (ExcelJS)Ctrl+~ displays the translated formula in every cell of a shared-formula block, so they all look like formulas. The highlighter, however, only sees a formula key on the master cell; followers expose sharedFormula and read as amber. So a block that Ctrl+~ shows as all-formulas may be coloured blue-then-amber. This is an ExcelJS storage nuance (common in LibreOffice-saved files), not a discrepancy in your data.
Ctrl+~ reveals protected hidden formulas differently
ExpectedOn a protected sheet with Hidden formulas, Ctrl+~ won't reveal the formula text either. The highlighter classifies based on what ExcelJS reads from the file. Behaviour on hidden/protected formula cells can differ from your expectation, so verify a protected sheet visually after running.
The highlighter shows values, not formula text
By designUnlike Ctrl+~, the highlighter does not replace values with formula text — it leaves the cell display alone and adds a colour. If you specifically want to SEE the formula strings, keep using Ctrl+~; if you want to KNOW which cells are formulas without losing the values, use the highlighter.
Existing fills are overwritten
OverwrittenEvery non-empty cell gets the blue or amber fill, replacing any prior fill. Ctrl+~ never touches your formatting; the highlighter does. Run it on a copy if your workbook already uses colour conventions you want to keep.
Hardcoded zeros and labels are amber
ExpectedA typed 0 or a text label is non-empty, so it's coloured amber — whereas under Ctrl+~ those cells just display normally. The amber count therefore includes labels and zeros, not only numeric assumptions.
Formula errors stay blue
FormulaA cell that evaluates to #REF! or #N/A is still a formula cell and colours blue, even though Ctrl+~ would show its formula text. To list errors with addresses, use the Error Locator.
Free tier can't use the permanent map
Pro requiredCtrl+~ is free in Excel; the permanent colour map here is Pro-minimum. Non-Pro accounts get "Formula Highlighter requires Pro tier."
CSV has nothing for either approach
Not supportedA CSV has no formulas, so neither Ctrl+~ nor the highlighter has anything to reveal. Only .xlsx is accepted by the tool.
Frequently asked questions
How is this different from pressing Ctrl+~?
Ctrl+~ (Show Formulas) is a temporary, single-sheet view that replaces values with formula text and resets when you close the file. The highlighter writes permanent blue/amber fills across all sheets, leaves the cell values visible, and the colours survive save and reopen. It tells you which cells are formulas without hiding the numbers.
Does the colour coding survive save and reopen?
Yes. The fills are standard XLSX cell formatting, so they persist through save, close, and reopen — the key advantage over Ctrl+~, which is a view-only toggle that resets every time.
Will the colours move if I sort or filter the data?
Yes. Cell fills travel with their cells during sort and filter. You can even use Data → Filter → Filter by Color to isolate all amber (hardcoded) cells — something Ctrl+~ can't do because it's an overlay, not data.
Can I see the actual formula text like Ctrl+~ shows?
Not with this tool — it colours cells, it doesn't display formula strings. If you need to read the formulas themselves, keep using Ctrl+~, or use the Formula Explainer to decode a specific formula into plain English.
Can I change the colours?
No. There's no colour picker; the fills are fixed at blue FFBDD7EE and amber FFFFEB9C. Run the tool, then swap the fills in Excel via Find & Replace by format if you need different colours.
Why does a shared-formula block look all-formula under Ctrl+~ but partly amber here?
Ctrl+~ translates and displays the formula in every shared-formula cell, so they all look like formulas. ExcelJS only exposes a formula key on the master cell; the followers expose sharedFormula and read as amber. It's a file-storage artefact (common in LibreOffice-saved files), not a data difference — click a follower and you'll see it does carry the shared formula.
Does it work on every sheet at once?
Yes — that's the default and only behaviour. The tool loops every worksheet in one pass, unlike Ctrl+~ which only affects the active sheet. No per-sheet toggling.
How do I remove the colours to get back to a plain workbook?
In the coloured copy, select all (Ctrl+A) and choose Home → Fill Color → No Fill (or Clear → Clear Formats). Or simply keep working from your original file, which was never modified — the tool outputs a separate formula-highlighted.xlsx.
Will it reveal formulas on a protected sheet with hidden formulas?
Behaviour on protected/hidden-formula cells can differ from a fully editable sheet, just as Ctrl+~ won't show hidden formula text on a protected sheet. Verify a protected sheet visually after running, and unprotect it first if you need a reliable map.
Is my workbook uploaded to a server?
No. ExcelJS runs entirely in your browser; the file never leaves your machine. Ctrl+~ is also local, so you keep the same privacy while gaining a permanent, shareable colour map.
Why is the count useful?
Ctrl+~ gives you no summary. The highlighter reports formula and hardcoded cell counts, so you instantly see how formula-driven the workbook is. Remember the hardcoded count includes text labels and typed zeros, so judge numeric density visually too.
What pairs well with this for a deeper audit?
Use the Error Locator to list error cells, the Dependency Map to trace precedents (the visual equivalent of Trace Precedents across the file), the Circular Reference Finder for loops, and Formula to Value to freeze formulas once you're done auditing.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.