Microsoft Excel it is characterized by tell with a great number of functions that allow working with different type of data. But the striking thing is that it does not have a tool that can subtract directly.
For it, It is necessary to use different formulas that will help you obtain the result you are looking for. In addition, you should consider that you need to use them in accordance with the type of data you have.
In this article we will show you, the functions you should use to achieve subtract two or more items. We will also indicate the steps you must take to subtract dates, time and any other format.
What are all the formulas used to subtract data in Excel?
In Excel you won’t be able to find one function that is used directly to subtract. Instead you can use the tool SUM and place a algebraic sum in which you must distinguish its positive and negative signs so that the program can bring the values.
Another way you have to subtract is to use the function PRODUCT for cases where you need subtract a certain percentage. You must bear in mind that to use this tool you will have to convert the percentage into a figure of both times.
You will also have the function DATED with which you can subtract dates, but we will explain this tool to you in the next paragraphs.
By last, if you need to subtract complex numbers, you must use the function IM.SUSTR with which you must indicate a number required and the others optionally. This tool can be used like this example IM.SUSTR (complex_num1, [núm_complejo2], [núm_complejo3], …).
Learn step by step how to subtract data or any element in Excel
We will show you below how you should do to subtract different data in Microsoft Excel:
In case you want subtract dates, you will have the possibility to express it in years, months or days. The first thing you should do is put the most recent date first and later subtract that cell with an older date. The result will give you the number of days that exist between both dates.
But there is a way much more practical to do so in which you can use a conditional formatting and setting values easier to understand. In this case the oldest date to watch go first. When you want to calculate day you should use the formula = DATE (Oldest Date; Newest Date; “D”) in this case the number you will get will be represented in days.
If what you are looking for is find the months that exist between certain dates you must use the formula = DATE (Oldest Date; Newest Date; “M”) and for years the formula will be = DATE (Oldest Date; Newest Date; “Y”).
To subtract data you must use a function SUM, but with negative signs. That is, the number you want to remove from the other must go with the opposite sign. The easiest formula is to make the cells A1 – B1, or in the place where the data is located.
For subtract percentages you must express them in so much for one by the first thing you should do is have the cells divided by 100If they are already expressed in this terminology, you should not do this step.
Suppose the cell A1 it is equal to 95%, which is the same to say that its value 0.95. The cell A2 has a percentage of 65%, or as for one it would be 0.65.
If both cells are subtracted that would give 0.95 – 0.65 = 0.30. That is, 30%.
Instead, if you want subtract a percentage from a number you must use the PRODUCT function. So if you want extract 15% to 90, you will have to perform this formula: 90 x (1-0.15) = 76.50
For subtract cells you will have to use in a practical way one cell minus the other. For example, A1 – B1.
Columns and rows
If what you are looking for is subtract columns and rows, the first thing you should do is total them. That is to say, you will have to make a general sum of each of the results of each column or row. Then you must apply formula of subtract cells, as we mentioned in the previous section.
In case you want subtract multiple rows or columns you must do A1 – B1 – C1 – D1 one for each element.
When you need subtract hours or time data, you must make a subtraction between two columns using the format h: mm.
In case you have to subtract more than 24 hours you will have to go to Category and then click on Cell format. Next, you must choose Custom and in kind to choose m / d / yyyy h: mm am / pm. and then click To accept.
Once you have made the formula you will have to go to a third cell to extract the result. So you will have to make the closest date minus the previous ones for 24 hours.
Therefore, the example would look like this:
- In cell A1 you will have 03/19/2020 6:30.
- In the A2 the value will be 03/10/2020 14:00, so the result in the A3 (A2 – A1) * 24 will be 31.5 hours.
If you have any questions, leave them in the comments, we will answer you as soon as possible, and it will surely be of great help to more members of the community. Thank you! 😉