How to Make an Excel Formula

Excel 2019 lets you control your data with formulas. When all else fails, you can even makes your own. Excel formulas consist of three crucial bits of information:

• An equal sign (=)
• One or more cell references
• The type of calculation to do on the data (addition, subtraction, and so on)

The equal sign (=) simply tells Excel not to treat the formula as text but as instructions for calculating something.

A cell reference is simply the unique row and column heading that identifies a single cell, such as A4 or D9.

The four common calculations that a formula can use are addition (+), subtraction (–), multiplication (*), and division ( / ). This table lists these and other mathematical operators you can use in a formula.

A simple formula uses a single mathematical operator and two cell references, such as:

=A4+C7

This formula consists of three parts:

• The equal sign (=): This identifies your formula. If you typed just A4+C7 in a cell, Excel would treat it as ordinary text.
• Two cell references: In this example, A4 and C7.
• The addition (+) mathematical operator.

To type a formula in a cell, follow these steps:

1. Click or use the arrow keys to select the cell where you want to store the formula.
   Excel highlights your selected cell.
2. Type the equal sign (=).
   This tells Excel that you are creating a formula.
3. Type a formula that includes one or more cell references that identify cells that contain data, such as A4 or E8.
   For example, if you want to add the numbers stored in cells A4 and E8, you would type =A4+E8.
4. Press Enter.

Typing cell references can get cumbersome because you have to match the row and column headings of a cell correctly. As a faster alternative, you can use the mouse to click in any cell that contains data; then Excel types that cell reference into your formula automatically.

To use the mouse to add cell references when creating a formula, follow these steps:

1. Click in the cell where you want to store the formula. (You can also select the cell by pressing the arrow keys.) Excel highlights your selected cell.
2. Type the equal sign (=).
   This tells Excel that anything you type after the equal sign is part of your formula.
3. Type any mathematical operators and click in any cells that contain data, such as A4 or E8.

   If you want to create the formula =A4+E8, you would do the following:

   1. Type =. This tells Excel that you’re creating a formula.
   2. Click cell A4. Excel types the A4 cell reference in your formula automatically.
   3. Type +.
   4. Click cell E8.
      Excel types in the E8 cell reference in your formula automatically.
4. Press Enter.

After you finish creating a formula, you can type data (or edit any existing data) into the cell references used in your formula to calculate a new result.

Organizing formulas with parentheses in Excel 2019

Formulas can be as simple as a single mathematical operator such as =D3*E4. However, you can also use multiple mathematical operators and cell references, such as

=A4+A5*C7/F4+D9

Two problems are associated with using multiple mathematical operators. First, they make a formula harder to read and understand. Second, Excel calculates mathematical operators from left to right, based on precedence, which means a formula may calculate results differently from what you intend.

Precedence tells Excel which mathematical operators to calculate first, as listed below. For example, Excel calculates multiplication before it calculates addition. If you had a formula such as

=A3+A4*B4+B5

Excel first multiplies A4*B4 and then adds this result to A3 and B5.

Typing parentheses around cell references and mathematical operators not only organizes your formulas but also tells Excel specifically how you want to calculate a formula. In the example =A3+A4*B4+B5, Excel multiplies A4 and B4 first. If you want Excel to first add A3 and A4, then add B4 and B5, and finally multiply the two results, you have to use parentheses, like this:

=(A3+A4)*(B4+B5)

Copying formulas in Excel 2019

In many spreadsheets, you may need to create similar formulas that use different data. For example, you may have a spreadsheet that needs to add the same number of cells in adjacent columns.

You can type nearly identical formulas in multiple cells, but that’s tedious and error-prone. For a faster way, you can copy a formula and paste it in another cell; Excel automatically changes the cell references.

You can see that cell B10 contains the formula =B3+B4+B5+B6+B7+B8+B9, which simply adds the numbers stored in the five cells directly above the cell that contains the formula (B10). If you copy this formula to another cell, that new formula will also add the eight cells directly above it. Copy and paste this formula to cell C10, and Excel changes the formula to =C3+C4+C5+C6+C7+C8+C9.

To copy and paste a formula so that each formula changes cell references automatically, follow these steps:

1. Select the cell that contains the formula you want to copy.
2. Press Ctrl+C (or click the Copy icon in the Home tab).

   Excel displays a dotted line around your selected cell.

3. Select the cell (or cells) where you want to paste your formula.

   If you select multiple cells, Excel pastes a copy of your formula in each of those cells.

4. Press Ctrl+V (or click the Paste icon in the Home tab).
   Excel pastes your formula and automatically changes the cell references.
5. Press Esc or double-click away from the cell with the dotted line to make the dotted line go away.