Subtraction for Whole Column in Excel (with 5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Subtraction for Whole Column in Excel

  • Write down the following formula in the formula bar.
=B5-C5

 

  • Press Enter to apply the formula.

Subtraction for Whole Column in Excel

  • Drag or double-click on the Fill Handle icon down the column. Then we get all the required savings.

Subtraction for Whole Column in Excel

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.
=D5-40

  • Press Enter to apply the formula.

Subtraction for Whole Column in Excel

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

Subtraction for Whole Column in Excel

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.

Subtraction for Whole Column in Excel

  • A Paste Special dialog box will appear. Select All in the Paste option and select Subtract from Operation and finally, click on ‘OK’.

Subtraction for Whole Column in Excel

  • This will subtract a particular value from column D.

Subtraction for Whole Column in Excel


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.
=D5-$G$5

  • Press Enter to apply the formula.

Subtraction for Whole Column in Excel

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

Subtraction for Whole Column in Excel

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.
=$B$5-C5

  • Press Enter to apply the formula.

Subtraction for Whole Column in Excel

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

Subtraction for Whole Column in Excel

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo