Excel is a spreadsheet program that allows you to perform calculations, analyses, and graphs with numerical data. Among the basic operations you can perform with Excel is subtraction. In this article, we'll see how to perform subtraction in Excel , using different methods and formulas.
Quick video tutorial to learn how to perform subtraction in Excel:
The simplest method: use the minus sign (-)
The simplest way to perform subtraction in Excel is to use a minus sign (-) between the values to be subtracted. For example, if you want to subtract 5 from 10, you can write the following in a cell:
=10-5
Then press Enter. The result will then be displayed in the cell:
=10-5 5
You can also use cell references instead of numerical values. For example, if you have the values 10 and 5 in cells A1 and B1, you can write the following in another cell:
=A1-B1
Then press Enter. The result will then be displayed in the cell:
=A1-B1 5
You can also use the minus sign (-) to subtract several values at once . For example, if you want to subtract 3, 4, and 5 from 20, you can write the following in a cell:
=20-3-4-5
Then press Enter. The result will then be displayed in the cell:
=20-3-4-5 8
The alternative method: use the SUBTRACT function
Another method for performing subtraction in Excel is to use the SUBTRACT function . This function takes two arguments: the first is the value from which you want to subtract, and the second is the value to be subtracted . For example, if you want to subtract 5 from 10, you can write the following in a cell:
=SUBTRACT(10;5)
Then press Enter. The result will then be displayed in the cell:
=SUBTRACT(10,5) 5
You can also use cell references instead of numerical values. For example, if you have the values 10 and 5 in cells A1 and B1, you can write the following in another cell:
=SUBTRACT(A1;B1)
Then press Enter. The result will then be displayed in the cell:
=SUBTRACT(A1;B1) 5
The SUBTRACT function cannot subtract more than two values at a time . If you want to subtract multiple values at once, you must nest several SUBTRACT functions. For example, if you want to subtract 3, 4, and 5 from 20, you can write the following in a cell:
=SUBTRACT(SUBTRACT(SUBTRACT(20,3),4),5)
Then press Enter. The result will then be displayed in the cell:
=SUBSTRATE(SUBSTRATE(SUBSTRATE(20,3),4),5) 8
The advanced method: using the SUM function with negative numbers
An advanced method for performing subtraction in Excel is to use the SUM function with negative numbers . The SUM function allows you to add multiple values together. If you use negative numbers as arguments, you are actually performing a subtraction. For example, if you want to subtract 5 from 10 , you can write the following in a cell:
=SUM(10;-5)
Then press Enter. The result will then be displayed in the cell:
=SUM(10,-5) 5
You can also use cell references instead of numerical values. For example, if you have the values 10 and 5 in cells A1 and B1, you can write the following in another cell:
=SUM(A1;-B1)
Then press Enter. The result will then be displayed in the cell:
=SUM(A1,-B1) 5
You can also use the SUM function to subtract multiple values at once . For example, if you want to subtract 3, 4, and 5 from 20, you can write the following in a cell:
=SUM(20;-3;-4;-5)
Then press Enter. The result will then be displayed in the cell:
=SUM(20,-3,-4,-5) 8
FAQ
How do I perform subtraction with percentages in Excel?
To subtract percentages in Excel , you must first convert the percentages to decimals, then use one of the methods described earlier. For example, to subtract 10% from 50, you can write the following in a cell:
=50-(10/100)*50
Or:
=SUBTRACT(50;(10/100)*50)
Or again:
=SUM(50;-(10/100)*50)
The result is then:
=50-(10/100)*50 45
How do I perform subtraction with dates in Excel?
To perform subtraction with dates in Excel , you must use the appropriate date format, then use one of the methods seen previously. For example, if you want to calculate the number of days between January 1, 2020, and December 31, 2020 , you can write the following in a cell:
=31/12/2020-01/01/2020
Or:
=SUBTRACT(31/12/2020;01/01/2020))
Or again:
=SUM(31/12/2020;-01/01/2020)
The result is then:
=31/12/2020-01/01/2020 365
How do I perform a subtraction with times in Excel?
To perform a subtraction with hours in Excel , you must use the appropriate time format, then use one of the methods seen previously. For example, if you want to calculate the number of hours between 8:00 AM and 5:00 PM , you can write the following in a cell:
=17:00-08:00
Or:
=SUBTRACT(17:00;08:00)
Or again:
=SUM(17:00,-08:00)
The result is then:
=17:00-08:00 9:00
How do I perform subtraction with fractions in Excel?
To subtract fractions in Excel , you must first convert the fractions to decimals, then use one of the methods described earlier. For example, to subtract 1/4 from 3/4, you can write the following in a cell:
=(3/4)-(1/4)
Or:
=SUBTRACT(3/4;1/4)
Or again:
=SUM(3/4;-(1/4))
The result is then:
=(3/4)-(1/4) 0.5
How do I perform conditional subtraction in Excel?
To perform conditional subtraction in Excel , you need to use the IF function or the SUMIF function. These functions allow you to check if a condition is met, and then perform a calculation accordingly. For example, if you want to subtract 10% from the price of a product if it exceeds 100 euros, you can write the following in a cell:
=IF(A1>100,A1-(10/100)*A1,A1)
Then press Enter. The result will then be displayed in the cell:
=IF(A1>100;A1-(10/100)*A1;A1) 90
If the value in cell A1 is greater than 100, it is reduced by 10%. Otherwise, it remains unchanged.
You can also use the SUMIF function, which allows you to perform a conditional sum. For example, if you want to subtract 10% from the total price of products that exceed 100 euros, you can write the following in a cell:
=SUM.IF(A1:A10;“>100”;A1:A10)-(10/100)*SUM.IF(A1:A10;“>100”;A1:A10)
Then press Enter. The result will then be displayed in the cell:
=SUM.IF(A1:A10;“>100”;A1:A10)-(10/100)*SUM.IF(A1:A10;“>100”;A1:A10) 810
If the cell range A1:A10 contains the following values:
| HAS |
| 50 |
| 120 |
| 80 |
| 150 |
| 90 |
| 200 |
| 60 |
| 180 |
| 70 |
| 160 |
The SUMIF function calculates the sum of values that are greater than 100, i.e.:
120 + 150 + 200 + 180 + 160 = 810
Then, she subtracts 10% of that amount, which is:
810 – (10/100) * 810 = 729
The final result is therefore:
729
Conclusion
In this article, we've seen how to perform subtraction in Excel using different methods and formulas. The simplest method is to use a minus sign (-) between the values to be subtracted. An alternative method is to use the SUBTRACT function, which takes two arguments: the value from which you want to subtract, and the value to be subtracted. The advanced method is to use the SUM function with negative numbers, which is essentially reverse addition. Each method has its advantages and disadvantages, depending on the number of values to be subtracted and the readability of the formula. If you're looking for other options for creating and editing spreadsheets , you can check out our comparison of the best online spreadsheets that offer similar or superior features to Excel.
