One of the most basic uses of an excel spread sheet is to subtract values in order to get a result.
In this tutorial we will cover how to subtract in Excel and get progressively more advanced and in addition learn some interesting and lesser used methods to subtract
Note: This will help you Subtract in all versions of Excel (2007, 2010, 2013, 2016)
The simplest way to subtract in a spread sheet is to click in a cell, then simply type the formula to subtract as follows; the equals sign (=) the equation (20 - 10)
So in the image below, I have clicked in cell A1 and typed
(Note: Cell A1 means the rectangle which corresponds to column A and row 1)
You can then either press tab to move the cursor to the cell on the right or enter to move to the cell below. Once you've moved out of the original cell, the sum of the equation will be what is showing. So for the example above you will see the number ten (10).
If you click back on the cell with your equation and look above in the formula bar you can see what you originally entered in to cell A1. This serves as a useful reminder of what numbers you subtracted.
Now that we have the basic idea in place, let’s go one step further, let’s put our values in two different cells and subtract the cells.
So let’s enter the number ‘20’ in cell A1 and enter 10 in the cell B1.
Now, let’s put a formula in another cell. Excel doesn’t mind which cell you put the formula in, but my recommendation is always to make things easy and organised for yourself, so let’s go with cell C1 to put our subtraction formula.
Now this time round we are going to put the names of the cells, NOT the actual values, so we write:
Excel is very clever and knows you are talking about cells rather than number and what you will notice is in versions of Excel 2007 onwards, Excel likes to colour the cells you are referring to in your formula to help you see what’s going on as you are working through it.
Just hit enter and Excel will give you the result.
What’s so great about this? Well, the main benefit is that we have set up a formula in cell C1, therefore if any of the values change, we won’t have to re-do the subtraction formula again. For example, if we change cell A1 to the number 50, then C1 updates automatically like this.
To extend this idea even further we can subtract a range cells which means subtracting one bunch of numbers from another bunch of numbers.
To subtract a range of cells you need to create a formula. So if you are creating an inventory spread sheet for an example you may have the worksheet set up as follows. (Sample headings are in parenthesis;)
-Column A - the list of products in your inventory. ( Prod. names)
-Column B - how many you had on hand at the start of the month. (Inv. 1st of May)
-Column C - how much of the products you have left. (Inv. 31st of May)
-Column D - total number of how much of each product sold (Prod. Sold)
While I have entered some made up data for the first three columns, I have left column D empty for the example.
In column D we will enter a formula for automatic calculation of columns B and C as follows into cell D2:
Press enter and we get our result
Now let’s apply this formula to the rest of the column (to product L), to do this we simply right click on cell D2 and a list will pop up. Select the "copy formula" option. Then move to cell D3. While holding down the left mouse button, travel down to cell D13 (which is the final product in our product list). The cells will become shaded which is an indication they have been selected. Then right click on this shaded area and hover over "paste special" and select ‘paste’ or ‘Formulas’ (called "formula only" in older versions of Excel).
This will automatically populate all the cells within the D column that you selected with the proper formula.
Occasionally you may have a list of values that you just want subtracted by the same number. Here is a way to quickly do that. Using the example above, let’s assume that 5 products were removed from every entry in ‘Inv. 1st May’ (Column B).
We can do this entering the number 5 in to any cell, so let’s go with E1.
Now, we simply copy cell E1 (right click on cell > copy)
Then we select cells B1 to B13 by left clicking.
Then right click on the selected (shaded) area and select the following:
Paste Special > Subtract > OK
Now, all the values in Column B will reduce by 5 and you will see the values in column D also reducing by 5 as we had previously set the formula in there.
If that doesn't quite make sense you can certainly follow the embedded video below which shows you how to subtract in Excel 2013 onwards.
Now that we have seen how to carry our subtraction as a result of using the ' - ', let's take a look at an actual Excel function. In this case let's look at the 'SUM' formula. (Note: sometimes functions referred to as a formulas)
Don't be mistaken in thinking the SUM will just 'add' values, we can also use the '-' subtraction symbol.
Here's an example with two numbers. In Cell A1, we type:
And press 'Enter' or 'Tab' and we get our result (10).
What's interesting is that as you start typing SUM, Excel show you a list of possible formulas. This is just good old Excel trying to help us out!
Here's another example where we are entering cell references as we did before. So, assuming we have values in B1 and A1 (I have put 20 and 10 as before) then in cell C1 we enter
And again pressing 'Enter' or 'Tab' and we get our result (10).
If you are ready to transform your Excel skills then take a look at our courses