Excel Formulas Cheat Sheet
The Excel formulas and functions worth memorizing, grouped by the job they do. Each row gives you the task in plain English, the copy-paste syntax, and one line on when to reach for it. Everything here works in Excel and in Google Sheets except where noted.
Excel functions list, by category
| What you want | Formula | Notes |
|---|---|---|
| Add up a range | =SUM(A1:A10) | Totals every number in the cells you give it. Blanks and text are ignored. |
| Sum cells that meet one condition | =SUMIF(range, criteria, sum_range) | Adds only the values where a single test is true, like sales for one region. |
| Sum rows that pass every test | =SUMIFS(sum_range, range1, crit1, range2, crit2) | The multi-condition version. A row has to clear every criterion to be counted. |
| Multiply a list together | =PRODUCT(A1:A10) | Multiplies all the numbers in a range instead of adding them. More ways to multiply in Excel. |
| Round to a set number of places | =ROUND(number, num_digits) | Use 0 digits for whole numbers and 2 for cents. |
| Always round up or down | =ROUNDUP(number, digits) =ROUNDDOWN(number, digits) | Forces the direction no matter what the next digit is. Handy for pricing and time. |
| Drop the decimals | =INT(A1) | Chops a number down to its whole part. |
| Get the remainder | =MOD(number, divisor) | Returns what is left over after a division. Good for every-nth-row logic. |
| Strip the minus sign | =ABS(A1) | Returns the size of a number with no sign. Useful for variance and gaps. |
| Total that ignores hidden rows | =SUBTOTAL(9, range) | Sums only the rows left after a filter. Swap the 9 for 1 to average or 3 to count. |
| Average a range | =AVERAGE(A1:A10) | The arithmetic mean of the numbers in the range. |
| Average only matching rows | =AVERAGEIF(range, criteria, avg_range) | Averages the rows that pass one test, like average order value by channel. |
| What you want | Formula | Notes |
|---|---|---|
| Join text together | =CONCAT(A1, B1) | Glues cells into one string with nothing between them. |
| Join with a separator | =TEXTJOIN(", ", TRUE, A1:A5) | Adds a delimiter between each value, and the TRUE tells it to skip blanks. |
| Grab characters from an end | =LEFT(text, num_chars) =RIGHT(text, num_chars) | Pulls a set number of characters from the start or the finish of the text. |
| Pull from the middle | =MID(text, start_num, num_chars) | Extracts characters starting at any position you point it to. |
| Measure text length | =LEN(A1) | Counts the characters in a cell, spaces included. |
| Clean up stray spaces | =TRIM(A1) | Removes extra spaces. This is the usual fix for a lookup that will not match. |
| Change the case | =UPPER(A1) =LOWER(A1) =PROPER(A1) | Caps, lowercase, or Title Case. |
| Find and replace inside a cell | =SUBSTITUTE(text, old_text, new_text) | Swaps one piece of text for another, every time it appears. |
| Format a number as text | =TEXT(A1, "0.00%") | Turns a number or a date into a string using the format pattern you give it. |
| Split one cell into many | =TEXTSPLIT(A1, ",") | Breaks text apart on a delimiter. This is the opposite of CONCATENATE. Excel 365. |
| Turn text digits into a number | =VALUE(A1) | Converts numbers stored as text so math works on them again. |
| What you want | Formula | Notes |
|---|---|---|
| Today and right now | =TODAY() =NOW() | TODAY gives the date, NOW the date and time. Both update on every recalculation. |
| Build a date from parts | =DATE(year, month, day) | Assembles a real date from separate year, month, and day numbers. |
| Days between two dates | =DATEDIF(start, end, "d") | Whole days between the two dates. See how to subtract dates in Excel. |
| Months between two dates | =DATEDIF(start, end, "m") | Counts complete months. Swap the "m" for "y" to get years. Guide: months between dates. |
| Shift a date by months | =EDATE(start, months) | Jumps forward or back N months and keeps the day. Guide: add months to a date. |
| Last day of the month | =EOMONTH(start, months) | Lands on the month end N months out. Use 0 for the month you are in. |
| Count business days | =NETWORKDAYS(start, end) | Workdays between two dates, weekends excluded. Add a holiday range as the third argument. |
| Add business days | =WORKDAY(start, days) | Returns the date that is N workdays out from the start. |
| Pull a piece of a date | =YEAR(A1) =MONTH(A1) =DAY(A1) | Extracts just that part of a date as a plain number. |
| Which day of the week | =WEEKDAY(A1, 2) | Returns 1 to 7. The 2 makes Monday day one. Pair it with IF to flag weekends. |
| What you want | Formula | Notes |
|---|---|---|
| Look up a value in a table | =VLOOKUP(value, table, col, FALSE) | Searches the leftmost column and returns a column to its right. Always pass FALSE for an exact match. Guide: VLOOKUP in Excel. |
| The modern lookup | =XLOOKUP(value, lookup_array, return_array) | Looks in any direction, is exact by default, and takes a not-found message as a fourth argument. Excel 365. |
| The flexible classic | =INDEX(return_range, MATCH(value, lookup_range, 0)) | MATCH finds the row, INDEX returns the value, and it works in any direction. Guide: INDEX and MATCH. |
| What you want | Formula | Notes |
|---|---|---|
| Return one of two answers | =IF(test, value_if_true, value_if_false) | Tests a condition and gives one result when it is true, another when it is false. |
| Check several conditions in order | =IFS(test1, val1, test2, val2) | Walks the tests top to bottom and returns the first one that is true. No nesting. |
| Nested IF | =IF(A1>90, "A", IF(A1>80, "B", "C")) | The classic way to grade into buckets. Readable up to about three levels. |
| Combine true or false tests | =AND(test1, test2) =OR(test1, test2) =NOT(test) | AND needs every test to pass, OR needs one, NOT flips the result. |
| Act only when a cell is filled | =IF(A1<>"", "Ready", "") | The standard blank check. Guide: Excel IF not blank. |
| Catch and replace errors | =IFERROR(formula, "") | Shows a fallback for any error instead of the error code. Guide: the IFERROR function. |
| Catch only a not-found error | =IFNA(formula, "Not found") | Narrower than IFERROR. It only traps #N/A, which is the usual lookup error. |
| Pick a result from a list of cases | =SWITCH(value, case1, result1, default) | Compares one value against several options and returns the match. |
| What you want | Formula | Notes |
|---|---|---|
| Count the numbers | =COUNT(A1:A10) | Counts only cells that hold numbers. Text and blanks are skipped. |
| Count everything that is filled | =COUNTA(A1:A10) | Tallies every non-empty cell, numbers and text alike. See how to count cells with text. |
| Count the blanks | =COUNTBLANK(A1:A10) | Returns how many cells in the range are empty. Good for spotting gaps. |
| Count cells that meet a test | =COUNTIF(range, criteria) | Counts the cells matching one condition. Guide: the COUNTIF function. |
| Count with several conditions | =COUNTIFS(range1, crit1, range2, crit2) | The multi-test version. Counts rows that satisfy every criterion at once. |
| Find the middle value | =MEDIAN(A1:A10) | The midpoint. Less skewed by outliers than an average. |
| Smallest and largest | =MIN(A1:A10) =MAX(A1:A10) | The bottom and top values in a range. |
| The Nth biggest or smallest | =LARGE(range, k) =SMALL(range, k) | LARGE pulls the Nth highest value and SMALL the Nth lowest. Good for top-5 lists. |
| Rank a value in a list | =RANK(number, ref, order) | The position of a number in a range. Use 0 for highest first, 1 for lowest first. |
| What you want | Formula | Notes |
|---|---|---|
| List the distinct values | =UNIQUE(A1:A100) | Spills the unique entries from a range. No helper columns needed. |
| Sort with a formula | =SORT(A1:B100, 2, -1) | Returns a range in order without touching the source data, and it updates live. |
| Filter rows by a condition | =FILTER(range, include, "None") | Spills only the rows that meet your test. The formula answer to AutoFilter. |
| Generate a list of numbers | =SEQUENCE(rows, columns, start, step) | Spills a series of numbers. Great for row counters and date scaffolds. |
Get the printable PDF
We will email you the Excel Formulas Cheat Sheet as a printable PDF, yours to keep. No cost, and you can unsubscribe any time.
How to use this cheat sheet
Find the job you are trying to do in the left column, copy the syntax, then swap the placeholders for your own ranges. Most of these are three arguments or fewer, so the fastest way to learn one is to type it into a scratch cell and watch the tooltip name each argument as you go. If a formula returns text instead of a result, the cell is formatted as Text, and if it returns an error, wrap it in IFERROR while you debug.
Press Ctrl and the grave accent key to toggle every formula on the sheet into view at once, which is the quickest way to audit someone else's file. For the long version of the most common ones, read the full guide to Microsoft Excel formulas, or start from a file where the formulas are already wired up.
Go deeper
Excel Cheat Sheet
The everything sheet. Formulas, shortcuts, formatting, and the features you actually use, in one place.
Open the Excel cheat sheetExcel Shortcuts Cheat Sheet
The keyboard shortcuts worth building into muscle memory, for Windows and Mac.
Open the shortcuts cheat sheetExcel Lookup Functions Cheat Sheet
VLOOKUP, XLOOKUP, and INDEX MATCH side by side, plus how to fix a lookup that returns #N/A.
Open the lookup cheat sheetGoogle Sheets Formulas Cheat Sheet
The same job-by-job list for Google Sheets, including the functions Excel does not have.
Open the Sheets cheat sheetQuestions people ask
What are the most important Excel formulas to learn first?
Start with SUM, AVERAGE, COUNTIF, and IF, because they cover most everyday spreadsheet work. Add TRIM and TEXT once you start importing data from other systems, then learn one lookup, either XLOOKUP if you have Excel 365 or VLOOKUP if you do not. Those seven will carry you further than any advanced function will.
How do I see the formulas in a sheet instead of the results?
Press Ctrl and the grave accent key, the one above Tab, to toggle formula view for the whole sheet, and press it again to switch back. You can also click Show Formulas on the Formulas tab. Here is the full walkthrough of showing formulas in Excel.
Why is my Excel formula showing as text instead of calculating?
The three usual causes are a cell formatted as Text before the formula was typed, a leading apostrophe in front of the equals sign, or the workbook set to manual calculation. Fix the format, delete the apostrophe, then set calculation back to Automatic. Here is the fix for Excel formulas not calculating.
What is the difference between VLOOKUP and XLOOKUP?
VLOOKUP only searches the leftmost column of a table and returns a column to its right, and it defaults to an approximate match unless you pass FALSE. XLOOKUP searches in any direction, is exact by default, and lets you set the not-found message inside the formula. Use XLOOKUP if you have Excel 365, and use INDEX with MATCH if you need lookups to survive in older files.
Want the formulas already built in?
Simple Sheets has 100+ done-for-you Excel and Google Sheets templates with the formulas wired up and tested. Skip the build, start from a finished file.
Browse the template libraryMore free stuff: our free Excel calculators and tools, our free Excel templates, and our accounting and finance templates.
