FormuladrillExcel formulas cheat sheet
The ones worth learning first
| What | How |
|---|---|
| Add a column | =SUM(B2:B20) |
| Add only the rows that match | =SUMIFS(C:C, A:A, "North", B:B, ">100") |
| Look something up (new Excel) | =XLOOKUP(E2, A:A, C:C, "Not found") |
| Look something up (any Excel) | =INDEX(C:C, MATCH(E2, A:A, 0)) |
| One result or another | =IF(B2>=50, "Pass", "Fail") |
| Hide an error | =IFERROR(A2/B2, 0) |
| Lock a reference before filling | $B$1 (press F4 in the formula) |
| Percent change | =(C2-B2)/B2 |
| Loan payment | =PMT(rate/12, years*12, -principal) |
| Count the rows that match | =COUNTIFS(A:A, "North", B:B, ">100") |
Totals and counts
| Sum a range | =SUM(B2:B20) |
| Average | =AVERAGE(B2:B20) |
| Smallest and largest | =MIN(B2:B20) =MAX(B2:B20) |
| Count numbersCounts cells that hold numbers only. | =COUNT(B2:B20) |
| Count non-empty cells | =COUNTA(A2:A20) |
| Count blanks | =COUNTBLANK(A2:A20) |
| Median | =MEDIAN(B2:B20) |
| Second largestSMALL(range, k) for the k-th smallest. | =LARGE(B2:B20, 2) |
| Weighted total (price x quantity) | =SUMPRODUCT(B2:B20, C2:C20) |
| Running total | =SUM($B$2:B2) then fill down |
| Round to 2 decimalsROUNDUP and ROUNDDOWN force the direction. | =ROUND(B2, 2) |
| Round to nearest 1,000 | =ROUND(B2, -3) |
| Margin | =(C2-B2)/C2 |
| Percent change | =(C2-B2)/B2 |
| Share of totalLock the total with $ or it slides when you fill down. | =B2/SUM($B$2:$B$20) |
Conditions
| If, then, else | =IF(B2>=50, "Pass", "Fail") |
| Several conditions in order | =IFS(B2>=90, "A", B2>=80, "B", TRUE, "C") |
| Nested IF (older Excel) | =IF(B2>=90, "A", IF(B2>=80, "B", "C")) |
| Both must be true | =IF(AND(B2>100, C2="Yes"), bonus, 0) |
| Either may be true | =IF(OR(B2>100, C2="VIP"), "Call", "") |
| Not | =IF(NOT(ISBLANK(A2)), "Filled", "Empty") |
| Count if one conditionWildcards: "North*" matches North East. | =COUNTIF(A:A, "North") |
| Count if several conditions | =COUNTIFS(A:A, "North", B:B, ">100") |
| Count above a cell's valueJoin the operator and the cell with &. | =COUNTIF(B:B, ">"&E1) |
| Sum if one condition | =SUMIF(A:A, "North", C:C) |
| Sum if several conditionsSum range comes first in SUMIFS, last in SUMIF. | =SUMIFS(C:C, A:A, "North", B:B, ">100") |
| Average if | =AVERAGEIF(A:A, "North", C:C) |
| Largest that matchesMINIFS for the smallest. | =MAXIFS(C:C, A:A, "North") |
| Between two dates | =SUMIFS(C:C, A:A, ">="&E1, A:A, "<="&E2) |
Lookups
| Exact lookup, any version | =INDEX(C:C, MATCH(E2, A:A, 0)) |
| Exact lookup, Excel 2021 and 365 | =XLOOKUP(E2, A:A, C:C, "Not found") |
| VLOOKUP exactFALSE means exact. Leaving it out gives approximate. | =VLOOKUP(E2, A:C, 3, FALSE) |
| VLOOKUP approximate (tax bands, grades)Table must be sorted ascending on the first column. | =VLOOKUP(E2, A:B, 2, TRUE) |
| Lookup to the leftVLOOKUP cannot look left; INDEX/MATCH and XLOOKUP can. | =XLOOKUP(E2, C:C, A:A) |
| Position of a value | =MATCH(E2, A:A, 0) |
| Two-way lookup (row and column) | =INDEX(B2:F20, MATCH(H1, A2:A20, 0), MATCH(H2, B1:F1, 0)) |
| Last match instead of first | =XLOOKUP(E2, A:A, C:C, , 0, -1) |
| Lookup with two keys | =XLOOKUP(E2&F2, A:A&B:B, C:C) |
| Return several columnsSpills across three cells in 365. | =XLOOKUP(E2, A:A, B:D) |
| Trim the key before looking up | =XLOOKUP(TRIM(E2), A:A, C:C) |
| Horizontal lookup | =HLOOKUP(E1, A1:F5, 3, FALSE) |
| Pick by position | =CHOOSE(B2, "Low", "Mid", "High") |
Text
| Join text | =A2&" "&B2 |
| Join with a separator, skip blanks | =TEXTJOIN(", ", TRUE, A2:A10) |
| First or last characters | =LEFT(A2, 3) =RIGHT(A2, 4) |
| Middle characters | =MID(A2, 3, 5) |
| Everything after a characterOlder Excel: =MID(A2, FIND("@",A2)+1, 99) | =TEXTAFTER(A2, "@") |
| Everything before a character | =TEXTBEFORE(A2, " ") |
| Find a positionSEARCH ignores case and allows wildcards. | =FIND("@", A2) |
| Replace text | =SUBSTITUTE(A2, "Ltd", "Limited") |
| Remove extra spaces | =TRIM(A2) |
| Capitalize names | =PROPER(A2) |
| Upper and lower case | =UPPER(A2) =LOWER(A2) |
| Length | =LEN(A2) |
| Text that looks like a number | =VALUE(A2) or =A2*1 |
| Number as formatted text | =TEXT(B2, "#,##0.00") |
| Date as text | =TEXT(A2, "mmm yyyy") |
| Repeat a character | =REPT("-", 20) |
| Split by delimiter (365) | =TEXTSPLIT(A2, ",") |
Dates
| TodayRecalculates every day. Ctrl+; types a fixed date. | =TODAY() |
| Now, with time | =NOW() |
| Days between | =B2-A2 |
| Due date | =A2+30 |
| Add months | =EDATE(A2, 3) |
| End of monthEOMONTH(A2, -1) is the end of last month. | =EOMONTH(A2, 0) |
| Year, month, day | =YEAR(A2) =MONTH(A2) =DAY(A2) |
| Build a date | =DATE(2026, 12, 31) |
| Weekday name | =TEXT(A2, "dddd") |
| Working days between | =NETWORKDAYS(A2, B2) |
| Add working days | =WORKDAY(A2, 10) |
| Age in years | =DATEDIF(A2, TODAY(), "y") |
| Text date to a real date | =DATEVALUE(A2) |
Finance
| Loan paymentNegative principal gives a positive payment. | =PMT(rate/12, years*12, -principal) |
| Present value | =PV(rate, n, -payment) |
| Future value | =FV(rate, n, -payment, -start) |
| Net present valueNPV starts one period out; add the year-0 cash flow outside. | =NPV(rate, C2:C6) + C1 |
| Internal rate of return | =IRR(C1:C6) |
| Compound annual growthOr =RRI(years, start, end). | =(end/start)^(1/years)-1 |
| Total interest paid | =-CUMIPMT(rate/12, n, principal, 1, n, 0) |
| Interest in one period | =IPMT(rate/12, period, n, -principal) |
| Principal in one period | =PPMT(rate/12, period, n, -principal) |
| Number of payments | =NPER(rate/12, -payment, principal) |
| Rate per period | =RATE(n, -payment, principal)*12 |
| Break-even units | =fixed/(price-variable) |
| Straight-line depreciation | =SLN(cost, salvage, life) |
Errors and checks
| Replace any error | =IFERROR(A2/B2, 0) |
| Replace only #N/A | =IFNA(VLOOKUP(...), "Missing") |
| Is it a number | =ISNUMBER(A2) |
| Is it blank | =ISBLANK(A2) |
| Is it an error | =ISERROR(A2) |
| Flag duplicates | =COUNTIF($A$2:A2, A2)>1 |
| Check two totals agree | =ABS(B20-C20)<0.01 |
| Find a hard-coded number in a formula column | =ISFORMULA(C2) |
| Show the formula text | =FORMULATEXT(C2) |
Arrays and dynamic functions (365)
| Unique values | =UNIQUE(A2:A100) |
| SortColumn 3, descending. | =SORT(A2:C100, 3, -1) |
| Filter rows | =FILTER(A2:C100, B2:B100>100, "None") |
| Sequence of numbers | =SEQUENCE(12) |
| Sum of a filtered set | =SUM(FILTER(C2:C100, A2:A100="North")) |
| Apply a formula to each row | =BYROW(B2:D100, LAMBDA(r, SUM(r))) |
| Reuse a calculation | =LET(x, B2*C2, IF(x>100, x, 0)) |
| Transpose | =TRANSPOSE(A1:E1) |
| Count unique | =ROWS(UNIQUE(A2:A100)) |
References
| Lock row and column | $B$1 |
| Lock the row onlyFill across: column moves, row stays. | B$1 |
| Lock the column onlyFill down: row moves, column stays. | $B1 |
| Cycle the locks | F4 while the cursor is on the reference |
| Another sheet | =Data!B2 |
| Sheet name with a space | ='Raw data'!B2 |
| Whole column | =SUM(B:B) |
| Address from text | =INDIRECT("B"&E1) |
| Shifted rangeVolatile; prefer INDEX when you can. | =OFFSET(A1, 2, 0, 5, 1) |
formuladrill.com: practice every one of these with instant checking. 112 entries.