Excel doesn’t have a selected perform made for subtraction, just like the SUM perform for addition. But you possibly can nonetheless carry out the perform by the minus (-) operator. It is without doubt one of the most often used and important operators in Excel.
You can subtract columns, cells, dates, instances, and even percentages using the minus operator. However, relying on what you’re making an attempt to subtract, it’s possible you’ll want to make use of totally different formulation in Excel.
Cell and Number Subtraction
You can subtract any quantity or cell reference containing numbers using the minus signal (-). A subtraction method syntax is:
In Excel, choose a cell the place you need the end result to be displayed.
Enter the primary worth, add a minus signal, and the second worth.
If you might be subtracting cell references, you possibly can click on on the actual cell whereas typing the method to reference it.
You may subtract a numeric worth from a cell reference containing numbers or vice versa.
Subtract Multiple Cells
Subtracting a number of cells is identical as subtracting solely two cells. You may do the identical with numeric values. Its syntax is
In some cases, you may as well use a shorter technique through the use of the SUM perform to subtract a number of cells. Its syntax is
For instance, if you wish to subtract the cell vary A1:B2 (A1, A2, B1, B2) from C1, right here’s what the method seems like =C1-SUM(A1:B2)
Subtract Values of Columns
To subtract one column’s contents from one other, you should utilize a single method on the prime and drag the fill deal with (the inexperienced sq. dot on the backside left) downwards. The fill deal with will copy and alter the method relative to its beginning cell and the cell it’s at the moment in.
For instance, your A column incorporates the whole inhabitants of various cities, and the B column incorporates the whole feminine inhabitants. Then, you possibly can kind in =A1-B1 within the C1 cell and drag the fill deal with downwards. The method within the C2 cell will routinely change to =A2-B2, and so forth.
Furthermore, you may as well drag the fill deal with to the facet to vary the relative reference of your cell. It will also be used to subtract the corresponding values of various units.
Subtract Columns by Absolute Cell Reference
You can subtract the values inside an entire column individually with a single cell using absolute cell reference. Doing so will lock a cell within the method, so it doesn’t get modified even whether it is copied. Its syntax is:
=cell-$cell letter$cell quantity
For instance, =A1-$B$1. If you copy this method downwards using the fill deal with, solely the A1 cell reference will change relative to its cell place, whereas the B1 cell will stay unchanged. In a nutshell, it’s like subtracting all the worker’s attendance to the whole working days to get their absent days.
Subtract Percentage Values
You can use the identical syntax as subtracting some other values to subtract percentages in Excel. However, to subtract a sure share from a numeric worth, you have to to make use of a distinct method, =worth* (1 - %)
For instance, =100*(1-10%) will subtract 10% from 100, which is 90. You may interchange the numeric worth with a cell reference containing a quantity.
Subtract Date and Time Values
You can subtract the date and time from the cell reference or with the corresponding worth in the identical cell. To subtract the date and time contained inside one other cell, you should utilize the identical method as subtracting cell references. However, to subtract date and time values in the identical cell, you want to use a distinct method.
Formula syntax for subtracting dates: =DATEVALUE("Date")-DATEVALUE("Date") For instance,=DATEVALUE("12/25/2022")-DATEVALUE("12/1/2022"). (US date format)
Formula syntax for subtracting time: =TIMEVALUE("Time")-TIMEVALUE("Time") For instance, =TIMEVALUE("5:00 PM")-TIMEVALUE("12:00 PM")
The Timevalue perform returns the time in decimal format by default. To change it back to the time format, you possibly can right-click the cell, choose Format cells, and select your format. Additionally, the date format in your method needs to be the identical for the end result to be correct.