How to Use the SUBTRACT Function in Excel
Excel is a powerful spreadsheet program from Microsoft that makes it easy to work with numbers and other values.
While Excel contains a lot of power, it’s also quite useful for simple addition, subtraction, multiplication, and division.
In fact, Excel’s simple formulas make it easy to add and subtract numbers and cell values without breaking out a calculator.
How to Use Subtract Function in Excel
Many of Excel’s most powerful capabilities come via the use of functions, which are a kind of predesigned formula.
For example, the SUM function automatically sums or totals a range of cells without you having to manually add each cell to the previous one.
Unfortunately, there is no SUBTRACT function in Excel. That’s because subtracting is one of the easiest things you can do in a spreadsheet.
I‘ve found that performing subtraction in Excel is as simple as entering a simple mathematical formula. Excel does the rest.
The formula for subtracting one number from another starts with an equal sign and looks like this:
=value1 – value2
It doesn’t get much easier than that. You can subtract discrete values or the values contained in individual cells.
For example, let’s say I want to put an aquarium in my home so I can enjoy tropical fish.
I made a spreadsheet of the different tasks I need to complete and how much I expect each purchase to cost. I then gather the receipts to note what I actually spent. We’ll use subtract to see if my expected costs match reality.
Step 1: Type an equal sign in a cell.
You subtract numbers and cell values in Excel using a simple formula. In Excel, every formula starts with an equal sign, so position your cursor in the cell where you want to show the solution and enter the following:
=
Step 2: Add the first cell address.
Position the cursor after the equal and either manually enter the first cell address or use your mouse to select the first cell. You should now see something like this:
=B2
Step 3: Enter a minus sign.
Position the cursor after the first cell address and enter a minus sign, like this:
=B2-
Step 4: Add the second cell address.
Position the cursor after the minus sign and either manually enter the second cell address or use your mouse to select the second cell. You now have the following formula:
=B2-C2
Step 5: Press enter to the solution.
Press enter to enter the formula, and the solution is displayed in the cell. After running the formula on every line in my sheet, I can see that I completed my aquarium with $236 to spare.
Tips for Subtracting in Excel
You’re not limited to subtracting just one cell from another. In my experience, I’ve found that Excel can perform many different types of subtraction.
For example, you can subtract entire rows and columns, as well as cell ranges. Here are three tips on how to get the most out of subtracting in Excel.
How to Subtract Multiple Cells
You’re not limited to simple subtraction in Excel. You can subtract multiple cells from a single cell by stringing together additional cell addresses with minus signs.
For example, to subtract cells C13 through C 18 from B12, use the formula:
=B12-C13-C14-C15-C16-C17-C18
You could also write this as subtracting a range of cells from the first cell. In this instance, you’d use the range B2:B4 and enter this formula:
=B12-(C13:C18)
Just enclose the range you’re subtracting within parentheses.
How to Subtract Entire Columns
You may want to subtract all the values in one column from all the values in another column. This is easily done by copying the formula from a single cell to all the cells in the solution column.
Simply enter your subtraction formula into the first cell in a new column. Then, copy that formula to the other cells in the column by dragging the corner of the first cell down the column.
All the cells in subsequent rows will subtract the same two relative cells as in the first formula.
For example, if you copy the formula =D1-C1 downward through a column, the cells in each subsequent row would contain the formulas:
=B2-C2
=B3-C3
=B4-C4
And so on.
How to Subtract the Same Number From a Column of Numbers
Excel also lets you subtract the same number or cell value from all the cells in a column. You do this by locking the repeated number with dollar signs ($).
Let’s say you want to subtract the value in a given cell from a range of cells. For our example, assume the cell you want to subtract is G1.
You can’t just enter G1 and then copy the formula because Excel will change the referenced cell as you copy the formula.
Instead, you need to lock G1 into the formula by using dollar signs ($) in front of the row and column referenced by entering $G$1.
This tells Excel to always reference cell G1, whatever the other values in the formula. You end up with something like this:
=C2-$G$1
Pro tip: You can then copy that formula down an entire column, subtracting the fixed number from each cell in that column.
To learn even more about Excel, check out our article on How to Use Excel Like a Pro. This useful article contains 29 powerful tips, tricks, and shortcuts that will make Excel even more powerful.
Getting Started
Excel includes powerful arithmetic capabilities, including the ability to subtract numbers and cell values. Excel makes simple subtraction as easy as writing numbers on a blackboard.
