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

The article will show you how to make all numbers positive in Excel. Sometimes in calculus and coordinate geometry, we have to take the positive value or the magnitude of the out even though it’s negative in value. This is a pretty basic task. Because you know that multiplying a negative number by another negative number gives a positive number as output. But this article will also show you how to convert complex numbers to positive numbers (magnitude) too. So stick together to this article to see how to make both negative and complex numbers as well as all kinds of numbers positive.

In the dataset, you will see two types of numbers: Negative and Complex. I’ll show you how to make these numbers positive in the following sections of this article.

1. Using Excel ABS Function to Make All Numbers Positive

The best way to make all numbers positive in a dataset is to apply the ABS function. It returns the absolute values of all numbers given in it. This function works on real numbers, so I’ll use real negative numbers in this section. Let’s go through the process below for a better understanding.

Steps:

• First, make a column to store the positive numbers and type the following formula in cell C5.

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

• After that, press the ENTER button and you will see the positive value of -4.

• Thereafter, use the Fill Handle to AutoFill the lower cells.

Finally, you can make all the numbers positive using the Excel ABS function.

Read More: How to Sum Absolute Value in Excel

2. Multiplying by Negative One (-1)

You can also make all the negative numbers positive just by multiplying them by -1. As multiplying negative numbers by negative numbers makes them positive, you will get the positive values of the given negative numbers by this operation. Let’s go through the process below.

Steps:

• First, make a column to store the positive numbers and type the following formula in cell C5.

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

• After that, hit the ENTER button and you will see the positive value of -4.

• Thereafter, use the Fill Handle to AutoFill the lower cells.

Finally, you can make all the numbers positive by multiplying the numbers by -1.

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. Although you can use the ABS function in this case, let’s go through this different approach.

Steps:

• First, make a column to store the positive numbers and type the following formula in cell C5.

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

• After that, press the ENTER button and you will see the positive value of -4.

• Thereafter, drag the Fill Icon to AutoFill the lower cells.

Finally, you can make all the numbers positive using the Excel IF function. Also notice that the value of 23 didn’t change.

Read More: Opposite of ABS Function in Excel

4. Applying the IMABS Function to Make All Complex Numbers Positive

This method is for making the complex or imaginary numbers positive or you can say converting them to their magnitude. Please see the description below.

Steps:

• First, we will generate some complex numbers by using the COMPLEX function. The formula is given below for the complex number in cell B5.

• Next, type the following formula in C5 with the IMABS function.

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

• After that, press the ENTER button and you will see the magnitude of 7+9i.

• Thereafter, use the Fill Handle to AutoFill the lower cells.

Thus, you can make the complex numbers positive using the Excel IMABS function.

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. Let’s go through the process below for a better understanding.

Steps:

• First, make a column to store the positive numbers and type the following formula in cell C5.

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

• After that, hit the ENTER button and you will see the magnitude of 7+9i.

• Thereafter, use the Fill Handle to AutoFill the lower cells.

Thus, you can make all the complex numbers positive by combining the Excel functions for complex numbers.

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

If you are not a formula guy, then you can use the Copy & Paste Special feature to make all the numbers positive. Let’s have a look at the description below.

Steps:

• First, type -1 in any cell to use it as a multiplier.
• Next, select the range of the negative numbers and right-click on it.
• After that, select Paste Special.

• Thereafter, the Paste Special dialog box will pop up. Select Multiply and then click OK.

• Finally, you will see the negative numbers converted to positive

Thus you can make all the numbers positive by using the Copy & Paste Special feature.

7. Changing the Number Format

You can also make the numbers positive by changing the number format. Please read the following description.

Steps:

• First, select the range of the negative numbers, then go to the Home tab and click on the drop down icon of the Number
• Next, select More Number Formats.

• In the Format Cells window, just type or copy the string #,###;#,###.00 in the Type
• Later, click OK.

This operation will make all the negative numbers positive.

Thus you can make the numbers positive by changing the number format.

8. Making All Numbers Positive Using Flash Fill

Flash Fill can also be an effective tool to make all the negative numbers positive. Please follow the description below.

Steps:

• First, type the positive form of the number in B5 and then go to Data >> Flash Fill (Data Tools Group).

This operation will convert the negative numbers of the dataset to positive numbers.

Read More: Changing Negative Numbers to Positive in Excel

9. Applying Find & Replace Feature

The Find & Replace feature can be a valuable tool to make the numbers positive. Please go through the discussion below.

Steps:

• First, select the range of the negative numbers and then go to Home >> Find & Replace >> Replace.

• Next, type – in the Find what section and put a Space in the Replace with section of the Find and Replace
• After that, select Replace All.

The execution of this command will make all the numbers positive.

10. Using Excel Power Query Editor to Make All Numbers Positive

You can also make the numbers positive by using the Power Query Editor. Using this is a versatile process. Let’s go through the description below for a better understanding.

Steps:

• First, select the range of the negative numbers and then go to Data >> From Range/Table (Get & Transform Data Group).
• A dialog box will show up. Make sure you select My table has headers and click OK.

• After that, your data will appear in the Power Query Editor. Select Transform Tab >> Scientific >> Absolute Value.

• Next, you will see the numbers converted to their positive
• After that, select Home >> Close & Load.

• Finally, you will see the positive numbers in a new sheet listed in a table.

Thus you can make all numbers positive by using the Power Query Editor.

Note:

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

11. Inserting VBA Code to Make All Numbers Positive

We can also use Microsoft Visual Basic for Applications (VBA) to make all the numbers positive. Just follow the steps given below.

Steps:

• First, go to the Developer Tab and then select Visual Basic.

• After that, the VBA editor will appear. Select Insert >> Module to open a VBA Module.

• Now, type 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.

• Now go back to your sheet, select the range of the numbers and run the Macro.

• After that, you will see the negative numbers in their positive form.

Thus you can make all numbers positive by using VBA.

Download Practice Workbook

Conclusion

Suffice it to say, that you will learn all possible ways of making all types of numbers positive in Excel after reading this article. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.

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