How to Change Positive Numbers to Negative in Excel (6 Ways)

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.


Download Practice Workbook


6 Effective Ways to Change Positive Numbers to Negative in Excel

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.

6 Effective Ways to Change Positive Numbers to Negative in Excel


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

Using Mathematical Operation to Change Positive Numbers to Negative in Excel

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.

Using Mathematical Operation to Change Positive Numbers to Negative in Excel

Now, you can see that I have copied the formula to all the other cells and changed my positive numbers to negative.

Read More: How to Apply Formula for Positive and Negative Numbers in Excel


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.

Applying Paste Special Feature in Excel-How to Change Positive Numbers to Negative in Excel

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.

Applying Paste Special Feature in Excel How to Change Positive Numbers to Negative in Excel

  • 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.

How to Change Positive Numbers to Negative in Excel Applying Paste Special Feature in Excel

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.

Applying Paste Special Feature in Excel


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.

Use of ABS Function to Change Positive Numbers to Negative 

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.

Use of ABS Function to Change Positive Numbers to Negative 

  • 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: [Fixed!] Excel Not Adding Negative Numbers Correctly (4 Solutions)


Similar Readings


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.

How to Change Positive Numbers to Negative in Excel Employing IF Function in Excel

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.

How to Change Positive Numbers to Negative in Excel Employing IF Function in Excel

  • 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.

Employing IF Function in Excel


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.

How to Change Positive Numbers to Negative in Excel Using Go to Special Command 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.

How to Change Positive Numbers to Negative in Excel Using Go to Special Command in Non-blank Cells

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.

How to Change Positive Numbers to Negative in Excel Using Go to Special Command in Non-blank Cells

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.

How to Change Positive Numbers to Negative in Excel Using Go to Special Command in Non-blank Cells

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.

How to Change Positive Numbers to Negative in Excel Using Go to Special Command in Non-blank Cells

Finally, you can delete the -1 you wrote in cell B11 and get your desired dataset.

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


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.

Applying VBA to Change Positive Numbers to Negative in Excel

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.

Applying VBA to Change Positive Numbers to Negative in Excel

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.

Applying VBA to Change Positive Numbers to Negative 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.


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. To get more articles like this visit ExcelDemy. If you have any questions please, let me know in the comment section below.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo