Here are the main ways to add numbers in Excel: how to add two numbers in different cells, how to add a number to a whole range, and how to sum by condition. I know five main ways:
- arithmetic operators (for addition, the plus sign);
- functions;
- pivot tables;
- Paste Special;
- the !SEMTools procedure.
Add numbers with the plus operator (+)
The simplest and shortest way is the plus operator. Add the values in A1 and B1:
=A1+B1
Add a constant, for example 10, to the number in A1:
=A1+10
| A | B | C | D | |
|---|---|---|---|---|
| 1 | A | B | A+B | A+10 |
| 2 | 5 | 7 | 12=A2+B2 | 15=A2+10 |
| 3 | 12.5 | -2.5 | 10=A3+B3 | 22.5=A3+10 |
| 4 | 100 | 100=A4+B4 | 110=A4+10 | |
| 5 | 3 | 7 | 10=A5+B5 | 13=A5+10 |
| 6 | 3 | abc | #VALUE!=A6+B6 | 13=A6+10 |
An empty cell counts as 0. A number stored as text (B5, left-aligned) is converted by +, but text like “abc” gives #VALUE!. SUM would skip both instead.
Add 10 to every cell of the range A1:B5 (Excel 365 and 2021, or as an array formula in older versions): the result spills into a range of the same size.
=A1:B5+10
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | A | B | A:B + 10 | ||
| 2 | 1 | 2 | 11=A2:B4+10 | 12 | |
| 3 | 3 | 4 | 13 | 14 | |
| 4 | 5 | 6 | 15 | 16 |
One formula in D2 spills into a block of the same size as A2:B4. The source stays unchanged; to replace it, copy the result and paste as values, or use Paste Special → Add.
SUM and other summing functions
- SUM is the basic function: it adds numbers, cells and ranges, for example =SUM(A1:A10) or =SUM(A1:A10,C1:C10). Text and empty cells are ignored.
- SUMIF adds the cells that meet one condition: =SUMIF(A2:A100,"East",B2:B100).
- SUMIFS adds by several conditions at once.
- SUMPRODUCT multiplies arrays element by element and adds the results, for example price × quantity.
The quickest way to insert SUM is AutoSum: select the cell below a column of numbers and press Alt+=. And to just see the sum without any formula, select the cells and look at the status bar at the bottom of the window.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Amount | Formula | Result | |
| 2 | North | 120 | =SUM(B2:B6) | 280=SUM(B2:B6) | |
| 3 | South | 80 | =SUMIF(A2:A6,"North",B2:B6) | 170=SUMIF(A2:A6,"North",B2:B6) | |
| 4 | North | 50 | =SUMIFS(B2:B6,A2:A6,"South",B2:B6,">50") | 80=SUMIFS(B2:B6,A2:A6,"South",B2:B6,">50") | |
| 5 | East | 70 | =SUMPRODUCT(--B2:B6) | 350=SUMPRODUCT(--B2:B6) | |
| 6 | South | 30 |
SUM skips B5, the number 70 stored as text, so the total is 280, not 350. SUMIF adds by one condition, SUMIFS by several. SUMPRODUCT(--B2:B6) converts text numbers and shows the real total.
Sum numbers with a pivot table
Pivot tables are a powerful tool for summing and other aggregation: they group large datasets, build summary reports and show sums, averages and other statistics.
- Select the data range: it should be a table with a header row.
- Go to Insert → PivotTable and choose where to place it.
- Drag the field to group by into Rows, and the numeric field into Values: Excel sums it by default.
- Add filters and columns to look at the data from different angles.
Pivot tables are useful for analyzing and summarizing large tables and seeing trends in them. For a quick summary without settings, see how to count unique values with a fast pivot.
Paste Special: add a number to a whole range
Paste Special adds a number to all selected numbers at once, right in place:
- type the number in any cell and copy it (Ctrl+C);
- select the range (or several ranges with Ctrl);
- right-click → Paste Special → Add → OK.
The catch: formulas in the range become formulas with “+10” appended, and the formatting of the copied cell may be pasted too if you don’t choose Values.
Add numbers to a range in place with !SEMTools
With my !SEMTools add-in, you add any number to a selected range in a couple of clicks. The data is updated in place, with no helper columns or functions.
Add a number to all values in a range
Select the range (hold Ctrl to select several cells or ranges), run CHANGE → Numbers → Arithmetic operations → Add, type the number and click OK.
Add a percentage to one or more numbers
To add N percent to a number, multiply it by (100 + N) percent: to add 20%, multiply by 120%. As a formula, it’s =A1*1.2. With !SEMTools, it’s one more procedure in the same menu, and you can also multiply numbers in place the same way.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Price | +20% | +N% (N in D1) | N, % | |
| 2 | 100 | 120=A2*1.2 | 115=A2*(1+$E$2) | 15% | |
| 3 | 250 | 300=A3*1.2 | 287.5=A3*(1+$E$2) | ||
| 4 | 80 | 96=A4*1.2 | 92=A4*(1+$E$2) |
Adding 20% means multiplying by 1.2 (120%). With the percentage in a separate cell, the formula =A2*(1+$E$2) works for any rate.
Why Excel doesn’t add numbers: common mistakes
- SUM returns 0 or less than expected. Some numbers are stored as text. Convert them to real numbers.
- =A1+B1 returns #VALUE!. One of the cells contains text, even a space. SUM ignores text, + doesn’t: use =SUM(A1,B1).
- The total is off by a cent. The values are rounded only by format. See rounding in Excel.
Add a number or a percentage to whole ranges right in place, without helper columns or Paste Special.
!SEMTools solves this and hundreds of other tasks in a couple of clicks!
Summary: how to add numbers in Excel
I hope you found what you were looking for on this page. The plus sign and SUM cover most needs, SUMIF and SUMIFS add by conditions, pivot tables summarize large tables, and Paste Special or the !SEMTools procedure add a number to a whole range in place.