Microsoft Excel is powerful spreadsheet software. It comes with various features like calculation formulae or tools, graphing tools, pivot tables, etc. These features make it easier for a user to organize and manage data. Sometimes, we need to subtract a single value from different values or a range of values from a single value in Excel. If you know the shortcut method to do this, you can save your time, otherwise, the calculation will be time-consuming. In this article, we will show you how to subtract multiple cells in Excel.
How to subtract multiple cells in Excel
We will see here how to subtract multiple cells from one cell in Microsoft Excel:
- By using a formula
- By using the SUM function
- Without using a formula
Let’s see all these methods in detail.
1] How to subtract multiple cells in Excel by using a formula
Here we will talk about how to subtract data arranged in multiple cells in a particular row and column from a single cell in Excel.
In the above screenshot, you can see sample data arranged in different rows and columns. In this example, we will subtract multiple cells in a particular row from a single cell. Here, we will subtract the values in cells B1, C1, D1, and E1 from cell A1.
To do so, we will use the minus sign in our formula. To subtract the cells B1, C1, D1, and E1 from cell A1, you have to write the following formula in the cell in which you want to display the result and press Enter. Here, we are going to display the result in cell F1.
=A1-B1-C1-D1-E1
You can copy the same formula to other cells by dragging the first cell. For example, if I want to copy the formula typed in the F1 cell to other cells in the F column, I will simply drag the F1 cell to the other cells in the F column.
You can use the same formula to subtract multiple numbers arranged in a column. Let’s say you want to subtract your expenditures from your income. Here, we have taken a similar example in which cell B1 shows total income, cells B2 to B6 show the amount spent on different items, and cell B7 shows the remaining amount.
To subtract cells B2 to B6 from cell B1, enter the following formula in the cell B7 and hit Enter:
=B1-B2-B3-B4-B5-B6
Read: How to create a Half Pie Chart in Excel.
2] How to subtract multiple cells in Excel by using the SUM function
You can also use the SUM function to subtract multiple cells from a single cell in Excel. This formula works for data arranged horizontally as well as vertically in Excel.
Let’s take the same example of income and expenditure in which cell B1 shows income and cells B2 to B6 show expenditures, then the formula to calculate the remaining amount is as follows:
=B1-SUM(B2:B6)
3] How to subtract multiple cells in Excel without using a formula
You can subtract multiple cells in Excel by using the Paste Special feature. But this trick works only if you have to subtract only one value from multiple cells. Let’s see how to do that.
In the example that we have taken here, we will subtract the value in cell B2 from the values in cells A2 to A8. Here, the result after subtraction will be displayed in the same cells (A2 to A8).
The following instructions will guide you on how to subtract a single cell from multiple cells in Excel by using the Paste Special feature.
- Copy the cell the value of which you want to subtract from the other cells.
- Select the cells from which you want to subtract the value.
- Right-click on the selected cells and then click on the Paste Special option. This will open the Paste Special window.
- In the Paste Special window, select Subtract and then click OK.
After that Excel will delete cell B2 from cells A2 to A8 and display the result in the same cells (A2 to A8).
Read: How to stop Excel from rounding numbers.
How do you subtract a range of cells in Excel?
You can subtract the range of cells in Excel by using the minus (-) sign or by using the SUM function. Let’s say, you want to subtract the cells B2 to B10 from the cell B1, then the formula should be this:
=B1-B2-B3-B4-B5-B6-B7-B8-B9-B10
If you do not want to type such a long formula, you can use the SUM function for the same. If you use the SUM function, you have to type the following formula:
=B1-SUM(B2:B10)
How do you subtract 3 cells in Excel?
If you want to subtract 3 cells in Excel, you can either use the minus (-) sign in your formula or use the SUM function. Both the formulae are as follows:
A1-A2-A3 A1-SUM(A2:A3)
The above formula is used to subtract cells A2 and A3 from cell A1. If you use the minus sign in the formula, you have to type each cell separately in the formula. This makes the formula longer for the longer range of cells. On the other hand, if you use the SUM function, you just have to enter the first and the last cell separated by a colon. This makes the formula shorter no matter how long is the range of cells you want to subtract.
That’s it.
Read next: How to Create an Organization Chart in Microsoft Excel.
