Excel Math Basics: A Beginner's Guide

Image of Microsoft Excel logo

How do I do math in Excel? How do I enter a simple equation? These are the first questions new Excel users ask. And we answer them for you in this tutorial. In a moment you'll learn how to add, subtract, multiply, divide, and more ... in Microsoft Excel. And we provide lots of examples and clear explanations!

Author: Patricia Lynn

Once you understand how to create basic math formulas in Microsoft Excel, your next step is to learn some of Excel's cool functions that provide the ability to count, round numbers, find averages, calculate square roots, use logic, and so on. Check out our other tutorials listed on the menu at right. PS: If you are new to Excel, we recommend you begin with our tutorial - Excel Made Easy - A Beginner's Guide.

If you're interested in a video-based introduction, we recommend Excel 2010 Tutorial for Beginners. We've reviewed this course (over 9 hours of hands-on lessons) and find it very well done. It is useful for Excel 2007 and 2010 users.

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 Calculations in Excel

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 the formulas and protect the worksheet. Instructions can be found in our tutorial Excel Made Easy: A Beginner's Guide - Locking Calls and Protecting Worksheets. The link will take you directly to the appropriate section of the tutorial.

The 5 basic rules to remember as we discuss Excel formulas are:

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

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 parentheses 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:

First, any math inside of parentheses is calculated.
On the second pass, all exponents are resolved.
Then any multiplication OR division is performed.
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

Calculate Percents in Excel

There are two ways to calculate percentages in Excel, depending on how the 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 the 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.

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. Excel worksheet example showing how Excel interprets cell references in a relational manner

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 when 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, right-click, and click Paste. (Or use the keyboard shortcut Ctrl+V.) To remove the animated border on the original cell, press Enter, press the Esc key, or click in another cell and begin typing.

When pasting the contents of a cell into multiple cells, the cell contents need only be copied once. Use the arrows on the keyboard to move to the other cells and paste.

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. Excel worksheet example showing absolute cell reference

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. When copying and pasting the formula into other cells, Excel will keep A1 constant. Look at the formula in cell B3 in the worksheet below; the formula displays in the Formula Bar.

Final Thoughts

Sometimes Excel displays an error in a worksheet cell instead of the expected value. Every Excel error messages begin with a pound sign (#). The most common error, #####, indicates that the cell is too narrow to display the data. To make the column wider, hover the cursor on the right side of the column heading and drag the column edge to the right.

We hope this article has been helpful. If you want to learn more about Excel, and you're interested in a video-based introduction, check out Excel 2010 Tutorial for Beginners, with over 9 hours of hands-on lessons. Cheers!

Share |