Excel Formulas Cheat Sheet
Boost your Excel skills with this quick reference guide of essential formulas for time functions, data counting, math, lookup, conditional operations, financial calculations, and more
Excel is a popular spreadsheet program that allows users to organize and manipulate data efficiently. Excel formulas enable you to perform various calculations, automate tasks, and extract meaningful insights from your data. Using an Excel formula cheat sheet, you can quickly reference and understand the syntax and usage of the most commonly used functions. This will empower you to leverage the full potential of Excel in data analysis, financial modeling, reporting, and other tasks, making your spreadsheets more effective and efficient.
This cheat sheet is a valuable resource for anyone who wants to learn Excel formulas. It is a quick and easy way to learn about the most common Excel functions and formulas, and it can help you perform calculations, analyze data, and create powerful spreadsheets.
Date and Time Functions
| Function | Description | Example |
|---|---|---|
| =EDATE | Returns the date that is a specified number of months before or after a start date. | =EDATE(A1, 2) |
| =EOMONTH | Returns the last day of the month that is a specified number of months before or after a start date. | =EOMONTH(A1, 1) |
| =DATE | Converts year, month, and day into a date. | =DATE(2023, 7, 27) |
| =TODAY | Returns the current date. | =TODAY() |
| =NETWORKDAYS | Returns the number of whole workdays between two dates. | =NETWORKDAYS(A1, A2) |
| =YEAR | Extracts the year from a date. | =YEAR(A1) |
| =YEARFRAC | Returns the year fraction representing the number of whole days between start_date and end_date. | =YEARFRAC(A1, A2) |
Counting Data Functions
| Function | Description | Example |
|---|---|---|
| =COUNT | Counts the number of cells that contain numbers. | =COUNT(A1:A10) |
| =COUNTIF | Counts the number of cells that meet a criterion. | =COUNTIF(A1:A10, ">5") |
| =COUNTA | Counts the number of cells that are not empty. | =COUNTA(A1:A10) |
Mathematical Functions
| Function | Description | Example |
|---|---|---|
| =LOG | Returns the logarithm of a number to a specified base. | =LOG(100, 10) |
| =EXP() | Returns e raised to the power of a number. | =EXP(1) |
| =MAX | Returns the maximum value in a range. | =MAX(A1:A10) |
| =MIN | Returns the minimum value in a range. | =MIN(A1:A10) |
| =MAXA | Returns the maximum value in a range, including logical values and text. | =MAXA(A1:A10) |
| =MINA | Returns the minimum value in a range, including logical values and text. | =MINA(A1:A10) |
| =SUM | Adds all the numbers in a range. | =SUM(A1:A10) |
| =SUMIF | Adds the cells specified by a given condition or criteria. | =SUMIF(A1:A10, ">5") |
| =SUMSQ | Returns the sum of the squares of the arguments. | =SUMSQ(A1:A10) |
| =AVERAGE | Returns the average of its arguments. | =AVERAGE(A1:A10) |
| =MEDIAN | Returns the median of its arguments. | =MEDIAN(A1:A10) |
| =PERCENTILE.INC | Returns the k-th percentile of values in a range. | =PERCENTILE.INC(A1:A10, 0.5) |
| =ROUNDUP | Rounds a number up, away from zero. | =ROUNDUP(A1, 2) |
| =ROUNDDOWN | Rounds a number down, towards zero. | =ROUNDDOWN(A1, 2) |
| =SUMPRODUCT | Returns the sum of the products of corresponding ranges. | =SUMPRODUCT(A1:A5, B1:B5) |
| =CEILING | Rounds a number up to the nearest multiple of significance. | =CEILING(2.5, 1) |
| =FLOOR | Rounds a number down to the nearest multiple of significance. | =FLOOR(2.5, 1) |
| =VAR.S | Estimates variance based on a sample. | =VAR.S(A1:A10) |
| =STDEV.S | Estimates standard deviation based on a sample. | =STDEV.S(A1:A10) |
| Autosum | Automatically sums a range of data. | =SUM(A1:A10) |
| =ABS | Returns the absolute value of a number. | =ABS(-4) |
Lookup Formulas
| Function | Description | Example |
|---|---|---|
| Index Match | Combines INDEX and MATCH for powerful lookups. | =INDEX(A1:C10, MATCH(E1, B1:B10, 0), 1) |
| =VLOOKUP | Looks for a value in the first column and returns a value in the same row from a specified column. | =VLOOKUP(D1, A1:C10, 2, FALSE) |
| =HLOOKUP | Looks for a value in the first row and returns a value in the same column from a specified row. | =HLOOKUP(D1, A1:F5, 3, FALSE) |
| =INDEX | Returns the value of an element in a table or an array, selected by the row and column number indexes. | =INDEX(A1:C10, 2, 3) |
| =MATCH | Searches for a value in a range and returns its relative position. | =MATCH(D1, A1:A10, 0) |
| =OFFSET | Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. | =OFFSET(A1, 2, 2) |
Conditional Functions
| Function | Description | Example |
|---|---|---|
| =IF | Returns a value if a condition is true, and another value if false. | =IF(A1 > 10, "Yes", "No") |
| =OR | Returns TRUE if any argument is TRUE. | =OR(A1 > 10, B1 < 5) |
| =XOR | Returns TRUE if an odd number of arguments is TRUE. | =XOR(A1 > 10, B1 < 5) |
| =AND | Returns TRUE if all arguments are TRUE. | =AND(A1 > 10, B1 > 5) |
| =NOT | Reverses the logic of its argument. | =NOT(A1 > 10) |
| =IFERROR | Returns a value if an error is found. | =IFERROR(A1/B1, "Error") |
Data Types
| Function | Description | Example |
|---|---|---|
| =ISNUMBER | Returns TRUE if the value is a number. | =ISNUMBER(A1) |
| =ISTEXT | Returns TRUE if the value is text. | =ISTEXT(A1) |
| =ISLOGICAL | Returns TRUE if the value is a logical value. | =ISLOGICAL(A1) |
| =N | Returns a value converted to a number. | =N(A1) |
| =VALUETOTEXT | Converts a value to text. | =VALUETOTEXT(A1) |
| =DATAVALUE | Converts a date in the form of text to a number that represents the date. | =DATAVALUE("10/10/2020") |
Financial Formulas
| Function | Description | Example |
|---|---|---|
| =NPV | Calculates the net present value of an investment based on a series of periodic cash flows and a discount rate. | =NPV(0.1, A1:A10) |
| =XNPV | Returns the net present value for a schedule of cash flows that is not necessarily periodic. | =XNPV(0.1, A1:A10, B1:B10) |
| =XIRR | Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. | =XIRR(A1:A10, B1:B10) |
| =YIELD | Returns the yield on a security that pays periodic interest. | =YIELD(DATE(2022,1,1), DATE(2032,1,1),0.05, 95,100,2) |
| =FV | Returns the future value of an investment. | =FV(0.05, 10, -1000, -10000) |
| =PV | Returns the present value of an investment. | =PV(0.05, 10, -1000, 0) |
| =PRICE | Returns the price per $100 face value of a security that pays periodic interest. | =PRICE(DATE(2022,1,1), DATE(2032,1,1), 0.05, 0.06, 100, 2) |
| =SLN | Returns the straight-line depreciation of an asset. | =SLN(10000, 1000, 5) |
| =DB | Returns the declining balance depreciation of an asset for a specified period. | =DB(10000, 1000, 5, 1) |
| =DDB | Returns the double-declining balance depreciation of an asset for a specified period. | =DDB(10000, 1000, 5, 1) |
Combining and Splitting Strings
| Function | Description | Example |
|---|---|---|
| ="black"&"friday" | Combines two strings into one. | ="black"&"friday" |
| =REPT | Repeats text a given number of times. | =REPT("black", 3) |
| =TEXTSPLIT | Splits text into multiple columns or rows. | =TEXTSPLIT(A1, ",") |
Mutating Strings
| Function | Description | Example |
|---|---|---|
| =MID | Returns a specific number of characters from a text string starting at the position you specify. | =MID(A1, 2, 3) |
| =UPPER | Converts a text string to all uppercase letters. | =UPPER(A1) |
| =LOWER | Converts a text string to all lowercase letters. | =LOWER(A1) |
| =PROPER | Capitalizes the first letter of each word in a text string. | =PROPER(A1) |
Data Manipulation
| Function | Description | Example |
|---|---|---|
| =FILTER | Filters a range of data based on criteria you define. | =FILTER(A1:C10, B1:B10 > 50) |
| =SORT | Sorts the contents of a range or array. | =SORT(A1:A10, 1, TRUE) |