Find Numbers That Net to Zero in Excel Column

Knock Off Positive And Negative Numbers In Excel

We many times have a scenario where we need to find positive and negative numbers that net off or offset to zero in a range of cells in Excel. Especially when we are trying to delete the offsetting amounts like in an accounting report where debits and credits that offset each other are to be eliminated or where we match debits and credits to arrive at a net sum of zero. 

We will now just look at how this can be done in Excel. The version I am using is Microsoft Office 2016. If you want to see the excel file for this purpose, you can download it from the link given at the end of this article. 

Assuming we have listed the numbers in one column ‘B’ as in the below screenshot.

In column ‘A’ enter zero and fill it down till the row where you have amounts in Column 'B'.

Sort the data by Column ‘B’ – Smallest to Largest. It will look like as shown below screenshot

In Cell F1, insert formula


If you see, we are just considering the negatives.

Similarly, in Cell F2, insert formula

=SUMPRODUCT(A9: A17, B9: B17)

This time we are including the positive values.

Next in Cell H1, insert a formula to sum values in Column ‘A’


In cell H2, sum the positive and negatives sum-products. We don’t need the sign so we will take the absolute value using ABS function as shown below


Next open the solver menu, if the solver menu is not available in Excel, then you may need to install the add-in, follow the below steps

1. Click on File Menu
2. Click Options
3. Click Add-ins
4. Click Go…
4. Install Solver (check it)
5. Click OK

You can find the detailed steps in the below link

Or watch the video to see how to install the solver

Now in Excel, click "Data" in the Ribbon and then click on Solver.

In the Solver dialog, select the options as shown in the below screenshot.

In Set Objective enter cell address $H$1 To Select option ‘Max’. If you want more accurate Net Off and wiling to allow more time for processing, you can also select option - Value Of: and Type 0 in box next to it.

By Changing Variable Cells, enter cell address $A$1:$A$17 the range in Column ‘A’ which we had filled down earlier. You should change this according to the number of rows to be solved.

Next click on Add

In the Cell Reference field enter ‘$A$1:$A$17’ You should change this according to the number of rows to be solved. In the dropdown select bin and in constraint enter '0' and click Add.

Again, click Add in Solver Parameters dialog.

In the cell reference field Enter Cell Address $H$2. 

In the drop-down select '<=' and in the 'constraint' field enter '5' and click Add

The window should now look like the one shown below

Click on Solve.

After a minute you may get to see this message

Click on Continue

Allow the solver to work on the range. This may take a few minutes depending on the size of data to be processed.

Once finished, you will get the message below

Click on OK.

The rows with net offs will be highlighted changing the values in Column ‘A’ to 1s.

I hope this tip was helpful, please share it with your friends and let me know your feedback in the comment box below.

Follow us on:

Share with your friends

Add your opinion
You can subscribe to our blog or follow us on social media to get notified about the new posts.
Text Content
Cookies used on the website!
We and our partners use cookies to personalize content and ads, to provide social media features and to analyze our traffic.
You consent to use of these cookies if you continue to use this website.
Learn more »