How to Put a Negative Number in Excel Formula (4 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

This tutorial will demonstrate the methods on how to put a negative number in an excel formula. Negative numbers are very important in case of temperatures below freezing point, penalties in a game, credit of money or negative scoring in the exam, etc. So, it is very important to learn how to put a negative number in an excel formula.


Download Practice Workbook

You can download the practice workbook from here.


4 Easy Methods to Put a Negative Number in Excel Formula

We’ll use a sample dataset overview as an example in Excel to understand easily. In this case, we have Numbered in column B and Converted Numbers in Column C. We will use this dataset in the below methods. If you follow the methods correctly then you should learn how to put a negative number in an excel formula on your own. The methods are:

Methods to Put a Negative Number


1. Applying IF Function

Our aim is to put a negative number in an excel formula by using the IF Function. The steps of this method are described below:

Steps:

  • First, select any blank cell (in this case C5 cell).
  • Then, insert the following formula in the selected cell.
=IF(B5>0,B5*-1,B5)

Methods to Put a Negative Number

  • After pressing the Enter button, you will get the desired result.

Methods to Put a Negative Number

  • Next, use the Fill Handle option to apply the same formula to all cells.

Methods to Put a Negative Number

  • Finally, you will get the final result similar to the below image.

Methods to Put a Negative Number

Read More: Excel Formula to Return Zero If Negative Value is Found


2. Use of ABS Function

We can put a negative number in an excel formula with the help of the ABS Function. The steps of this method are:

Steps:

  • At first, select any blank cell (in this case C5 cell) and insert the following formula
    =ABS(B5)*-1

Methods to Put a Negative Number

  • Second, use the Fill Handle to apply this formula to all the cells.

Methods to Put a Negative Number

  • Last, you will get results like the below image.

Methods to Put a Negative Number

Read More: How to Add Negative Numbers in Excel (4 Easy Ways)


3. Utilizing Excel’s Paste Special Feature

Another method to put a negative number in an excel formula is by utilizing the Paste Special Feature. The steps of this method are described below.

Steps:

  • To begin with, paste the numbers you want to convert into the desired column.

Methods to Put a Negative Number

  • In addition, insert (-1) into any blank cell (in this case cell D5) and copy the cell.

Methods to Put a Negative Number

  • Furthermore, select the numbers you want to convert.

Methods to Put a Negative Number

  • Next, right-click on the column and choose the option Paste Special.

Methods to Put a Negative Number

  • After that, the Paste Special dialog box will open on the screen.
  • Then, select All in the Paste section, the Multiply option in the Operation section, and press OK.

  • Finally, you will get the desired result.

Read More: How to Show Negative Numbers in Excel (4 Quick Tricks)


4. Applying VBA Code

Applying VBA code is another method for putting a negative number in an excel formula. The steps of this method are.

Steps:

  • To begin with, press Alt + F11 to open the VBA window.
  • Then, go to the Insert option and select the Module option.

  • In addition, insert the VBA code.
Sub Put_Negative_Number()
Dim wsheet As Worksheet
Dim rnge As Range
Dim rslt As Range
Set wsheet = Application.ActiveSheet
Set rnge = Application.Selection
For Each cell In rnge
If cell.Value > 0 Then
cell.Value = cell.Value * -1
End If
Next
End Sub

  • Furthermore, go to the Developer option and use the Macros option to open the Macro tab.
  • Next, select the macro and range from the worksheet and press the Run option.

  • Finally, you will get results like the below image.

Read More: How to Count Negative Numbers in Excel (3 Easy Ways)


Highlighting Negative Numbers

Now, we want to highlight the negative numbers in our worksheet to identify the negative numbers briefly. This can be done by following the below steps.

Steps:

  • At first, select the range of the cells you want to highlight negative numbers.

  • Fourth, put 0 in the Format cells that are Less THAN cells and also select the desired color in the Less Than dialog box and press OK.

  • Last, you will get the result like the below image.


Things to Remember

  • In the case of using the first two methods at first, you have listed the numbers you want to convert and then use the formulas accordingly.
  • When using the Paste Special method, make sure to copy (-1) and select the range after that. Otherwise, you won’t get the desired result.
  • If you have used the VBA code, make sure to save it in the Enabled Macro book. Otherwise, the code won’t run.

Conclusion

Henceforth, follow the above-described methods. Thus, you will learn how to put a negative number in an excel formula. Let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo