Explain or Write Spreadsheet Formulas in Plain English

Workflow area: Spreadsheets & Data · Tier: Foundation · Hands-on time: ~25 minutes

Formulas are where office AI pays off fastest: the syntax is fiddly, the documentation is dense, and a model translates "average deal size per region, but only closed deals" into AVERAGEIFS in seconds. The discipline that separates a power user from a future incident is the second half of this playbook — you never paste a formula you don't understand, and you always test it against a case where you already know the answer.

When to use — and when not to

• Use it to write formulas from intentlookups, conditional sums and counts, date arithmetic, text splitting and cleaning, nested IF logic you can describe but not spell.
• Use it to decode inherited workbooksthe 400-character nested formula a departed colleague left behind, explained in plain English before you dare touch it.
• Use it to fix errorspaste the formula plus the error (#N/A, #REF!, #VALUE!) and the fix usually comes back with the reason.
• Skip it for one-click worksums, sorts and basic filters are faster by hand than by prompt.
• Skip pasting whole confidential workbooksthe formula question rarely needs the data. Describe the columns; leave the numbers behind.

The recipe

1. 1.

   Describe the sheet, then the goal. The model cannot see your workbook. Column letters, what each holds, and one example row beat any amount of adjectives:

   Copy

   I am in [Excel / Google Sheets].
   
   My sheet:
   - Column A: order date (e.g. 14-Jul-2026)
   - Column B: region (North / South / East / West)
   - Column C: status (Won / Lost / Open)
   - Column D: deal value in ₹ (e.g. 250000)
   
   In cell G2, I want: the average deal value for the region named
   in F2, counting only "Won" deals from the last 90 days.
   
   Give me the formula, then explain each part in one line each,
   then tell me one edge case where it could give a wrong or
   misleading answer.
   

2. 2.

   Read the explanation before the formula. Good output:

   Copy

   =AVERAGEIFS(D:D, B:B, F2, C:C, "Won", A:A, ">="&TODAY()-90)
   
   - AVERAGEIFS(D:D, ...) — averages deal values where ALL
     conditions match
   - B:B, F2 — region must equal the value in F2
   - C:C, "Won" — status must be exactly "Won"
   - A:A, ">="&TODAY()-90 — order date within the last 90 days
   
   Edge case: if no deals match, it returns #DIV/0! — wrap in
   IFERROR to show "no data". Dates stored as text will silently
   fail the date condition.
   

   The edge-case line is the professional's habit: every formula has one, and asking for it up front means you find out before the review meeting.

3. 3.

   Test against a known answer. Before trusting the formula, verify it on a case you can check by hand: filter the sheet to one region manually, eyeball the matching rows, and confirm the formula's number. One verified cell buys confidence in ten thousand.

4. 4.

   Decode before you modify. For inherited formulas, reverse the direction:

   Copy

   Explain this formula step by step, in plain English, assuming I
   know basic Excel but not these functions. Then tell me: what
   breaks if rows are added above row 5?
   
   [PASTE FORMULA]
   

   Never edit a formula you cannot narrate. If the explanation surprises you, the workbook's author may have made the same mistake — check the output, not just the syntax.

5. 5.

   Ask for the robust version. Once the formula works, one more prompt: "Make it robust: handle empty cells, wrap errors with a clear message, and note if a newer function (XLOOKUP, FILTER, LET) does this more cleanly." Modern functions are usually simpler and easier for the next person to read — and the next person is often you.

Make it yours

• Copilot in Excel: ask in the sidebar — it sees your actual table, so you can skip the column description; it can insert the formula directly. Still ask "explain what you just inserted" before accepting, and know that very large or messy ranges degrade its suggestions.
• Gemini in Google Sheets: same pattern in the side panel; Sheets' ARRAYFORMULA, QUERY and IMPORTRANGE are its home turf where Excel answers differ — always state which app you are in.
• ChatGPT or Claude with a sample: paste ten representative, non-sensitive rows and ask it to build and dry-run the formula against them, showing the expected result — this catches date-format and text-vs-number traps before you touch the real workbook.
• Beyond formulas: the same recipe writes conditional-formatting rules, data-validation lists and pivot-table configurations — describe columns and intent, demand the explanation, test on a known case.

Quality check

• You can narrate the formulaevery function and range, one sentence each, without notes.
• Tested against a hand-checked caseat least one cell verified manually before the formula ships.
• Edge cases namedyou know what happens with empty cells, no matches, text-formatted dates.
• Right dialectExcel and Google Sheets differ (XLOOKUP availability, QUERY, separator conventions); the model was told which one you use.
• The next reader survivesprefer the readable modern function over the clever nested one.