An Excel spreadsheet is useful for displaying information in an organized way, but Excel can be used for so much more. Its powerful computing capacity allows you to enter, manipulate and analyze numbers. To use this feature, you need to be familiar with formulas, which are basically mathematical equations. Here we describe how to create simple formulas and slightly more complex formulas in Excel.
This article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Microsoft 365.
Basics of Excel formulas
Writing a spreadsheet formula is different from writing an equation in math class. The most notable difference is that Excel formulas begin with an equals sign (=) instead of stopping there.
Excel formulas look like =3+2 Instead of 3 + 2 =.
The equals sign indicates that what follows is part of a formula and not just a word or number to appear in the cell. After entering the formula and pressing Enter on your keyboard the result of the formula will appear in the cell.
For example, if you enter the above formula, =3+2 in a cell and press Enter the result, 5appears in the cell. The formula is still there, but it doesn’t appear in your spreadsheet. However, when you select the cell, the formula will appear in the formula bar at the top of the Excel screen.
Improve formulas with cellular references
Excel formulas can also be developed using cell references. To continue our example, do not enter the numbers 3 etc 2Instead, the cells into which those numbers were entered are named (see the Using Cell References section below for more information on cell naming). When you write a formula in this way, the formula cell will always show the sum of the digits in those cells even if the digits change.
Here is a concrete example of the usefulness of this approach. Suppose you manage a team of sales reps and track their monthly and quarterly sales. You want to calculate the total sales for the year. Instead of entering each quarterly sales value into a formula, use cell references to identify the cells in the spreadsheet that contain those values.
Using cell references
Each cell in Excel is part of a row and a column. Rows are identified by numbers (1, 2, 3, etc.) on the left side of the table, while columns are identified by letters (A, B, C, etc.) at the top. To refer to a cell, use the column letter and row number together, e.g A1 or W22 (the column letter always comes first). When you have a cell selected, you can see its reference at the top of the screen in the Name box next to the formula bar.
In the image above, notice the cell references in the formula bar: E2, i2, M2etc Q2. They refer to the quarterly sales figures of the seller named John. The formula adds these numbers to get the annual sales. If you update the numbers in one or more of those cells, Excel recalculates and the result is always the sum of the numbers in those cells.
Create a formula with cell references
Try to create a simple formula using cell references.
- First you need to fill the table with data. Open a new Excel file and select the cell C1 to make it the active cell.
- Type 3 in the cell, and then press Enter on your keyboard.
- cells C2 should be selected. If not, select the cell C2. Type 2 in the cell and the press Enter on your keyboard.
- Now create the formula. select cell D1 and type =C1+C2. Notice that each cell reference is highlighted as you type.
- To press Enter to complete the formula. The answer 5 appears in the cell D1.
When you select a cell D1 the complete formula again =C1+C2 appears in the formula bar above the worksheet.
Grab cell references with pointing
Pointing is another way to refer to the values you want to include in your formula. To do this, you need to use your mouse pointer to select cells to include in your formula. This method is the fastest among those discussed; It’s also the most accurate because you eliminate the risk of making mistakes when entering numbers or cell references. Here’s how (starting with the table examples above):
- Select a cell E1 to make it the active cell and type the equal sign (=).
- Use your mouse pointer to select a cell C1 to enter the cell reference in the formula.
- Type a plus sign () and then select it with the pointer C2 to enter the second cell reference in the formula.
- To press Enter to complete the formula. The result appears in the cell E1.
- To see how changing any of the values in the formula changes the result, edit the data in the cell C1 out 3 at 6 and the press Enter on your keyboard. Note that the results are in cells D1 etc E1 both change 5 at 8thbut the formulas remain unchanged.
Mathematical operators and order of operations
We now move on to operations other than addition, including subtraction, division, multiplication, and exponentiation. The mathematical operators used in Excel formulas are similar to those you might recognize from math class:
- Subtraction – minus sign ( – )
- Addition – plus sign ( )
- Division – Forward Slash ( / )
- Multiplication – Asterisk ( * )
- Exponentiation—Caret ( ^ )
When multiple operators are used in a formula, Excel follows a specific order to perform the mathematical operations. An easy way to remember the order of operations is to use the acronym BEDMAS.
- Bsnowshoes
- Expositors
- Dvision
- Mmultiplication
- Aadditive
- Ssubtraction
Excel actually considers division and multiplication to be equally important. It performs these operations in the order in which they occur, from left to right. The same applies to addition and subtraction.
Here is a simple example of the current order of operations. In the formula =2*(3+2) The first operation Excel performs is the one inside the brackets (3+2), with the result 5. It then performs the multiplication operation, 2*5with the result 10. (Values in the formula could be represented by cell references instead of numbers, but Excel would perform the operations in the same order). Try entering the formula in Excel to see if it works.
Enter a complex formula
Now let’s create a more complex formula.
- Open a new table and populate it with the data as follows:
- 7 in the cell C1
- 5 in the cell C2
- 9 in the cell C3
- 6 in the cell C4
- 3 in the cell C5
- Select a cell D1 to make it the active cell, and type the equals sign followed by a left parenthesis (=().
- Select a cell C2 To enter the cell reference in the formula, then type the minus sign (–).
- Select a cell C4 To enter that cell reference in the formula, then type a right parenthesis ()).
- Enter the multiplication sign (*), then select the cell C1 to enter that cell reference in the formula.
- Type the plus sign () and then select C3 to enter that cell reference in the formula.
- Enter the division sign (/), then select C5 to enter that cell reference in the formula.
- To press Enter to complete the formula. The answer -4 appears in the cell D1.
In the example above, Excel returned the result of -4 Use the BEDMAS rules as follows:
- In brackets. Excel first performed the operation in brackets, C2-C4 or 5-6 for a result of -1.
- Exhibitor. This formula does not contain an exponent, so Excel skipped this step.
- division and multiplication. There are two such operations in the formula, and Excel performed them from left to right. First he multiplied -1 par 7 (The content of the cell C1) to get a result -7. She then proceeded to the division operation, C3/C5 or 9/3for a result of 3.
- addition and subtraction. The last operation performed by Excel was add -7+3 for the end result of -4.