The Excel Formulas Cheat Sheet provides ready-to-copy syntax for twelve source-checked Excel functions across math, logical, conditional, lookup, text, statistics, and date categories, presented in a searchable in-browser reference at /dev/excel-formulas-cheat-sheet/. Each entry stores a vendor-style argument signature, a short example, and a brief purpose statement, so the reference helps users recall the shape of a formula rather than reproduce the entire Excel function catalog. The goal is fast lookup when a formula is half-remembered: type the function name, the category, or the task, scan the matching row, and copy the signature into the workbook.
Unlike a documentation portal, the cheat sheet is intentionally compact. It exists to answer "what is the syntax, and is that argument optional?" quickly, without walking through full formula construction. Examples use English function names, comma separators, A1 references, and a leading equals sign. None of those defaults is guaranteed in a real workbook: regional settings can switch commas to semicolons, and some installations show localized function names. The cheat sheet calls out those adjustments explicitly and leaves the localization step to the user.
What the Twelve Functions Cover
The reference covers twelve widely used functions spread across seven categories. The compact list targets the formulas people reach for in daily reporting, finance, operations, and data cleanup work, rather than obscure statistical or financial functions. The exact coverage is shown in the table below.
| Function | Category | Primary purpose |
|---|---|---|
| SUM | Math | Add a range of numbers. |
| AVERAGE | Statistics | Compute the arithmetic mean of a range. |
| IF | Logical | Return one of two values based on a condition. |
| COUNTIF | Conditional | Count cells matching a criterion. |
| SUMIF | Conditional | Sum values associated with matching cells. |
| XLOOKUP | Lookup | Look up a value in one array and return a value from another. |
| INDEX | Lookup | Return a cell value at a given position in a range. |
| MATCH | Lookup | Return the relative position of a value in a range. |
| TEXT | Text | Convert a numeric value to a formatted text string. |
| ROUND | Math | Round a number to a specified number of digits. |
| CONCAT | Text | Join text strings into one string. |
| TODAY | Date | Return the current date as a serial number. |
Several of these functions overlap in behavior but differ in output. SUMIF adds values, while COUNTIF only counts them. MATCH returns a position, while INDEX returns a value at a position, and the two are often combined. TEXT, CONCAT, and ROUND all change a value in a way that can affect downstream arithmetic or sorting. Treating each function's output as a separate type is the safest mental model when wiring them together. For a wider walkthrough of the same twelve functions across daily reporting tasks, see the Excel Formulas Cheat Sheet bulk guide covering the twelve daily functions.
How to Find a Formula Example Step by Step
The cheat sheet works as a four-step lookup workflow. The steps are deliberately short because the goal is to recall syntax, not to walk through formula construction.
- Search by function, category, or task. Type the function name (for example, "XLOOKUP"), the category ("lookup"), or a phrase describing the goal ("find a value"). The reference matches against normalized name, category, signature, and purpose text.
- Read the signature and note which bracketed arguments are optional. Each entry shows the argument list with square brackets indicating optional arguments. These brackets are notation and should not be typed into the formula.
- Compare the example with your ranges, locale, and Excel version. Confirm that the cell references, separators, and function name match your workbook. Swap commas for semicolons if your regional setting uses them.
- Copy the syntax and validate the completed formula with known test cases. Paste the signature into the formula bar, fill in the arguments, and run the formula against a few known inputs to confirm the result before relying on it for live work.
The reference runs entirely in the browser. It does not open, upload, evaluate, or modify a workbook, and it cannot see the Excel version in use. That isolation is intentional: the cheat sheet never executes a formula, so it cannot be tricked by malicious input, and it cannot leak workbook data.
Reading the Signature: Brackets, Ellipses, and Locale
Signatures in the cheat sheet follow vendor-style notation. Square brackets mark optional arguments, ellipses indicate that additional arguments may follow, and commas separate arguments in the displayed examples. Three habits prevent the most common signature mistakes.
- Do not type the square brackets. A signature such as IF(condition, value_if_true[, value_if_false]) means the third argument can be omitted; the literal brackets must not appear in the cell.
- Treat ellipses as continuation markers. A signature like CONCAT(text1[, text2, ...]) shows that more arguments can follow. The actual formula includes only the arguments used.
- Match the argument separator to your locale. A workbook configured for comma-as-decimal may use semicolons as the list separator, in which case every example with commas must be rewritten.
Function names may also appear localized in some Excel installations. The cheat sheet always shows English names in its examples, so a workbook in a German or French locale would need substitutions like WENN for IF or SOMME for SUM. The function purpose stays the same; only the spelling changes.
Lookup Function Examples: XLOOKUP, INDEX, MATCH
Lookup functions demand more care than the others in the list because they return results from positions or arrays and assume certain matching conditions. XLOOKUP accepts a lookup array and a return array as separate arguments, plus optional arguments for the not-found message, match mode, and search mode. A typical signature looks like XLOOKUP(lookup_value, lookup_array, return_array[, if_not_found[, match_mode[, search_mode]]]). Exact and approximate matching behave differently: exact match demands a one-to-one correspondence, while approximate match assumes a sorted lookup array and can return misleading values on unsorted data.
MATCH returns a relative position within a range, not the value at that position. INDEX takes a position and returns the value at that position in a separate range. Used together, INDEX(return_range, MATCH(lookup_value, lookup_array, 0)) reproduces a two-dimensional lookup that older VLOOKUP formulas struggle to express. The cheat sheet lists 0 as the safe match mode for exact lookup, because 1 and -1 rely on sorted data assumptions that are easy to miss.
Before trusting any lookup result in financial, operational, or reporting work, inspect duplicates, blanks, text-versus-number mismatches, and error values in both arrays. A formula that returns a number can still encode the wrong row or the wrong array if the ranges are swapped or absolute references are dropped.
Conditional and Text Examples: COUNTIF, SUMIF, IF, TEXT, CONCAT
Conditional functions depend on criteria syntax. COUNTIF(range, criterion) counts cells that match the criterion, while SUMIF(criteria_range, criterion, sum_range) adds values associated with the matching cells. Quotation marks are required for many text criteria and operators, for example COUNTIF(A:A, ">100") or SUMIF(B:B, "Apples", C:C). A criterion written without quotes often returns 0 or a syntax error.
A concrete worked example makes the syntax concrete. Given the range B2:B4 containing Apples, Pears, Apples and the range C2:C4 containing 10, 15, 20, the formula =SUMIF(B2:B4, "Apples", C2:C4) adds the values in C2 and C4 where the criterion "Apples" matches, returning 30. The arithmetic is 10 + 20 = 30.
The IF function chooses between two return values but does not validate that the underlying business rule is correct. A formula like IF(A1 > 0, "Profit", "Loss") returns "Profit" for any positive number; it does not check whether the value represents a real transaction, whether the row should be excluded, or whether the comparison should reference a different cell. Successful evaluation is not the same as correct logic.
TEXT(value, format_text) converts a numeric value to formatted text, which can affect later arithmetic and sorting. A column formatted with TEXT(A1, "yyyy-mm-dd") cannot be summed or averaged without first being converted back. CONCAT(text1, text2, ...) joins text strings but does not automatically add delimiters; concatenating first and last names requires an explicit space. ROUND(value, num_digits) changes the underlying value, not only its display, so downstream calculations will use the rounded figure. Each of these behaviors is easy to miss until a downstream report breaks.
Audit and Validate Before Trusting the Result
A formula that calculates successfully can still encode the wrong range, omit rows, use an unintended absolute reference, or hide an error behind a fallback. Audit precedents and test known cases before deploying. The cheat sheet's role ends at syntax; the verification step is on the user.
Three habits reduce the risk. First, save a recoverable copy of the workbook before changing important formulas. Second, recalculate and inspect representative rows after edits rather than trusting one visible cell. Third, confirm named ranges — a formula that references Sales_Total instead of C2:C100 depends on the named range pointing at the right cells.
Function availability also varies by Excel version. XLOOKUP, for example, is not present in some older perpetual releases of Excel. The cheat sheet does not detect the user's version, so compatibility should be confirmed against the linked vendor documentation at Microsoft Excel functions alphabetical and the target release. For decisions involving money, compliance, safety, or large datasets, use independently checked examples, error handling, workbook protection, peer review, and controlled test data before deployment.
The cheat sheet's compact scope is its main strength: twelve functions, twelve signatures, twelve examples, no workbook upload, no formula evaluation. For quick recall work in a browser, that is exactly enough.