How to Subtract Sum of Several Cells from Fixed Number in Excel

Get FREE Advanced Excel Exercises with Solutions!

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 that will be very useful in any Excel-related task.

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

how to subtract sum of several cells from fixed number


1. Using Formula to Subtract

The easiest way to subtract the sum of several cells from a fixed number is to use a formula in Excel. Here we will use the SUM function and then subtract operation. Let us see how to do this.

Steps:

  • First, go to cell C10 and insert the following formula:
=SUM(C5:C9)

formula to subtract sum of several cells from fixed number

  • 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:
=B5-C10

formula to subtract sum of several cells from fixed number

  • Finally, press Enter again and you should now see the subtracted value.

Read More: Excel formula to find difference between two numbers


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.

Steps:

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

copy number to subtract sum of several cells from fixed number

  • Next, copy cell C10 as previously and then right-click on cell D5.
  • Here, click on Paste Special.

opening paste special to subtract sum of several cells from fixed number

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

Read More: How to Create a Subtraction Formula in Excel


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.

Steps:

  • First, go to the Developer tab and select Visual Basic.

opening vba window to subtract sum of several cells from fixed number

  • Now, select Insert in the VBA window and click on Module.

insert module to subtract sum of several cells from fixed number

  • 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

vba code to subtract sum of several cells from fixed number

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

Read More: How to Subtract Multiple Cells in Excel


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.

Download Practice Workbook

You can download the practice workbook from here.


Conclusion

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. If you have any queries, please let me know in the comments.


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.
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo