Referring to cells in formulas
As well as numbers, Excel formulas can refer to the contents of different cells. When a formula refers to a cell, the number in the cell is used to compute the formula. In the image below, for example, cell A1 contains the number 2; cell A2 contains the number 3; and cell A3 contains the formula=A1+A2. As shown in cell A3, the result of the formula is 5. If you change the number in cell A1 from 2 to 3, the result of the formula in cell A3 (=A1+A2) becomes 6, not 5. When a formula refers to a cell and the number in the cell changes, the result of the formula changes as well.
A simple Excel formula.
To see the value of using cell references in formulas, consider the worksheet shown below. The purpose of this worksheet is to track the budget of a school's Parent Teacher Association (PTA):
- Column C, Actual Income, lists income from different sources.
- Column D, Projected Income, shows what the PTA members thought income from these sources would be.
- Column E, Over/Under Budget, shows how actual income compares to projected income from the different sources.
Using Excel formulas in a worksheet.As the figures in the Actual Income column (column C) are updated, figures in the Over/Under Budget column (column E) and the Total Income row (row 8) change instantaneously. These figures change instantaneously because the formulas refer to the numbers in cells, not to unchanging numbers (known as constants).
The image below shows the formulas used to calculate the data in the worksheet above. In column E, formulas deduct the numbers in column D from the numbers in column C to show where the PTA over- or under-budgeted for the different sources of income. In row 8, you can see how the SUM function is used to total cells in rows 3 through 7
The formulas used to generate the numbers in the Excel worksheet above.
Excel is remarkably good about updating cell references in formulas when you move cells. To see how good Excel is, consider what happens to cell addresses in formulas when you delete a row in a worksheet. If a formula refers to cell C1 but you delete row B, row C becomes row B and the value in cell C1 changes addresses from C1 to B1. You would think that references in formulas to cell C1 would be out of date, but you would be wrong. Excel automatically adjusts all formulas that refer to cell C1. Those formulas now refer to cell B1 instead.
In case you’re curious, you can display formulas in worksheet cells instead of the results of formulas by pressing Ctrl+’ (apostrophe) or clicking the Show Formulas button on the Formulas tab. (You may have to click the Formula Auditing button first, depending on the size of your screen.) Click the Show Formulas button a second time to see formula results again.Referring to formula results in Excel 2019formulas
Besides referring to cells with numbers in them, you can refer to formula results in a cell. Consider the worksheet below. The purpose of this worksheet is to track scoring by the players on a basketball team over three games:- The Totals column (column E) shows the total points each player scored in the three games.
- The Average column (column F), using the formula results in the Totals column, determines how much each player has scored on average. The Average column does that by dividing the results in column E by 3, the number of games played.
Using Excel formula results as other formulas.
In this case, Excel uses the results of the total-calculation formulas in column E to compute average points per game in column F.
Operators in Excel 2019 formulas
Addition, subtraction, and division aren’t the only operators you can use in formulas. The table below explains the arithmetic operators you can use and the key you press to enter each operator. In the table, operators are listed in the order of precedence.| Precedence | Operator | Example Formula | Returns |
| 1 | % (Percent) | =50% |
50 percent, or 0.5 |
| 2 | ^ (Exponentiation) | =50^2 |
50 to the second power, or 2500 |
| 3 | * (Multiplication) | =E2*4 |
The value in cell E2 multiplied by 4 |
| 3 | / (Division) | =E2/3 |
The value in cell E2 divided by 3 |
| 4 | + (Addition) | =F1+F2+F3, |
The sum of the values in those cells |
| 4 | – (Subtraction) | =G5–8, |
The value in cell G5 minus 8 |
| 5 | & (Concatenation) | =″Part No. ″&D4 |
The text Part No. and the value in cell D4 |
| 6 | = (Equal to) | =C5=4, |
If the value in cell C5 is equal to 4, returns TRUE; returns FALSE otherwise |
| 6 | <> (Not equal to) | =F3<>9 |
If the value in cell F3 is not equal to 9, returns TRUE; returns FALSE otherwise |
| 6 | < (Less than) | =B9<E11 |
If the value in cell B9 is less than the value in cell E11, returns TRUE; returns FALSE otherwise |
| 6 | <= (Less than or equal to) | =A4<=9 |
If the value in cell A4 is less than or equal to 9, returns TRUE; returns FALSE otherwise |
| 6 | > (Greater than) | =E8>14 |
If the value in cell E8 is greater than 14, returns TRUE; returns FALSE otherwise |
| 6 | >= (Greater than or equal to) | =C3>=D3 |
If the value in cell C3 is greater than or equal to the value in cell D3, returns TRUE; returns FALSE otherwise |
SUM, for example, adds the numbers in cells. AVG finds the average of different numbers.
The order of precedence for Excel formulas
When a formula includes more than one operator, the order in which the operators appear in the formula matters a lot. Consider this formula:=2+3*4
Does this formula result in 14 (2+[3*4]) or 20 ([2+3]*4)? The answer is 14 because Excel 2019 performs multiplication before addition in formulas. In other words, multiplication takes precedence over addition.
The order in which calculations are made in a formula that includes different operators is called the order of precedence. Be sure to remember the order of precedence when you construct complex formulas with more than one operator:
- Percent (%)
- Exponentiation (^)
- Multiplication (*) and division (/); leftmost operations are calculated first
- Addition (+) and subtraction (-); leftmost operations are calculated first
- Concatenation (&)
- Comparison (<, <=, >,>=, and <>)
=2+3*4 equals 20 when it is written this way: =(2+3)*4.