Want to find out if you’re in profit or loss from all of your investments? Need to update the inventory by subtracting sold items from stored items? Or want to know if you’re within the monthly budget for your household expenditure target? In all such cases, you need to know how to use the Excel subtraction formula.
Microsoft Excel supplies endless mathematical formulas for domains like statistics, finance, engineering, basic calculation, logic, database, lookup, references, trigonometry, and so on.
However, you’ll be surprised there’s no subtraction formula in Excel. However, it’s one of the most basic mathematical operators used from basic calculations to the most complex financial mathematics.
Whenever you need to deduct an amount or value from another figure, you need to use the subtraction formula of Excel. But as I said before there’s no such function, then how do you do it or how do other millions of Excel users do it?
Read on to find out several ways to subtract numbers and values in Excel using a mathematical operator as an Excel formula for subtraction.
What Is the Excel Formula for Subtraction?
There are no dedicated functions for subtraction and division in Excel. You can get dedicated functions for addition, like SUM, and multiplication, like PRODUCT, in Excel.
However, the basic mathematical operator for subtraction is the minus sign (-) and for division, it’s the divide (/) sign. These signs work seamlessly when used between two or many numbers or in nested complex formulas.
Therefore, to deduct any value from a parent number, you can use the minus (-) sign effortlessly on the Excel desktop and web app.
Using this operator, you can subtract anything like numbers, dollars, days, months, hours, minutes, percentages, lists, matrices, and text strings.
Find below some frequently-used cases where you can use the subtract operator in Excel:
1. Excel Subtraction Formula to Deduct One Number From Another
Consider you need to use the subtract operator to deduct one large number from another quickly while working on Excel. Instead of switching to an online calculator or Windows calculator app, perform the calculation in Excel by following these steps:
- Select a cell where you need the subtracted value from two values or figures.
- Type the equal (=) sign to start the formula.
- Enter the first number from which you want to deduct another value.
- Now type the minus (-) sign.
- Then, type the value you want to deduct.
- Hit Enter and you’ve created a DIY Excel subtraction formula.
This instance isn’t just for two numbers. You can subtract as many numbers as you want by entering a minus sign before each number. Look at the following image to understand better:
If you’ve returned from the grocery store and want to check the bill to ensure you didn’t pay anything extra, you can make a quick subtraction of all the items bought from the total money paid by following the above formula.
2. Excel Subtraction Formula to Deduct One Cell From Another
You can happily use the above formula to subtract one or many numbers from a parent value. How about you want to create an automatic Excel worksheet where you can just enter the item values and the function will subtract those from a parent value?
In this case, you can’t manually subtract one or many numbers. You must use the subtraction of cell references. Find the instructions here:
- Type the values you want to subtract in two adjacent cells.
- Then, select the third cell where you want the subtracted figure.
- Start the formula by entering equal (=) and then select the first cell, put a minus (-), and then select the second cell.
- Hit enter to subtract one cell from another.
In this case, the formula you just created will always deduct whichever numbers are there in the second cell from the first cell.
In the above example of Apple sales, you can change the selling price and buy price anytime you want. Cell J3 will still deduct cell F3 from cell C3 and thus you can recycle the formula.
3. Excel Subtraction Formula to Deduct Column Values
Let’s consider you’ve got two columns of prices for various items in your shop inventory. One column is for the sell price and another for the buy price. You need to deduce the profit from these values. Here’s how you must proceed in Excel to figure out your gains:
- Create a list of items to sell.
- Now, create column headers for sell and buy prices.
- Create another column heading for profit and loss.
- Now, enter the values in their respective cells.
- The profit and loss column should be empty.
- When done populating the values, select the first cell under the profit and loss column.
- Begin the formula with an equal (=) sign and then select the sell price for the first item.
- Put the mathematical operator for subtracting, which is the minus (-) sign.
- Now, select the cell that contains the buy price for the same item.
- Hit Enter to get the profit or loss from sales of the first item.
- Again select the first cell beneath the profit and loss column header.
- Press Ctrl + C (copy command).
- Select the rest of the profit and loss column for other items.
- Press Ctrl + V (paste command).
- You get the profit and loss value from your sales proceeds by using a DIY subtract formula for Excel.
4. Excel Subtraction Formula With SUM Function
If you need to deduct many cell values from one cell, then you can either manually deduct each cell from the parent cell or use the SUM function along with the subtraction operator by following these quick steps:
- In the above example, you need to find out if you’re in profit or loss in the cell with the interrogation mark.
- Select the cell below the Net Profit column header.
- Type an equal (=) sign and then select the cell just below the Gross Sell Price column header.
- Now, put a minus (-) sign and then type the opening parentheses.
- Enter SUM and put another open parenthesis.
- Select cell ranges C2 through C17.
- Close the formula by entering two closing parentheses.
- Hit Enter to get the profit or loss summary.
5. Excel Subtraction Formula for Matrices
Let’s consider you got two matrices and you need the subtraction value for the corresponding cells in another matrix. Here’s how you can use a DIY subtraction formula in Excel to calculate matrix values:
- Create two matrices.
- Create the third matrix where you need subtracted values.
- Select all the nine cells of the third matrix and enter the following formula:
{=(A2:C4)-(E2:G4)}
- Now, don’t press the Enter key. Instead, press Ctrl + Shift + Enter.
- This will create an array formula for all the cells automatically and subtract one matrix from the other as shown in the image.
6. Excel Subtraction Formula: Other Popular Use Cases
You can use the subtraction operator to deduct various values from its parent value and some examples are as mentioned below:
You may use the following formula to deduct a month from another month and deduce days:
1-Mar-23 - 1-Feb-23 = 28 =(B2-C2)
Also, use the below-mentioned formula to deduct end time from start time to know the consumed time against a project:
16:00 - 12:45 = 3:15 =(H2-I2)
Furthermore, you can deduct a percentage from a percentage or deduct a percentage from a value to get the resulting values:
100% - 80% = 20% =(B6-C6) (deducting percentage from percentage)
$3,000.00 - 30% = $2,100.00 ={B8*(1-30%)} (reducing an amount by 30%)
Conclusion
So far, you’ve explored many ways to use the DIY Excel subtraction formula which uses the subtraction operator or the minus (-) sign.
If I missed any method you know about Excel formula for subtraction, do mention that in the comment box below.
Next up, effortlessly learn to combine two columns in Excel and lock or unlock cells in Excel.
