Excel Formulas Cheat Sheet: 40 Formulas on One Page
An Excel formulas cheat sheet with the 40 formulas you'll use most: maths, conditions, lookups, text, dates and errors, each with a one-line example.
By Ayoub Farid · · 3 min read
Bookmark this page. It lists the formulas that cover most everyday Excel and Google Sheets work, each with a short example. Assume a sheet where column A holds names or items, B holds amounts, and C holds categories or dates.
Maths and summary
| Formula | Example | Does |
|---|---|---|
| SUM | =SUM(B2:B13) | Adds a range |
| AVERAGE | =AVERAGE(B2:B13) | Mean |
| MEDIAN | =MEDIAN(B2:B13) | Middle value |
| MIN / MAX | =MAX(B2:B13) | Smallest / largest |
| COUNT | =COUNT(B2:B13) | Counts numbers |
| COUNTA | =COUNTA(A2:A13) | Counts non-empty cells |
| COUNTBLANK | =COUNTBLANK(B2:B13) | Counts empty cells |
| ROUND | =ROUND(B2,2) | Rounds to 2 decimals |
| SUMPRODUCT | =SUMPRODUCT(B2:B7,C2:C7) | Multiplies pairs, adds them |
Conditions
| Formula | Example | Does |
|---|---|---|
| IF | =IF(B2>=100,"Yes","No") | One of two results |
| IFS | =IFS(B2>=90,"A",B2>=80,"B",TRUE,"C") | Several conditions |
| AND / OR | =IF(AND(B2>0,C2="Paid"),"OK","Check") | Combine tests |
| COUNTIF | =COUNTIF(C2:C50,"Coffee") | Counts matches |
| COUNTIFS | =COUNTIFS(C2:C50,"Coffee",B2:B50,">3") | Counts with several conditions |
| SUMIF | =SUMIF(C2:C50,"Coffee",B2:B50) | Adds matches |
| SUMIFS | =SUMIFS(B2:B50,C2:C50,"Coffee",D2:D50,"Jan") | Adds with several conditions |
| AVERAGEIF | =AVERAGEIF(C2:C50,"Food",B2:B50) | Averages matches |
Lookups
| Formula | Example | Does |
|---|---|---|
| VLOOKUP | =VLOOKUP(F2,A2:C50,3,FALSE) | Finds a row, returns a column |
| XLOOKUP | =XLOOKUP(F2,A2:A50,C2:C50,"Not found") | Modern lookup, any direction |
| INDEX + MATCH | =INDEX(C2:C50,MATCH(F2,A2:A50,0)) | Flexible lookup |
| MATCH | =MATCH(F2,A2:A50,0) | Position of a value |
Text
| Formula | Example | Does |
|---|---|---|
| TRIM | =TRIM(A2) | Removes extra spaces |
| UPPER / LOWER / PROPER | =PROPER(A2) | Changes case |
| LEFT / RIGHT / MID | =LEFT(A2,3) | Takes characters |
| LEN | =LEN(A2) | Counts characters |
| & / CONCAT | =A2&" "&B2 | Joins text |
| TEXTJOIN | =TEXTJOIN(", ",TRUE,A2:A6) | Joins a range with a separator |
| TEXT | =TEXT(C2,"dd mmm yyyy") | Formats a number as text |
Dates
| Formula | Example | Does |
|---|---|---|
| TODAY | =TODAY() | Today's date |
| NOW | =NOW() | Date and time |
| DATEDIF | =DATEDIF(C2,TODAY(),"y") | Whole years between dates |
| NETWORKDAYS | =NETWORKDAYS(C2,D2) | Working days |
| EOMONTH | =EOMONTH(C2,0) | Last day of the month |
Errors and dynamic arrays
| Formula | Example | Does |
|---|---|---|
| IFERROR | =IFERROR(B2/C2,0) | Replaces errors |
| UNIQUE | =UNIQUE(C2:C50) | Distinct values |
| FILTER | =FILTER(A2:B50,C2:C50="Coffee") | Rows that match |
| SORT | =SORT(A2:B50,2,-1) | Sorted copy |
References
B2: relative, moves when copied$B$2: absolute, never moves$B2/B$2: mixed, one part locked- Press F4 (Windows) or Cmd+T (Mac) while editing to cycle through them. See the F4 shortcut guide.
Turn the cheat sheet into skill
A cheat sheet helps you remember syntax, but you only learn a formula by writing it. Cellio gives you short exercises where you build each formula on a real sheet and get it checked by calculation. Start with the beginner's path for the order.
Frequently asked questions
What are the most important Excel formulas?
SUM, AVERAGE, COUNT, IF, COUNTIF, SUMIF(S), VLOOKUP or XLOOKUP, IFERROR, and absolute references with $.
Do these formulas work in Google Sheets?
Yes, all of them, including XLOOKUP, UNIQUE, FILTER and SORT.
Which formulas need Excel 365?
XLOOKUP, UNIQUE, FILTER and SORT need Excel 2021 or Microsoft 365. Older versions don't have them.
Is there a printable version?
You can print this page from your browser; the tables fit on a few pages.