Adding numbers in a column or on a row is one of the most basic Excel Functions. Here are 3 easy ways to do it.
- Use simple addition ( the plus sign +)
- Use the SUM() function
- Use the AUTOSUM button
Simple addition
In the example below we have a list of cells containing the amount of money in sales for 12 months for 4 products. Assuming that we want to add all the amounts in January, let’s do a simple addition of the 4 numbers highlighted.
How to create a simple addition
A simple addition looks like this: Total = B2 + B3+ B4+ B5
The Excel Formula is built as you type or as you select each cell to be added.
How to build up the addition formula
Here are the steps:
- type =
- select B2
- type +
- select B3
- type +
- select B4
- type +
- select B5
- Type Enter
Alternatively, you can type the entire formula using your keyboard
=B2+B3+B4+B5 (type Enter to calculate the formula)
Notice how the cells in the formula are highlighting as you type. To identify the cells, Excel uses a different color for each one.
When you press Enter, the formula is calculating the result and Excel is displaying it in the B6 cell (or wherever you typed the formula):
What happens when you add empty cells
If one or more of the cells are empty, Excel will consider them zero.
What happens if you add cells that are not numbers
If one or more of the cells are not a number, the formula will result in an error
As any Excel formulas, the result will always show the current value of the addition of these cells.
Any time you change one of the values in the added cells, the result will change immediately to show the correct sum of these cells.
How to build the formula using the mouse
Here is how you do the addition using the mouse to point at the cells as you add.
- Click on the cell where you want the result of the calculation to appear (B6)
- Type = (press the “equals” (=) key to start writing your formula)
- Click on the first cell to be added (B2)
- Type + (that’s the plus sign)
- Click on the second cell to be added (B3)
- Type + again, and the next cell to be added.
- Repeat until all cells to be added have been clicked.
- Press Enter.
This will create the same addition formula as above without the manual typing.
Only use this approach for a limited number of cells due to the difficulty of keeping track of all the cells to be added.
Use the next method (the SUM() function) for a larger set of numbers.
Use the SUM() function to add up numbers in a column
The SUM() function is a more efficient way to add up cells.
You can use the SUM function to add up individual cells, or to add up a range of cells simply by specifying the first and last cell in a range of cells to be added up.
The SUM() function will add up the values in all the cells between the start cell and the end cell in the shortest path possible.
It becomes very useful when adding hundreds, or thousands of cells. Excel has over 1,000,000 rows so imaging typing that many cells into an addition. It is much easier to type a formula like SUM (A1:A1000000)
How to use the SUM() function
If you look at the earlier example, you could use SUM() as shown in the screenshot to achieve the same result.
Notice how the cells included in the formula are highlighted just like in the addition case above.
However, in this case, a range is highlighted from the first cell to the last cell specified. This is a useful way to check whether your formula is using the correct range for the SUM.
This formula adds up all the cells from B2 to B5 inclusive. This method can be used just as easily to add up several thousands of cells in a row or column, as well as a set of rows or a set of columns.
Add numbers in a column (B2 to B5)
In the example below, the SUM function is adding numbers in a column. As you type the formula, observe the blue mark showing you the range that is added.
Add numbers in a row (B2 to H2)
In the screenshot below, the same function can be used to sum up the values in a row.
Several rows and columns (all rows and columns B2 to H5)
In the next example, Excel is adding all the values in an array.
Multiple ranges
You can use SUM to add numbers in multiple ranges (only the months of January, March and May). Press CTRL while selecting all the ranges needed with the mouse.
While you type, notice the Range is highlighted so that you are aware of what is being added in the formula.
The AUTOSUM button
The last and most efficient method to sum up numbers in a row or column is to use the AUTOSUM button.
This button will use Excel artificial intelligence to evaluate your data set. If your selection is on the first empty cell under a column when you press the AUTOSUM button, Excel will assume that you want to add all the numbers in that column and place the result in the empty cell. This is what 99% of users do. This method saves even more time in typing additions or SUM formulas but just pressing one button.
Where can I find the AUTOSUM button
Autosum is in the Home tab, in the Editing subsection
Modify the resulting formula as you like to reduce or increase the range of numbers.
There are other function under the Autosum dropdown that you can use. You can calculate the average of your numbers, count them, pick the minimum or the maximum and even explore other numerical functions.
These are the three ways Excel can add numbers on a column or row.
