Formula map

Colour-code the sheets you choose and see how a spreadsheet is built, without opening formulas one at a time.

You will often need to review a model you did not build. Formula Map gives you a first read of it. Repeated calculations show up as solid bands of colour, and the cells that do not follow the pattern are easy to pick out.

Apply the map

Press Alt + Shift + Q, choose the sheets you want analysed, and apply the map. Every cell is coloured by what it is: a formula copied down, across or both ways, a one-off formula, a hardcoded number, a text label, a Boolean, a data validation input, or an error.

While the map is active an icon sits next to the Formula Explorer icon. Click it to reopen the legend, check which sheets are mapped, or remove the map. Which categories you show, and the colour each one gets, are set in Settings.

Formula Explorer: colour-coded formula map in Google Sheets

What the colours mean

A short worked example, mapped with the default palette.

ABCDE
1Q1Q2Q3Total
2Units120135148=SUM(B2:D2)
3Revenue1,1401,2831,443=SUM(B3:D3)
4Cost9001,0101,120=SUM(B4:D4)
5Gross=B3-B4=C3-C4=D3-D4=SUM(B5:D5)
6Overhead150150150=SUM(B6:D6)
7Net=B5-B6=C5-C6=D5-D6=SUM(B7:D7)
8Margin=B7/B3=C7/C3=D7/B3=SUM(B8:D8)

Column E is one formula copied down, so it reads as a single band. Rows 5, 7 and 8 were copied across. The inputs are yellow. D8 divides by the wrong row, which breaks the pattern its neighbours establish, so it is flagged rather than left to blend in. Column A stays uncoloured because text labels are switched off by default.

Formulas

Copied down
Copied across
Copied down and across
Unique formula

Cell contents

Number
Text labeloff by default
Boolean
Data validation input

References

Cross-sheet referenceoff by default
External reference (IMPORTRANGE)off by default
Formula without referencesoff by default

Flags

Inconsistent formula
Error

Every colour can be changed and every category can be switched off, in Settings. Unique formula and inconsistent formula share a colour out of the box; give them separate ones if you want to tell them apart at a glance.

Features

Remove
Colours restored on removal
Formula Map saves the colours it replaces, so removing the map puts your sheets back the way they were. Everything it needs to do that is kept inside your own spreadsheet, not on our servers.
Before you move cells
Renaming a sheet while a map is active is fine. Moving cells is not, so remove the map before you insert, delete, move or sort mapped cells.
Map
Copy direction
Blocks of matching formulas are identified as copied down, across, or both ways, so a repeated calculation reads as one block rather than fifty separate cells. Differences in spacing and layout are ignored.
Inconsistent formulas
A single cell that breaks an established pattern is flagged. The check is cautious by design and leaves totals rows and section boundaries alone, so a flag is usually worth a look.
References
Formulas reaching another sheet, pulling from another file with IMPORTRANGE, or using no references at all each get their own colour.
Identify
Hardcoded inputs
Numbers, text and Boolean values typed straight into cells are coloured separately from calculated ones, which makes manual inputs easy to find.
Inputs and errors
Data validation inputs and cells returning a formula error each get their own colour, so the places people type into and the places that are broken stand apart.
Control
Your colours
Any category can be recoloured or switched off in Settings. The defaults follow the conventions most reviewers already use.
Per-sheet control
Pick one sheet or several before you apply. Anything you did not select is left alone.
Legend on hand
While a map is active, an icon next to the Formula Explorer icon reopens the legend, shows which sheets are mapped, and removes the map.
Works inside Google Sheets.