Download free Excel cheat sheet (shortcuts)
📥 Download Excel Cheat SheetExcel Formula Cheat Sheet
The ultimate reference guide to master Excel functions, from simple calculations to advanced data analysis.
Math & Arithmetic
Text Functions
Logical Functions
Lookup & Reference
Date & Time
Statistical
Financial
Error Codes
Math & Arithmetic Functions
| Function | Description | Syntax / Example |
|---|---|---|
| SUM | Adds all numbers in a range of cells. | =SUM(A1:A5) |
| SUMIF | Sums the cells specified by a given condition. | =SUMIF(A1:A10, ">50") |
| SUMIFS | Adds cells that meet multiple criteria. | =SUMIFS(C2:C10, A2:A10, "East", B2:B10, ">500") |
| AVERAGE | Returns the average of its arguments. | =AVERAGE(A1:A10) |
| AVERAGEIF | Returns the average of all the cells in a range that meet a given criterion. | =AVERAGEIF(B2:B10, ">70") |
| AVERAGEIFS | Finds the average of cells that meet multiple criteria. | =AVERAGEIFS(C2:C10, A2:A10, "West") |
| MIN | Returns the minimum value in a list of arguments. | =MIN(A1:A10) |
| MAX | Returns the maximum value in a list of arguments. | =MAX(A1:A10) |
| PRODUCT | Multiplies all the numbers given as arguments. | =PRODUCT(A1:A5) |
| QUOTIENT | Returns the integer portion of a division. | =QUOTIENT(A1, B1) |
| MOD | Returns the remainder after a number is divided by a divisor. | =MOD(A1, B1) |
| ROUND | Rounds a number to a specified number of digits. | =ROUND(A1, 2) |
| ROUNDUP | Rounds a number up, away from zero. | =ROUNDUP(A1, 0) |
| ROUNDDOWN | Rounds a number down, toward zero. | =ROUNDDOWN(A1, 0) |
| INT | Rounds a number down to the nearest integer. | =INT(A1) |
Text Functions
| Function | Description | Syntax / Example |
|---|---|---|
| CONCAT | Combines the text from multiple ranges and/or strings. | =CONCAT(A1, " ", B1) |
| CONCATENATE | Joins two or more text strings into one string. | =CONCATENATE(A1, B1) |
| TEXTJOIN | Combines text from multiple ranges/strings, with a specified delimiter. | =TEXTJOIN(", ", TRUE, A1:A5) |
| LEFT | Returns the first character(s) in a text string. | =LEFT(A1, 5) |
| RIGHT | Returns the last character(s) in a text string. | =RIGHT(A1, 3) |
| MID | Returns characters from a text string, starting at the specified position. | =MID(A1, 2, 4) |
| LEN | Returns the number of characters in a text string. | =LEN(A1) |
| LOWER | Converts all letters in a text string to lowercase. | =LOWER(A1) |
| UPPER | Converts all letters in a text string to uppercase. | =UPPER(A1) |
| PROPER | Capitalizes the first letter in each word of a text string. | =PROPER(A1) |
| TRIM | Removes all spaces from text except for single spaces between words. | =TRIM(A1) |
| CLEAN | Removes all non-printable characters from text. | =CLEAN(A1) |
| FIND | Finds one text string within another text string (case-sensitive). | =FIND("D", A1) |
| REPLACE | Replaces part of a text string with a different text string. | =REPLACE(A1, 1, 3, "New") |
| SUBSTITUTE | Substitutes new text for old text in a text string. | =SUBSTITUTE(A1, "2023", "2024") |
Logical Functions
| Function | Description | Syntax / Example |
|---|---|---|
| IF | Returns one value if a condition is true and another if false. | =IF(A1 > 50, "Pass", "Fail") |
| IFERROR | Returns a value you specify if a formula evaluates to an error. | =IFERROR(A1/B1, "Error") |
| IFNA | Returns the value you specify if the expression resolves to #N/A. | =IFNA(VLOOKUP(A1, B:C, 2, 0), "Not Found") |
| AND | Returns TRUE if all its arguments are TRUE. | =AND(A1 > 10, B1 < 5) |
| OR | Returns TRUE if any argument is TRUE. | =OR(A1 = "Red", A1 = "Blue") |
| NOT | Reverses the logic of its argument. | =NOT(A1 > 5) |
| IFS | Checks multiple conditions and returns a value for the first TRUE condition. | =IFS(A1 > 90, "A", A1 > 80, "B") |
| SWITCH | Evaluates an expression against a list of values and returns the match. | =SWITCH(A1, 1, "Mon", 2, "Tue") |
Lookup & Reference Functions
| Function | Description | Syntax / Example |
|---|---|---|
| VLOOKUP | Searches for a value in the leftmost column of a table. | =VLOOKUP(A2, D2:F10, 2, FALSE) |
| HLOOKUP | Searches for a value in the top row of a table. | =HLOOKUP(A1, A1:F2, 2, FALSE) |
| XLOOKUP | Modern lookup; searches a range for a match and returns corresponding item. | =XLOOKUP(A2, A:A, B:B) |
| LOOKUP | Looks up values in a vector or array. | =LOOKUP(10, A1:A10, B1:B10) |
| INDEX | Returns a value or reference from within a table or range. | =INDEX(A1:C10, 3, 2) |
| MATCH | Returns the relative position of an item in a range. | =MATCH("Apples", A1:A10, 0) |
| OFFSET | Returns a reference offset from a starting cell or range. | =OFFSET(A1, 2, 1) |
| TRANSPOSE | Flips the orientation of a range (rows to columns or vice versa). | {=TRANSPOSE(A1:B5)} |
| INDIRECT | Returns the reference specified by a text string. | =INDIRECT(A1) |
| CHOOSE | Chooses a value from a list based on an index number. | =CHOOSE(2, "First", "Second") |
Date & Time Functions
| Function | Description | Syntax / Example |
|---|---|---|
| TODAY | Returns the current date. | =TODAY() |
| NOW | Returns the current date and time. | =NOW() |
| DATE | Returns the serial number for a specific date. | =DATE(2024, 1, 1) |
| DATEDIF | Calculates the difference between two dates. | =DATEDIF(A1, B1, "Y") |
| YEAR / MONTH / DAY | Extracts the specific part of a date. | =YEAR(A1), =MONTH(A1) |
| HOUR / MIN / SEC | Extracts the specific part of a time. | =HOUR(A1), =MINUTE(A1) |
| WEEKDAY | Converts a serial number to a day of the week. | =WEEKDAY(A1) |
| WORKDAY | Returns the date before or after a number of workdays. | =WORKDAY(A1, 10) |
| NETWORKDAYS | Returns the number of whole workdays between two dates. | =NETWORKDAYS(A1, B1) |
Statistical Functions
| Function | Description | Syntax / Example |
|---|---|---|
| COUNT | Counts cells with numbers. | =COUNT(A1:A10) |
| COUNTA | Counts non-empty cells. | =COUNTA(A1:A10) |
| COUNTBLANK | Counts empty cells in a range. | =COUNTBLANK(A1:A10) |
| COUNTIF | Counts cells that meet a single criterion. | =COUNTIF(A1:A10, "Yes") |
| COUNTIFS | Counts cells that meet multiple criteria. | =COUNTIFS(A2:A10, "East", B2:B10, ">50") |
| MEDIAN | Returns the median of the given numbers. | =MEDIAN(A1:A10) |
| MODE | Returns the most common value. | =MODE(A1:A10) |
| STDEV.P/S | Calculates standard deviation (Population or Sample). | =STDEV.P(A1:A10) |
| VAR.P/S | Calculates variance (Population or Sample). | =VAR.P(A1:A10) |
Financial Functions
| Function | Description | Syntax / Example |
|---|---|---|
| PMT | Calculates the payment for a loan. | =PMT(rate, nper, pv) |
| IPMT/PPMT | Calculates interest/principal portion of a payment. | =IPMT(...), =PPMT(...) |
| FV/PV | Returns future or present value of an investment. | =FV(...), =PV(...) |
| NPV | Returns the net present value of an investment. | =NPV(rate, values) |
| IRR | Returns the internal rate of return. | =IRR(A1:A10) |
| RATE | Returns the interest rate per period of an annuity. | =RATE(nper, pmt, pv) |
Common Excel Error Codes
| Error | Meaning | Solution |
|---|---|---|
| #DIV/0! | Division by zero or an empty cell. | Replace 0 with a value or use IFERROR. |
| #VALUE! | Invalid input type (e.g., text where a number is expected). | Clean and convert text to numbers. |
| #REF! | Reference is no longer valid (e.g., deleted cell). | Update formula reference or undo deletion. |
| #NAME? | Excel doesn't recognize text (often a typo). | Check spelling of function or named range. |
| #N/A | Data is not available (common in lookups). | Verify data existence or use IFNA. |
| #NUM! | Formula contains invalid numeric data. | Check for impossible calculations. |
| #NULL! | Incorrect range syntax (e.g., space instead of colon). | Correct the range separators. |