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)
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
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)
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)^{2}+(Imaginary Coefficient)^{2}]
 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 79i. The IMPRODUCT function multiplies the complex number in B5 with its conjugate form and returns 130 (=7^{2}+9^{2}). 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 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 rightclick 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 and select Flash Fill (Data Tools Group).
The negative numbers of the dataset will be converted to positive numbers.
Read More: Changing Negative Numbers to Positive in Excel
Method 9 – Applying the Find & Replace Feature
Steps:
 Select the range of the negative numbers and go to Find & Replace and select 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 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.
 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.
 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.
 Go back to your sheet, select the range of the numbers, and run the Macro.
 The negative numbers will be converted to positive.
Download the Practice Workbook
Related Articles
 Calculate Absolute Percentage Error with Excel Function
 How to Calculate Absolute Difference between Two Numbers in Excel
<< Go Back to Excel ABS Function  Excel Functions  Learn Excel
Get FREE Advanced Excel Exercises with Solutions!