Excel is a fantastic tool for calculations, and subtraction is a basic operation you’ll use often. Knowing how to subtract in Excel can really speed up your data crunching and make you more efficient.
Subtracting Between Cells
To subtract one cell’s value from another, you’ll use cell references and the minus (-
) operator. Start your formula with an equals sign (=
), then enter the cell references you want to subtract, separated by the minus sign.
=Cell1 - Cell2
For example, if you want to subtract the value in cell B2
from the value in cell A1
and place the result in cell C1
, your formula in C1
would be =A1-B2
.
Subtracting Multiple Columns
To subtract an entire column, enter your subtraction formula in the first cell, then drag the fill handle (that little square at the bottom-right of the cell) down to apply the formula to the rest of the column.
This extends the subtraction formula across the column, automatically adjusting the cell references for each row and displaying the results in column C
.
You can also extend your formulas to subtract multiple cells within the formula, for example =A1 - B1 - C1
.
Subtracting Numbers Directly in a Cell
You can also perform subtraction directly within a cell by typing numbers. Just start with the equals sign (=
) and separate your numbers with the minus sign (-
).
=Number1 - Number2
For example, if you enter =73 - 23
into cell E2
and press Enter
, the cell will display 50
.
Subtracting a Sum of Cells
When you need to subtract the sum of several cells from another value, the SUM
function makes things easier. Use the SUM
function to add the cells you want to subtract, then put a minus sign in front of the SUM
function to subtract that sum.
=A1 - SUM(A2:A6)
For instance, to subtract the total of cells A2
through A6
from cell A1
, you would use the formula =A1 - SUM(A2:A6)
.
Subtracting the Same Number From Multiple Cells
If you need to subtract the same number from a bunch of cells, you can use an absolute cell reference. This locks the reference to a specific cell using dollar signs ($
).
For instance, to subtract the value in cell B8
from the cells B1
through B6
, you would create an absolute reference to B8
like this: $B$8
.
Here’s how the formula would look:
=B1 - $B$8
Drag the fill handle down to apply the subtraction to the remaining cells. Each will subtract the value in $B$8
.
Alternative Method: Paste Special
Another way to subtract the same number is with Paste Special. This method is effective when you don’t want to create formulas.
- Copy the cell containing the number you want to subtract (for example, cell
D1
).
- Select the range of cells where you want to perform the subtraction (
A1:A6
). - Right-click and select “Paste Special”.
- In the “Paste Special” dialog box, choose “Subtract” under “Operation”, then click “OK”.
This method directly subtracts the copied number from each of the selected cells.
Using these methods, you can handle your subtraction tasks in Excel with flexibility and efficiency.