• Printable List Of Excel Formulas
• Microsoft Excel Basic Formulas Pdf
• Microsoft Excel Advanced Formulas Tutorial Pdf Free Download

Mastering basic Excel formulas is critical for beginners to become proficient in financial analysis. Microsoft Excel is considered the industry standard piece of software in data analysis. Microsoft's spreadsheet program also happens to be one of the most preferred software by investment bankers. These advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis.

An Excel spreadsheet is helpful for displaying information in an organized way but Excel can be used for so much more. Its powerful calculation ability enables you to enter, manipulate, and analyze numbers. To take advantage of this function, you need to learn about formulas, which are essentially math equations. Here we describe how to create basic and slightly more complex formulas in Excel.

This article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Office 365.

Excel Formula Basics

Writing a spreadsheet formula is different from writing an equation in math class. The most notable difference is that Excel formulas start with the equal sign (=) instead of ending with it.

Excel formulas look like =3+2 instead of 3 + 2 =.

The equal sign indicates that what follows is part of a formula and not just a word or number that you want to appear in the cell. After you type the formula and press Enter on your keyboard, the result of the formula appears in the cell.

For example, if you type the formula above, =3+2 into a cell and press Enter, the result, 5, appears in the cell. The formula is still there, but it doesn't appear in your spreadsheet. If you select the cell, though, the formula appears in the formula bar at the top of the Excel screen.

Improve Formulas with Cell References

Excel formulas can also be developed using cell references. Continuing with our example, you would not enter the numbers 3 and 2, but instead would name cells where these numbers have been entered (see Using Cell References below for more on cell naming). When you write a formula this way, the formula cell always shows the sum of the numbers in those cells, even if the numbers change.

Here's a real-life example of how this approach can be useful. Say you lead a team of salespeople and are tracking their monthly and quarterly sales. You want to calculate their total sales for the year. Instead of entering every quarterly sales value into a formula, you use cell references to identify the cells where those values can be found within the spreadsheet.

Using Cell References

Each cell in Excel is part of a row and a column. Rows are designated with numbers (1, 2, 3, etc.) shown along the left side of the spreadsheet, while columns are designated with letters (A, B, C, etc.) shown along the top. To refer to a cell, use the column letter and row number together, such as A1 or W22 (the column letter always comes first). If you have a cell selected, you can see its reference at the top of the screen in the Name Box next to the formula bar.

In the image above, notice the cell references in the formula bar: E2, I2, M2, and Q2. They refer to the quarterly sales numbers for the salesperson named Jean. The formula adds those numbers together to come up with the annual sales number. If you update the numbers in one or more of those cells, Excel will recalculate and the result will still be the sum of the numbers in the referred cells.

Create a Formula With Cell References

Try creating a simple formula using cell references.

1. First, you must populate the spreadsheet with data. Open a new Excel file and select cell C1 to make it the active cell.

2. Type 3 in the cell, then press Enter on your keyboard.

3. Cell C2 should be selected. If it's not, select cell C2. Type 2 in the cell and press Enter on your keyboard.

4. Now create the formula. Select cell D1 and type =C1+C2. Notice that when you type each cell reference, that cell becomes highlighted.

5. Press Enter to complete the formula. The answer 5 appears in cell D1.

If you select cell D1 again, the complete formula =C1+C2 appears in the formula bar above the worksheet.

Enter Cell References With Pointing

Pointing is yet another way to refer to the values you want to include in your formula; it involves using your pointer to select cells to include in your formula. This method is the fastest of those we've discussed; it's also the most accurate because you eliminate the risk of making a mistake in typing in numbers or cell references. Here's how to do it (starting with the spreadsheet from the examples above):

1. Select cell E1 to make it the active cell and type in the equal sign (=).

2. Use your pointer to select cell C1 to enter the cell reference in the formula.

3. Type a plus sign (+), then use your pointer to select C2 to enter the second cell reference into the formula.

4. Press Enter to complete the formula. The result appears in cell E1.

5. To see how altering one of the formula values alters the result, change the data in cell C1 from 3 to 6 and press Enter on your keyboard. Notice that the results in cells D1 and E1 both change from 5 to 8, though the formulas remain unchanged.

Mathematical Operators and Order of Operations

Now we turn to operations besides addition, including subtraction, division, multiplication, and exponentiation. The mathematical operators used in Excel formulas are similar to those you may remember from math class:

• Subtraction – minus sign ( - )
• Addition – plus sign ( + )
• Division – forward-slash ( / )
• Multiplication – asterisk ( * )
• Exponentiation – caret ( ^ )

If more than one operator is used in a formula, Excel follows a specific order to perform the mathematical operations. An easy way to remember the order of operations is to use the acronym BEDMAS.

• Brackets
• Exponents
• Division
• Multiplication
• Addition
• Subtraction

Excel actually considers division and multiplication to be of equal importance. It performs these operations in the order in which they occur, from left to right. The same is true for addition and subtraction.

Here's a simple example of the order of operations in use. In the formula =2*(3+2) the first operation Excel completes is the one inside the brackets (3+2), with the result of 5. It then performs the multiplication operation, 2*5, with the result of 10. (The values in the formula could be represented by cell references rather than numbers, but Excel would perform the operations in the same order.) Try entering the formula into Excel to see it work.

Enter a Complex Formula

Now let's create a more complex formula.

1. Open a new spreadsheet and populate it with data as follows:

   • 7 in cell C1
   • 5 in cell C2
   • 9 in cell C3
   • 6 in cell C4
   • 3 in cell C5

2. Select cell D1 to make it the active cell and type the equal sign followed by a left bracket (=().

3. Select cell C2 to enter the cell reference in the formula, then type the minus sign (-).

4. Select cell C4 to enter this cell reference into the formula, then type a right bracket ()).

5. Type the multiplication sign (*), then select cell C1 to enter this cell reference into the formula.

6. Type the plus sign (+), then select C3 to enter this cell reference into the formula.

7. Type the division sign (/), then select C5 to enter this cell reference into the formula.

8. Press Enter to complete the formula. The answer -4 appears in cell D1.

How Excel Calculated the Result

In the above example, Excel arrived at the result of -4 using the BEDMAS rules as follows:

Printable List Of Excel Formulas

• Brackets. Excel first carried out the operation within the brackets, C2-C4 or 5-6 for a result of -1.
• Exponents. There are no exponents in this formula, so Excel skipped this step.
• Division and Multiplication. There are two of these operations in the formula and Excel performed them from left to right. First, it multiplied -1 by 7 (the content of cell C1) to get a result of -7. It then performed the division operation, C3/C5 or 9/3, for a result of 3.
• Addition and Subtraction. The last operation Excel performed was the addition of -7+3 for the final result of -4.

Get started on how to create formulas and use built-in functions to perform calculations and solve problems.

Important: The calculated results of formulas and some Excel worksheet functions may differ slightly between a Windows PC using x86 or x86-64 architecture and a Windows RT PC using ARM architecture. Learn more about the differences.

Create a formula that refers to values in other cells

1. Select a cell.

2. Type the equal sign =.

   Note: Formulas in Excel always begin with the equal sign.

3. Select a cell or type its address in the selected cell.

4. Enter an operator. For example, – for subtraction.

5. Select the next cell, or type its address in the selected cell.

6. Press Enter. The result of the calculation appears in the cell with the formula.

See a formula

1. When a formula is entered into a cell, it also appears in the Formula bar.

2. To see a formula, select a cell, and it will appear in the formula bar.

Enter a formula that contains a built-in function

1. Select an empty cell.

2. Type an equal sign = and then type a function. For example, =SUM for getting the total sales.

3. Type an opening parenthesis (.

4. Select the range of cells, and then type a closing parenthesis).

5. Press Enter to get the result.

Download our Formulas tutorial workbook

We've put together a Get started with Formulas workbook that you can download. If you’re new to Excel, or even if you have some experience with it, you can walk through Excel’s most common formulas in this tour. With real-world examples and helpful visuals, you’ll be able to Sum, Count, Average, and Vlookup like a pro.

Formulas in-depth

You can browse through the individual sections below to learn more about specific formula elements.

The parts of an Excel formula

A formula can also contain any or all of the following: functions, references, operators, and constants.

Parts of a formula

1. Functions: The PI() function returns the value of pi: 3.142...

2. References: A2 returns the value in cell A2.

3. Constants: Numbers or text values entered directly into a formula, such as 2.

4. Operators: The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies numbers.

Using constants in Excel formulas

A constant is a value that is not calculated; it always stays the same. For example, the date 10/9/2008, the number 210, and the text 'Quarterly Earnings' are all constants. An expression or a value resulting from an expression is not a constant. If you use constants in a formula instead of references to cells (for example, =30+70+110), the result changes only if you modify the formula. In general, it's best to place constants in individual cells where they can be easily changed if needed, then reference those cells in formulas.

Using references in Excel formulas

A reference identifies a cell or a range of cells on a worksheet, and tells Excel where to look for the values or data you want to use in a formula. You can use references to use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, and to other workbooks. References to cells in other workbooks are called links or external references.

• The A1 reference style

  By default, Excel uses the A1 reference style, which refers to columns with letters (A through XFD, for a total of 16,384 columns) and refers to rows with numbers (1 through 1,048,576). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, B2 refers to the cell at the intersection of column B and row 2.

  To refer to	Use
  The cell in column A and row 10	A10
  The range of cells in column A and rows 10 through 20	A10:A20
  The range of cells in row 15 and columns B through E	B15:E15
  All cells in row 5	5:5
  All cells in rows 5 through 10	5:10
  All cells in column H	H:H
  All cells in columns H through J	H:J
  The range of cells in columns A through E and rows 10 through 20	A10:E20

• Making a reference to a cell or a range of cells on another worksheet in the same workbook

  In the following example, the AVERAGE function calculates the average value for the range B1:B10 on the worksheet named Marketing in the same workbook.

  1. Refers to the worksheet named Marketing

  2. Refers to the range of cells from B1 to B10

  3. The exclamation point (!) Separates the worksheet reference from the cell range reference

  Note: If the referenced worksheet has spaces or numbers in it, then you need to add apostrophes (') before and after the worksheet name, like ='123'!A1 or ='January Revenue'!A1.

• The difference between absolute, relative and mixed references

  1. Relative references A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy or fill the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy or fill a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2.

     Copied formula with relative reference

  2. Absolute references An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy or fill the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, so you may need to switch them to absolute references. For example, if you copy or fill an absolute reference in cell B2 to cell B3, it stays the same in both cells: =$A$1.

     Copied formula with absolute reference

  3. Mixed references A mixed reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy or fill the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copy or fill a mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1.

     Copied formula with mixed reference

• The 3-D reference style

  Conveniently referencing multiple worksheets If you want to analyze data in the same cell or range of cells on multiple worksheets within a workbook, use a 3-D reference. A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. Excel uses any worksheets stored between the starting and ending names of the reference. For example, =SUM(Sheet2:Sheet13!B5) adds all the values contained in cell B5 on all the worksheets between and including Sheet 2 and Sheet 13.

  • You can use 3-D references to refer to cells on other sheets, to define names, and to create formulas by using the following functions: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA, and VARPA.

  • 3-D references cannot be used in array formulas.

  • 3-D references cannot be used with the intersection operator (a single space) or in formulas that use implicit intersection.

  What occurs when you move, copy, insert, or delete worksheets The following examples explain what happens when you move, copy, insert, or delete worksheets that are included in a 3-D reference. The examples use the formula =SUM(Sheet2:Sheet6!A2:A5) to add cells A2 through A5 on worksheets 2 through 6.

  • Insert or copy If you insert or copy sheets between Sheet2 and Sheet6 (the endpoints in this example), Excel includes all values in cells A2 through A5 from the added sheets in the calculations.

  • Delete If you delete sheets between Sheet2 and Sheet6, Excel removes their values from the calculation.

  • Move If you move sheets from between Sheet2 and Sheet6 to a location outside the referenced sheet range, Excel removes their values from the calculation.

  • Move an endpoint If you move Sheet2 or Sheet6 to another location in the same workbook, Excel adjusts the calculation to accommodate the new range of sheets between them.

  • Delete an endpoint If you delete Sheet2 or Sheet6, Excel adjusts the calculation to accommodate the range of sheets between them.

• The R1C1 reference style

  You can also use a reference style where both the rows and the columns on the worksheet are numbered. The R1C1 reference style is useful for computing row and column positions in macros. In the R1C1 style, Excel indicates the location of a cell with an 'R' followed by a row number and a 'C' followed by a column number.

  Reference	Meaning
  R[-2]C	A relative reference to the cell two rows up and in the same column
  R[2]C[2]	A relative reference to the cell two rows down and two columns to the right
  R2C2	An absolute reference to the cell in the second row and in the second column
  R[-1]	A relative reference to the entire row above the active cell
  R	An absolute reference to the current row

  When you record a macro, Excel records some commands by using the R1C1 reference style. For example, if you record a command, such as clicking the AutoSum button to insert a formula that adds a range of cells, Excel records the formula by using R1C1 style, not A1 style, references.

  You can turn the R1C1 reference style on or off by setting or clearing the R1C1 reference style check box under the Working with formulas section in the Formulas category of the Options dialog box. To display this dialog box, click the File tab.

Microsoft Excel Basic Formulas Pdf

Need more help?

Microsoft Excel Advanced Formulas Tutorial Pdf Free Download

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.