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

The sample dataset contains Months and Loss columns, where we will change Loss to Profit. To do that, we will change the positive numbers to negative because negative profit is the loss.

Method 1 – Using Mathematical Operation to Change Positive Numbers to Negative in Excel

Steps:

• Select the cell with the positive number you want to change to negative. Here, we selected cell C5.
• Enter the following formula in cell C5.

In the example, we 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 a result.

• Press ENTER to change the number to negative.

• Drag the Fill Handle to copy the formula to the other cells.

The formula will be copied to all the other cells and change all positive numbers to negative.

Read More: Excel Formula for Working with Positive and Negative Numbers

Method 2 – Applying Paste Special Feature in Excel

In this sample dataset, Expenses are written as positive numbers here. We will change these positive numbers to negative.

Steps:

• Type -1 in any cell outside your dataset. We wrote it in cell B11.
• Copy cell B11.

• Select the range where you want to change positive numbers to negative.

• Right-click on the selected data.
• Select Paste Special.

• In the Paste Special dialog box, select Values from Paste.
• Select Multiply from Operation.
• Click OK.

The positive numbers will change to negative numbers.

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

Method 3 – Use of ABS Function to Change Positive Numbers to Negative

Here, the Expenses column has a mix of positive and negative numbers. We will change the positive numbers to negative and keep the negative numbers as it is.

Steps:

• Select the cell where you want to change the positive number to negative. We selected cell D5.
• In cell D5 write the following formula.

• Hit ENTER to get the result.

• Drag the Fill Handle to copy the formula to other cells.

The formula will be copied to all the other cells and change positive numbers to negative.

Method 4 – Employing IF Function in Excel

Steps:

• Select the cell where you want to change positive number to negative. We selected cell D5.
• Enter the following formula in cell D5

Here, in the IF function, we 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.

• Press ENTER to get the result.

• Drag the Fill Handle to copy the formula to the other cells.

The formula will be copied to all the other cells and change positive numbers to negative.

Method 5 – Using Go to Special Command in Non-blank Cells

This sample dataset includes some empty cells. We will only change the numbers in non-blank cells.

Steps:

• Type -1 in any cell outside your dataset. We wrote it in cell B11.
• Copy cell B11.

• Select the range where you want to change positive numbers to negative.

• Go to the Home tab.
• Select Find & Select.

• Select Go To Special.

A dialog box named Go To Special will appear.

• Select Constants from Select.
• Click OK.

You can see that only the non-blank cells are selected.

• Right-click on any of the selected cells.
• Select Paste Special.

The Paste Special dialog box named will appear.

• Select Values from Paste.
• Select Multiply from Operation.
• Click OK.

The positive numbers will change to negative numbers in the non-blank cells.

Read More: How to Sum Negative and Positive Numbers in Excel

Method 6 – Applying VBA to Change Positive Numbers to Negative in Excel

Steps:

• Go to the Developer tab
• Select Visual Basic.

The Visual Basic window will open.

• Go to the Insert tab. 
• Select the Module option.

In the Module enter 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

Save the code and go back to your worksheet.

• Select the range where you want to change positive numbers to negative.
• Go to the Developer tab.
• Select Macros.

• In the Macro dialog box, select the Sub Procedure you created in your VBA as the Macro name. Here, we selected the Macro named Positive_to_Negative.
• Click Run.

The numbers will change from positive to negative 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, it will change the negative numbers to positive while applying these three methods.

Download Practice Workbook

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