Excel formulas cheat sheet
54 functions on one page. Examples run on the sample sheet shown below, and every result is calculated. Print it with Ctrl+P / Cmd+P.
Examples use this sample sheet of orders:
A
B
C
D
E
F
1
Order
Region
Rep
Units
Price
Revenue
2
1001
East
Maya
4
12
48
3
1002
West
Omar
2
30
60
4
1003
East
Lena
7
12
84
5
1004
North
Maya
1
45
45
6
1005
West
Lena
5
30
150
7
1006
East
Omar
3
18
54
8
1007
North
Lena
6
12
72
9
1008
West
Maya
8
18
144
Math
| Function | What it does | Example | Result |
|---|---|---|---|
| SUM | Adds numbers and ranges. | =SUM(F2:F9) | 657 |
| AVERAGE | The mean of the numbers. | =AVERAGE(E2:E9) | 22.125 |
| MIN | The smallest number. | =MIN(D2:D9) | 1 |
| MAX | The largest number. | =MAX(F2:F9) | 150 |
| COUNT | Counts cells that contain numbers. | =COUNT(D2:D9) | 8 |
| COUNTA | Counts cells that are not empty. | =COUNTA(C2:C9) | 8 |
| ROUND | Rounds to a number of decimals. | =ROUND(AVERAGE(E2:E9),1) | 22.1 |
| ABS | Removes the minus sign. | =ABS(-12) | 12 |
| MEDIAN | The middle value. | =MEDIAN(F2:F9) | 66 |
| ROUNDUP | Rounds up, away from zero. | =ROUNDUP(AVERAGE(E2:E9),0) | 23 |
| ROUNDDOWN | Rounds down, toward zero. | =ROUNDDOWN(AVERAGE(E2:E9),0) | 22 |
Conditional
| Function | What it does | Example | Result |
|---|---|---|---|
| COUNTIF | Counts cells that match a condition. | =COUNTIF(B2:B9,"East") | 3 |
| COUNTIFS | Counts rows that match every condition. | =COUNTIFS(B2:B9,"West",D2:D9,">4") | 2 |
| SUMIF | Adds values whose row matches a condition. | =SUMIF(B2:B9,"West",F2:F9) | 354 |
| SUMIFS | Adds values whose row matches every condition. | =SUMIFS(F2:F9,B2:B9,"East",C2:C9,"Omar") | 54 |
| AVERAGEIF | Averages values whose row matches a condition. | =AVERAGEIF(C2:C9,"Lena",F2:F9) | 102 |
| AVERAGEIFS | Averages values whose rows match every condition. | =AVERAGEIFS(F2:F9,B2:B9,"East",D2:D9,">3") | 66 |
| MAXIFS | The largest value among rows that match every condition. | =MAXIFS(F2:F9,B2:B9,"West") | 150 |
| MINIFS | The smallest value among rows that match every condition. | =MINIFS(F2:F9,C2:C9,"Maya") | 45 |
| COUNTBLANK | Counts empty cells. | =COUNTBLANK(G2:G9) | 8 |
Logic
| Function | What it does | Example | Result |
|---|---|---|---|
| IF | Returns one value if a test is true, another if false. | =IF(F6>=100,"Big","Small") | Big |
| AND | TRUE only if every test is true. | =AND(B4="East",D4>3) | TRUE |
| OR | TRUE if any test is true. | =OR(B2="West",D2>3) | TRUE |
| NOT | Flips TRUE and FALSE. | =NOT(B2="West") | TRUE |
| IFERROR | Shows a fallback when a formula errors. | =IFERROR(VLOOKUP(1012,A2:F9,5,FALSE),0) | 0 |
| IFS | Returns the value for the first test that is TRUE. | =IFS(F6>=100,"Large",F6>=50,"Medium",TRUE,"Small") | Large |
Lookup
| Function | What it does | Example | Result |
|---|---|---|---|
| VLOOKUP | Finds a row by its first column and returns another column. | =VLOOKUP(1006,A2:F9,3,FALSE) | Omar |
| XLOOKUP | Searches one column and returns the matching value from another. | =XLOOKUP(150,F2:F9,A2:A9) | 1005 |
| MATCH | Returns the position of a value in a list. | =MATCH(1005,A2:A9,0) | 5 |
| INDEX | Returns the value at a position in a range. | =INDEX(D2:D9,MATCH(1008,A2:A9,0)) | 8 |
Text
| Function | What it does | Example | Result |
|---|---|---|---|
| CONCAT | Joins text together. | =CONCAT(C2," ",B2) | Maya East |
| UPPER | Converts to capitals. | =UPPER(C2) | MAYA |
| LOWER | Converts to lowercase. | =LOWER(B2) | east |
| LEN | Counts characters. | =LEN(C3) | 4 |
| LEFT | The first characters of a text. | =LEFT(B4,1) | E |
| RIGHT | The last characters of a text. | =RIGHT(A2,2) | 01 |
| TRIM | Removes extra spaces. | =TRIM(" Maya ") | Maya |
| PROPER | Capitalizes the first letter of each word. | =PROPER("maria garcia") | Maria Garcia |
| SUBSTITUTE | Replaces text inside text. | =SUBSTITUTE("555-0142","-","") | 5550142 |
| TEXT | Formats a number or date as text. | =TEXT(F6,"$#,##0") | $150 |
| TEXTJOIN | Joins text with a separator between items. | =TEXTJOIN(", ",TRUE,C2:C5) | Maya, Omar, Lena, Maya |
Dates
| Function | What it does | Example | Result |
|---|---|---|---|
| DATE | Builds a date from a year, month and day. | =TEXT(DATE(2026,12,25),"dddd") | Friday |
| YEAR | The year of a date. | =YEAR(DATE(2026,11,2)) | 2026 |
| MONTH | The month of a date, 1–12. | =MONTH(DATE(2026,11,2)) | 11 |
| DAY | The day of the month. | =DAY(DATE(2026,11,2)) | 2 |
| WEEKDAY | The day of the week as a number. 1 = Sunday by default. | =WEEKDAY(DATE(2026,11,2)) | 2 |
| EOMONTH | The last day of a month, some months away. | =TEXT(EOMONTH(DATE(2026,11,2),0),"m/d/yyyy") | 11/30/2026 |
| NETWORKDAYS | Counts weekdays between two dates, including both. | =NETWORKDAYS(DATE(2026,11,2),DATE(2026,11,30)) | 21 |
| TODAY | Today’s date. It updates every day. | =TEXT(TODAY(),"mmmm d, yyyy") | October 31, 2026 |
Dynamic arrays
| Function | What it does | Example | Result |
|---|---|---|---|
| UNIQUE | Lists each value once. The result spills down. | =UNIQUE(B2:B9) | East, West, North |
| SORT | Sorts a range. The result spills down. | =SORT(F2:F9,1,-1) | 150, 144, 84, 72, 60, 54, … |
| FILTER | Returns only the rows that meet a condition. The result spills. | =FILTER(C2:C9,B2:B9="West") | Omar, Lena, Maya |
| SUMPRODUCT | Multiplies ranges item by item, then adds the results. | =SUMPRODUCT(D2:D9,E2:E9) | 657 |
Finance
| Function | What it does | Example | Result |
|---|---|---|---|
| PMT | The payment for a loan with a fixed rate. | =ROUND(PMT(6%/12,60,-20000),2) | 386.66 |