# Excel negative numbers

Negative numbers in Excel can sometimes cause some confusion for users. Below some of the most common issues/ questions addressing Excel negative numbers.

## 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

1. negative numbers in red (or another colour) post
2. 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.

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:

1. Type -1 into any cell (E7 in this case)
2. Copy it
3. highlight the cells you want to change (E51 to J51)
4. Click on Paste Special
5. Choose the multiple option
6. 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:

1. Go to the Data tab
2. Click on Data Validation
3. Change Allow to ‘Decimal’ (or ‘Whole Numbers’ if you only want whole numbers)
4. Change Data to ‘less than’ (or whatever else you would like to control)
5. 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)

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

Identify positive and negative numbers in excel

Best Excel Online Course for you