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!

5 ★ | 1 Vote

May be interested

  • Excel tricks that let you write formulas more easilyExcel tricks that let you write formulas more easily
    if you've ever stared at a long excel formula and thought, 'is there a better way to write it?', the good news is that there is. use lambda and let.
  • How to lock formulas in Excel - Protect excel formulasHow to lock formulas in Excel - Protect excel formulas
    when sharing excel files, if you don't want others to edit the formulas or view the formulas, you can lock the formulas.
  • Complete guide to Excel 2016 (Part 1): Get familiar with Microsoft ExcelComplete guide to Excel 2016 (Part 1): Get familiar with Microsoft Excel
    microsoft excel is a spreadsheet processing program designed to record, present processing information in tabular form, perform calculations and build visual statistics in the table. join tipsmake.com to get acquainted with microsoft excel 2016!
  • How to calculate time in Excel: Add and subtract timeHow to calculate time in Excel: Add and subtract time
    excel can calculate the amount of time by adding / subtracting method to give you the result of the amount of time in minutes, hours, days, and months. the following software tips will guide you the most basic formulas. the problem is that you have time to complete two steps of work, and you want to know how many hours and minutes it will take to complete the job.
  • MS Excel 2003 - Lesson 5: Excel formulas and functionsMS Excel 2003 - Lesson 5: Excel formulas and functions
    formulas in excel are programs that perform calculations on data tables. these formulas perform very precise calculations such as addition, multiplication, or comparison of values ​​in a worksheet.
  • Excel 2016 - Lesson 2: Learn about OneDrive - Complete guide to Excel 2016Excel 2016 - Lesson 2: Learn about OneDrive - Complete guide to Excel 2016
    in the introduction of the excel 2016 section, tipsmake.com helped you get acquainted with microsoft excel. today, we continue to share with you what you need to know about onedrive. please join us!
  • Complete tutorial of Excel 2016 (Part 5): Basics of cells and rangesComplete tutorial of Excel 2016 (Part 5): Basics of cells and ranges
    whenever you work with excel, you will need to enter information - or content - into the cells . cells are the basic building blocks of a spreadsheet. you will need to learn the basics of cells and cell contents to calculate, analyze, and organize data in excel.
  • How to display formulas and print formulas in ExcelHow to display formulas and print formulas in Excel
    display formulas and print formulas in excel so you can see the formula directly in the data cell, not see the results anymore. to be able to display and print the formula so please follow the instructions below of tipsmake.com.
  • How to Copy Formulas in ExcelHow to Copy Formulas in Excel
    the excel program has a feature that makes it easy for users to copy formulas for entire rows or columns, but you don't always get the results you want. when you receive results that are not as expected or encounter #ref and /div0 errors, you will definitely feel unhappy. don't worry, though - you don't need to edit every cell in a 5,000-line spreadsheet. this article will provide some simple ways to help you copy formulas to other cells.
  • Complete guide to Excel 2016 (Part 10): Use the Find and Replace functionComplete guide to Excel 2016 (Part 10): Use the Find and Replace function
    when working with a lot of data in excel 2016, you may find it difficult and time-consuming to determine the specific location of the information.