Alok Paul
Subtraction is a common operation in mathematics. In Microsoft Excel, we don’t have any built-in function for subtraction. We will show how to create the formula in Excel to subtract in this tutorial.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
10 Ways to Create a Formula to Subtract in Excel
The basic formula for subtraction is the difference between two numbers.
1st value is called minuend and 2nd part is called subtrahend. We will form new subtraction formulas based on this basic.
1. Input Numbers Directly on Excel Subtraction Formula
We can directly input the values in the formula for subtraction.
We will use the following dataset.
Step 1:
- Go to Cell C7.
- Put an Equal sign(=). Insert 5000 then put a minus sign(-). Lastly, put 1000.
=5000-1000
Step 2:
- Hit the Enter button.
We get the result after subtraction.
Read More: How to Create a Formula in Excel without Using a Function (6 Approaches)
2. Use Cell Reference to Create Subtraction Formula in Excel
We will use a cell reference to create a formula for subtraction in Excel.
Step 1:
- Go to Cell C7.
- Type the formula below.
=C4-C5
Step 2:
- Press the Enter button.
Read More: How to Create a Formula in Excel (5 Ways)
3. Create a Formula to Subtract Multiple Cells From One Cell
We can make 3 formulas for this purpose as follows-
3.1 Subtract Using Minus Sign
We can use a minus sign(-) to subtract multiple cells from one cell.
The dataset below will be used for this purpose. We will subtract the costs like house rent, electricity bill, internet bill, transport cos and food from the salary.
Step 1:
- Go to Cell C11.
- Write the formula below.
=C5-C6-C7-C8-C9-C10
Step 2:
- Press the Enter button.
Here, we get the savings.
3.2 Subtract Using SUM Function
The SUM function adds the given values or cell references.
We can use the SUM function for subtracting multiple cells from one cell. We will add all the costs and then subtract them from the salary.
Put the following formula on Cell C11.
=C5-SUM(C6:C10)
3.3 Subtract Using SUM Function and Using Negative Numbers
We will use only the SUM function to subtract multiple cells from one cell.
Step 1:
- Here, we will convert the cost values into negative numbers. Just put minus sign(-) before each value.
Step 2:
- Move to Cell C11.
- Put the following formula on that cell.
=SUM(C5:C10)
Read More: How to Create a Formula in Excel for Multiple Cells (9 Methods)
4. Create Formula to Subtract Multiple Columns in Excel
We will calculate the profit by subtracting cost from revenue. We will consider the dataset below.
- Go to Cell E5.
- Put the following formula and the drag Fill Handle icon.
=C5-D5
We get the result of the difference between the two columns.
If we use Excel 365, can apply the following formula and don’t need to drag the Fill Handle icon.
=C5:C10-D5:D10
5. Subtract Specific Number from a Column in Excel
We will subtract a specific number from all cells of a column.
The following dataset is representing the salary of employees. We will subtract a specific fee from the salary.
Step 1:
- Go to Cell C5.
- Put the following formula and drag the Fill Handle icon.
=C5-100
We net the amount after deducting the fee from the salary.
Step 2:
- We can use Absolute Cell Reference instead of the direct value. So, replace the previous formula with the following one.
=C5-$D$5
We get the same result.
In case of the Excel 365, the following formula can be used.
=C5:C10-$D$5
6. Excel Subtraction Formula for Percentage Values
We can directly input the percentage values in the formula or use the cell references to subtract them. Follow the steps below:
- In Cell C7, put the formula to input values directly.
- And in Cell C8, use cell references to subtract. The formulas are:
=100%-10%
=C4-C5
We get the subtracted result for both situations.
We can also subtract percentages from numbers. Deduct the percentage value from 1 and multiply that by the given number.
- Now, go to Cell E5.
- Put the formula below and drag the Fill Handle icon.
=C5*(1-D5)
We get the subtracted values.
Read More: How to Create a Formula to Calculate Percentage in Excel
7. Subtraction Formula in Excel for Date Values
We can easily make a formula for date values. Just subtract the starting date from the end date.
The submission date and deadline are given in the dataset and we will calculate the overdue days.
Follow the steps below.
- Go to Cell D5.
- Put the formula below and pull the Fill Handle icon.
=B5-C5
We get overdue days for all the rows.
We can also do this using Excel functions.
- Go to Cell D12.
- Write the formula based on the DATEÂ function.
=DATE(2021,3,14)-DATE(2021,3,10)
We get the overdue days by subtraction. We have another function DATEVALUE for this operation.
- Now, apply another formula based on the DATEVALUE function and Cell D12.
=DATEVALUE("14-03-2021")-DATEVALUE("10-03-2021")
Read More: How to Create a Formula in Excel to Change Date by 1 Year (3 Methods)
8. Subtraction Formula for Time Values in Excel
We can create a formula to subtract time values in Excel. The basic formula for subtracting time values is the difference between start and end time.
We can do this by Cell Reference or using the Time function.
Cell Reference
- Go to Cell D5.
- Put the following formula on that cell and drag the Fill Handle icon.
=C5-B5
We get the duration of work.
Now we will use a function to get the duration of work.
- Now, we will use a formula based on the TIME function on Cell D10.
=TIME(16,0,0)-TIME(8,0,0)
Read More: How to Create a Custom Formula in Excel (A Step-by-Step Guideline)
9. Excel Subtraction Formula to Deal with Matrix
We can create a formula to subtract one matrix from another. Consider the following two matrices Matrix 1 and Matrix 2. We will subtract Matrix 1 from Matrix 2.
Step 1:
- Go to Cell D11.
- Put the formula below and hit the Enter button.
=F5-B5
Step 2:
- Now, drag the Fill Handle icon to Cell F11.
- Again, drag the Fill Handle icon downwards till Cell F13.
We get the result using the simple formula.
10. Subtraction Formula for Text in Excel
We have two options in text subtraction in Excel.
We will consider the dataset below for this method.
10.1 Case-sensitive Formula for Text
This section is for case-sensitive cases of text.
Step 1:
- Go to Cell D5.
- Put the formula below.
=TRIM(SUBSTITUTE(B5,C5,""))
Step 2:
- Hit the Enter button and drag the Fill Handle icon.
Step 3:
- Now, change the case of Cell C5 and look at the dataset.
As this is a case-sensitive formula, subtract operation is failed due to case difference.
10.2 Case-insensitive Formula for Text
We will apply this for case-insensitive cases.
Step 1:
- Put the following formula on Cell D5.
=TRIM(REPLACE(B5,SEARCH(C5,B5),LEN(C5),""))
Step 2:
- Hit the Enter and drag the Fill Handle icon.
We can see that case differences do not hamper the formula.
Conclusion
In this article, we discussed how to create a formula in Excel to Subtract. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
