Excel Formulas Cheat Sheet Explained: 12 Essentials

The Excel Formulas Cheat Sheet is a compact, browser-based reference for 12 widely used spreadsheet functions, with searchable syntax, examples, and copyable argument signatures.

Its purpose is practical: help you recall the shape of a formula, choose an appropriate function, and assemble a working formula for a particular workbook. The covered functions span math, logical, conditional, lookup, text, statistics, and date tasks. The reference is intentionally narrower than the full Excel function catalog and does not replace Microsoft documentation.

It is also a reference rather than a spreadsheet engine. The tool runs entirely in the browser and never opens, uploads, evaluates, or modifies a workbook. It cannot detect your Excel version or regional settings, so the final formula must be adapted and tested in the target application.

How to Read an Excel Formula Signature

Excel formulas begin with an equals sign. The reference examples use English function names, commas, A1-style references, and a leading equals sign. A function generally consists of a function name followed by arguments in parentheses. Each argument can be a value, a cell or range reference, or another expression whose result supplies the required input.

Reference signatures use special notation to show optional parts:

• Square brackets mark optional arguments. They are normally not typed into the formula.
• An ellipsis indicates that additional arguments may follow.
• Commas separate arguments in the displayed examples, although some regional configurations require semicolons.

For example, a signature containing =IF(test,yes,[no]) shows that the second return value is optional. The square brackets are explanatory notation, not part of a valid formula. A copied signature is therefore a starting point: replace its placeholders with actual values or references and adjust it for the destination workbook.

Parentheses also affect how Excel interprets the parts of a formula, so preserve them while substituting arguments. If the workbook uses localized function names, translated date formats, different decimal notation, or semicolon separators, adjust those elements before entering the formula.

Find and Validate a Formula in Excel

1. Define the result you need. Decide whether you are calculating a total, counting matches, selecting one of two outcomes, finding a value, joining text, or producing a date. This prevents choosing a formula with the wrong job.
2. Search the reference. Enter a function name, category, signature, or purpose. Narrowing the list by task can reveal useful functions that would be difficult to recall from memory.
3. Read the signature and example. Identify required arguments first, then note optional arguments shown in square brackets. Confirm that the example uses a data shape comparable to your workbook.
4. Compare ranges, locale, and compatibility. Check whether the example expects a single cell, a column, a row, or a larger table. Verify named ranges, table names, data types, the argument separator, and whether the function exists in your Excel version.
5. Copy and adapt the syntax. Replace placeholders with the correct values or references, remove the explanatory square brackets, and change commas to semicolons if required. Keep raw values as numbers whenever later calculations depend on them.
6. Test with known cases. Enter the completed formula in the target workbook and test cases with expected results, including relevant blanks, duplicates, mismatched types, or missing keys. Recalculate and inspect representative rows rather than trusting a single visible cell.

The 12 Verified Functions and Their Jobs

The reference includes the following functions. The argument patterns show how the functions are commonly assembled, but they still need to be adapted to the actual workbook.

Function	Best for	Argument pattern	Important consideration
SUM	Adding numeric values	=SUM(number1,[number2],...)	Confirm that every intended input range is included.
AVERAGE	Calculating an average	=AVERAGE(number1,[number2],...)	Check that excluded or nonnumeric inputs match your intent.
IF	Selecting one of two return values	=IF(logical_test,value_if_true,[value_if_false])	It evaluates a condition but does not prove the business rule is correct.
COUNTIF	Counting cells that meet criteria	=COUNTIF(range,criteria)	Text criteria and operators often require quotation marks.
SUMIF	Summing values associated with matching cells	=SUMIF(range,criteria,[sum_range])	Confirm that the matching range and summed range align.
XLOOKUP	Finding and returning a matching value	=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])	Check exact or approximate matching, duplicates, missing keys, and version availability.
INDEX	Returning a value from a specified position	=INDEX(array,row_num,[column_num])	The row and column positions must correspond to the intended array.
MATCH	Finding the relative position of a match	=MATCH(lookup_value,lookup_array,[match_type])	It returns a position rather than the matched value itself.
TEXT	Converting a value into formatted text	=TEXT(value,format_text)	The result is text, so later arithmetic or sorting may behave differently.
ROUND	Rounding a number	=ROUND(number,num_digits)	It changes the underlying value rather than only its display.
CONCAT	Joining multiple text values	=CONCAT(text1,[text2],...)	It does not add delimiters automatically.
TODAY	Returning the current date	=TODAY()	It changes when the workbook recalculates.

When to Use Math or Conditional Formulas

SUM and AVERAGE handle basic numeric analysis, but the function name is only one part of the calculation. Selecting the wrong range, including an unintended header row, or using an absolute reference incorrectly can produce a valid-looking but incorrect result. Confirm the inputs and inspect the cells that feed the formula.

COUNTIF and SUMIF solve related but different problems. COUNTIF counts cells that satisfy criteria, while SUMIF adds values associated with cells that satisfy the same type of criteria. For example, =COUNTIF(StatusRange,"Paid") counts matching status cells, whereas =SUMIF(StatusRange,"Paid",AmountRange) adds values from the associated amount range. Replace these illustrative names with the actual ranges in your workbook. Quotation marks are required for many text criteria and operators.

IF chooses between two return values according to its logical test. The syntax can work perfectly while the underlying condition still encodes the wrong rule. An optional fallback can also hide an error instead of revealing it. Check the condition, both possible outcomes, and the precedents that feed them.

Lookup Formulas: Check the Match Before the Result

XLOOKUP accepts separate lookup and return arrays, plus optional not-found, match, and search behavior. This makes it useful for returning a value associated with a key, but it also makes careful configuration important. A missing key, duplicate key, approximate match, or wrong return array can each lead to a misleading outcome.

MATCH and INDEX divide the lookup task into two parts. MATCH returns the relative position of a match, while INDEX returns a value at a specified position. They are often combined, but combining them does not remove the need to check the selected row and column.

Before trusting a lookup result, confirm:

• Whether the match mode is exact or approximate and whether its assumptions fit the data.
• Whether unsorted data could make an approximate lookup misleading.
• Whether duplicate keys create ambiguous matches.
• Whether blanks are intentional values or missing data.
• Whether text-versus-number mismatches are preventing valid matches.
• Whether error values appear in the lookup or return range.

If an if_not_found fallback is used, make sure it does not conceal a bad key or incomplete source range. A fallback can make a report look complete while hiding a data-quality problem.

Text, Date, and Rounding Are Not Interchangeable

• TEXT converts a numeric value into formatted text. That text can behave differently in later arithmetic, sorting, exports, or automation. When the value must remain numeric, use display formatting rather than converting it with TEXT.
• ROUND changes the stored value to the requested precision. It is not merely a visual adjustment, so use it when downstream calculations should receive the rounded number.
• CONCAT joins text values but does not insert spaces, punctuation, or other delimiters automatically. Add any required separator as part of the text being joined.
• TODAY returns the current date and is volatile with respect to recalculation. Its displayed value can change when the workbook recalculates, which matters for reports that need a fixed reference date.

These distinctions matter whenever one formula feeds another calculation, an export, or an automated process. Check the data type at each boundary rather than judging only by the cell's final appearance.

Validate the Formula in the Target Workbook

Formula availability and syntax can vary by Excel release. In particular, XLOOKUP is not present in some older perpetual releases. A reference entry can help you remember its shape, but it cannot certify that your installation supports it. For critical syntax or version questions, consult Microsoft's Excel functions alphabetical reference and test in the target version.

Use a repeatable validation process before publishing or automating a workbook:

• Save a recoverable copy before changing an important file.
• Test known cases with expected results and inspect error handling around each formula.
• Confirm ranges, omitted rows, named ranges, table names, and absolute or relative references.
• Audit precedents and verify that fallback values do not hide upstream problems.
• Recalculate the workbook and inspect representative rows from different parts of the data set.
• Use workbook protection, independently checked examples, controlled test data, and peer review for decisions involving money, compliance, safety, or large data sets.

Know the Boundary of the Reference

The compact list deliberately covers 12 common functions rather than the entire Excel catalog. It is useful for recalling a function name, understanding argument order, and finding an example before you build a formula. It does not evaluate your data, audit business logic, prove that a range is complete, or guarantee compatibility with a particular Excel version.

For functions outside the list, version-sensitive behavior, or consequential financial and operational logic, combine the cheat sheet with official documentation and controlled workbook testing. The safest workflow is to find the likely function, adapt its signature to the real ranges and locale, and validate the completed formula where Excel will actually run it.

For a deeper look, see How to Remove Excel Links: Cell and Workbook Links.

For a deeper look, see Excel Keyboard Shortcuts Alternative: Windows, Mac, Web.