Excel is a spreadsheet software that allows you to perform calculations, analyzes and graphs with numerical data. Among the basic operations that can be performed with Excel is subtraction. In this article we will see how to do subtraction in Excel , using different methods and formulas.
Quick video tutorial to learn how to do subtraction in Excel:
The simplest method: use the minus sign (-)
The simplest way to subtract in Excel is to use the minus sign (-) between the values to be subtracted. For example, if we want to subtract 5 from 10, we can write in a cell:
=10-5
And press Enter. The result is then displayed in the cell:
=10-5 5
We can also use cell references instead of numeric values. For example, if we have the values 10 and 5 in cells A1 and B1, we can write in another cell:
=A1-B1
And press Enter. The result is then displayed in the cell:
=A1-B1 5
You can also use the minus sign (-) to subtract several values at once . For example, if we want to subtract 3, 4 and 5 from 20, we can write in a cell:
=20-3-4-5
And press Enter. The result is then displayed in the cell:
=20-3-4-5 8
The alternative method: use the SUBTRACT function
Another method to do a subtraction in Excel is to use the SUBTRACT function . This function takes two arguments: the first is the value we want to subtract from, and the second is the value to subtract . For example, if we want to subtract 5 from 10, we can write in a cell:
=SUBTRACT(10;5)
And press Enter. The result is then displayed in the cell:
=SUBTRACT(10;5) 5
We can also use cell references instead of numeric values. For example, if we have the values 10 and 5 in cells A1 and B1, we can write in another cell:
=SUBTRACT(A1;B1)
And press Enter. The result is then 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 several values at once, you must nest several SUBTRACT functions. For example, if we want to subtract 3, 4 and 5 from 20, we can write in a cell:
=SUBTRACT(SUBTRACT(SUBTRACT(20;3);4);5)
And press Enter. The result is then displayed in the cell:
=SUBTRACT(SUBTRACT(SUBTRACT(20;3);4);5) 8
The advanced method: using the SUM function with negative numbers
An advanced method for doing subtraction in Excel is to use the SUM function with negative numbers . The SUM function allows you to add several values together. If we use negative numbers as arguments, we actually get a subtraction. For example, if we want to subtract 5 from 10 , we can write in a cell:
=SUM(10;-5)
And press Enter. The result is then displayed in the cell:
=SUM(10;-5) 5
We can also use cell references instead of numeric values. For example, if we have the values 10 and 5 in cells A1 and B1, we can write in another cell:
=SUM(A1;-B1)
And press Enter. The result is then displayed in the cell:
=SUM(A1;-B1) 5
You can also use the SUM function to subtract several values at once . For example, if we want to subtract 3, 4 and 5 from 20, we can write in a cell:
=SUM(20;-3;-4;-5)
And press Enter. The result is then displayed in the cell:
=SUM(20;-3;-4;-5) 8
FAQs
How to subtract with percentages in Excel?
To subtract with percentages in Excel , you must convert the percentages to decimal numbers, then use one of the methods seen previously. For example, if we want to subtract 10% from 50, we can write in a cell:
=50-(10/100)*50
Or :
=SUBTRACT(50;(10/100)*50)
Or :
=SUM(50;-(10/100)*50)
The result is then:
=50-(10/100)*50 45
How to subtract with dates in Excel?
To do a subtraction with dates in Excel , you must use the appropriate date format, then use one of the methods seen previously. For example, if we want to calculate the number of days between January 1, 2020 and December 31, 2020 , we can write in a cell:
=31/12/2020-01/01/2020
Or :
=SUBTRACT(12/31/2020;01/01/2020)
Or :
=SUM(12/31/2020;-01/01/2020)
The result is then:
=31/12/2020-01/01/2020 365
How to subtract with hours in Excel?
To subtract with hours in Excel , you must use the appropriate time format and then use one of the methods seen previously. For example, if we want to calculate the number of hours between 8:00 a.m. and 5:00 p.m. , we can write in a cell:
=17:00-08:00
Or :
=SUBTRACT(17:00;08:00)
Or :
=SUM(17:00;-08:00)
The result is then:
=17:00-08:00 9:00
How to subtract with fractions in Excel?
To subtract with fractions in Excel , you must convert the fractions to decimal numbers, then use one of the methods seen previously. For example, if we want to subtract 1/4 from 3/4, we can write in a cell:
=(3/4)-(1/4)
Or :
=SUBTRACT(3/4;1/4)
Or :
=SUM(3/4;-(1/4))
The result is then:
=(3/4)-(1/4) 0.5
How to do conditional subtraction in Excel?
To do a conditional subtraction in Excel , you must 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 we want to subtract 10% from the price of a product if it exceeds 100 euros, we can write in a cell:
=IF(A1>100;A1-(10/100)*A1;A1)
And press Enter. The result is then 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 make a conditional sum. For example, if we want to subtract 10% from the total price of products that exceed 100 euros, we can write in a cell:
=SUM.IF(A1:A10;“>100”;A1:A10)-(10/100)*SUM.IF(A1:A10;“>100”;A1:A10)
And press Enter. The result is then displayed in the cell:
=SUM.IF(A1:A10;“>100”;A1:A10)-(10/100)*SUM.IF(A1:A10;“>100”;A1:A10) 810
If 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 this amount, i.e.:
810 – (10/100) * 810 = 729
The end result is therefore:
729
Conclusion
We have seen in this article how to do subtraction in Excel , using different methods and formulas. The simplest method is to use the minus sign (-) between the values to be subtracted. The alternative method is to use the SUBTRACT function, which takes two arguments: the value from which we want to subtract, and the value to subtract. The advanced method is to use the SUM function with negative numbers, which is like doing inverse addition. Each method has its advantages and disadvantages, depending on the number of values to subtract 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 better functionality to Excel.
