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

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.

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: Adding and Subtracting in Excel in One Formula (4 Easy Ways)


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 Add or Subtract Based on Cell Value in Excel (3 Ways)


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 Add and Subtract in One Cell in Excel (6 Ways)


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.

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. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo