Bookmark and Share

Search Google

Excel Math Formulas

This tutorial describes how to incorporate math formulas in a Microsoft Excel spreadhseet, and gives many examples. It also discusses Order of Operations, how Excel interprets formulas, and Absolute Cell Reference.

To see examples of Excel functions, see the tutorial Excel Functions. For an introduction to Microsoft Excel, see our tutorial Excel Made Easy. Click on a topic below.

Basic Math Formulas

Whenever the contents of the cells referenced in a formula change, Excel will automatically recalculate the answer for you. That is what makes the spreadsheet so powerful. As some formulas can get extremely complicated, it is a good idea to lock those cells that contain formulas to prevent accidently typing over the formulas. If you need to change a formula, just unlock the spreadsheet. Instructions on locking and unlocking cells can be found in our Excel Made Easy tutorial. The 5 basic rules to remember as we discuss Excel formulas are:

  1. All Excel formulas start with an equal (=) sign. This tells Excel that it is a formula.
  2. The answer to the formula displays in the cell into which the formula is entered.
  3. Cells are referenced in a formula by their column-row identifier, ie. A1, B2 (think Bingo).
  4. The symbols for addition, subtraction, multiplication, and division are: + - * /
  5. You do not have to enter capital letters in your formula; Excel will automatically capitalize them for you.

Example of simple math formulas:

  • =A1+A6   this Excel formula adds the contents of cell A1 and A6
  • =A1+A2+A3   this Excel formula adds the contents of the three cells specified. (See the SUM function for adding multiple numbers)
  • =A3-A1   this Excel formula subtracts the contents of cell A1 from the contents of cell A3
  • =B2*B3   this Excel formula multiples the numbers in cells B2 and B3
  • =G5/A5   this Excel formula divides G5 by A5. (NOTE: If you see the error message #DIV/O! in a cell, you are trying to divide by zero or a null value - which is not allowed.)
  • =G5^2   this formula tells Excel to square the value in cell G5. The number after the caret is the exponent. Likewise, the formula H2^3 would cube the value in cell H2.

We can combine multiple operations in one formula. Make sure you use parenthesis where needed or you may not get the correct results (see Order of Operations below). Here are some examples:

  • =(C1+C3)/C4   This Excel formula adds the value in C1 to the value in C3, and then divides the result by the value in C4
  • =4*(A2+A5)+3   This Excel formula adds the contents of A2 and A5, multiples this sum by 4, and then adds 10.

Back to Top

Order of Operations

Remember the Order of Operations by remembering the phrase Please Excuse My Dear Aunt Sally. The letters stand for: Parentheses, Exponents, Multiplication, Division, Addition, and Subtraction. And all operations are carried out from left to right. Here is how the order is applied:

  1. First, any math inside of parentheses ic calculated.
  2. On the second pass, all exponents are resolved.
  3. Then all multiplication OR division is performed.
  4. Lastly, all addition OR subtraction is performed.

Note: Even though the Aunt Sally phrase may imply that multiplication is done before division, and addition is done before subtraction, that is not true. They are performed during the same step or pass through the formula.

Let's practice with a simple formula:  (2*4)+3^2-8/4

  • Pass 1 - Parentheses: 2*4 = 8. Now our expression reads: 8+3^2-8/4
  • Pass 2 - Exponents: 3^2=9. Now our expression reads: 8+9-8/4
  • Pass 3 - Mult and Div: 8/4=2. Now our expression reads: 8+9-2
  • Pass 4 - Add and Subt: The answer is 15

Now test your skill on a complicated formula:  3^(6/3)+(3*3)-2*(6-3)

  • Pass 1 - Parentheses: 6/3=2, 3*3=9, and 6-3=2. So now our formula reads: 3^2+9-2*3
  • Pass 2 - Exponents: 3^2=9. So now our formula reads: 9+9-2*3
  • Pass 3 - Mult and Div: 2*3=6. So now our formula reads: 9+9-6
  • Pass 4 - Add and Subt: 12

Back to Top

Calculating Percentages in Excel

There are two ways you can calculate percentages, depending on how you want the answer displayed in the Excel spreadsheet cell. Let’s assume A2=25, and B2=50. If you enter the formula =A2/B2 in C2, the answer will be .5. However, if you format C2 to display a Percentage, Excel will display 50% instead of .5 in cell C2.

Let‘s suppose you're dividing the values in column A by the values in column B, putting the answers in column C, and column C has a column heading of PERCENT. In our example we would want the number 50 displayed in cell C2. Use this Excel formula: =(A2/B2)*100. Excel will divide A2 by B2 and get .5, then multiple .5 by 100 and display the answer, 50, in cell C2.

How Excel Interprets Cell References in Formulas

Excel interprets cell references in formulas in a ‘relational’ manner. It looks at how cells relate to each other, positionally, on the grid. This is good news! Let's see why.

Let's calculate some simple column totals. We will add the contents of A1 to A2, and put the answer in A3. So in cell A3 we will type the formula =A1+A2. See the image below.

This is how Excel interprets the formula in cell A3: Take the number in the cell two cells above my current position, add it to the number in the cell one cell above my current position, and put the answer here in this cell!

This is handy is because now you can copy the formula in A3, paste it into cells B3 through E3, and Excel will automatically change the cell references in the formulas. In cell B3, Excel will write the formula =B1+B2, and so on. The same method is used by Excel if you are performing calculations on rows of numbers.

So, we have not only learned how Excel interprets formulas, but we have learned a fabulous timesaver! Notice how a cell's contents, including the formula, is always displayed in the white box at the top of the Excel spreadsheet.

Back to Top

Copying and Pasting Formulas

To copy the contents of a cell, click in the cell, right-click, and click Copy. (Or use the keyboard shortcut Ctrl+C.) Then place the cursor in the receiving cell and right-click and click Paste. (Or use the keyboard shortcut Ctrl+V.) After you paste you will notice that the first cell is blinking. To end the blinking, press the Enter key, press the Esc key, or click into a cell and begin typing.

If you are copying an Excel formula and pasting it into multiple cells, you only have to copy once. You can paste repeatedly, by moving from cell-to-cell with a keyboard arrow key.

Using an Absolute Cell Reference in Excel

What if you want to copy a formula but DON'T want Excel to automatically change one of the cell references? In the sample below, we want to divide each number in Row 2 by the number in A1, and place the answers in Row 3. Normally, the formula in cell A3 would be =A2/A1. However, if you copy and paste this formula into B3, Excel will change the formula to =B2/B1.

We can easily tell Excel NOT to change A1 in the formula by using an absolute cell reference for A1. To specify an absolute cell reference, place a $ before the column letter and row number of the cell. The formula in cell A3 should now read: =A2/$A$1. And when you copy and paste this formula into the other cells in Row 3, Excel will keep A1 constant in all of the formulas. If you look in the upper right corner of the example, you will see what the Excel formula looks like when it was copied from cell A3 to B3.

►  Related tutorial: Excel Functions.

►  Featured tutorial: All About Your Credit Report! (Don't be tricked by other sites. We explain how to get your FREE copy sponsored by the U.S. government).

Back to Top