Complete guide to Excel 2016 (Part 13): Introduction to formulas

One of the most powerful features of Excel is the ability to calculate digital information using formulas. Like a computer, Excel can add, subtract, multiply and divide. In this article, TipsMake.com will show you how to use cell references to create simple formulas in Excel 2016. Please refer!

Formulas in Excel 2016:

1. I. Calculation operator
2. II. Understanding Cell References (Cell references)
3. III. Create a recipe
4. IV. Modify values ​​with cell references
5. V. Create a formula using point-and-click methods
6. VI. Copy the formula with the Fill Handle
7. VII. Edit a formula

Watch the video below to learn more about creating formulas in Excel 2016:

I. Calculation operator

Excel 2016 uses standard operators for formulas, such as plus sign plus (+) , a minus sign minus (-) , an asterisk for multiplication (*) , slash allow division (/) and caret for exponent (^) .

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 1

All formulas in Excel must start with an equal sign (=) . This may be because the cell contains or is equal to the formula and value that it calculates.

II. Understanding Cell References (Cell references)

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

In the formula below, cell A3 adds the values ​​of cell A1 and cell A2 by making cell references:

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 2

When you press Enter , the formula calculates and displays the answer in cell A3 :

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 3

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

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 4

By combining an operator with cell references, you can create a series of simple formulas in Excel. Formulas can also include a combination of cell and numeric references, as in the examples below:

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 5

III. Create a recipe

In the example below, we will use a simple formula and cell reference to calculate the budget.

1. Select the cell containing the formula. In the example, we will select cell D12 .

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 6

2. Enter an equal sign (=) . Notice how it appears in both the cell and the formula bar.

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 7

3. Enter the cell 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.

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 8

4. Type the math operator you want to use. In the example, we will enter the plus sign (+) .

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

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 9

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

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 10

1. If the result of a formula is too large to display in a cell, it may appear as table sign (#######) instead of a value. This means that the column is not wide enough to display the cell contents. Simply increase the column width to display the cell contents.

IV. Modify values ​​with cell references

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

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 11

1. Excel doesn't always tell you that your recipe has errors, so you must check all the formulas.

V. Create a formula using point-and-click methods

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

1. Select the cell containing the formula. In the example, we will select cell D4 .

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 12

2. Enter an equal 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.

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 13

4. Type the operator calculation you want to use. In the example, we will enter the multiplication sign (*) .

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

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 14

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

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 15

VI. Copy the formula with the Fill Handle

Formulas can also be copied to cells adjacent to the Fill Handle , which can save a lot of time and effort if you need to do the same calculations multiple times in a spreadsheet. The Fill Handle is a 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 into the cells you want to fill.

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 16

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

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 17

VII. Edit a formula

Sometimes you may want to modify an existing formula. In the example below, we entered an incorrect cell address in the formula, so we need to fix it.

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

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 18

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

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 19

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

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 20

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

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 21

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

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 22

1. If you change your mind, you can press the Esc key on the keyboard or click the Cancel command on the formula bar to avoid changing your recipe.

Complete guide to Excel 2016 (Part 13): Introduction to formulas Picture 23

To display all formulas in a spreadsheet, you can hold down the Ctrl key and press ` (grave accent). The accent key is located in the top left corner of the keyboard. You can press Ctrl + ` again to switch to normal view.

Having fun!

Lesley Montoya

Update 25 May 2019