Excel 2019 (Part 12): Introduction to Formulas

One of the most powerful features in Excel is its ability to perform numerical calculations using formulas. Just like a calculator, Excel can add, subtract, multiply, and divide. This article will show you how to use cell references to create simple formulas.

 

Mathematical operators

Excel uses standard operators for formulas: the plus sign for addition (+), the minus sign for subtraction (-), the asterisk for multiplication (*), the forward slash for division (/), and the caret (^) for exponents.

All formulas in Excel must begin with an equals sign (=). This is because the cell contains the formula and the value it calculates.

Learn about cell references.

Although you can create simple formulas in Excel using numbers (for example, =2+2 or =5*5 ), in most cases you will use cell addresses to create formulas. This is called creating cell references. Using cell references ensures that your formulas are always correct because you can change the values ​​of the referenced cells without having to rewrite the formula.

In the formula below, cell A3 calculates the sum of the values ​​in cells A1 and A2 by creating a cell reference:

 

When you press Enter , the formula will calculate and display the answer in cell A3:

If the values ​​in the referenced cells change, the formula will automatically recalculate:

By combining a mathematical operator with a cell reference, you can create many simple formulas in Excel. Formulas can also include a combination of cell references and numbers, as in the examples below:

How to create a recipe

The example below will use a simple formula and cell references to calculate the budget.

1. Select the cell that will contain the formula. For example, we would select cell D12.

2. Type the equals sign (=). Note how it appears in both the cell and the formula bar.

 

3. Enter the address of the cell you want to reference first in the formula—cell D10 in the example. A blue border will appear around the referenced cell.

4. Enter the mathematical operator you want to use. For example, you would enter the plus sign (+).

5. Enter the address of the second cell you want to reference in the formula—cell D11 in the example. A red border will appear around the referenced cell.

6. Press Enter on your keyboard. The formula will be calculated and the value will be displayed in the cell. If you select the cell again, note that the cell displays the result, while the formula bar displays the formula.

If the result of a formula is too large to be displayed in a cell, it may appear as hash symbols ( ####### ) instead of a value. This means the column is not wide enough to display the cell's contents. Simply increase the column width to display the cell's contents.

Modify values ​​using cell references.

The real advantage of cell references is that they allow you to update data in your spreadsheet without having to rewrite formulas. The example below modifies the value of cell D10 from $1,200 to $1,800. The formula in D12 will automatically recalculate and display the new value in cell D12.

 

Excel doesn't always tell you if your formula is wrong, so checking all your formulas is up to you.

How to create formulas using the point-and-click method

Instead of manually entering cell addresses, you can point and click on the cells you want to include in the formula. This method can save a lot of time and effort when creating formulas. The example below will create a formula to calculate the cost of ordering a number of plastic silverware boxes.

1. Select the cell that will contain the formula. For example, we would select cell D4.

2. Type the equals sign (=).

3. Select the cell you want to reference first in the formula—cell B4 in the example. The cell address will appear in the formula.

4. Enter the mathematical operator you want to use. For example, you would enter the multiplication sign (*).

5. Select the cell you want to reference second in the formula—cell C4 in the example. The cell address will appear in the formula.

6. Press Enter on the keyboard. The formula will be calculated and the value will be displayed in the cell.

Copy formulas using the Fill Handle.

Formulas can also be copied to adjacent cells using the Fill Handle, which can save a lot of time and effort if you need to perform the same calculation multiple times in a worksheet. The Fill Handle is the small square in the lower right corner of the selected cell(s).

 

1. Select the cell containing the formula you want to copy. Click and drag the Fill Handle to the cells you want to fill.

2. After you release the mouse button, the formula will be copied to the selected cells.

How to edit a formula

Sometimes you might want to modify an existing formula. The example below entered an incorrect cell address in its formula, so it needs to be edited.

1. Select the cell containing the formula you want to edit. For example, we would select cell D12.

2. Click on the formula bar to edit. You can also double-click on a cell to view and edit the formula directly within the cell.

3. A border will appear around any referenced cell. For example, this would change the first part of the formula to reference cell D10 instead of cell D9.

4. When you're finished, press Enter on your keyboard or select the Enter command in the formula bar.

5. The formula will be updated and the new value will be displayed in the cell.

If you change your mind, you can press the Esc key on your keyboard or click the Cancel command in the formula bar to avoid accidentally making changes to the formula.

To display all the formulas in the spreadsheet, you can hold down the Ctrl key and press ` (the accent mark). The accent mark is usually located in the upper-left corner of the keyboard. You can press Ctrl + ` again to return to normal view.