Here’s one of the basic uses of an excel spread sheet.
But what we will do is to get progressively more advanced and learn some interesting and lesser used methods to subtract.
Note: This will help you Subtract in all versions of Excel (2007, 2010, 2013, 2016)
Basic Subtraction in Excel
How To Use Subtraction In Excel 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).
GIF1If you click back on the cell with your equation and look above in the formula bar you can see what you originally entered.
How to subtract cells
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 gives the result
What’s so great about this? Well, the main benefit is that we have set up a formula in cell C1, so if any of the values change, then we don’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.
How To Use Subtraction In Excel Subtracting a Range of Cells
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)
The first 3 columns are self-explanatory and I have entered some made up data for the example.
In column D we will enter a formula for automatic calculation of columns B and C as follows;
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.
Subtracting a range of cells within the cells
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, 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 do this 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.
You can follow the embedded video below which shows you how to subtract in Excel 2013 onwards.