Subtraction for Whole Column in Excel (with 5 Examples)

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.

Here, we cover the five most effective methods to subtract the whole column in Excel. It includes the subtraction of two columns in Excel and the subtraction of a particular number from a whole column with or without a subtraction 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. Subtraction of Cells Between Two Columns in Excel

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.

• 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.

Read More: How to Subtract Two Columns in Excel

2. Subtraction of a Specific Value in Excel 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.

• 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.

Read More: How to Subtract the Sum of Several Cells from a Fixed Number in Excel

3. Paste Special Tool to Subtract a Certain Value from Whole Column

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. Use of Absolute Cell Reference for Subtraction in Excel from Whole Column

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.

• 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 Create a Subtraction Formula in Excel

5. Subtraction of Multiple Excel 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 in Excel 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.

• 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.

Read More: Excel formula to find the difference between two numbers

Download Practice Workbook

Download this practice workbook.

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.

Related Articles

• How to Subtract from Different Sheets in Excel
• Excel VBA: Subtract One Range from Another
• How to Subtract in Excel Based on Criteria
• How to Subtract from a Total in Excel

<< Go Back to Subtract in Excel | Calculate in Excel | Learn Excel