Beginner’s Guide to Excel Formulas

Content
  1. Just learning Excel formulas? This guide is for you
  2. Basics of Excel Formulas
  3. Always start with an equal sign
  4. Improve formulas with cell references
  5. About cell links
  6. Basic Excel Formula Example
  7. self study
  8. Enter formula
  9. Enter cell references that indicate:
  10. Update formula
  11. Mathematical operators and order of operations
  12. Mathematical Operators
  13. Order of operations
  14. How the order of operations works
  15. Using multiple operators in Excel formulas
  16. Enter data
  17. More complex Excel formula
  18. Detailed steps for entering a formula
  19. How Excel calculates the answer from a formula

Just learning Excel formulas? This guide is for you

If a formula is entered correctly and the data used in the formula changes, the answer is automatically recalculated and updated.

This tutorial describes how to create and use formulas and provides a step-by-step example of a simple Excel formula. It also includes an example of a complex formula that uses Excel’s sequence of operations to calculate the correct answer.

Remark † The instructions in this article apply to Excel 2019, 2016, 2013, and 2010; Excel Online; Excel 2019 for Mac, Excel 2016 for Mac, and Excel 2011 for Mac.

Basics of Excel Formulas

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

Always start with an equal sign

The most notable difference is that Excel formulas start with an equal sign instead of ending with one.

Excel formulas look like this:
= 3 + 2
instead of:
3 + 2 =

The equal sign (=) always comes in the cell where you want the formula answer to appear. The equal sign means that what follows is part of the formula and not just a name or number.

After you enter a formula, the cell that contains the formula displays the answer, not the formula. To see the formula, select the cell containing the answer and the formula will appear in the formula bar above the worksheet.

While this simple example works, it has one major drawback. If you need to change the data used in a formula, you must edit or rewrite the formula.

Improve formulas with cell references

It is better to write a formula so that the data can be changed without changing the formula itself. You can do this by entering data into the cells of the worksheet and then selecting the cells that contain the data to be used in the formula.

So if it is necessary to change the data of a formula, the change is made by changing the data in the cells of the table, not by changing the formula itself.

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

About cell links

  • A cell is the intersection of a vertical column and a horizontal row on a worksheet.

  • Each column is identified by a letter at the top of the column, such as A, B, and C.

  • Each line is identified by a number on the left edge of the line, such as 1, 2, and 3.

  • A cell reference is a combination of a column letter and a row number that intersect at the location of the cell, for example A1 B2 C3 and W345.

  • When writing cell references, the column letter always comes first.

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

The current cell (the link to the selected cell) also appears in the Name box above column A on the worksheet.

So instead of writing this formula in cell D1:

 = 3 + 2 

Enter data in cells C1 and C2, and write this formula instead:

 = C1 + C2 

Basic Excel Formula Example

Before you start building formulas, first fill in all the data in the table. This makes it easy to determine which cell references to include in a formula.

Entering data into a worksheet cell consists of two steps:

  1. Enter data in the cell.

  2. Click Enter or select another cell to complete the entry.

self study

To enter data as shown in the example accompanying this section:

  1. Select cell C1 to make it active.

  2. Enter 3 in a cell and click Enter

  3. Select cell C2

  4. Enter 2 in a cell and click Enter

Enter formula

  1. Select cell D1 The in which the results of the formula are displayed.

  2. Enter the following formula in cell D1:

     = C1 + C2 
  3. Click Enter to complete the formula.

  4. Answer 5 displayed in cell D1.

  5. If you select a cell again D1 full formula = C1 + C2 appears in the formula bar above the worksheet.

Entering cell references as part of a formula is a valid way to enter them, as evidenced by the answer 5 in cell D1. But there is another way to do it.

Enter cell references that indicate:

The best way to enter cell references in a formula is to use a hint. Hint involves selecting cells to enter their cell reference into the formula. The main benefit of using a pointer is that it helps eliminate potential errors caused by entering the wrong cell reference.

In this step of the tutorial, you select cells to enter the cell references for the formula in cell D2.

  1. Select cell D2 to make it active.

  2. Enter an equal sign () to cell D2 to start the formula.

  3. Select cell C1 to enter a cell reference in a formula.

  4. Enter a plus sign (

  5. Select cell C2 to enter a reference to the second cell in the formula.

  6. Click Enter to complete the formula.

The answer 5 appears in cell D2.

Update formula

To check the value of using cell references in an Excel formula, change the data in cell C1 from 3 to 6 and click Enter

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

Mathematical operators and order of operations

Creating formulas in Microsoft Excel is not difficult. It’s just a matter of concatenating the cell references of your data in the correct order with the correct math operator.

Mathematical Operators

Mathematical operators used in Excel formulas are similar to those used in math class.

  • Subtract – minus sign (

  • Complement – plus sign (

  • Division – forward slash (

  • Multiplication – asterisk (

  • Exposure – transport (

Order of operations

If more than one operator is used in a formula, Excel performs a specific order in which the math operations are performed.

You can change this order of operations by adding parentheses to the equation. An easy way to remember the order of operations is to use the acronym BEDMAS.

  • B rackets
  • E exponents
  • d IVISION
  • m duplication
  • A edition
  • s subtract

How the order of operations works

All operations in parentheses are performed first, followed by any exponents.

Excel then considers the operations of division or multiplication equally important. These operations are performed in the order they are performed from 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. Depending on who comes first in the equation, addition or subtraction is done first.

Using multiple operators in Excel formulas

The second formula example requires Excel to use the order of operations to calculate the answer.

Enter data

Open a blank sheet and enter the data shown in cells C1 through C5 in the image above.

More complex Excel formula

Use the hint, along with the appropriate parentheses and math operators, to enter the following formula in cell D1:

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

When you’re done, click Enter and the answer -4 appears in cell D1.

Detailed steps for entering a formula

If you need help, use these steps to enter the formula:

  1. Select cell D1 to make it active.

  2. Enter an equal sign () to cell D1.

  3. Enter an open parenthesis () after the equal sign.

  4. Select cell C2 to enter a cell reference in a formula.

  5. Enter a minus sign () after C2.

  6. Select cell C4 to enter that cell reference into the formula.

  7. Enter a round closing bracket () after C4.

  8. Enter the multiplication sign () after the closing parenthesis.

  9. Select cell C1 to enter that cell reference into the formula.

  10. Enter a plus sign () after C1.

  11. Select cell C3 to enter that cell reference into the formula.

  12. Enter a division character () after C3.

  13. Select cell C5 to enter that cell reference into the formula.

  14. Click Enter to complete the formula.

  15. The answer -4 appears in cell D1.

  16. If you select a cell again D1 full function = (C2-C4) * C1 + C3/C5 appears in the formula bar above the worksheet.

How Excel calculates the answer from a formula

Excel gets the answer -4 for the above formula by using BEDMAS rules to perform various math operations in the following order:

  1. Excel performs the subtraction operation (C2-C4) or (5-6) first because it is enclosed in square brackets and gets the result -1.

  2. Excel multiplies -1 by 7 (the contents of cell C1) to get the answer -7.

  3. Excel jumps ahead to divide 9/3 (the contents of cells C3 and C5) because the division comes before the addition in BEDMAS to give a result of 3.

  4. The last operation to do is to add -7 + 3 to get the answer for the whole formula -4.

If you need to add a column or row of numbers, Excel has a built-in formula, the SUM function, that makes the task quick and easy.

Leave a Reply

Your email address will not be published.