Being able to calculate the difference between two numbers in Excel is one of the most needed requirements in a job. You have to be ready to do it at any moment. Especially when Excel is one of the top three tools you use in your work, you must know how to calculate the difference between two numbers in Excel.
There are many ways you can do it. But we understand that simple methods are always the best in any kind of work. This is why in this visual step-by-step guide, we are going to discuss the 4 fast and easy methods you can follow and use in Excel to calculate the difference between two numbers.
We are also sharing with you the worksheets that we have used for this article. In the provided file, you will find a practice sheet for yourself as well. It has the same data as a template so you can use it to follow the methods step-by-step and practice these methods more effectively.
Let’s dive in!
How to Calculate the Difference Between Two Numbers in Excel: 4 Simple Methods
The four methods we are going to share with you today are the easiest you can find. Depending on your situation, you will have to pick the method you are going to work with. However, learning all four methods will prove useful and help make your Excel game flawless.
Now, we must reiterate that you should practice the techniques at least a few times. It will help you become familiar with them and later on, you will be able to use whichever method you feel is suitable for your task.
Let’s start with the first method then!
Method 1: Manual Subtraction to Calculate the Difference Between Two Numbers
This method is the most straightforward one. It is a simple subtraction method that finds the difference between two values that you are subtracting one another from.
To do this in Excel, follow the steps discussed below.
Click on the cell you want the difference to calculate into. Write down the subtraction formula in =NUMBER1-NUMBER2 format. Hit Enter to calculate the difference and show it in the cell.
As you can see, through your manual input of values with the numeric minus operator in the middle, you are getting the difference between those two numbers.
However, there is one thing you will notice if you do the calculation in the second row using the same technique.
Do you see how the difference is shown in a negative value? Understandably, it is because the number you are subtracting (also known as Subtrahend) is lesser than the number you are subtracting it from (also known as Minuend).
This shows that Method 1 works in both cases where positive and negative differences are shown.
Still, for this method, there are a few downsides.
- It is extremely time-consuming if you have to deal with a lot of numbers. You will have to find the differences one by one for all the numbers in that case.
- As this method uses no function in Excel except for standard numerical calculation, there is no way to fill the other cells with a formula and find all the differences in a few seconds.
Now, there are other methods that address these downsides and give you some better options. Keep on reading to find them out.
Method 2: Finding the Difference Between Two Numbers Using Cell Reference
In the previous method, manually inputting numbers from two cells was required to find the difference. But in this one, we are going with our old friend Cell Reference technique to make the job a bit easier. Let’s find out!
1. Click on the cell you want to see the output in.
2. Now, start writing the formula to calculate with Cell Reference. As shown in the picture below, first write = and then click on the cell with the first number for your difference. Then hit the – (minus) button and then click on the second cell. Hit Enter to see the output.
3. As you can see in the right picture above, the difference in this case 55 is being shown. Now comes the best part of this method. Unlike Method 1, you do not have to use the numerical subtraction system manually for all the cells to find the differences. Rather, in this Method 2, you can fill the cells below with the formula to calculate the other differences within seconds!
To do this, click on the cell you have written your formula for Cell Reference in. You can see a little box in the right-bottom corner of the cell selection box. It is called Fill Handle. You can either click on the Fill Handle, hold it, and drag it down to fill the cells with the formula. Or, you can simply double-click on the Fill Handle and see the cells become filled instantly.
Immediately, you will see that the cells below have been filled with the formula and are now showing the differences between the two corresponding cells.
This method is undoubtedly more efficient than the previous one when you are dealing with a huge amount of data.
Method 3: Calculating the Difference with SUM Function
In this method, we show you how to calculate the difference between two numbers using the famous SUM function in Excel. This is similar to calculating using Cell Reference because this method uses cell referencing as well. However, the addition of the SUM function makes it a safer option.
Let’s see how to use this method!
1. Click on the cell where you want to see the calculated difference between two numbers show up.
2. Now start writing the SUM formula to calculate the difference. First, write =SUM( and then click on the cell that has your minuend (first number). In our case, we wanted to find the difference between profit and loss for January, so we clicked on the cell that has Profit value. Next, put the minus (-) sign and then click on the cell with your subtrahend (second number). In our case, it is the cell with a Loss value for January. Close the parentheses and hit Enter.
The difference will be shown in the output cell instantly.
3. To copy the SUM formula to the cells below, click and drag the Fill Handle or simply double-click on the Fill Handle.
And there’s that!
Method 4: Calculating the Difference with ABS Function in Excel
When you want to calculate the absolute difference which will be a positive value, using the ABS function to calculate the difference between two numbers is a highly recommended method.
In all the methods you saw till now, the numerical calculation follows the natural method. That is, if your first number is bigger than the second number and you want to find the difference between those two numbers, a positive difference value will be shown. Contrarily, a smaller first number and a bigger second number with subtraction in the middle will show you a negative value.
ABS function avoids this problem if you are looking for only a positive value. It disregards which cell has a bigger or smaller number. The ABS formula returns the absolute difference value for you.
And it is not that complex at all! In simple few steps, you can be done with the process and see the differences as you would like to.
1. Click on the cell you want to see the difference between the two numbers.
2. Now start writing your ABS formula. Begin by writing =ABS( and then click on the cell that has the first number. At this point, like always, put in the minus (-) and then complete the process by clicking on the cell with the second number.
3. Press the Enter button to initiate the calculation and see the difference.
4. Now, to copy the formula to the other cells, click on the cell with the formula and then click and drag or double-click the Fill Handle.
5. Now you will see the absolute difference values in corresponding cells.
Below is shown a comparison between this method with the ABS function versus those other methods where the ABS function was not used.
If you compare the negative difference values in the first picture against the ones where the ABS formula was used, you can clearly see that the ABS function has returned all the values in positives. It simply tells you differences which is a really important thing in many situations.
We have shown you four different methods for calculating the difference between two numbers in Excel. It must be said that all the methods have their fruitful usage in certain situations. Now, depending on how much data you are dealing with and what would be convenient for you, you can choose any of the four methods discussed in this guide.
To sum it up, Method 1 is time-consuming and error-prone. If you have only a few numbers in your hand to calculate the differences, this can be your method for the occasion. Anything more than that, the other three methods will do wonders for you. And if you are looking for only positive difference values, remember that only Method 4 helps you do it. Method 1, Method 2, and Method 3 will show you the differences in both positive and negative values.
Practice all the methods a few times to learn them properly. It will help you use the appropriate method when needed. Then go ahead and… Excel!