Negative numbers in Excel can sometimes cause some confusion for users. Below some of the most common issues/ questions addressing Excel negative numbers.
Table of contents
Entering a negative number in Excel
At its very simplest you need to add a negative symbol (dash) in front of the number. If you are referring to a cell that has a number in it, you can make it negative by putting a dash in front of the reference. Note below that the negative sign (-) between the = and $B$4 converts 27 057.03 to -27 057 .
Formatting negative numbers in Excel
As per the Financial Modelling and Spreadsheet best practice we prefer to have negative numbers appear in red and/ or with brackets. To see how to get fancy with this look at the
- negative numbers in red (or another colour) post
- negative numbers in brackets post
At its most basic, you go to Format Cells and choose your preferred number option as shown below
Change negative to positive (or visa versa)
Formula
If you need to use a formula, you can create a new column or row and simply link to the cells with a – in front of the reference e.g. =-B4 as shown above. If the number in cell B4 is positive it will change to negative and if it is negative it will change to positive.
Convert to opposite sign in place (over the current cells)
You have probably used the Paste Special function in Excel, but did you know that you can use it to overwrite numbers with themselves but with the opposite sign?
Below we want to change the red block to the opposite sign (negative to positive and positive to negative).
To do this you:
- Type -1 into any cell (E7 in this case)
- Copy it
- highlight the cells you want to change (E51 to J51)
- Click on Paste Special
- Choose the multiple option
- Click OK
What you are telling Excel is to take the -1 and multiple it into every cell you have highlighted.
You can see more in the Data Cleanup Course
Why won’t Excel sum the negative numbers
If you are creating a sum of some cells and Excel seems to ignore them, then Excel doesn’t see them as a negative number.
This could be as a result of a number reasons but the basic cause is that Excel doesn’t think you are referring to a number.
Look at the Data Cleanup Course to see options for fixing this.
What sign is a number
If you want to know what sign a number is, you could use an IF function e.g. =IF(A1>0,”+”,”-“).
Excel has another function called SIGN which you can use. Simply point at the cell and it will return:
- 1 if a positive number
- 0 if the number is zero
- -1 if the number is negative
Stop people entering negative numbers or force them to enter negative numbers
If you want to avoid people entering negative numbers (or if you want to force them to only enter negative numbers) you can use the Data Validation tool.
You can choose the cell (B1 in the example below), and:
- Go to the Data tab
- Click on Data Validation
- Change Allow to ‘Decimal’ (or ‘Whole Numbers’ if you only want whole numbers)
- Change Data to ‘less than’ (or whatever else you would like to control)
- Type 0 into the Maximum box
Now, if someone tries to enter a positive number in this cell, Excel will tell them that it is not possible (or you can give them a special error message)
Related
Change the invert if negative colour in Excel charts
Sum all the negative numbers in Excel
Excel negative numbers in brackets
Excel formula to change negative to positive
Match positive and negative numbers in excel