Durjoy Paul
Subtraction is one of the most common techniques in Excel. We use this in our day-to-day calculations numerous times. In Microsoft Excel, subtraction can be applied in far more efficient ways. This article provides you with a fruitful overview of subtraction. This article will cover subtraction for the whole column in Excel. I hope you will enjoy this and gather a lot of new knowledge about Excel.
Download Practice Workbook
Download this practice workbook.
5 Examples with Subtraction Formula for Whole Column in Excel
Here, we cover the five most effective methods to subtract the whole column in Excel. It includes subtraction of two columns and subtraction of a particular number from a whole column with or without a formula. All the methods are practically proven and fairly easy to digest. To show all the methods effectively, we take a dataset that basically includes income and expenses.
1. Subtract Cells Between Two Columns
When you have two columns and need to calculate values in the third column, we can use this method. Here, we have income and expenses and need to find out savings. So, we may use this method easily and effectively.
Steps
- Select cell D5.
- Write down the following formula in the formula bar.
=B5-C5 
Â
- Press Enter to apply the formula.
- Drag or double-click on the Fill Handle icon down the column. Then we get all the required savings.
2. Subtract a Specific Value from Whole Column
When you have a condition where you need to spend a specific amount from savings unexpectedly, you may apply this method undoubtedly. A particular value will be deleted from the whole column with ease.
Steps
- First, consider an individual value. Here we take the value as 40 which needs to subtract from the whole column.
- Select cell E5.
- Write the following formula in the formula bar.
=D5-40
- Press Enter to apply the formula.
- Drag or double-click the Fill Handle icon down the column. On one occasion, there is a negative saving which means expenses are greater than income.
3. Subtract a Particular Value from Whole Column Using Paste Special Command
You can use Paste Special command to subtract a specific number from the whole column. It brings a static solution whereas formulas give you a dynamic solution that changes with the alteration of the dataset.
Steps
- Set a specific value as a fee. Here, we set $40 as a fee and copy it.
- Now, copy column D to column E otherwise Paste Special will rewrite the previous column. Select column E.
- Go to the Home tab and click Paste from the Clipboard group.
- Select Paste Special from the Paste option.
- A Paste Special dialog box will appear. Select All in the Paste option and select Subtract from Operation and finally, click on ‘OK’.
- This will subtract a particular value from column D.
4. Subtract Using Absolute Cell Reference
Another way to subtract a specific cell from the whole column is by defining the absolute cell reference in the formula bar. In order to apply this to all cells, we need to fix it by using the ‘$’ sign.
Steps
- Select cell E5.
- Write the following formula in the formula bar.
=D5-$G$5
- Press Enter to apply the formula.
- Drag or double-click on the Fill Handle icon down the column. That will provide the result of subtraction. This also provides a dynamic solution where you can change the fee, it will automatically change the result.
Read More: How to Subtract from a Total in Excel (3 Quick Methods)
5. Subtract Multiple Cells from One Particular Cell
When you have a case where your income is fixed for all months. Then you don’t need to show it every month. You can fix your income in an individual cell and then subtract multiple cells from that fixed cell.
Steps
- First, write the value in cell B5.
- Select cell D5 where you want to put your result.
- Now, write the following formula in the formula bar. Here, the ‘$’ sign means this cell must be fixed in all the remaining cases.
=$B$5-C5
- Press Enter to apply the formula.
- Drag or double-click on the Fill Handle icon down the column. Here, cell B5 is fixed in all the calculations that is what we want.
Conclusion
We have discussed five different methods to use subtraction for the whole column in Excel. As a regular excel user, it is useful to have sound knowledge of subtraction. These methods will help you to do some complex work. I wish you enjoy this article thoroughly and gain some fruitful knowledge. If you have any questions, feel free to ask in the comment box, and don’t forget to visit the Exceldemy page.
