How to Add Numbers in Excel Using a Formula

Math doesn't have to be hard when you use Excel

Add numbers in Microsoft Excel using a formula.

As with all basic math operations in Excel to add two or more numbers in Excel you need to create a formula.

Note: To add several numbers that are located in a single column or row in a worksheet, use the SUM Function, which offers a shortcut to creating a long addition formula.

Important points to remember about Excel formulas:

  1. Formulas in Excel always begin with the equal sign ( = );
  2. The equal sign is always typed into the cell where you want the answer to appear;
  3. The addition sign in Excel is the plus symbol ( + );
  4. The formula is completed by pressing the Enter key on the keyboard.

Use Cell Reference in Addition Formulas

How to Add Numbers in Excel Using a Formula
© Ted French

In the image above, the first set of examples (rows 1 to 3) use a simple formula - located in column C - to add together the data in columns A and B.

Although it is possible to enter numbers directly into an addition formula - as shown by the formula:

= 5 + 5

in row 2 of the image - it is much better to enter the data into worksheet cells and then use the addresses or references of those cells in the formula - as shown by the formula:

=A3 + B3

in row 3 above.

One advantage of using cell references rather than the actual data in a formula is that, if at a later date, it becomes necessary to change the data it is a simple matter of replacing the data in the cell rather than rewriting the formula.

Normally, the results of the formula will update automatically once the data changes.

Entering Cell References With Point and Click

Although it is possible to just type the above formula into cell C3 and have the correct answer appear, it is usually better to use point and click, or pointing, to add the cell references to formulas in order to minimize the possibility of errors created by typing in the wrong cell reference.

Point and click involves simply clicking on the cell containing the data with the mouse pointer to add the cell reference to the formula.

Creating the Addition Formula

The steps used to create the addition formula in cell C3 are:

  1. Type an equal sign (=) in cell C3 to begin the formula.
  2. Click on cell A3 with the mouse pointer to add that cell reference to the formula after the equal sign.
  3. Type the plus sign (+) into the formula after A3.
  4. Click on cell B3 with the mouse pointer to add that cell reference to the formula after the addition sign.
  5. Press the Enter key on the keyboard to complete the formula.
  6. The answer 20 should be present in cell C3.

Note: Even though you see the answer in cell C3, clicking on that cell will display the formula =A3 + B3 in the formula bar above the worksheet.

Changing the Formula

If it becomes necessary to correct or change a formula, two of the best options are:

  • Double click on the formula in the worksheet to place Excel in Edit mode and then make changes to the formula - works best for minor changes.
  • Click once on the cell containing the formula and re-enter the entire formula - best for major changes.

Creating More Complex Formulas

To write more complex formulas that include multiple operations - such as division or subtraction or addition - as shown in rows five to seven in the example, use the steps listed above to get started and then just continue to add the correct mathematical operator followed by the cell references containing the new data.

Before mixing different mathematical operations together in a formula, however, it is important to understand the order of operations that Excel follows when evaluating a formula. 

Creating a Fibonacci Sequence

Creating a Fibonacci Sequence with an Addition Formula in Excel
© Ted French

A Fibonacci sequence, created by the twelfth century Italian mathematician Leonardo Pisano, form a continuous series of increasing numbers.

These series are often used to explain, mathematically, among other things, different patterns found in nature such as:

  • The spiral shape of different sea shells;
  • The arrangement of leaves on a tree branch;
  • The reproduction pattern of bees.

After two starting numbers, each additional number in the series is the sum of the two preceding numbers.

The simplest Fibonacci  sequence, shown in the image above, begins with the numbers zero and one:

0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610, 987, 1597, 2584 …

Fibonacci and Excel

Since a Fibonacci series involves addition, it can be easily created with an addition formula in Excel as shown in the image above.

The steps below detail how to create the simplest Fibonacci sequence using a formula. The steps involve creating the first formula in cell A3 and then copying that formula to the remaining cells using the fill handle.

Each iteration, or copy, of  the formula, adds together the previous two numbers in the sequence.

The steps below create the sequence in one column, rather than in three columns shown in the image example to make the copying process easier.

To create the Fibonacci series shown in the example using an addition formula:

  1. In cell A1 type a zero (0) and press the Enter key on the keyboard.
  2. In cell A2 type a 1 and press the Enter key.
  3. In cell A3 type the formula =A1 + A2 and press the Enter key.
  4. Click on cell A3 to make it the active cell.
  5. Place the mouse pointer over the fill handle - the black dot in the bottom right corner of cell A3 - the pointer changes to a black plus sign ( + ) when it is over the fill handle.
  6. Ccick and hold on the fill handle and drag the mouse pointer down to cell A31.
  7. A31 should contain the number 514229