We all know basic subtraction. It is mainly finding differences between two or more values. This article will show you subtraction formula in Excel that you can your excel workbook and improve your knowledge.
Download Practice Workbook
Basic Subtraction Formula
There is no function to subtract in Excel. You have to subtract using formulas. Subtraction always starts with an Equal (=) sign. Otherwise, it won’t work.
This is how you can subtract in a cell:
This will give you the result which is 100.
7 Ways to Use Subtraction Formula in Excel
1. Subtraction Formula Between Two Cells
You can subtract values from different cells using cell references. Here is a dataset which is showing a person’s salary and his expenses.
Now if we want to know about his Savings then we have to subtract his Expenses from his Salary. Here you can do it using cell references.
After that press ENTER. You can see the result.
2. Subtraction Between Multiple Cells
You can also subtract multiple cells at once. The formula is also the same.
Here you can see a dataset that shows a person’s salary and all his expenses. Now we want to know his savings which can be achieved by subtracting all the values from his salary.
Now add a cell named Savings. Here C2 cell is the salary. For finding savings, we are just subtracting every expense from his salary by following the method.
After entering that formula. Hit Enter and you can see the result.
3. Subtraction Between Columns
Now, sometimes you can find yourself in a situation where you need to subtract values between columns. You can also do it by cell reference.
a. Subtract Column Values
Here is a dataset of a shop that has product names, its purchase price, and selling price in different columns.
If you want to know the net profit for each product, you just need to subtract “Purchase Price” from “Selling Price”.
All you have to perform is basic subtraction. Here we are subtracting cell D4 from cell C4.
After you are done with your formula, press ENTER.
Now drag the fill handle (“+” sign) across all those cells to copy that formula.
Here, you can see your desired result “Net Profit” for each product.
b. Subtract a Specific Value from a Different Column
Sometimes there can be another situation that you need to subtract a specific value from another cell from an entire column. You can do this by absolute cell references. If you don’t know about absolute cell reference then please check our article about “What is and How to Do Absolute Cell Reference in Excel?”
Here is a dataset that shows different people’s amounts. Now, a particular fee is needed to be deducted from their amount. The fee is given in cell G3.
Now, we have to subtract that fee from each of their amounts. You have to use absolute cell reference $G$3 for that fee.
Then press Enter.
Now, drag that cell to copy your formula across the entire column. It will give you the amount that has been left after deduction.
4. Subtraction Formula for Percentages in Excel
The basic formula will work fine when you are doing subtraction between percentages.
=75% – 25%
You can also do it by cell reference by following:
You can subtract a percentage from a number. It means a decrease by percentage.
= number* (1 – percentage)
You can reduce an amount by 30% by this:
= 1000 * (1 – 30%)
You can do it by cell reference in excel.
Here is a dataset of some businessmen who have invested in the Stock Market and lost a percentage of their amount.
We are gonna find how much money remains after calculating loss by using our decrease by percentage formula.
Hit Enter. It will show the remaining amount.
Now, drag the formula to the end of that column to get all those remaining amounts.
5. Formula for Dates
No complexity around here to subtract between two dates. The following formula will work easily:
= End Date – Start Date
There are also a few ways you can do this using the following ways:
6. Subtraction Formula for Time
This is also the same as subtracting dates.
= End time – Start Time
Using Cell reference it will look like this:
= B2 - C2
7. Subtraction Formula for Text
Now you might ask whether you can subtract a text from another text using the minus(-) sign. The answer is No, you can’t. But we can subtract a specific word or text from another text value. But you have to do this with the help of other functions.
a. Subtract Formula for Case-Sensitive Text
To subtract a text of a cell from another cell, we are gonna use TRIM and Substitute function.
The substitute function will replace the text from desired text with a space.
Then Trim function will remove that extra space.
The above formula will work like this:
b. Subtract Formula for Case-Insensitive Text
The previous formula will remove exact text from the Name. But if you don’t want your formula to be case-sensitive then you can use the following formula:
The SEARCH function returns the position of the first character to subtract within the original string, ignoring the text case.
This number goes to the start number argument of the REPLACE function.
LEN is basically to find the length of the substring that should be removed. This number goes to the num_chars argument of REPLACE.
Here you can see C4 is the substring that we want to remove from B4.
These formulas will definitely help you for improving your Excel knowledge. To get the best out of it, you must try it on your own. If you want to know more about Excel’s functions and formulas, make sure you are checking our website Exceldemy.com other Excel-related articles.