#
Excel Formulas Cheat Sheet

A resource that provides a clear and concise view of the most used Excel functions and formulas.

## What Is An Excel Formulas Cheat Sheet?

The Excel Formula Cheat Sheet is a resource that provides a clear and concise view of the most used Excel functions and formulas.

Microsoft Excel has become an essential tool for many professions nowadays, especially the financial profession. Financial professionals are now expected to be experts at Excel as a minimum entry requirement.

In finance, where accuracy and efficiency are paramount, Excel stands as a linchpin for professionals, helping them analyze huge amounts of complex financial data and create models.

Excel formulas are the backbone of Excel financial modeling, analysis, and decision-making. They are expressions that can perform calculations, return information, and manipulate the content of other cells.

This cheat sheet discusses the basics of Excel formulas and the most commonly used functions, which are predefined formulas used as building blocks for more complex formulas and are crafted specifically for financial professionals.

For each function, we provide a brief description of what it does and provide its syntax. Moreover, we will discuss functions that are commonly used together to perform complex tasks.

## Excel Formulas Basics

Excel formulas always begin with an equals sign (=). Formulas may include arithmetic operators, logical operators, and/or functions. Functions are predefined formulas that take inputs, process them, and output a value or a set of values depending on the function’s purpose.

Arithmetic operators include (+) for addition, (-) for subtraction, (*) for multiplication, (/) for division, and (^) for raising to a power. Excel follows the order of operations (PEMDAS: parentheses, exponentials, multiplication and division, addition and subtraction).

For example, the following formula adds 5 to 3 and raises the result to a power of 2, yielding 64:

*=(5+3)^2*

Logical operators include equal to (=), not equal to (<>), greater than and less than (>, <), and greater than or equal to and less than or equal to (>=, <=). Formulas with logical operators output TRUE or FALSE, depending on the argument passed.

For example, this formula, which evaluates whether 5 is less than 3, returns FALSE:

*=5<3*

In the rest of this article, we will explore the most commonly used Excel functions. We have divided the functions we will discuss into five categories: arithmetic, statistical, text, logical, data retrieval, and financial.

Several can be combined in one formula to perform more complex processes. Be careful of overcomplicating your formulas, especially without properly documenting your work.

Note that in some examples, we use cell references as inputs; in others, we hardcode the inputs into the formulas.

Both approaches work in all functions. However, it is considered best practice not to hardcode any value into a formula or at least to keep hardcoded values at a minimum.

## Arithmetic Functions

### SUM

The SUM function is one of the simplest and most commonly used Excel functions. It is used to output the sum of a set of cells. The general formula is given below:

*=SUM (values)*

For example, to get the sum of cells B1, B2, B3, B4, and B5, we can write the formula as:

*=SUM (B1, B2, B3, B4, B5)*

We can also get the same result by specifying a contiguous range of cells from B1 to B5 as follows:

*=SUM (B1:B5)*

In addition, we can get the sum of non-contiguous ranges of cells B1 to B5, C2 to C4, and D7 using the following formula:

*=SUM (B1:B5, C2:C4, D7)*

### COUNT

The COUNT function is used to get the number of cells in a range. The formula is:

*=COUNT (cells)*

### COUNTA

The COUNTA function is used to get the number of non-empty cells in a range. The formula is:

*=COUNTA (cells)*

Note, however, that cells with empty text (“”) and those with errors are not considered empty and will be included in the count.

### MAX

The MAX function is used to get the maximum value of a set of values. The formula is:

*=MAX (values)*

### MIN

The MIN function is used to get the minimum value of a set of values. The formula is:

*=MIN (values)*

## Statistical Functions

### AVERAGE

The AVERAGE function calculates the arithmetic mean (sum divided by number) of a set of values. The formula is:

*=AVERAGE (values)*

### MEDIAN

The MEDIAN function outputs the middle value or the average of the two middle values of a set of values. The formula is:

*=MEDIAN (values)*

### VAR.P/VAR.S

The VAR.P and VAR.S functions are used to calculate the variance of a population or sample, respectively, of a set of values. The formulas are:

*=VAR.P (values)*

*=VAR.S (values)*

### STDEV.P/STDEV.S

The STDEV.P function is used to calculate the standard deviation (square root of variance) of an entire population, while the STDEV.S function is used for a sample from a population.

The formulas are:

*=STDEV.P (values)*

*=STDEV.S (values)*

## Text Functions

### TRIM

The TRIM function takes a text input and all spaces in it, except for single spaces between words. The formula is

*=TRIM (text)*

For example, the following formula returns “Hello fellow WSO members!”

*=TRIM (“Hello fellow WSO members”)*

### LEFT

The LEFT function takes a text input, extracts a specific number of characters starting from the left, and returns the characters. The formula is:

*=LEFT (text, number of characters)*

### MID

The MID function takes a text input and extracts a specific number of characters starting from a specified character whose position in the text is called the start number. The formula is:

*=MID (text, start number, number of characters)*

### RIGHT

The RIGHT function takes a text input, extracts a specific number of characters starting from the right, and returns the characters. The formula is:

*=RIGHT (text, number of characters)*

## Logical Functions

### IF

The IF function evaluates an argument and outputs a value or the output of another formula depending on whether the argument is true or false. The general formula is:

*=IF (argument, output if true, output if false)*

For example, the following formula evaluates whether the value in B2 is greater than 0. If it is, the formula divides the value in B3 by the value in B2 and outputs the result. If not, it outputs the text “N/A.”

*=IF (B2>0, B3/B2, “N/A”)*

### AND

The AND function evaluates a set of arguments and outputs TRUE if and only if all the arguments are true and FALSE if at least one of the arguments is false. The general formula is:

*=AND (argument 1, argument 2, etc)*

It can be combined with the IF function to determine the output based on a set of arguments. For example, the following formula evaluates whether the values in both B2 and B3 are greater than zero. If they are, it outputs the result of B3/B2. If not, it outputs the text “N/A.”

*=IF(AND(B2>0, B3>0), B3/B2, “N/A”)*

### OR

The OR function evaluates a set of arguments and outputs TRUE if at least one of the arguments is true and FALSE if and only if all the arguments are false. The general formula is:

*=OR (argument 1, argument 2, etc)*

As with the AND function, the OR function can be combined with the IF function to create more complex formulas.

## Data Retrieval Functions

### VLOOKUP

The VLOOKUP (vertical lookup) function retrieves a value from a table, given the corresponding value in the first column of the table. 0 for range lookup looks for an exact match, while 1 (default) looks for an approximate match. The formula is:

*=VLOOKUP (lookup value, lookup table, column number, [range lookup])*

For example, the following formula searches for a value in the third column of Table1 corresponding to “United States” in the first column:

*=VLOOKUP (“United States”, Table1, 3, 0)*

### HLOOKUP

The HLOOKUP (horizontal lookup) function is used to retrieve a value from a table, given the corresponding value in the first row of the table. 0 for range lookup looks for an exact match, while 1 (default) looks for an approximate match. The formula is:

*=HLOOKUP (lookup value, lookup table, row number, [range lookup])*

### INDEX

The INDEX function retrieves a value from a table. Its inputs are the data table (lookup array) and row and/or column number. If one is not specified, it is assumed to be. However, at least one has to be specified. The formula is:

*=INDEX (lookup array, row number, column number)*

### MATCH

The MATCH function searches for a specific value in a range of cells and returns the position of the cell containing the value within the range.

An optional match type may be specific. 1, the default, means that the function will look for the greatest value that is less than or equal to the lookup value. -1 means that it will look for the smallest value greater than or equal to the lookup value. 0 looks for the first exact match.

The formula of the MATCH function is

*=MATCH (lookup value, lookup array, match type)*

The MATCH function is very commonly combined with the INDEX function. This combination allows for more powerful and versatile data retrieval than VLOOKUP or HLOOKUP. The formula is:

=INDEX (data table, MATCH (column header, column headers row, 0), MATCH (row header, row headers column, 0))

## Financial Functions

### NPV

The NPV calculates the net present value of an investment by discounting a series of regular future payments starting from the next period at a discount rate. The formula is:

*=NPV (discount rate, payments)*

For example, to calculate the NPV of an investment with an initial cost of $5,000, yearly payments in cells B2:B5, and a discount rate of 10%, we can use the following formula:

*=NPV (0.1, B2:B5) - 5,000*

### IRR

The IRR function calculates an investment's internal rate of return, that is, the discount rate at which the investment's net present value becomes zero.

The function takes as inputs payments (negative values) and receipts (positive values) occurring at regular time intervals and an optional guess of the final result. The formula is:

*=IRR (cash flows, [guess])*

### PMT

The PMT function calculates the payment for a loan with constant payments at regular time intervals and a constant interest rate.

It takes the interest rate, number of periods, present value (current balance of the loan), and an optional future value (remaining balance at the end) as inputs with a default of zero and type of default of 0 or 1 if the payments are due at the beginning or end of the period, respectively.

The formula is:

*=PMT (rate, number of periods, present value, [future value], [type])*

or Want to

Sign upwith your social account?