How to Make All Numbers Positive in Excel (11 Effective Methods)

Method 1 – Using Excel ABS Function to Make All Numbers Positive

Steps:

Make a column to store the positive numbers and enter the following formula in cell C5.

=ABS(B5)

Using Excel ABS Function to Make All Numbers Positive

The formula will return the absolute value of -4 which is 4.

Press ENTER and you will get the positive value of -4.

Use the Fill Handle to AutoFill the lower cells.

Read More: How to Sum Absolute Value in Excel

Method 2 – Multiplying by Negative One (-1)

Steps:

Make a column to store the positive numbers and add the following formula in cell C5.

=-1*B5

The formula will return the positive value of -4 which is 4.

Press ENTER and you will get the positive value of -4.

Use the Fill Handle to AutoFill the lower cells.

Method 3 – Using the Excel IF Function to Make All Numbers Positive

If your dataset contains both negative and positive numbers, it’s good to use the IF function. Because you don’t want your positive numbers to be converted to negative numbers.

Steps:

Make a column to store the positive numbers and add the following formula in cell C5.

=IF(B5<0,-1*B5,B5)

The formula will return positive values for all given numbers both negative and positive. Here, it will return 4.

Press ENTER and you will get the positive value of -4.

Use the Fill Handle to AutoFill the lower cells.

Notice that the value of 23 didn’t change as it is a positive number.

Read More: Opposite of ABS Function in Excel

Method 4 – Applying the IMABS Function to Make All Complex Numbers Positive

Steps:

Generate some complex numbers by using the COMPLEX function. Enter the formula given below in cell B5.

=COMPLEX(7,9)

Add the following formula in C5 with the IMABS function.

=IMABS(B5)

The formula will return the magnitude of 7+9i which is 11.40175…. The absolute value or magnitude for any complex number is √[(Real Part)²+(Imaginary Coefficient)²]

Press ENTER to get the magnitude of 7+9i.

Use the Fill Handle to AutoFill the lower cells.

Method 5 – Combining Excel Complex Number Functions for Complex Numbers

You can also make the complex numbers in their magnitude or positive form by combining the SQRT, IMPRODUCT, and IMCONJUGATE functions. Steps:

Make a column to store the positive numbers and add the following formula in cell C5.

=SQRT(IMPRODUCT(B5,IMCONJUGATE(B5)))

The IMCONJUGATE function returns the conjugate value of the complex number in B5 which is 7-9i. The IMPRODUCT function multiplies the complex number in B5 with its conjugate form and returns 130 (=7²+9²). The SQRT function returns the square root of 130 which is the magnitude of 7+9i.

Press ENTER to get the magnitude of 7+9i.

Use the Fill Handle to AutoFill the lower cells.

Method 6 – Applying Copy & Paste Special Feature to Convert All to Positive Numbers

Steps:

Enter -1 in any cell to use it as a multiplier.
Select the range of the negative numbers and right-click on it.
Select Paste Special.

The Paste Special dialog box will pop up. Select Multiply and click OK.

The negative numbers will be converted to positive.

Method 7 – Changing the Number Format

Steps:

Select the range of the negative numbers, go to the Home tab and click on the drop down icon of the Number group.
Select More Number Formats.

In the Format Cells window, enter the string #,###;#,###.00 in the Type box.
Click OK.

All the negative numbers will turn to positive.

Method 8 – Making All Numbers Positive Using Flash Fill

Steps:

Enter the positive form of the number in B5 and go to Data >> Flash Fill (Data Tools Group).

The negative numbers of the dataset will be converted to positive numbers.

Method 9 – Applying Find & Replace Feature

Steps:

Select the range of the negative numbers and go to Home >> Find & Replace >> Replace.

Enter – in the Find what section and put a Space in the Replace with section of the Find and Replace window.
Select Replace All.

It will make all the numbers positive.

Method 10 – Using Excel Power Query Editor to Make All Numbers Positive

Steps:

Select the range of the negative numbers and go to Data >> From Range/Table (Get & Transform Data Group).
A dialog box will show up. Select My table has headers and click OK.

Your data will appear in the Power Query Editor. Select Transform Tab >> Scientific >> Absolute Value.

The numbers will be converted to positive.
Select Home >> Close & Load.

The positive numbers will be in a new sheet listed in a table.

Note:

You can also do a similar operation from the Power Query Editor by selecting Transform >> Standard >> Multiply. A window will pop up, insert -1 in the Multiply section and click OK. Do the Close & Load operation. You will get the same result.

Method 11 – Inserting VBA Code to Make All Numbers Positive

Steps:

Go to the Developer Tab and select Visual Basic.

The VBA editor will appear. Select Insert >> Module to open a VBA Module.

Add the following code in the VBA Module.

Sub MakeAllPositive()
For Each mn_cell In Selection
    If mn_cell.Value < 0 Then
        mn_cell.Value = -mn_cell.Value
    End If
Next mn_cell
End Sub

The code will return the positive form of a number if it’s less than zero. So only the negative numbers will be changed to positive, the positive numbers will remain the same. The Macro uses an IF Statement and a For Loop to find the numbers that are less than zero.