| Formula | Syntax example | What it does | Planning use |
|---|---|---|---|
| SUM | =SUM(A1:A10) | Adds all numbers in a range | The base of most totals. Check the range covers every row you mean to include. |
| AVERAGE | =AVERAGE(A1:A10) | Calculates the average of a range of numbers | Use it next to SUM when a total alone hides how values are spread. |
| IF | =IF(A1>10, “Yes”, “No”) | Returns one value if a condition is true, another if false | Good for flags and labels that drive filters or reports. |
| IFERROR | =IFERROR(A1/B1, “Error”) | Returns a value if an error is found, otherwise the formula result | Wrap it around divisions and lookups, and keep the fallback visible. |
| VLOOKUP | =VLOOKUP(B1, A1:C10, 2, FALSE) | Looks up a value in a table and returns a corresponding value | A compact choice for a single lookup against a table. |
| INDEX | =INDEX(A1:C10, 2, 3) | Returns the value of a cell in a specified row and column | Use it once you know the row and column you need. |
| MATCH | =MATCH(100, A1:A10, 0) | Searches a range and returns the position of a value | Supplies the position that INDEX needs. |
| DATEDIF | =DATEDIF(“2023-1-1”, “2023-12-25”, “M”) | Calculates days, months or years between two dates | Suits durations such as tenure or time since an event. |
Syntax examples and descriptions follow the Dataquest Excel cheat sheet, which is organized by category for easy reference, while the wider formula families come from Exceljet’s formula library.
A good excel formulas cheat sheet pdf pairs each function with a syntax example and a plain-language purpose. Start with SUM, AVERAGE, IF, VLOOKUP and INDEX MATCH, then add date and error-handling formulas as your tasks demand.
The same habit of keeping short, reusable instructions helps with software generally, and our guide to the best ai tools for beginners shows where else it pays off.
What a formula reference changes in daily work
A compact reference shortens the gap between a spreadsheet problem and a working formula, because you copy a tested pattern instead of rebuilding syntax from memory. The effect depends on how the reference is organized and how much it tries to hold.

- Faster choice of function. Dataquest groups its examples by category, so you scan to the problem type (totals, conditions, lookups, dates) rather than reading every entry.
- Fewer syntax slips. Each example shows argument order and range style, so you only replace the cell references with your own.
- Safer failure handling. An error wrapper such as IFERROR sits in the reference as a standard pattern, which prompts you to decide what a failed calculation should show.
- A natural limit. A printable page cannot hold every function. Keep the formulas you use weekly on the page and send the long tail to a searchable library of worked examples.
The decision that matters most is scope. A sheet with eight to a dozen well-chosen formulas gets used. A sheet that tries to list everything is rarely read, and it duplicates what Exceljet already provides in searchable form.
The most useful Excel formulas
The most useful Excel formulas are the ones that cover totals, conditions, lookups and dates. Microsoft Excel offers many more functions, but these groups solve the bulk of everyday reporting tasks.
Totals and averages
SUM and AVERAGE are the starting point. Dataquest describes SUM as adding all numbers in a range and AVERAGE as calculating the average of a range of numbers, and both take the same range style, which makes them easy to pair. Put them side by side when you report a figure, so the reader sees the total and its typical value together.

Conditions and grouped sums
IF returns one value when a condition is true and another when it is false. That makes it the usual way to turn a number into a label, such as a pass or fail flag. For totals per category, Exceljet’s sum-by-group examples list SUMIF, SUMIFS and IF as the related functions, so treat a request like “total per region” as a SUMIF-type task and open the matching worked example for the exact arguments.
Lookups
VLOOKUP, XLOOKUP and INDEX with MATCH are the three lookup approaches that Exceljet lists among its key functions. Dataquest gives syntax for VLOOKUP, INDEX and MATCH. For XLOOKUP, copy the pattern straight from the Exceljet entry rather than writing it from memory, and note which of the three your team already uses so shared workbooks stay consistent.
Dates and error handling
DATEDIF calculates days, months or years between two dates, and IFERROR returns a chosen value when a formula errors out. Both are small additions that repay their space on a cheat sheet, since date gaps and divide-by-blank errors turn up in almost every working file.
VLOOKUP versus INDEX MATCH
VLOOKUP performs the whole lookup in a single formula, while INDEX MATCH splits the job into finding a position and then returning the cell at that position. The Dataquest examples show the split clearly.
The VLOOKUP example, =VLOOKUP(B1, A1:C10, 2, FALSE), takes the value to find, the table, a number and a final argument, then returns the corresponding value. MATCH searches a range and returns the position of a value. INDEX returns the value of a cell at a given row and column. Feed the position from MATCH into INDEX and you have the same kind of lookup built from two parts.
- Choose VLOOKUP when you want the shortest formula for a straightforward lookup and your table is already laid out the way the formula expects.
- Choose INDEX MATCH when you want the position as a separate, checkable result, or when you want to reuse that position for several INDEX calls.
- Consider XLOOKUP when your workbook standard allows it, and check its entry in Exceljet before rolling it out to colleagues.
Whichever you pick, each lookup example ends with a final argument (FALSE for VLOOKUP, 0 for MATCH). Keep that argument exactly as the reference shows it until you have confirmed what it does in your own data.
Edge cases that cause wrong answers
Most wrong results come from copying an example without adapting it. The reference examples use placeholder ranges, so the first fix is always to replace them with your own.
- Fallback values that hide problems. IFERROR returns your chosen value whenever an error is found, otherwise the formula result. The Dataquest example uses the visible text “Error” as the fallback. A visible word makes failures easy to spot, while a blank would let them pass unnoticed.
- Ranges that stop short. A range like A1:A10 covers exactly those cells. If the data grows past the last row, SUM and AVERAGE ignore the extra rows, so extend the range when you add data.
- Typed dates in DATEDIF. The example writes both dates as text in quotes. In a real workbook you will usually point to cells holding dates instead, and the final argument sets whether the result is days, months or years.
- Mismatched positions in INDEX. INDEX counts rows and columns inside the range you give it, so its row and column numbers only mean something relative to that range.
Building your own reference page
A personal reference beats a generic download because it reflects the formulas you actually write. Follow this order when you assemble it:
- Pick the categories you work in, such as totals, conditions, lookups and dates.
- Copy one syntax example per formula from the Dataquest cheat sheet and keep its purpose line beside it.
- Add a short note on the edge case that has bitten you before.
- Link to the matching Exceljet entry for anything that needs a full explanation.
- Save a copy where you can open it from any device, and print it if you prefer paper by your desk.
If you ask an AI assistant to draft a formula, check its answer against your reference before using it in a shared file. A clear request gets a better draft, and How To Write A Prompt: A Practical 2026 Guide To Clear, Effective Prompts – Worio explains how to word one. Teachers who keep gradebooks in spreadsheets can browse ai tools for teachers for other time-savers alongside these formulas.
Once the core set is stable, extend the page with functions that Exceljet lists among its key topics, such as FILTER, COUNTIFS, SUMIFS, SORT and TEXTSPLIT, adding only those you reach for repeatedly.
Frequently asked questions
How many formulas should a cheat sheet hold?
Only the ones you use regularly. Exceljet lists 1042 formulas with working examples for 1000+ common tasks, which is far more than a printable page can carry, so use the sheet for your core set and the library for everything else.
Does IFERROR repair the formula underneath?
No. According to Dataquest, it returns the value you choose if an error is found and otherwise returns the result of the formula, so the cause of the error remains to be fixed.
Can DATEDIF measure more than days?
Yes. Dataquest describes it as calculating the number of days, months or years between two dates, and its example uses “M” as the unit argument.
