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 (^) .

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:

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

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

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:

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 .

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

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.

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.

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.

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.

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 .

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.

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.

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

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.

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

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 .

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.

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 .

4. When finished, press Enter on the 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 box.

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.

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!