In this tutorial, I am going to show you 3 suitable ways to subtract the sum of several cells from a fixed number in excel. You can quickly use these methods even in large datasets to perform subtraction of multiple cells. Throughout this tutorial, you will also learn some important excel tools and functions which will be very useful in any excel related task.
Download Practice Workbook
You can download the practice workbook from here.
3 Suitable Ways to Subtract Sum of Several Cells from Fixed Number in Excel
We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 3 columns. The Yearly Sales Target, Monthly Sales, and Total Sales denote the number of units of sales in each case. But you can use the methods of this tutorial for other types of data like currency, percentage, time, etc although you might have to do some minor modifications.
1. Using Formula to Subtract
- First, go to cell C10 and insert the following formula:
- Now, press Enter and you should get the total sales value inside cell C10.
- Then, double-click on cell D5 and enter the below formula:
- Finally, press Enter again and you should now see the subtracted value.
2. Utilizing Paste Special Feature
The Paste Special feature in excel allows us to paste selectively. So you can actually customize the way you want to paste the data you copied. Also, you can perform simple math operations with this feature. Let us see how to use this feature in our case to subtract the sum of several cells from a fixed number.
- To begin with, select cell B5 and press Ctrl+C to copy this cell.
- Now, go to cell D5 and paste here using Ctrl+V.
- Next, copy cell C10 as previously and then right-click on cell D5.
- Here, click on Paste Special.
- Now, select Values and Subtract from the options as in the image below and click OK.
- Consequently, this should perform the subtraction inside cell D5.
3. Applying VBA Code
If you are comfortable working with VBA, then this is probably the best option to subtract the sum of several cells from a fixed number in excel. Also, you can reuse the code for other tasks as well. Follow the steps below to apply the VBA code.
- First, go to the Developer tab and select Visual Basic.
- Now, select Insert in the VBA window and click on Module.
- Next, type in the formula below in the new window:
Sub SubtractfromFixedCell() Range("C10").Value = Application.WorksheetFunction.Sum(Range("C5:C9")) ActiveCell.Value = Range("B5") - Range("C10") End Sub
- Then, click on cell D5 and open the macro from the Developer tab by clicking on Macros.
- Now, in the Macro window, select the SubtractfromFixedCell Macro and click Run.
- Immediately, the VBA code will do the subtraction operation and give the final result in cell D5.
Things to Remember
- The result in the first method is dynamic which means the value will change if we alter the input.
- In the first method, you can also use the AutoSum feature instead of typing the SUM function manually.
- You can use the shortcut Ctrl+Alt+V to open Paste Special.
- Similarly, you can open the VBA window using the shortcut Alt+F11.
I hope that you were able to apply the methods that I showed in this tutorial on how to subtract the sum of several cells from a fixed number in excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.