How to Create Formulas in Excel

You can create and use formulas in Excel. A formula in Excel is a mathematical equation used to calculate a value. In Excel, a formula must always begin with the equal sign (=), which indicates to Excel that it must interpret the data in the cell as a formula.

When you enter a formula, Excel displays the result of the formula in the cell rather than the formula itself. For example, if you type =2+6 in a cell, Excel displays the result of that calculation (8). The Formula bar, located at the top of the worksheet window, is the area in which you can view, enter, and edit formulas.

Excel also provides hundreds of predefined functions that allow you to perform calculations, from easy to complex, without having to build the formulas from scratch. For instance, the SUM function quickly adds together a range of cells, so you don’t have to add each cell individually.

Before creating formulas in Excel, you should become familiar with some key terms for creating formulas:

• Operator: An operator is a sign or symbol that specifies the type of calculation that should be performed, such as an addition (a plus sign +) or a multiplication (*). For example, in the formula =150+25, the operator is the plus sign, and it adds together the values 150 and 25.
• Operand: Every Excel formula includes at least one operand, which is the data that Excel uses in the calculation. The simplest type of operand is a number; however, you can also include a reference to worksheet data, such as a cell address (B1), as an operand.
• Arithmetic formula: An arithmetic formula combines a numeric operand (a number or a function that returns a numerical value as a result) with an operator to perform a calculation. As you can see in the following table, there are six arithmetic operators you can use to construct arithmetic formulas.

Order of operations in Excel formulas

Many errors occur in formulas when mathematical operators are not entered in the proper order. When creating formulas, you must keep in mind the order of operations: exponentiation occurs before multiplication or division, and multiplication occurs before addition and subtraction. You can alter the order of operations by enclosing segments of the formula in parentheses. For example, the formula =(4+10)*2 forces Excel to first add 4 and 10 and then multiply the result by 2.

Comparison formula in Excel formulas

A comparison formula combines a numeric operand, such as a number, with special operators to compare one operand to another. The most common comparison operators are greater than (>), less than (<), and equal to (=) or some combination thereof. A comparison formula returns a logical result of 1 or 0. If the comparison is true, the formula returns a value of 1. If the comparison is false, the formula returns a value of 0.

Comparison formulas often consist of If…Then statements. For example, the formula =IF(C2>D2,"Yes","No") returns the label Yes if the value in cell C2 is greater than the value in cell D2; otherwise, it returns the label “No.”

Entering simple formulas in Excel

You can create formulas by using the mouse or the keyboard.

Building a formula in Excel manually by following these steps:

1. Launch Microsoft Excel and open a Blank workbook.
2. Type 24 in cell A2 and 12 in cell A3.
3. Type =24*12 in cell A4.
   
   When you enter a formula, Excel displays the result of the formula in the cell and the formula in the Formula bar.
4. Press Enter. Excel displays the result of the formula, 288, in cell A4.

Another way to create a formula is to refer to the cell that contains the value, rather than enter the value itself. When you do, Excel updates the formula automatically when the original value changes. For instance, the values used for the formula in cell A4 actually reside in cells A2 and A3. Note that Excel also color-codes each cell reference in the formula to help you keep track of the data in your worksheet.

Follow these steps to create a formula referencing a cell:

1. Type = in cell A5.
2. Continue typing A2*A3 in cell A5 and press Enter. Excel displays the result of 288.
3. Type 57 in cell A2 and press Enter. Excel updates the result in cell A5 to 684.

Building a formula using your cursor

In addition to typing the cell reference, you can also build a formula by pointing to the cell that contains the data you want to use, as shown by the following set of steps.

1. Type = in cell A6.
2. Using the arrow keys or the mouse, move the cell pointer to cell A2.
3. Type the multiplication operator, (*) and move the cell pointer to cell A3.
4. Press Enter on your keyboard. Excel displays the result 684.