Excel is a spreadsheet program that has a plethora of functions. Still, the software doesn't have a subtraction feature, which seems like a no-brainer to include. As such, Excel users manually enter formulas into the function bar to subtract numbers. Here are some of the different ways you can infer values in Excel spreadsheets.
Subtract by including values in the formula
You do not need to enter values into worksheet cells to subtract numbers. Instead, you can include the values to be subtracted in the formula itself. First, select a cell to add the formula to. Then click in the Excel function bar and type '=' followed by the values you need to infer. For example, type '=25-5' in the function bar and press Enter. The formula cell will return the value 20.
Subtract worksheet cell references
However, most Excel users will need to subtract the numbers entered into the columns and rows of the spreadsheet. To subtract cell values, you'll need to include their row and column references in the formula instead. For example, enter the values "345" and "145" in cells B3 and B4 in a blank Excel worksheet like in the snapshot below.
Select cell B5 and click in the fx bar to enter a formula. Enter the formula '=B3-B4' and press the Return key. B5 will now return the value 200 as shown below.
Subtract a number from each value in a range of cells
If you need to subtract only one value from each number in a range of cells, you can copy the formula to other cells. For example, enter the values '25', '35' and '55' in cells C3:C5. Select cell D3, enter the formula '=C3-5' in the function bar and press the Return key. D3 will now subtract 5 from 25 in cell C3.
Copy the formula in D3 to the other cells below by selecting D3, clicking the lower right corner of the cell and dragging it to the ones below as shown below. Now the cells directly below D3 also subtract 5 from the numbers in C4 and C5.
Subtract a cell range total from a single value
What if you need to subtract a column total for a group of cells from a single value? One way to do this would be to enter a SUM function into a cell that sums the range and then subtracts that total with a separate formula. However, you can also subtract a cell range total by including SUM in a formula.
For example, enter the values '45', '55' and '75' in cells B7 to B9. Then enter "200" in cell B11. Select cell B12, click in the fx bar and enter '=B11-SUM(B7:B9)' in the formula bar. Cell B12 will then return the value of 25 as shown directly below. This effectively subtracts the total of cells B7:B9 from 200 in cell B11.
Subtract at least two total cell range values
You can also subtract totals from cell ranges without first entering SUM functions in the worksheet. Instead, add cell range references to a formula and subtract them. For example, enter the numbers '25', '15' and '35' in cells C7:C9. You can now subtract the total of cell range C7:C9 from the range B7:B9 entered earlier.
Select C11 as the cell to add the formula to, then enter '=SUM(B7:B9)-SUM(C7:C9)' in the function bar. Press Enter to add the function to the worksheet, which will return the value 100 in C11. This therefore subtracts the range of cells C7:C9 from the total of the range of cells B7:B9, which is otherwise 175 – 75.
Subtract percentage values from numbers
To subtract a percentage value, such as 50%, from a number, you must enter the value in a cell in percentage format. Then you can add a formula that subtracts the percentage of a number in another cell. As an example, enter the value "150" in cell E3. You need to convert cell F3 to percentage format by right-clicking the cell and selecting Format Cells > Number > Percentage > OK. Enter "50" into F3, which will then be in percentage format, as shown directly below.
You can now add a formula that subtracts 50% from 150. Click on cell G3 and enter the formula '=E3-(F3*E3)' in the function bar. Cell G3 will return a value of 75, which is 50% of 150. So the formula subtracted 75 from 150.
Subtraction of values across multiple worksheets
Excel doesn't limit you to subtracting values in single worksheets. As such, you can subtract cell numbers in one worksheet from those in another. For example, enter "55" in cell B14 on Sheet1 of a spreadsheet. Then click on the Sheet2 tab at the bottom of the application window to open a blank spreadsheet. Enter '5' in cell B3 of Sheet2. So this formula will subtract B3 in Sheet2 from B14 in Sheet1.
Now click on B4 in Sheet2 to add the formula to this cell. Enter the formula '=Sheet1!B14-Sheet2!B3' in Excel's function bar and press Return. Cell B4 will now deduct 5 in the B3 of Sheet2 from the value of B14 in Sheet1. The cell returns a value of, as you probably guessed, 50.
So these were some of the subtraction formulas that subtract values in Excel worksheets. You can copy the above formulas to your worksheets using the shortcut keys Ctrl + C and Ctrl + V and modify their cell references as needed. Check out this Tech Junkie's Guide if you need to subtract dates in Excel.
