Excel Math Basics: Writing Formulas and Expressions
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 in this tutorial. We'll learn how to add, subtract, multiply, divide, and more. And we provide lots of examples and clear explanations!
Once you understand how to create basic math formulas, you'll want to use some of Excel's cool functions that provide the ability to count, round numbers, find averages, calculate square roots, and so on. See our Excel Functions Tutorial Menu.
If you're interested in a video-based course, we recommend Excel 2010 Tutorial for Beginners. We have this course (over 9 hours of hands-on lessons) and find it very well done. It is useful for Excel 2007 and 2010, and several chapters can be previewed online.
Click on a link to go directly to that topic:
- Basic Math Formulas in Excel
- Mathematical Order of Operations
- Calculating Percentages in Excel
- Copying and Pasting Formulas in a Spreadsheet
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. A cell, or a group of cells, can be locked via the Protection tab on the Format window. Instructions for protecting a worksheet or workbook can be found in the "Protecting a Worksheet or Workbook" section of our tutorial Excel Made Easy: A Beginners Guide.
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 3.
Mathematical 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
Calculating Percentages in Excel
There are two ways to calculate percentages in Excel, depending on how the worksheet (spreadsheet) 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.
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.
When performing calculations, the resulting number may, at times, be quite large - as when dividing numbers. If the number is too large to fully display in the cell, you may see ##### in the cell. When this happens, either make the cell wider, change the cells display font, or format the cell to display fewer decimal points.
We hope this article has been helpful. If you're interested in a video-based introduction, check out Excel 2010 Tutorial for Beginners. It's a good value. Cheers!
Please link to this tutorial using the following HTML:
<a href="http://www.keynotesupport.com/excel-basics/excel-math-formulas.shtml">Excel Math Basics: A Beginners Guide</a>
- » Excel Made Easy - A Beginner's Guide
- » Formatting Cells in Microsoft Excel
- » Excel Math Basics: Writing Formulas and Expressions
- » Cell References in Microsoft Excel
- » Linking Worksheet Data in Excel
- » Microsoft Excel: Cool Keyboard Shortcuts
- » Using the Auto Fill Features of Excel
- » How to Create an Excel Chart
- » Chart Types: Pie, Column, Line, Bar, Area, Scatter
- » Customizing Charts: A Comprehensive Guide
Search Keynote Support