Cheat Sheet for Excel: Formulas Worth Asking AI For

A cheat sheet for Excel used to mean a printed list taped next to the monitor. Now it is more often a chat window: type the task, get a formula back. That works well for some of what a cheat sheet is for, and badly for the rest. The split is not about which formula you need — it is about whether the answer depends on syntax the model has seen a million times, or on numbers only you have.

The ten worth knowing by name

These cover most of what comes up in ordinary office work. Knowing the name is enough to ask for the rest — you do not need to memorise the exact syntax of any of them.

• —SUM, AVERAGE, COUNT — the three you already know, and the baseline every other formula on this list builds on.
• —IF — one condition, two outcomes. "Over budget" or "on budget", nothing in between.
• —SUMIF, COUNTIF, AVERAGEIF — the same three arithmetic functions, but only for rows that meet one condition.
• —SUMIFS — the multi-condition version, for when "this region, this month, this product" all have to hold at once.
• —XLOOKUP, or INDEX/MATCH on an older file — look a value up in one column and pull back the matching row from another.
• —IFERROR — wraps any formula above and swaps a #N/A or #DIV/0! for a message you chose.
• —LEFT, RIGHT, MID — pull a fixed slice of text out of a cell, useful for codes and IDs that follow a pattern.
• —TEXTJOIN — combines several cells into one string with a separator you set, the newer and more forgiving cousin of CONCATENATE.
• —A conditional formatting rule — not a formula you type into a cell, but the same logic, applied as colour instead of a result.
• —A PivotTable — not a formula either, but the tool that replaces most of the above once the question becomes 'summarise this by category and month'.

The formula bar is where you read back exactly which of these a cell is actually running — worth checking before you trust a sheet someone else built. For the lookup and multi-condition formulas specifically, Excel's advanced functions covers the syntax each one expects in more depth than a cheat sheet can.

Asking AI for the syntax

A chat tool is reliable for the part of this that is really a lookup problem: which function, which argument order, which punctuation. That is well-represented territory — Excel formula syntax has been documented and discussed online for three decades, which is exactly the kind of pattern a language model reproduces accurately. Vendor guidance from OpenAI and Anthropic both make the same point in different words: the more precisely you describe the task, the more precisely you get back what you meant, rather than a plausible guess at what you meant.

Weak prompt: "Excel formula to total sales by region."

Better prompt: "I have a table with headers Region (column B) and Sales (column D), data starting row 2 through row 400. Write a SUMIF formula in a new cell that totals Sales where Region equals the value in cell F1."

The second version gets you a formula you can paste in and check immediately, because it names the exact ranges rather than leaving the model to guess your layout. How to write a prompt that works on the first try covers the same pattern for tasks well beyond Excel: specificity about the actual inputs is what the first prompt was missing, not a smarter model.

Where this goes wrong

The failure mode is not the syntax — it is the model answering a question about your data as if it were also a syntax question. Ask it to total a column and it will hand back a formula, correctly. Ask it what that column adds up to, with the numbers pasted into the chat rather than live in a sheet, and it may compute it wrong, or round differently than you expect, and say so with exactly the same confidence as the correct answer. A survey of hallucination in large language models describes this directly: fluent, well-formed output is not evidence that the content is right, because the model is not checking its answer against your spreadsheet — it is predicting what a plausible answer looks like.

This is also why whether ChatGPT can actually read an Excel file is worth checking before you rely on it for anything beyond formula syntax. Pasting fifty rows into a chat box and asking for a total is not the same operation as writing a SUM formula that Excel itself evaluates — one trusts a model's arithmetic, the other trusts Excel's.

The checks, before you trust the number

• —Paste a formula into a cell and look at the result immediately — never copy a formula into a report without seeing it run once.
• —For anything with a range ($A$2:$A$400), confirm the range actually covers all your rows, not just the ones visible when the model guessed.
• —If the model computed a number directly rather than giving you a formula, redo it in Excel with SUM or SUMIF and compare — treat any mismatch as the model being wrong, not the sheet.
• —Wrap lookups in IFERROR before sharing a sheet, so a missing match shows a message instead of a cryptic #N/A.
• —Check AutoSum picked the range you meant — it guesses, and the guess is sometimes one row short.

This is the same caution the NIST AI Risk Management Framework puts in more formal terms for organisations deploying AI tools generally: know what the tool is reliable at, verify the output where it matters, and do not extend trust from one task to a different one just because both involved the same chat window.

What to do with this on Monday

Keep the ten names above somewhere you can see them, and use a chat tool for the syntax, not the arithmetic. If a formula touches money that someone else will see — a forecast, an expense report, a number in a deck — run it once in a test cell with numbers you already know the answer to before trusting it on the real range. Building a report in Excel and reading an existing table correctly both lean on the same ten formulas, so getting comfortable with this list pays off well beyond any one sheet.

Treating AI as reliable for syntax and unreliable for your actual numbers is also the broader skill worth building. The wage premium for workers with measurable AI skills grew to 62% according to PwC's 2026 analysis of job postings — not because those workers trust AI blindly, but because they know which parts of a task it is actually good at. Coursium teaches that distinction directly, with short lessons built around exactly this kind of worked example rather than abstract theory. Learn more about how the lessons work.