Here’s one of the basic uses of an excel spreadsheet.
Subtraction
We need some rules to follow to get progressively more advanced learning and understanding lesser-used methods to subtract.
Note: This will help you Subtract in all versions of Excel (2007, 2010, 2013, and 2016)
Basic Subtraction in Excel
The simplest way to subtract in a spreadsheet 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
=20-10
(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.
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 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 organized for yourself, so let’s go with cell C1 to put our subtraction formula.
Now, this time we are going to put the names of the cells, NOT the actual values, so we write:
=A1-B1
Excel is very clever and knows you are talking about cells rather than a number, and what you will notice is in versions of Excel 2007 onwards, Excel likes to color 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, 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.
Subtracting A Range Of Cells
To extend this idea even further, we can subtract a range of 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 spreadsheet, you may have the worksheet set up as follows.
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 May)
Column C – how much of the products you have left. (Inv. 31st May)
Column D – total number of how much of each product sold (Prod. Sold)
The first three 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;
=B2-C2
Press enter, and we’ll 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 select with the proper formula.
Subtracting A Range Of Cells Within The Cells
Occasionally, you may have a list of values that you just want to subtract 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 by entering the number 5 into 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 five as we had previously set the formula in there.
You can follow the embedded video below, which shows you how to subtract Excel from 2013 onwards.
