We all are aware of the basic arithmetic operations which are addition, subtraction, multiplication & division. We use subtraction to minus a number from another number. Subtract in excel allow us to minus numbers, cell values, numbers in columns, date, time and many more.
In this section, we will study subtraction in excel with the different type of values as described below:
- Subtract Numbers
- Subtract Values in Cells
- Subtract a Value from Values in Range
- Subtract Values in Columns
- Subtract Dates
- Subtract Time
- Subtract Matrix
Steps to Subtract in Excel 2016:
Subtract Numbers
To subtract two numbers, you simply have to write the formula in the cell you want the result to be displayed in. Like in the example below, I have written the formula “=53-26” in the cell A2.
After writing the formula press enter to display the result.
Subtract Values in Cells
If the values to be subtracted are present in the cells. Then the subtraction formula should have the cell reference instead of actual numbers. Like in the example below, the two numbers to be subtracted are present in cell A2 & B2. So, I have mentioned the cell references in my subtraction formula in cell C2.
After writing the formula press enter to display the result.
Subtract a Value from Values in Range
If you want to subtract a single value from all the values in the range. Write the formula in some cell (in this example B2) and press enter.
Now drag down formula till you have values in the range to obtain subtraction result from other numbers in the range.
As you can see we have subtracted number 19 from the values in the cell range A2:A6.
Other option to obtain the same is to write the number in some cell and then lock the cell in the formula. For example, the number to subtracted is written in cell B2 and while writing the formula we have locked the cell B2.
To obtain subtraction result from other numbers in the range drag down the formula.
Subtract Values in Columns
To subtract the values, present in the columns, write the formula of subtraction with the cell references.
Now drag down the formula till there are values in the range.
Subtract Dates
To subtract two dates in excel we will discuss three methods. The first method is to simply put the dates in two different cells and supply those cell references in the formula as in the example below.
Note: Be sure that your number format is General.
The second method is to put your dates directly in the formula using DATE function as in the example below.
Keep the Number format as General to obtain a meaningful result.
You can also use the DATEVALUE function in case you have dates in text format.
Subtract Time
We will discuss time subtraction with two methods. With the first method, put your time values in two different cells and supply those references in the subtraction formula as you can see in the image below:
To get the correct result your formula cell must be in the Time Format.
You can directly supply the time value in the formula using the Time function.
Subtract Matrix
If you want to subtract two matrices, simply select the range of cells that has rows and columns equal to the operating matrices.
The formula would be:
=(A2:C4)-(E2:G4) // press Cntrl+Shift+Enter to convert this into an array formula.
It will display only the first value of the result matrix, to get other values of the result matrix, drag the formula in both the direction (right & down).
So, this is all about subtraction in excel. We have covered many scenarios & you can explore to get more. Hope the content is useful. Keep reading.
Leave a Reply