In this tutorial, I am going to share with you 4 possible solutions to the problem of excel not adding negative numbers correctly. These solutions are quick and also easy to apply. Although adding numbers in Excel is one of the easiest operations, having negative numbers can pose challenges if you do not know how to overcome them. In the following sections, I am going to guide you through some simple steps to overcome this problem.
Download Practice Workbook
You can download the practice workbook from here.
Negative Numbers in Excel
Excel can work with both positive and negative numbers easily and effectively. But in some cases, negative numbers can cause some problems. Choosing the wrong format for negative numbers is a common cause of errors. There are certain best practices that will allow excel to recognize the negative numbers.
4 Possible Solutions If Excel Is Not Adding Negative Numbers Correctly
The dataset that we will be using, shows the cash flow records. The positive cash flows indicate money that is coming as revenue. The negative numbers denote money that is going out as expenses.
1. Converting Negative Numbers with Paste Special Option
Many times excel fails in adding negative numbers correctly because of the wrong format that is set for them. In this method, we will separate the values of the numbers to solve the problem.
- First, select the cells from C5 to C9.
- Then, press Ctrl+C to copy the cell data.
- Now, right-click on any of the selected cells and select Paste Special.
- Next, in the Paste Special window, select Values from the Paste options and click OK.
- As a result, all the cells will now have only numeric values.
- Here, double-click on cell C10 and type the following formula:
- Finally, you should get the sum of all the positive and negative numbers.
2. Adding Negative Numbers Ignoring Signs
In many situations, we may want to ignore the number signs when excel is not adding negative numbers correctly. We can do this by combining the SUM function and the IF function. See the following steps.
- To begin with, double-click on cell C10 and insert the following formula:
- Consequently, Excel will ignore any negative sign and add all cell data.
3. Using SIGN Function for Adding Negative Numbers Separately
We can use the SIGN function to denote the negative numbers when excel is not adding them correctly. In the dataset for this method, we will sum the positive and negative values in separate cells. Follow the steps below.
- To start this method, click on cell C5 and type in the following formula:
- Now, you will see the value 1 in cell C5 which denotes that there is a positive number in cell B5.
- Next, drag the Fill Handle to copy the formula of cell C5 to all the cells below.
- After that, double-click on cell F6 and enter the below formula:
- Next, press Enter and this will sum all the positive values having the sign as 1.
- Then, double-click on cell F7 and insert the following formula:
- Here, press Enter and excel will sum all the negative values with the sign as -1.
4. Adding Negative Numbers Using Excel AutoSum Feature
The SUM function might not always give accurate results when dealing with negative numbers. In this case, we may try using the AutoSum feature to fix the problem of excel not adding negative numbers correctly. Let us see how we can do that.
- First, click on cell C10.
- Now, go to the Formula tab and then to AutoSum.
- Here, click on Sum.
- Finally, you should get the total value considering the negative numbers.
Things to Remember
- In some situations, you might want to conditionally format the cells with negative numbers which will make them more visible.
- The arithmetic operations that excel performs on negative numbers are the same as with positive numbers.
- There are various ways of representing negative numbers in excel including using parentheses.
I hope that you were able to solve the problem where excel is not adding negative numbers correctly using the above fixes. You can try these fixes one by one and see which one best suits your situation. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.