Excel doesn’t have a specific function made for subtraction, like the SUM function for addition. But you can still perform the function by the minus (-) operator. It is one of the most frequently used and essential operators in Excel.
You can subtract columns, cells, dates, times, and even percentages using the minus operator. However, depending on what you’re trying to subtract, you may need to use different formulas in Excel.
Cell and Number Subtraction
You can subtract any number or cell reference containing numbers using the minus sign (-). A subtraction formula syntax is:
=value-value.
- In Excel, select a cell where you want the result to be displayed.
- Enter the first value, add a minus sign, and the second value.
- If you are subtracting cell references, you can click on the particular cell while typing the formula to reference it.
- Hit Enter.
You can also subtract a numeric value from a cell reference containing numbers or vice versa.
Subtract Multiple Cells
Subtracting multiple cells is the same as subtracting only two cells. You can also do the same with numeric values. Its syntax is
=value-value-value …
In some instances, you can also use a shorter method by using the SUM function to subtract multiple cells. Its syntax is
=cell-SUM(cell range)
For example, if you want to subtract the cell range A1:B2 (A1, A2, B1, B2) from C1, here’s what the formula looks like =C1-SUM(A1:B2)
Subtract Values of Columns
To subtract one column’s contents from another, you can use a single formula at the top and drag the fill handle (the green square dot at the bottom left) downwards. The fill handle will copy and adjust the formula relative to its starting cell and the cell it is currently in.
For example, your A column contains the total population of different cities, and the B column contains the total female population. Then, you can type in =A1-B1 in the C1 cell and drag the fill handle downwards. The formula in the C2 cell will automatically change to =A2-B2, and so on.
Furthermore, you can also drag the fill handle to the side to change the relative reference of your cell. It can also be used to subtract the corresponding values of different sets.
Subtract Columns by Absolute Cell Reference
You can subtract the values inside a whole column individually with a single cell using absolute cell reference. Doing so will lock a cell in the formula, so it doesn’t get changed even if it is copied. Its syntax is:
=cell-$cell letter$cell number
For example, =A1-$B$1. If you copy this formula downwards using the fill handle, only the A1 cell reference will change relative to its cell position, while the B1 cell will remain unchanged. In a nutshell, it is like subtracting all the employee’s attendance to the total working days to get their absent days.
Subtract Percentage Values
You can use the same syntax as subtracting any other values to subtract percentages in Excel. However, to subtract a certain percentage from a numeric value, you will need to use a different formula, =value* (1 - %)
For instance, =100*(1-10%) will subtract 10% from 100, which is 90. You can also interchange the numeric value with a cell reference containing a number.
Subtract Date and Time Values
You can subtract the date and time from the cell reference or with the corresponding value in the same cell. To subtract the date and time contained within another cell, you can use the same formula as subtracting cell references. However, to subtract date and time values in the same cell, you need to use a different formula.
- Formula syntax for subtracting dates:
=DATEVALUE("Date")-DATEVALUE("Date")
For example,=DATEVALUE("12/25/2022")-DATEVALUE("12/1/2022"). (US date format)
- Formula syntax for subtracting time:
=TIMEVALUE("Time")-TIMEVALUE("Time")
For example,=TIMEVALUE("5:00 PM")-TIMEVALUE("12:00 PM")
The Timevalue function returns the time in decimal format by default. To change it back to the time format, you can right-click the cell, select Format cells, and choose your format. Additionally, the date format in your formula has to be the same for the result to be accurate.
