# Formula Explainer: see how any Excel or Google Sheets formula works

> Paste a formula and see every part of it calculated on real data, in order, with each function's arguments named and common mistakes flagged.

Source: https://spreadsheetpractice.com/tools/formula-explainer · Updated October 29, 2026

## What it does

Paste an Excel or Google Sheets formula and see every part of it calculated on real data, in order, with each function's arguments named.

- Step-by-step values, innermost first
- Checks for approximate VLOOKUPs, unlocked ranges, quotes and more
- Use a sample table or paste your own

Open it at [Formula Explainer](https://spreadsheetpractice.com/tools/formula-explainer). It runs in the browser; nothing you paste is uploaded.

## What it checks

- VLOOKUP, HLOOKUP or MATCH without an exact-match argument
- Lookup tables and criteria ranges without $ signs, which move when the formula is filled
- Numbers typed into formulas that belong in their own cell
- Text criteria without quotes, and cell references inside quotes
- IFERROR hiding every error where IFNA would catch only "not found"
- Volatile functions, whole-column ranges in SUMPRODUCT, SUM(A1+A2), and IFs nested four deep
- Google Sheets functions in an Excel formula, and the reverse

## Worked examples

### Total units for one region in one month

`=SUMIFS(D2:D9,A2:A9,"East",F2:F9,">="&DATE(2026,3,1),F2:F9,"<"&DATE(2026,4,1))`

The date conditions are text joined to a number: ">="&DATE(2026,3,1) becomes ">=46082", the date's serial number. [Explain it](https://spreadsheetpractice.com/tools/formula-explainer?f=%3DSUMIFS(D2%3AD9%2CA2%3AA9%2C%22East%22%2CF2%3AF9%2C%22%3E%3D%22%26DATE(2026%2C3%2C1)%2CF2%3AF9%2C%22%3C%22%26DATE(2026%2C4%2C1)))

### Look up a price with XLOOKUP

`=XLOOKUP("Lamp",C2:C9,E2:E9,"Not found")`

XLOOKUP returns the first match from the top, and the fourth argument replaces #N/A when nothing matches. [Explain it](https://spreadsheetpractice.com/tools/formula-explainer?f=%3DXLOOKUP(%22Lamp%22%2CC2%3AC9%2CE2%3AE9%2C%22Not%20found%22))

### Two-way lookup with INDEX and MATCH

`=INDEX(A2:F9,MATCH("Fay",B2:B9,0),MATCH("Units",A1:F1,0))`

Each MATCH turns a label into a position; INDEX takes the row and column positions and returns the cell where they meet. [Explain it](https://spreadsheetpractice.com/tools/formula-explainer?f=%3DINDEX(A2%3AF9%2CMATCH(%22Fay%22%2CB2%3AB9%2C0)%2CMATCH(%22Units%22%2CA1%3AF1%2C0)))

### Clean up a name

`=PROPER(TRIM(A3))`

Inner functions run first: TRIM removes the extra space, then PROPER fixes the capitals. [Explain it](https://spreadsheetpractice.com/tools/formula-explainer?f=%3DPROPER(TRIM(A3)))

### Grade from a score with a lookup table

`=XLOOKUP(B4,D2:D6,E2:E6,,-1)`

Match mode -1 finds an exact match or the next smaller value, so 64 lands on the 60 row: a D. [Explain it](https://spreadsheetpractice.com/tools/formula-explainer?f=%3DXLOOKUP(B4%2CD2%3AD6%2CE2%3AE6%2C%2C-1))

### Average salary in one department

`=ROUND(AVERAGEIF(C2:C6,"Finance",D2:D6),0)`

AVERAGEIF averages only the rows whose department is Finance; ROUND tidies the result. [Explain it](https://spreadsheetpractice.com/tools/formula-explainer?f=%3DROUND(AVERAGEIF(C2%3AC6%2C%22Finance%22%2CD2%3AD6)%2C0))

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.