Are you wanting to learn how to subtract in Excel?
First of all, if you are looking for the subtraction function in Excel, you will not find it. This is because there is no subtraction function in Excel. Instead, there is a subtraction formula that can be created using equals and minus in Excel.
In this blog I will teach you multiple ways to use a subtract formula that will help you become confident with subtraction in Excel.
It is simple when you're shown how but read to the end to make sure you understand what happens when you multiply and subtract or divide and subtract in the same formula.
This is a tricky one, so please don’t skip over it!
How to subtract in Excel
Let’s look at writing a subtraction formula in Excel.
In the example below we are going to take away or subtract the money that we've spent from our budget, to calculate the difference. The result will be held in cell C4.
Step 1: select the cell that will hold the Excel subtraction formula and then press Equals. In the example above this would be cell C4.
Tip: When you're writing a formula within Excel, whether you're doing multiplication, addition, subtraction, division, you always need to click on the cell where you will be typing the formula first.
Step 2: once you’ve done this you need to tell Excel you're about to do a calculation.
We do this by pressing our Equals key (=) on the keyboard.
You’ll find your Equals key top right of your keyboard, near your Backspace key.
When you press Equals, you're now saying to Excel, "Hey, I'm about to go and do a calculation" and Excel sits up and takes notice.
Step 3: type the Excel subtraction formula using real numbers, cell references or a mixture of both.
The following instruction covers how to subtract in Excel using real numbers, how to create Excel subtraction formulas using just cell references, and a mixture of cell references and real numbers.
How to subtract in Excel using real numbers
I watch a lot of people write subtraction formulas using real numbers.
Although Excel will always give you the correct answer when you subtract this way, this isn’t always the best way to subtract in Excel.
Let’s look at this method first and then I’ll explain why this isn’t always the best method.
In the example below 1000 has been typed after the Equals sign.
The subtraction or minus sign (-) has then been typed. You can find the subtraction symbol on your numeric keyboard, or just to the left of your Equals key on the keyboard.
Then 564 has been typed. The formula is =1000-564.
We can now calculate the subtraction formula by pressing the Enter key on the keyboard.
Excel will perform the calculation and give us the subtraction sum. You will be able to see the result down on the grid part of your worksheet and the formula up in the Formula Bar.
However, what if we now realised our budget was $1200, it wasn't $1000, and we now need to update the value in cell A4 to $1200.
Wouldn't it be great if when we updated the value in A4, the Remaining value in C4 immediately updated to reflect the difference?
So, let’s look at creating an Excel subtraction formula using cell references instead of real numbers.
Excel subtraction formulas using cell references
In the example below I’m now referencing the cells holding the information.
The formula is =A4-B4, meaning you are now asking Excel to subtract the value held in cell B4 from the value in A4.
To do this, click into the cell you want your subtraction sum. In this example, it is cell C4 and press Equals.
Select the cell with the number you are subtracting from (cell A4).
Press your Minus key then select the cell with the number in which you are subtracting (cell B4).
The beauty about this, is when you reference cells, instead of the real numbers, should you update a value in a cell used in a formula, the subtraction sum will automatically update.
In the example below, the budget was changed from $1,200 to $1,000 and it updated the sum in cell C4 to $436.
Tip: If you wanted to, you could actually type =A4-B4 into the cell. You don't need uppercase A, uppercase B. A quicker way to place cell references into a formula is to press = and then just click on to the cell you require, and the cell reference will be added to your formula (you can see me doing this in the video at the top of this blog )
Adding and subtracting in one Excel formula
When you are adding and subtracting in one Excel formula be sure to write the formula from left to right, in the order that you would like the calculations performed. For example
=1200-564+120-56
will be calculated from left to right, leaving a result of 700.
This is fine if you are happy with the formula being calculated from left to right. However, there are times when you may wish to calculate the formula in a different way.
For example, if you wanted to add the 564 + 120 first and then subtract this from 1200 and then further reduce the result by 56, writing the formula from left to right isn’t going to work.
=1200-564+120-56
For this type of subtraction formula in Excel you need to follow the steps below for Using brackets in an Excel subtract formula.
Using brackets in an Excel subtract formula
It's really important that you understand what can happen when you're creating subtraction formulas with more than just the subtraction or minus sign.
Let’s say you needed to create one formula that included a subtraction and a multiplication, or you wanted to add and subtract in one formula.
There is a key element to these formulas that you need to know so that you avoid incorrect calculations.
This element is the use of brackets (parentheses) in Excel formulas.
In the example below I'm using an asterisk symbol to include a multiplication into the formula. I’m wanting to take the remaining budget and multiply it by 1.15 to add 15% GST to my result. We are clicked into cell C8 and you can see the formula in the Formula bar.
The problem is I've ended up with a figure that is actually less than what we had originally in C4.
So, let me explain what's happened.
When you have more than just the subtraction symbol in a formula, you need to look at that formula and think, "What part of this formula needs to be calculated first?"
There is a rule behind this called the BEDMAS rule, also known as the BODMAS or PEDMAS rule.
A simple overview of this rule applied in Excel is this. If you are looking at a formula, and a certain part of that formula needs to be calculated first, you put brackets around it.
Without the brackets, Excel is whipping onto B8 and multiplying that by 1.15 first, then it's deducting the A8.
Sound complicated?
Don’t be concerned.
All you need to remember is that when you have more than one operator, for example, a multiplication sign, a plus sign, or a division sign, included with your subtraction sign in a formula, make sure that you bracket the part of the formula that needs to be calculated first.
That's it. That's all you have to remember.
Take a look at the formula and the result now that we have included brackets.
The brackets around the A8 minus the B8, means that Excel will calculate this part of the formula first.
Excel will then take the result if this calculation, and then carry on and multiply by 1.15.
In the Adding and subtracting in one Excel formula example above we would use brackets to add 564+120 prior to subtracting it from 1200.
Tip : use the same steps as above to create division, multiplication, and addition formulas in Excel.
The Table below shows the standard operator symbols that are used in Excel for these calculations.
You can find these symbols on your keyboard and numeric pad (if your keyboard has one ).
For example, =B4/A4 will divide cell B4 into A4, =B4+C4 would add the two cells together and =C4*1.15 (which you have seen already) would multiply C4 by 1.15.
TO DO THIS... | USE THIS SYMBOL... |
---|---|
Divide | / (forward slash) |
Multiply | * (asterisk a.k.a. 'star') |
Addition | + (plus sign) |
How to Subtract in Excel
I really hope you're feeling confident about subtracting in Excel now.
Just remember to use cell references instead of real numbers if you're going to update the cells regularly, and don't forget your brackets.
Was this blog helpful? Let us know in the Comments below.
If you enjoyed this post check out the related posts below.