Tool 06 · Data lab

Excel Formula Explainer

FORMULA :: STEP BY STEP

Paste any Excel formula and read it in plain English, one step at a time from the inside out, with the usual mistakes flagged and a lesson for each function. Free, and nothing leaves your browser.

100% in your browser: your data never leaves your device

STEP 01

Paste a formula

| from Excel or Google Sheets, with or without the =
Try one

How do you read a long Excel formula?

Start at the innermost brackets and work outwards, because that is the order Excel works it out in. Each function hands its answer to the one around it. Take this lookup, which finds the price of P-103:

  • A2:A5 step 1: MATCH looks down the codes
  • A4 P-103 is 3rd, so MATCH gives 3
  • C2:C5 step 2: INDEX takes the 3rd price
  • E2 the answer

=INDEX(C2:C5, MATCH("P-103", A2:A5, 0)) →2.25

Read from the inside: MATCH("P-103",A2:A5,0) looks down the codes and finds P-103 in third place, so it gives 3. Then INDEX(C2:C5, 3) takes the third price, 2.25. The explainer above numbers the steps the same way and lights up each part of the formula as you point at its step.

What do the arguments of a function mean?

Each function takes its inputs in a fixed order, separated by commas. For VLOOKUP that's what to look for, where to look, which column to bring back, and whether the match must be exact:

=VLOOKUP(A2,$D$2:$F$50,3,FALSE)
✎ lookup_value, table_array, col_index_num, range_lookup. The explainer lists each argument beside the part of the formula it holds.

Square brackets in a function's help, as in [range_lookup], mean the argument can be left out. Leaving one out isn't always harmless: without FALSE, VLOOKUP does an approximate match. The VLOOKUP lesson shows what goes wrong.

Why does a formula work in one row and break when filled down?

Usually a range without $ signs. Fill =VLOOKUP(A2,D2:F50,3,FALSE) down one row and the table becomes D3:F51; a few rows later the first rows of the table are no longer in it. Lock the range as $D$2:$F$50 by pressing F4. The explainer flags ranges like this, and the locking the table range lesson lets you watch one slide. For a whole formula that's still wrong, why VLOOKUP is not working goes through the other causes.

Revision Q&A

(click a question) Lab module 06
Q1: Is the formula explainer free?

Yes, free with no sign-up. Paste a formula and the explanation appears as you type. It runs in your browser, so the formula is never sent anywhere.

Q2: Which functions does it understand?

Over 90 of the functions people use most: lookups (VLOOKUP, XLOOKUP, INDEX and MATCH), IF, IFS and IFERROR, SUMIFS and COUNTIFS, text, date and dynamic array functions such as FILTER and UNIQUE. A function it doesn't know is flagged, and the rest of the formula is still explained.

Q3: Why does it explain the formula from the inside out?

Because that is how Excel works it out: the innermost function first, then the one around it, using that result. In =INDEX(C2:C5,MATCH("P-103",A2:A5,0)) MATCH runs first and INDEX uses its answer.

Q4: What problems does it check for?

VLOOKUP or MATCH left on approximate match, ranges without $ that slide when the formula is filled down, IFERROR hiding errors it shouldn't, a column number past the end of the table, SUMIFS ranges of different sizes, division that can give #DIV/0!, and IFs nested three deep.

Q5: Does it work with Google Sheets formulas?

Yes, for the functions both share, which is most of them. Formulas written with semicolons between arguments, as in many European versions of Excel, work too.

Q6: Can I share an explanation?

Yes. The formula is kept in the page address, so Copy link gives a link that opens the same explanation. It is stored after the # sign, which browsers never send to the server.

Q7: Does it show the result of the formula?

No: it explains what the formula does, not the answer, because it doesn't have your data. To see results, the lessons let you type formulas into a spreadsheet in your browser and check them.

Know someone fighting with a spreadsheet? Send them this page.