Excel Formulas Cheat Sheet: 40 Essential Functions With Syntax (Printable PDF)
Formulas are where Excel earns its keep, and the same forty or so functions cover most spreadsheet work. This cheat sheet lists them by job, with the syntax and a worked example for each, in tables that print cleanly. Excel formulas are identical on Windows and Mac, so you only need one sheet. The keyboard shortcuts for writing and checking formulas differ, and those are at the end.
Quick answer: For lookups use XLOOKUP (or INDEX with MATCH in older versions), for conditional totals use SUMIFS and COUNTIFS, for decisions use IF with IFERROR as a safety net, and for text clean-up use TRIM, SUBSTITUTE and TEXTJOIN. Syntax for each is below. To keep a copy, press ⌘P and choose Save as PDF from the PDF menu.
How to use this cheat sheet
Square brackets around an argument mean it is optional. Commas separate arguments in most regions, but if your computer is set to a region that uses a comma as the decimal separator, Excel uses semicolons instead, so a formula copied from a web page may need its separators changed. Function names are not case sensitive. And the functions marked Microsoft 365 are not in older perpetual versions, so check yours if you get a #NAME? error.
Lookup and reference
| Function | Syntax | Example and use |
|---|---|---|
| VLOOKUP | VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) | =VLOOKUP(A2, Prices!A:C, 3, FALSE) finds a product and returns column 3. Use FALSE for an exact match |
| XLOOKUP | XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) | =XLOOKUP(A2, Prices!A:A, Prices!C:C, "Not found"). Looks left or right. Microsoft 365 and recent versions |
| INDEX | INDEX(array, row_num, [column_num]) | =INDEX(C2:C100, 5) returns the fifth value in the range |
| MATCH | MATCH(lookup_value, lookup_array, [match_type]) | =MATCH(A2, B2:B100, 0) returns the position of an exact match |
| INDEX with MATCH | INDEX(return_range, MATCH(value, lookup_range, 0)) | The classic two-way lookup that works in every version |
VLOOKUP is the one everyone learns first, and it has two traps. It can only look to the right of the lookup column, and a missing FALSE as the last argument gives approximate matches that look right and are wrong. XLOOKUP removes both problems and returns a clean message when nothing is found. If your workbook has to open in older versions, INDEX with MATCH is the safe alternative.
Logic and error handling
| Function | Syntax | Example and use |
|---|---|---|
| IF | IF(logical_test, value_if_true, [value_if_false]) | =IF(B2>=50, "Pass", "Fail") |
| IFS | IFS(test1, value1, test2, value2, ...) | =IFS(B2>=90, "A", B2>=80, "B", TRUE, "C"). Avoids deeply nested IFs |
| IFERROR | IFERROR(value, value_if_error) | =IFERROR(A2/B2, 0) replaces an error with 0 |
| AND, OR | AND(logical1, logical2, ...) | =IF(AND(A2>0, B2>0), "OK", "Check") |
| SWITCH | SWITCH(expression, value1, result1, ..., [default]) | =SWITCH(A2, 1, "Low", 2, "Medium", "High") |
Wrap a calculation in IFERROR only when you know what the error means. Using it to hide every problem makes a model look finished when it is not. Our guide to Excel for accountants on Mac explains why audit-friendly workbooks show errors early.
Maths and statistics
| Function | Syntax | Example and use |
|---|---|---|
| SUM | SUM(number1, [number2], ...) | =SUM(B2:B20) |
| SUMIFS | SUMIFS(sum_range, criteria_range1, criteria1, ...) | =SUMIFS(C:C, A:A, "North", B:B, ">100") adds values that meet every condition |
| COUNTIFS | COUNTIFS(criteria_range1, criteria1, ...) | =COUNTIFS(A:A, "North", B:B, ">100") |
| AVERAGEIFS | AVERAGEIFS(average_range, criteria_range1, criteria1, ...) | =AVERAGEIFS(C:C, A:A, "North") |
| ROUND | ROUND(number, num_digits) | =ROUND(B2, 2) rounds to two decimals |
| SUMPRODUCT | SUMPRODUCT(array1, [array2], ...) | =SUMPRODUCT(B2:B10, C2:C10) multiplies pairs and adds them |
| LARGE | LARGE(array, k) | =LARGE(B2:B100, 3) returns the third largest value |
SUMIFS and COUNTIFS take the range to add first and then pairs of range and criterion, which is the reverse of the older SUMIF order. A single mixed-up argument is the commonest cause of a total that is quietly wrong, so check one result by hand.
Text
| Function | Syntax | Example and use |
|---|---|---|
| LEFT, RIGHT | LEFT(text, [num_chars]) | =LEFT(A2, 3) returns the first three characters |
| MID | MID(text, start_num, num_chars) | =MID(A2, 4, 2) |
| LEN | LEN(text) | =LEN(A2) counts characters, spaces included |
| TRIM | TRIM(text) | =TRIM(A2) removes extra spaces |
| SUBSTITUTE | SUBSTITUTE(text, old_text, new_text, [instance_num]) | =SUBSTITUTE(A2, CHAR(160), " ") removes non-breaking spaces |
| TEXT | TEXT(value, format_text) | =TEXT(A2, "dd mmm yyyy") turns a date into formatted text |
| TEXTJOIN | TEXTJOIN(delimiter, ignore_empty, text1, ...) | =TEXTJOIN(", ", TRUE, A2:A6) joins a list |
| TEXTSPLIT | TEXTSPLIT(text, col_delimiter, [row_delimiter]) | =TEXTSPLIT(A2, ",") splits text into columns. Microsoft 365 |
Text functions are the heart of data clean-up. When you paste a table from a PDF, TRIM and SUBSTITUTE with CHAR(160) remove the hidden characters that stop formulas working, and the full workflow is in our guide to cleaning PDF data pasted into Excel.
Dates
| Function | Syntax | Example and use |
|---|---|---|
| TODAY | TODAY() | =TODAY() returns the current date and updates daily |
| EOMONTH | EOMONTH(start_date, months) | =EOMONTH(A2, 0) returns the last day of the month |
| EDATE | EDATE(start_date, months) | =EDATE(A2, 3) moves a date three months on |
| DATEDIF | DATEDIF(start_date, end_date, unit) | =DATEDIF(A2, B2, "m") counts complete months. Works but is undocumented in the function list |
| NETWORKDAYS | NETWORKDAYS(start_date, end_date, [holidays]) | =NETWORKDAYS(A2, B2) counts working days |
Dates are numbers underneath, which is why you can subtract one from another. If a date will not sort or calculate, it is probably text, and the same cleaning guide shows how to convert it.
Dynamic arrays
| Function | Syntax | Example and use |
|---|---|---|
| FILTER | FILTER(array, include, [if_empty]) | =FILTER(A2:C100, C2:C100>1000, "None") returns matching rows |
| UNIQUE | UNIQUE(array, [by_col], [exactly_once]) | =UNIQUE(A2:A100) lists each value once |
| SORT | SORT(array, [sort_index], [sort_order], [by_col]) | =SORT(A2:B100, 2, -1) sorts by column 2, largest first |
| SEQUENCE | SEQUENCE(rows, [columns], [start], [step]) | =SEQUENCE(12) returns 1 to 12 |
These functions return many values at once and spill into neighbouring cells. Leave room for the result, or Excel shows #SPILL!. They are available in Microsoft 365 and in recent versions of Excel, so check before sharing a workbook with someone on an older version.
Finance
| Function | Syntax | Example and use |
|---|---|---|
| PMT | PMT(rate, nper, pv, [fv], [type]) | =PMT(5%/12, 60, -20000) monthly payment on a loan |
| NPV | NPV(rate, value1, [value2], ...) | =NPV(8%, B2:B6) + B1 discounts later cash flows, then adds the initial one |
| IRR | IRR(values, [guess]) | =IRR(B1:B6) returns the internal rate of return |
| XNPV | XNPV(rate, values, dates) | =XNPV(8%, B1:B6, A1:A6) uses actual dates |
| FV | FV(rate, nper, pmt, [pv], [type]) | =FV(5%/12, 60, -200) value of regular savings |
Signs matter here. Money you pay out is negative and money you receive is positive, so PMT usually needs the loan amount as a negative number to return a positive payment, depending on how you set it up. NPV assumes the first value arrives at the end of the first period, which is why the example adds the initial amount separately. Our guide to Excel for finance professionals on Mac puts these to work.
What the errors mean
| Error | Meaning | Usual fix |
|---|---|---|
| #N/A | A lookup found no match | Check spelling and spaces, or wrap in IFERROR or use the if_not_found argument |
| #REF! | A referenced cell was deleted | Undo, or rewrite the reference |
| #VALUE! | Wrong type of value, often text in a calculation | Convert text to numbers |
| #DIV/0! | Division by zero or an empty cell | Test the divisor with IF |
| #NAME? | Unrecognised function or name | Check spelling, and your Excel version |
| #SPILL! | A dynamic array has no room to spill | Clear the cells in its path |
Keyboard shortcuts for working with formulas
The formulas themselves do not change between platforms, but the keys for entering and auditing them do.
⌘T or F4 cycles a reference through the four absolute and relative forms, such as $A$1, A$1, $A1 and A1. See the absolute reference guide.
⌃ with the grave accent key (the key above Tab) switches between showing formulas and showing values.
⇧F3 (with fn on a MacBook) opens the Insert Function dialog.
⌘= calculates the workbook now, useful when calculation is set to manual.
⌃⇧Return confirms an array formula in older versions.
⌃U edits the active cell, as described in the F2 guide.
The full lists are in our 50 essential shortcuts cheat sheet and the advanced shortcuts cheat sheet.
How to print this as a PDF
The tables on this page are wide, so print in landscape. Press ⌘P, choose landscape in the print options if offered, and use the PDF menu at the bottom left to save it. If you copy the tables into your own workbook to make a personal sheet, our guide to fitting an Excel sheet on one page as a PDF shows how to get it onto a single page, and the full export workflow is in how to save Excel as PDF on a Mac.
Common mistakes
Using VLOOKUP without FALSE and getting approximate matches.
Hiding every error with IFERROR.
Mixing up the argument order in SUMIFS and SUMIF.
Typing numbers into formulas instead of referencing a cell, which makes the model hard to audit.
Using a dynamic array function in a workbook that older versions of Excel will open.
Frequently asked questions
Are Excel formulas different on Mac?
No. Functions are the same on Windows and Mac. The keyboard shortcuts for entering them differ.
Should I use XLOOKUP or VLOOKUP?
XLOOKUP if everyone who opens the file has a version that supports it. Otherwise INDEX with MATCH, or VLOOKUP with FALSE.
Why does my formula return #NAME?
The function name is misspelled, or your version of Excel does not include it.
Why do I need semicolons instead of commas?
Your regional settings use the comma as the decimal separator, so Excel uses semicolons between arguments.
How many Excel formulas do I really need?
Most people get through the majority of tasks with about 20 functions. This sheet lists 40 to give you room to grow.