This is the only Excel formula reference you need. 100 formulas organized by category — each with syntax, a real example, and a plain-English explanation of what it does. Styled like a real Excel sheet so it feels familiar.
Verma Learning covers 90 of these 100 formulas with 7,000+ practice questions. After reading, download and practice.
A — FormulaB — CategoryC — Syntax & ExampleD — What It Does
| # | Formula | Category | Syntax & Example | What It Does |
|---|---|---|---|---|
| LOOKUP & REFERENCE FORMULAS | ||||
| 1 | VLOOKUP | Lookup | =VLOOKUP("E105", A:D, 3, FALSE) | Searches first column for a value, returns from a specified column. The most-tested formula in interviews. |
| 2 | HLOOKUP | Lookup | =HLOOKUP("Sales", A1:Z3, 2, FALSE) | Like VLOOKUP but horizontal — searches first row, returns from a specified row. |
| 3 | XLOOKUP | Lookup | =XLOOKUP("E105", A:A, C:C, "N/A") | Modern replacement for VLOOKUP. Looks in any direction, built-in error handling. Excel 365 only. |
| 4 | INDEX | Lookup | =INDEX(C2:C100, 5) | Returns value from a range at a specific row position. Usually paired with MATCH. |
| 5 | MATCH | Lookup | =MATCH("Delhi", A:A, 0) | Returns the position (row number) of a value in a range. 0 = exact match. |
| 6 | INDEX-MATCH | Lookup | =INDEX(C:C, MATCH("E105", A:A, 0)) | The professional lookup combo. Can look left, doesn't break when columns change. |
| 7 | INDIRECT | Reference | =INDIRECT("Sheet"&A1&"!B2") | Creates a cell reference from a text string. Used for dynamic sheet references in MIS dashboards. |
| 8 | OFFSET | Reference | =OFFSET(A1, 3, 2, 1, 1) | Returns a reference offset from a starting cell by rows and columns. Used for dynamic ranges. |
| 9 | CHOOSE | Lookup | =CHOOSE(2, "A", "B", "C") | Returns value from a list based on index number. Returns "B" (2nd item). |
| 10 | LOOKUP | Lookup | =LOOKUP(2, 1/(A:A="X"), B:B) | Returns last matching value. Useful trick for finding the last occurrence. |
| 11 | ROW | Reference | =ROW(A5) | Returns the row number of a cell. =ROW(A5) returns 5. Useful for auto-numbering. |
| 12 | COLUMN | Reference | =COLUMN(C1) | Returns the column number. =COLUMN(C1) returns 3. |
| 13 | ROWS | Reference | =ROWS(A1:A50) | Counts how many rows in a range. Returns 50. |
| 14 | COLUMNS | Reference | =COLUMNS(A1:E1) | Counts how many columns in a range. Returns 5. |
| 15 | TRANSPOSE | Reference | =TRANSPOSE(A1:E1) | Converts rows to columns and vice versa. Dynamic array in 365. |
| MATH & AGGREGATION FORMULAS | ||||
| 16 | SUM | Math | =SUM(B2:B100) | Adds all numbers in a range. The first formula everyone learns. |
| 17 | SUMIF | Math | =SUMIF(A:A, "Delhi", C:C) | Sums values where one condition is met. Sum sales for Delhi only. |
| 18 | SUMIFS | Math | =SUMIFS(C:C, A:A, "Delhi", B:B, "Sales") | Sums values where multiple conditions are met. The MIS workhorse formula. |
| 19 | SUMPRODUCT | Math | =SUMPRODUCT((A2:A100="X")*(C2:C100)) | Multiplies arrays element-by-element and sums. Handles complex multi-condition calculations. |
| 20 | AVERAGE | Statistics | =AVERAGE(B2:B100) | Returns the arithmetic mean. Ignores text and blanks. |
| 21 | AVERAGEIF | Statistics | =AVERAGEIF(A:A, "Delhi", C:C) | Average of values where condition is met. |
| 22 | AVERAGEIFS | Statistics | =AVERAGEIFS(C:C, A:A, "Delhi", B:B, ">5000") | Average with multiple conditions. |
| 23 | COUNT | Statistics | =COUNT(B2:B100) | Counts cells containing numbers only. Ignores text and blanks. |
| 24 | COUNTA | Statistics | =COUNTA(A2:A100) | Counts all non-empty cells (numbers, text, errors — everything except blank). |
| 25 | COUNTBLANK | Statistics | =COUNTBLANK(A2:A100) | Counts empty cells. Useful for finding missing data. |
| 26 | COUNTIF | Statistics | =COUNTIF(A:A, "Sales") | Counts cells matching one condition. |
| 27 | COUNTIFS | Statistics | =COUNTIFS(A:A, "Delhi", B:B, ">5000") | Counts cells matching multiple conditions. |
| 28 | MAX | Statistics | =MAX(B2:B100) | Returns the largest value in a range. |
| 29 | MIN | Statistics | =MIN(B2:B100) | Returns the smallest value in a range. |
| 30 | LARGE | Statistics | =LARGE(B:B, 3) | Returns the 3rd largest value. Use for "Top N" reports. |
| 31 | SMALL | Statistics | =SMALL(B:B, 2) | Returns the 2nd smallest value. |
| 32 | RANK | Statistics | =RANK(B2, B:B, 0) | Returns the rank of a number. 0 = descending (largest=1). |
| 33 | ROUND | Math | =ROUND(3.14159, 2) | Rounds to specified decimal places. Returns 3.14. |
| 34 | ROUNDUP | Math | =ROUNDUP(3.141, 2) | Always rounds up. Returns 3.15. |
| 35 | ROUNDDOWN | Math | =ROUNDDOWN(3.149, 2) | Always rounds down. Returns 3.14. |
| 36 | INT | Math | =INT(7.9) | Rounds down to nearest integer. Returns 7. |
| 37 | MOD | Math | =MOD(10, 3) | Returns remainder after division. Returns 1. Used to check odd/even. |
| 38 | ABS | Math | =ABS(-42) | Returns absolute (positive) value. Returns 42. |
| 39 | POWER | Math | =POWER(2, 10) | Returns 2 raised to the power 10 = 1024. Same as 2^10. |
| 40 | PRODUCT | Math | =PRODUCT(A1:A5) | Multiplies all numbers in a range together. |
| 41 | SUBTOTAL | Math | =SUBTOTAL(9, B2:B100) | SUM/AVERAGE/COUNT that ignores filtered-out rows. 9=SUM, 1=AVG, 2=COUNT. |
| 42 | AGGREGATE | Math | =AGGREGATE(9, 6, B2:B100) | Like SUBTOTAL but also ignores errors. 6=ignore errors. Essential for messy data. |
| 43 | MEDIAN | Statistics | =MEDIAN(B2:B100) | Returns the middle value. Better than AVERAGE for skewed data. |
| 44 | MODE | Statistics | =MODE(B2:B100) | Returns the most frequent value. |
| 45 | STDEV | Statistics | =STDEV(B2:B100) | Standard deviation — measures how spread out values are. |
| TEXT FORMULAS | ||||
| 46 | LEFT | Text | =LEFT("ABCDEF", 3) | Returns first N characters. Returns "ABC". |
| 47 | RIGHT | Text | =RIGHT("ABCDEF", 2) | Returns last N characters. Returns "EF". |
| 48 | MID | Text | =MID("ABCDEF", 2, 3) | Returns N characters starting from position. Returns "BCD". |
| 49 | LEN | Text | =LEN("Hello") | Returns character count. Returns 5. Used to validate data lengths. |
| 50 | FIND | Text | =FIND("@", "a@b.com") | Returns position of a character. Case-sensitive. Returns 2. |
| 51 | SEARCH | Text | =SEARCH("hello", "Hello World") | Like FIND but case-insensitive. Returns 1. Supports wildcards. |
| 52 | SUBSTITUTE | Text | =SUBSTITUTE(A1, " ", "") | Replaces specific text. Here removes all spaces. |
| 53 | REPLACE | Text | =REPLACE(A1, 3, 2, "XX") | Replaces characters at a specific position. Position 3, length 2, with "XX". |
| 54 | TRIM | Text | =TRIM(" Hello World ") | Removes extra spaces. Returns "Hello World". Essential for data cleaning. |
| 55 | CLEAN | Text | =CLEAN(A1) | Removes non-printable characters from imported/pasted data. |
| 56 | UPPER | Text | =UPPER("hello") | Converts to uppercase. Returns "HELLO". |
| 57 | LOWER | Text | =LOWER("HELLO") | Converts to lowercase. Returns "hello". |
| 58 | PROPER | Text | =PROPER("john doe") | Capitalizes first letter of each word. Returns "John Doe". |
| 59 | CONCATENATE | Text | =CONCATENATE(A1, " ", B1) | Joins text strings. Older function — use & or TEXTJOIN instead. |
| 60 | TEXTJOIN | Text | =TEXTJOIN(", ", TRUE, A1:A10) | Joins a range with a delimiter. TRUE = ignore blanks. Modern and powerful. |
| 61 | TEXT | Text | =TEXT(A1, "DD-MMM-YYYY") | Formats a number/date as text. Turns 46022 into "20-Jun-2026". |
| 62 | VALUE | Text | =VALUE("1234") | Converts text that looks like a number into an actual number. |
| 63 | NUMBERVALUE | Text | =NUMBERVALUE("1.234,56", ",", ".") | Converts text to number with locale-aware decimal/thousand separators. |
| 64 | REPT | Text | =REPT("*", 5) | Repeats text N times. Returns "*****". Used for in-cell bar charts. |
| 65 | EXACT | Text | =EXACT("ABC", "abc") | Case-sensitive comparison. Returns FALSE. Regular = ignores case. |
| 66 | CHAR | Text | =CHAR(10) | Returns character for a code. CHAR(10) = line break inside a formula. |
| 67 | CODE | Text | =CODE("A") | Returns ASCII code of first character. "A" = 65. |
| DATE & TIME FORMULAS | ||||
| 68 | TODAY | Date | =TODAY() | Returns today's date. Auto-updates daily. Use for age/tenure calculations. |
| 69 | NOW | Date | =NOW() | Returns current date AND time. Updates on every recalculation. |
| 70 | DATE | Date | =DATE(2026, 6, 20) | Creates a date from year, month, day values. |
| 71 | YEAR | Date | =YEAR(A1) | Extracts the year from a date. Returns 2026. |
| 72 | MONTH | Date | =MONTH(A1) | Extracts the month number. Returns 6 for June. |
| 73 | DAY | Date | =DAY(A1) | Extracts the day of the month. |
| 74 | WEEKDAY | Date | =WEEKDAY(A1, 2) | Returns day of week (1=Monday with type 2). Used in attendance reports. |
| 75 | DATEDIF | Date | =DATEDIF(A1, B1, "M") | Months between two dates. "Y"=years, "D"=days. Hidden function — not in autocomplete. |
| 76 | EDATE | Date | =EDATE(A1, 3) | Date N months from a date. 3 months ahead, -3 months ago. |
| 77 | EOMONTH | Date | =EOMONTH(A1, 0) | Last day of the month. 0=current month, 1=next month. |
| 78 | NETWORKDAYS | Date | =NETWORKDAYS(A1, B1) | Working days between two dates (excludes Sat/Sun). Add holidays as 3rd argument. |
| 79 | WORKDAY | Date | =WORKDAY(A1, 10) | Date that is N working days from a date. Used for deadline calculations. |
| 80 | HOUR | Time | =HOUR(A1) | Extracts hour from a time value (0-23). |
| 81 | MINUTE | Time | =MINUTE(A1) | Extracts minutes from a time value (0-59). |
| 82 | SECOND | Time | =SECOND(A1) | Extracts seconds from a time value. |
| LOGICAL FORMULAS | ||||
| 83 | IF | Logical | =IF(A1>100, "Pass", "Fail") | The most important formula. If condition is true, return X; otherwise Y. |
| 84 | IFS | Logical | =IFS(A1>=90,"A", A1>=80,"B", TRUE,"C") | Multiple IF conditions without nesting. Cleaner than nested IF. Excel 365/2019+. |
| 85 | AND | Logical | =AND(A1>0, B1>0) | Returns TRUE only if ALL conditions are true. |
| 86 | OR | Logical | =OR(A1>100, B1>100) | Returns TRUE if ANY condition is true. |
| 87 | NOT | Logical | =NOT(A1="Error") | Reverses TRUE/FALSE. Returns TRUE if A1 is NOT "Error". |
| 88 | IFERROR | Logical | =IFERROR(VLOOKUP(...), "N/A") | Returns custom value if formula errors. Wraps VLOOKUP to hide #N/A. |
| 89 | IFNA | Logical | =IFNA(VLOOKUP(...), "Not Found") | Like IFERROR but only catches #N/A. Other errors still show. |
| 90 | SWITCH | Logical | =SWITCH(A1, 1,"Jan", 2,"Feb", 3,"Mar") | Cleaner than nested IF for mapping values. Excel 365/2019+. |
| 91 | TRUE / FALSE | Logical | =TRUE() | Returns the boolean value TRUE or FALSE. Used in some array formulas. |
| 92 | ISBLANK | Info | =ISBLANK(A1) | TRUE if cell is empty. Used in data validation and conditional formatting. |
| 93 | ISNUMBER | Info | =ISNUMBER(A1) | TRUE if cell contains a number. Useful for data type checking. |
| 94 | ISTEXT | Info | =ISTEXT(A1) | TRUE if cell contains text. |
| 95 | ISERROR | Info | =ISERROR(A1) | TRUE if cell contains any error (#N/A, #REF!, #VALUE!, etc.). |
| DYNAMIC ARRAY FORMULAS (Excel 365) | ||||
| 96 | UNIQUE | Array | =UNIQUE(A2:A100) | Returns unique values from a range. Spills automatically. Replaces Remove Duplicates. |
| 97 | SORT | Array | =SORT(A2:B100, 2, -1) | Returns sorted range. Column 2, descending (-1). No manual sort needed. |
| 98 | FILTER | Array | =FILTER(A:C, B:B="Sales") | Returns filtered rows where condition is met. Like AutoFilter but with a formula. |
| 99 | SEQUENCE | Array | =SEQUENCE(10, 1, 1, 1) | Generates a sequence: 10 rows, 1 column, starting at 1, step 1. Returns 1-10. |
| 100 | LET | Array | =LET(x, VLOOKUP(...), IF(x>100, x*1.1, x)) | Name intermediate results to avoid repeating calculations. Cleaner, faster formulas. |
Practice All 100 Formulas with Real Datasets
Reading a formula list teaches you what exists. Practicing teaches you how to use it. Verma Learning has 7,000+ questions covering 90 of these 100 formulas — type the formula, submit, get instant feedback.
Download Free — vermalearning.comWhich Formulas to Learn First?
Don't try to learn all 100. Follow this priority order:
- Week 1-2: SUM, AVERAGE, COUNT, IF, CONCATENATE, LEN, LEFT, RIGHT, TRIM (Rows 16, 20, 23, 83, 59, 49, 46, 47, 54)
- Week 3-4: SUMIF, SUMIFS, COUNTIF, COUNTIFS, nested IF (Rows 17, 18, 26, 27, 83-84)
- Week 5-7: VLOOKUP, INDEX-MATCH, IFERROR (Rows 1, 6, 88)
- Week 8-10: Date functions, TEXT, SUBSTITUTE (Rows 68-79, 61, 52)
- Week 11+: SUMPRODUCT, XLOOKUP, Dynamic Arrays (Rows 19, 3, 96-100)