When working with Excel, you may face situations where you will need to change positive numbers to negative. The main objective of this article is to explain how to change positive numbers to negative in Excel.
How to Change Positive Numbers to Negative in Excel: 6 Effective Ways
Here, I have taken the following dataset to explain this article. The dataset contains Months and Loss. I will change the Loss to Profit. To do that I will change the positive numbers to negative because negative profit is the loss.
1. Using Mathematical Operation to Change Positive Numbers to Negative in Excel
In this first method, I will use a mathematical operation to change positive numbers to negative in Excel.
Let’s see the steps.
Steps:
- Firstly, select the cell where you want to change the positive number to negative. Here, I selected cell C5.
- Secondly, in cell C5 write the following formula.
=C5*-1
Here, I multiplied the value in cell C5 by -1. The formula will not change the value. It will only change the number from positive to negative and return the negative value as result.
- Finally, press ENTER to change the number to negative.
- After that, drag the Fill Handle to copy the formula.
Now, you can see that I have copied the formula to all the other cells and changed my positive numbers to negative.
Read More: Excel Formula for Working with Positive and Negative Numbers
2. Applying Paste Special Feature in Excel
To explain this method, I have taken the following dataset. This dataset contains Months, Income, and Expense. The Expenses are written as positive numbers here. I will change these positive numbers to negative.
Let’s see how to change positive numbers to negative in Excel by applying the Paste Special feature.
Steps:
- Firstly, write -1 in any cell outside your dataset. Here, I wrote it in cell B11.
- Secondly, copy cell B11.
- Thirdly, select the range where you want to change positive numbers to negative.
- After that, Right-click on the selected data.
- Next, select Paste Special.
Now, you will see a dialog box named Paste Special will appear.
- Firstly, select Values from Paste.
- Secondly, select Multiply from Operation.
- Thirdly, select OK.
Now, you will see that the positive numbers are changed to negative numbers.
Finally, you can delete the -1 you wrote in cell B11 and get your desired dataset.
3. Use of ABS Function to Change Positive Numbers to Negative
To explain this method, I have taken the following dataset. This dataset contains Months and Expense. Here, you can see that some of the expenses are written as a positive number. I will change the positive numbers to negative and keep the negative numbers as it is.
Let’s see how to change positive numbers to negative in Excel with the use of the ABS function.
Steps:
- Firstly, select the cell where you want to change the positive number to negative. Here, I selected cell D5.
- Secondly, in cell D5 write the following formula.
=ABS(C5)*-1
Formula Breakdown
- Here, in the ABS function, I selected C5 as a number. The ABS function will return the absolute value of the number in cell C5.
- And then, multiplied the absolute value by -1. Which will change the sign of the number to negative without changing the value.
- Finally, the formula will change the number in cell C5 to a negative number.
- Thirdly, select ENTER to get the result.
- Now, drag the Fill Handle to copy the formula to other cells.
Finally, you can see that I have copied the formula to all the other cells and changed positive numbers to negative.
Read More:Â How to Convert Negative Value to Positive in Excel Using Formula
4. Employing IF Function in Excel
For you to understand this method clearly, I have taken the following dataset. This dataset contains Months and Expense. Here, you can see that some of the expenses are written as a positive number. I will change the positive numbers to negative and keep the negative numbers as it is.
Let’s see how to change positive numbers to negative in Excel by employing the IF function.
Steps:
- Firstly, select the cell where you want to change positive number to negative. Here, I selected cell D5.
- Secondly, in cell D5 write the following formula.
=IF(C5<0,C5,C5*-1)
Here, in the IF function, I selected C5<0 as logicat_test, C5 as value_if_true, and C5*-1 as value_if_false. The IF function will check if the value in cell C5 is Less than 0. If it is true, the formula will return the value in cell C5 as result. Otherwise, it will multiply the value in cell C5 by -1 and will return it as result.
- Thirdly, press ENTER to get the result.
- Now, drag the Fill Handle to copy the formula to the other cells.
Finally, you can see that I have copied the formula to all the other cells and changed positive numbers to negative.
5. Using Go to Special Command in Non-blank Cells
To explain this method, I have taken the following example. Here, the dataset contains information about Income and Expense. I will convert the positive numbers to negative in the Expense column. I will only change the numbers in non-blank cells.
Let’s see how to change positive numbers to negative in only non-blank cells in Excel by using the Go to Special command.
Steps:
- Firstly, write -1 in any cell outside your dataset. Here, I wrote it in cell B11.
- Secondly, copy cell B11.
- Thirdly, select the range where you want to change positive numbers to negative.
- After that, go to the Home tab.
- Next, select Find & Select.
Here, you will see a drop-down menu will appear.
- Then, select Go To Special.
Now, you will see a dialog box named Go To Special will appear.
- Firstly, select Constants from Select.
- Secondly, select OK.
Here, you will see that only the non-blank cells are selected.
- After that, Right-click on any of the selected cells.
- Next, select Paste Special.
Now, you will see a dialog box named Paste Special will appear.
- Firstly, select Values from Paste.
- Secondly, select Multiply from Operation.
- Thirdly, select OK.
Now, you will see that the positive numbers are changed to negative numbers in the non-blank cells.
Finally, you can delete the -1 you wrote in cell B11 and get your desired dataset.
Read More: How to Sum Negative and Positive Numbers in Excel
6. Applying VBA to Change Positive Numbers to Negative in Excel
In this method, I will explain how you can change the positive numbers to negative in Excel by applying VBA. To explain this method I have taken the following dataset. This dataset contains Months and Expense. Here, you can see that some of the expenses are written as a positive number. I will change the positive numbers to negative and keep the negative numbers as it is.
Let’s see the steps.
Steps:
- Firstly, go to the Developer tab
- Secondly, select Visual Basic.
Now, you will see the Visual Basic window will open.
- After that, go to the Insert tab.Â
- Now, select the Module option.
After that, you will see a Module has opened. In that Module type in the following code.
Sub Positive_to_Negative()
Dim current_sheet As Worksheet
Dim selected_range As Range
Dim Result As Range
Set current_sheet = Application.ActiveSheet
Set selected_range = Application.Selection
For Each cell In selected_range
   If cell.Value > 0 Then
   cell.Value = cell.Value * -1
   End If
Next
End Sub
Code Breakdown
- Here, I have created a Sub Procedure named Positive_to_Negative.
- Then, I declared a Variable named current_sheet as Worksheet, and another Variable named selected_range as Range. I also declared a Variable named Result as Range.
- Next, I used the Set statement to assign the Application.ActiveSheet to current_sheet and Application.Selection to selected_range.
- After that, I used a For Next Loop that will check each cell in the selected_range.
- Then, in the For Next Loop, I used an If Statement. Which will check if the cell value in the selected range is greater than 0. If it is then, it will multiply the cell value by -1 and return it as a new cell value.
- After that, I ended the If statement.
- Finally, I ended the Sub Procedure.
Now, Save the code and go back to your worksheet.
- Firstly, select the range where you want to change positive numbers to negative.
- Secondly, go to the Developer tab.
- Thirdly, select Macros.
Here, a dialog box will appear.
- Firstly, select the Sub Procedure you created in your VBA as the Macro name. Here, I selected the Macro named Positive_to_Negative.
- Secondly, select Run.
Now, you can see that the numbers are changed to negative from positive in the selected range.
Read More: How to Make Negative Numbers Red in ExcelÂ
Things to Remember
- It should be noted that Method-1, Method-2, and Method-5 can only be used when all the numbers are positive. Otherwise, you will change the negative numbers to positive while applying these three methods.
Practice Section
Here, I have provided a practice sheet for you to practice how to change positive numbers to negative in Excel.
Download Practice Workbook
Conclusion
In this article, I tried to explain how to change positive numbers to negative in Excel with 6 effective methods. I hope this was helpful for you. If you have any questions please, let me know in the comment section below.
Related Articles
- How to Put Negative Percentage Inside Brackets in Excel
- How to Put Parentheses for Negative Numbers in Excel
- How to Add Brackets to Negative Numbers in Excel
- Excel Negative Numbers in Brackets and Red
- How to Move Negative Sign at End to Left of a Number in Excel
<< Go Back to Negative Numbers in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
