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

Method 1 – Using the 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 Minus One (-1)

Steps:

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

=-1*B5

Multiplying by Negative One (-1)

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

Steps:

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

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

Using Excel IF Function to Make All Numbers Positive

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)

Applying IMABS Function to Make All Complex Numbers Positive

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.

All Numbers in Positive with IMABS

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)))

Combining Excel Complex Number Functions to Make All Complex Numbers Positive

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.

All Complex Numbers in Positive

Method 6 – Applying the Copy & Paste Special Feature to Convert 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.

Applying Copy & Paste Special Feature

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

The negative numbers will be converted to positive.

All Numbers in 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.

Changing Number Format

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 and select Flash Fill (Data Tools Group).

Make Numbers Positive Using Flash Fill

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

Method 9 – Applying the Find & Replace Feature

Steps:

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

Applying Find & Replace Feature to Make Numbers Positive

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 the Excel Power Query Editor to Make All Numbers Positive

Steps:

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

Applying Power Query Editor to Make All Numbers Positive

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

The numbers will be converted to positive.
Select Home and choose 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.

Implementing VBA

The VBA editor will appear. Select Insert and 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.