Excel Math Basics: A Beginner's Guide

Author: Patricia Lynn

Greetings! In a moment you will learn how to tell Microsoft Excel to add, subtract, multiply and divide! You will also learn how to calculate percentages, the mathematical Order of Operations, how Excel interprets formulas, and Absolute Cell Reference.

Our tutorial will make it easy for you to become proficient in using Excel in no time.

If you are brand new to Excel, we recommend you begin with our introduction to Microsoft Excel - Excel Made Easy - A Beginner's Guide.

Once you understand how to do math in Microsoft Excel, you need to learn some basic Excel functions that will not only help you create formulas, but allow you to count, round, find averages, use logic, etc. Check out our other tutorials listed on the menu at right.

Click on a link to go directly to that topic:

• Basic Math Formulas
• Order of Operations
• Calculating Percentages
• How Excel Interprets Cell References in Formulas
• Copying and Pasting Formulas
• Using an Absolute Cell Reference

Basic Math Formulas

Whenever the contents of the cells referenced in a math formula change, Excel will automatically recalculate the answer for you. That is what makes this software program 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.

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 any multiplication OR division is performed.
4. Lastly, any 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 illustrate with a simple formula:  4+2*3

• Pass 1 - Since the multiplication must be done first, our expression resolves itself to 4+6=10

Let's practice with a more complex 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 - Multiply and Divide: 8/4=2. Now our expression reads: 8+9-2
• Pass 4 - Add and Subtract: 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 - Multiply and Divide: 2*3=6. So now our formula reads: 9+9-6
• Pass 4 - Add and Subtract: 12

Back to Top

Calculate Percents in Excel

There are two ways you can calculate percentages in Excel, depending on how your worksheet is designed.

Display a Percent Sign in the Cell

To calculate a percentage and have the percent sign display in the cell, just enter the formula in the cell and format the cell as a Percentage. Let's suppose your formula in cell C2 is =A2/B2. If A2=25 and B2=50, then 25÷50=.5 and .5 will display in cell C2. However, format C2 as a Percentage and 50% will display instead.

As we learned in our beginner's tutorial, Excel Made Easy, to format a cell or group of cells, right-click in the cell and click "Format Cells...." Click "Percentage" on the Number tab, indicate the number of decimal points, if any, and click "OK."

Use a Column Heading of Percent and No Percent Sign in the Cell

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. Using the example above, we would want "50" to display in cell C2, not .5. To express percentages in a cell, multiply by 100. In our example, our new formula would be =(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. You could then copy and paste the formula in C2 to the other cells in column C.

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.

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.

How to Use 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.

We hope this article has been helpful. Cheers!

Related tutorials:
►COUNTIF Basics and Excel Keyboard Shortcuts.

      Back to Top