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:
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:
- First, select any blank cell (in this case C5 cell).
- Then, insert the following formula in the selected cell.
- After pressing the Enter button, you will get the desired result.
- Next, use the Fill Handle option to apply the same formula to all cells.
- Finally, you will get the final result similar to the below image.
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:
- At first, select any blank cell (in this case C5 cell) and insert the following formula
- Second, use the Fill Handle to apply this formula to all the cells.
- Last, you will get results like the below image.
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.
- To begin with, paste the numbers you want to convert into the desired column.
- In addition, insert (-1) into any blank cell (in this case cell D5) and copy the cell.
- Furthermore, select the numbers you want to convert.
- Next, right-click on the column and choose the option Paste Special.
- 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.
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.
- 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.
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.
- At first, select the range of the cells you want to highlight negative numbers.
- Second, go to the Home tab and choose the Conditional Formatting option.
- Third, select the Less Than… option from the Highlight Cells Rules option.
- 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.
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.
- How to Add Brackets to Negative Numbers in Excel (3 Easy Ways)
- How to Change Positive Numbers to Negative in Excel (6 Ways)
- How to Apply Formula for Positive and Negative Numbers in Excel
- Excel Negative Numbers in Brackets and Red (2 Examples)
- [Fixed!] Excel Not Adding Negative Numbers Correctly (4 Solutions)