We all know basic subtraction. It is mainly about finding differences between two or more values. This article will show you how to apply subtraction formula in Excel so that you can your excel workbook and improve your knowledge.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
Basic Subtraction Formula
There is no function to subtract in Excel. You have to perform subtraction using formulas in Excel. Subtraction always starts with an Equal (=) sign. Otherwise, it won’t work.
=number 1 - number 2
8 Suitable Ways to Apply Subtraction Formula in Excel
This article will demonstrate how to apply the Subtraction formula in Excel to determine different criteria according to different data sets and how to subtract from text to text by combining the TRIM and SUBSTITUTE functions to subtract Case-Sensitive text and merging the TRIM, REPLACE, SEARCH, and LEN functions to subtract Case-Insensitive text. Here, we will show how to determine the date of differences between two dates and the time of differences between two times, calculate percentages, find the differences between two cells, and subtract a specific cell value from any cell range by using the Subtraction formula in Excel.
Method 1: Finding Differences Between Two Cells by Using Subtraction Formula in Excel
You can perform subtraction of values from different cells using cell references. Here is a dataset that shows a person’s salary and expenses.
Steps:
- Therefore, if we want to know about his Savings then we have to perform subtraction of his Expenses from his Salary. Here you can do it using cell references.
=B5-C5
- After that press ENTER. You can see the result.
- As you can see, we have successfully performed subtraction using a formula in Excel.
Read More: How to Add and Subtract Multiple Cells in Excel (3 Examples)
Method 2: Calculating Differences in Multiple Cells by Applying Subtraction Formula in Excel
You can also do the subtraction of multiple cells at once. The formula is also the same.
Steps:
- Firstly, 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 calculated by subtracting all the values from his salary.
- Then, add a cell named Savings. Here C4 cell is the salary. For finding savings, we are just subtracting every expense from his salary by following the Formula.
=C4-C5-C6-C7-C8-C9
- After that press ENTER. You can see the result.
- Finally, you will get the following result.
Read more: How to Subtract Multiple Cells in Excel (6 Effective Methods)
Method 3: Using Subtraction Formula Between Two Columns
Now, sometimes you can find yourself in a situation where you need to do subtraction of values between columns. You can also do it by cell reference.
3.1: Subtract Two Column Values
Here is a dataset of a shop that has product names, purchase prices, and selling prices in different columns. If you want to know the net profit for each product, you just need to subtract the “Purchase Price” from “Selling Price”.
Steps:
- Firstly, choose the E5 cell.
- Secondly, write down the following formula here.
=C5-D5
- Thirdly, press Enter.
- So, drag the Fill Handle across all those cells to copy that formula.
- Finally, you can see your desired result “Net Profit” for each product.
3.2: Subtract a Specific Value from Each Cell in a Different Column
Sometimes there can be another situation in which you need to perform subtraction for a specific value from another cell in an entire column. You can do this by using absolute cell references. If you don’t know about absolute cell reference, then please check our article, “What is and How to Do Absolute Cell Reference in Excel?”
Steps:
- So, 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 F5.
- Now, we have to subtract that fee from each of their amounts. You have to use absolute cell reference $F$5 for that fee.
=C5-$F$5
- 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 the deduction.
- Lastly, you will get the final output shown in the below image.
Read More: How to Subtract Columns in Excel (6 Easy Methods)
Method 4: Calculating Differences in Percentages by Inserting Subtraction Formula in Excel
The basic formula will work fine when you are doing subtraction between percentages.
=75% - 25%
You can subtract a percentage from a number. It means a decrease in percentage.
= number* (1 - percentage)
You can reduce an amount by 30% by this:
= 1000 * (1 - 30%)
Steps:
- Here is a dataset of some businessmen who have invested in the Stock Market and lost a percentage of their amount.
- So, we are gonna find how much money remains after calculating the loss by using our decrease by percentage formula.
=B5*(1-C5)
- Therefore, hit Enter. It will show the remaining amount.
- Then, drag the formula to the end of that column to get all those remaining amounts.
- As a result, you can see all the output in the below image.
Read More: How to Subtract in Excel Based on Criteria (3 Suitable Examples)
Method 5: Finding Differences Between Two Dates by Using Subtraction Formula in Excel
There is no complexity around here to subtract between two dates. The following formula will work easily:
= End Date - Start Date
Steps:
- Firstly, choose the D5 cell.
- Secondly, write down the following formula here.
=B5-C5
- Thirdly, press Enter.
- Therefore, you will see the result for the D5 cell.
- Then, use the Fill Handle tool and drag it down from the D5 cell to the D9 cell.
- Consequently, you will find all the results.
Method 6: Finding Differences Between Two Times by Applying Subtraction Formula in Excel
This is also the same as subtracting dates.
= End time - Start Time
- Using Cell reference it will look like this:
=C5-B5
- Then, hit Enter.
- Therefore, you will see the result for the D5 cell.
- Then, use the Fill Handle tool and drag it down from the D5 cell to the D6 cell.
- Therefore, the given image shows all the final output.
Method 7: Combining TRIM and SUBSTITUTE Functions to Subtract Case-Sensitive 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.
To subtract the text of a cell from another cell, we are going to use the TRIM and SUBSTITUTE functions.
Steps:
- Firstly, choose the D5 cell.
- Then, the following formula will work like this.
=TRIM(SUBSTITUTE(B5,C5,""))
- After that, press Enter.
- Firstly, the SUBSTITUTE function will replace the text from desired text with a space.
- Then, the TRIM function will remove that extra space.
- Therefore, you will see the result for the D5 cell.
- After that, use the Fill Handle tool and drag it down from the D5 cell to the D9 cell.
- Therefore, the given image shows all the final output.
Method 8: Merging TRIM, REPLACE, SEARCH and LEN Functions to Subtract Case-Insensitive Text
The previous formula will remove the exact text from the name. But if you don’t want your formula to be case-sensitive then you can use the instructions in this section.
Steps:
- Firstly, choose the D5 cell.
- Secondly, write down the following formula here.
=TRIM(REPLACE(B5,SEARCH(C5,B5),LEN(C5),""))
- Thirdly, press Enter.
- Here you can see C5 is the substring that we want to remove from B5.
- 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.
- The LEN function is basically to find the length of the substring that should be removed. This number goes to the num_chars argument of the REPLACE function.
- Therefore, you will see the result for the D5 cell.
- Then, use the Fill Handle tool and drag it down from the D5 cell to the D9 cell.
- Finally, you can see your desired result in the below image.
Conclusion
These formulas will definitely help you improve your Excel knowledge. To get the most 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.
Related Articles
- How to Subtract from a Total in Excel (3 Quick Methods)
- Subtraction for Whole Column in Excel (with 5 Examples)
- How to Subtract from Different Sheets in Excel (2 Suitable Ways)
- Excel VBA: Subtract One Range from Another (3 Handy Cases)