A Beginner's Guide to Excel Formulas

01
of 07

Excel Formulas Overview

Formulas on a blackboard
Jon Boyes/Photographer's Choice RF/Getty Images

Excel Formulas Overview

Excel formulas allow you to perform calculations on number data entered into a worksheet.

Excel formulas can be used for basic number crunching, such as addition or subtraction, as well as more complex calculations, such as payroll deductions, finding a student's average on test results, and calculating mortgage payments.

Additionally, if the formula is entered correctly and the data used in the formula changes, by default, Excel will automatically recalculate and update the answer.

This tutorial covers in detail how to create and use formulas, including a step by step example of a basic Excel formula.

It also includes a more complex formula example that relies on Excel's order of operations to calculate the correct answer.

The tutorial is intended for those with little or no experience in working with spreadsheet programs such as Excel.

Note: If you want to add up a column or row of numbers, Excel has a built in formula called the SUM function that makes the job quick and easy.

02
of 07

Excel Formula Basics

Excel Formula Basics
Excel Formula Basics. © Ted French

Always Start with the Equal Sign

Writing a spreadsheet formula is a little different than writing one in math class.

The most notable difference being that in Excel, formulas start with the equal sign ( = ) rather than ending with it.

Excel formulas look like this:

=3 + 2

rather than:

3 + 2 =

Additional Points

  • The equal sign always goes in the cell where you want the formula's answer to appear.
  • The equal sign informs Excel that what follows is part of a formula, and not just a name or a number.
  • As shown in the image above, once entered, the cell containing the formula shows the answer, rather than the formula.
  • The formula can always be seen in the formula bar located above the worksheet.

03
of 07

Using Cell References in Excel Formulas

Using Cell References in Excel Formulas
Using Cell References in Excel Formulas. © Ted French

Improving the Formula: Using Cell References

While the formula on the previous page works, it has one major drawback - If you need to change the data used in the formula, you need to edit or rewrite the formula.

A better way would be to write a formula so that the data can be changed without having to change the formula itself.

This can be done by entering the data into worksheet cells and then informing the program which cells contain the data to be used in the formula.

This way, if the formula's data needs to be changed, it is done by altering the data in the worksheet cells, rather than altering the formula itself.

In order to tell Excel which cells contain the data you want to use, each cell has an address or cell reference.

About Cell References

  • A cell is the intersection point between a vertical column and a horizontal row in the worksheet
  • Each column is identified by a letter at the top of the column - such as A, B, C
  • Each row is identified by a number located at the left edge of the row - such a 1, 2, 3
  • A cell reference, then, is a combination of the column letter and row number that intersect at a cell's location -- such as A1, B2, C3, or W345
  • When writing cell references, the column letter always comes first

To find a cell reference, simply look up to see which column the cell is in, and then look to the left to find which row it is in.

The current cell - the reference of the cell currently clicked on - is also displayed in the Name Box located above column A in the worksheet.

So, instead of writing this formula in cell D1:

 = 3 + 2

It would be better to enter the data into cells C1 and C2 and write this formula instead:

 = C1+C2

04
of 07

Excel Basic Formula Example

Using Cell References in a Basic Excel Formula
Using Cell References in the Formula. © Ted French

Excel Formula Example Overview

This example gives step by step instructions to create the basic Excel formula seen in the image above.

A second, more complex example using multiple mathematical operators and involving Excel's order of operations is included on the last page of the tutorial.

Entering the Tutorial Data

It is usually best to first enter all of data into the worksheet before creating the formulas. This makes it easier to tell which cell references need to be included in the formula.

Entering data into a worksheet cell is a two-step process:

  1. Type the data into the cell
  2. Press the Enter key on the keyboard or click on another cell with the mouse pointer to complete the entry

Tutorial Steps

  1. Click on cell C1 to make it the active cell
  2. Type a 3 into the cell and press the Enter key on the keyboard
  3. If necessary, click on cell C2
  4. Type a 2 in into the cell and press the Enter key on the keyboard

Entering the Formula

  1. Click on cell D1 - this is the location where the results of the formula will be seen
  2. Type the following formula into cell D1:
     =C1+C2 
  3. Press the Enter key on the keyboard to complete the formula
  4. The answer 5 should appear in cell D1
  5. If you click on cell D1 again, the complete function =C1+C2 appears in the formula bar above the worksheet

Improving the Formula - Again: Entering Cell References with Pointing

Typing in the cell references as part of a formula is a valid way of entering them - as proven by the answer of 5 in cell D1 - it's just not the best way to do it.

The best way to enter the cell references into a formula is to use pointing.

Pointing involves clicking on cells with the mouse pointer to enter their cell reference into the formula. The main advantage of using pointing is that it helps to eliminate possible errors caused by typing in the wrong cell reference.

The instructions on the next page use pointing to enter the cell references for the formula into cell D2.

05
of 07

Using Pointing to Enter Cell References into an Excel Formula

Using Pointing to Enter Cell References into an Excel Formula
Using Pointing to Enter Cell References. © Ted French

Using Pointing to Enter Cell References

This step in the tutorial uses the mouse pointer to enter the cell references for the formula into cell D2.

  1. Click on cell D2 to make it the active cell
  2. Type the equal sign ( = ) into cell D2 to start the formula
  3. Click on cell C1 with the mouse pointer to enter the cell reference into the formula
  4. Type a plus sign (+)
  5. Click on cell C2 with the mouse pointer to enter the second cell reference into the formula
  6. Press the Enter key on the keyboard to complete the formula
  7. The answer 5 should appear in cell D2

Updating the Formula

To test the value of using cell references in an Excel formula, change the data in cell C1 from 3 to 6 and press the Enter key on the keyboard.

The answers in both cells D1 and D2 should automatically change from 5 to 8, but the formulas in both remain unchanged.

06
of 07

Mathematical Operators and the Order of Operations

Excel's Mathematical Operators
Excel's Mathematical Operators.

As shown by the just-completed example, creating formulas in Microsoft Excel is not difficult.

It is just a matter of combining, in the right order, the cell references of your data with the correct mathematical operator.

Mathematical Operators

The mathematical operators used in Excel formulas are similar to the ones used in math class.

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

Order of Operations

If more than one operator is used in a formula, there is a specific order that Excel will follow to perform these mathematical operations.

This order of operations can be changed by adding brackets to the equation. An easy way to remember the order of operations is to use the acronym:

BEDMAS

The Order of Operations is:

Brackets
Exponents
Division
Multiplication
Addition
Subtraction

How the Order of Operations Works

  • Any operation(s) contained in brackets will be carried out first followed by any exponents.
  • After that, Excel considers division or multiplication operations to be of equal importance, and carries out these operations in the order they occur left to right in the equation.
  • The same goes for the next two operations addition and subtraction. They are considered equal in the order of operations. Whichever one appears first in an equation, either addition or subtraction, is the operation carried out first.

Example: Using Multiple Operators and the Order of Operations in an Excel Formula

On the next page are instructions for creating a formula that includes multiple mathematical operators and uses Excel's order of operations to calculate the answer.

07
of 07

Using Multiple Operators in Excel Formulas

Using Excel's Order of Operations in a Formula's Calculations
Using Excel's Order of Operations in a Formula's Calculations. © Ted French

Example: Using BEDMAS in More Complex Excel Formulas

This second formula example, shown in the image above, requires Excel to utilize its order of operations to calculate the answer.

Entering the Data

  1. Open a blank worksheet and enter the data shown in cells C1 to C5 in the image above

A More Complex Excel Formula

Use pointing along with the correct brackets and mathematical operators to enter the following formula into cell D1.

 =(C2-C4)*C1+C3/C5

Press the Enter key on the keyboard when finished and the answer -4 should appear in cell D1. The details of how Excel calculates this answer are listed below.

Detailed Steps for Entering the Formula

If you need help, use the steps below to enter the formula.

  1. Click on cell D1 to make it the active cell
  2. Type the equal sign ( = ) into cell D1
  3. Type a round open bracket " ( " after the equal sign
  4. Click on cell C2 with the mouse pointer to enter the cell reference into the formula
  5. Type the minus sign ( - ) after C2
  6. Click on cell C4 to enter this cell reference into the formula
  7. Type a round closing bracket " ) " after C4
  8. Type the multiplication sign ( * ) after the closing round bracket
  9. Click on cell C1 to enter this cell reference into the formula
  10. Type the plus sign ( + ) after C1
  11. Click on cell C3 to enter this cell reference into the formula
  12. Type the division sign ( / ) after C3
  13. Click on cell C5 to enter this cell reference into the formula
  14. Press the Enter key on the keyboard to complete the formula
  15. The answer -4 should appear in cell D1
  16. If you click on cell D1 again, the complete function =(C2-C4)*C1+C3/C5 appears in the formula bar above the worksheet

How Excel Calculates the Formula Answer

Excel arrives at the answer of -4 for the formula above using the BEDMAS rules to carry out the various mathematical operations in the following order:

  1. Excel first carries out the subtraction operation (C2-C4) or (5-6), since it is surrounded by brackets, and gets the result of -1
  2. Next the program multiplies that -1 by 7 (contents of cell C1) to get an answer of -7
  3. Then Excel skips ahead to divide 9/3 (contents of C3/C5) since it comes before addition in BEDMAS, to get a result of 3
  4. The last operation that needs to be carried out is to add -7 + 3 to get an answer for the entire formula of -4