Make All Numbers in a List Positive in Excel

Add to Favorites
Author: | Edits: don

Take a list of numbers and make them all positive, regardless of whether the list contains a mixed set of positive and negative numbers, only negative numbers, or only positive numbers.

In other words, this method can be applied to an entire list, without knowing that every number in that list is negative, and still get only positive values returned.

This tutorial follows another tutorial (Make Negative Numbers Positive in Excel and Vice Versa) on this topic, but it is important to single out this method since it is the best way to get positive values from a list of potentially negative values.

Steps to Make All Numbers Positive in Excel

To do this, we will use the ABS() function.  This is the absolute value function and it returns a number without its sign, as in without a negative sign.

  1. Next to the data that you want to make positive, type =ABS(cell reference) like this:
    9420d7def39a68d0d7f2c4078afaafda.png
  2. Once you enter that function simply copy it down by selecting cell B1 and double-clicking the bottom right corner of the cell.
    81a1abfd4c75ce5830db66fc777c75b6.png

It's as easy as that!

Now, all numbers in the list are positive, regardless of the size or type of number.

Make the New Numbers Actual Numbers

This just takes the ABS function's output in column B and turns it into actual numbers instead of the ABS() function.

Select the desired cells > Ctrl + C > Alt + E + S + V and then Enter.

Notes

This is better than using the Copy > Paste Special method to make numbers positive because that method will turn all positive numbers from the list into negative numbers at the same time as converting the negative ones to positive.  As such, the Absolute Value method mentioned here is the preferred method for ensuring that a list of numbers contains only positive numbers.

Download the accompanying Excel workbook to see this in action.


Excel Function: ABS()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Average the Visible Rows in a Filtered List in Excel
Tutorial: Average the results from a filtered list in Excel. This method averages only the visible ...
Find the Most Frequently Occurring Value in a List in Excel
Tutorial: Use a formula to return the most frequently occurring word or value from a range of cells...
Format Cells as an Accounting Number in Excel Number Formatting
Macro: This free Excel macro formats a selected cell as an Accounting number. This means that the...
Highlight, Sort, and Group the Top and Bottom Performers in a List in Excel
Tutorial: How to highlight the rows of the top and bottom performers in a list of data. This allows...
Changing Any Element in a Chart in Excel
Tutorial: In this tutorial I am going to go through the Layout tab in more detail and show you how t...
Formula to Get the Last Value from a List in Excel
Tutorial: Formulas that you can use to get the value of the last non-empty cell in a range in Excel...
Tutorial Details
Excel Function: ABS()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course