Working in the Microsoft Excel spreadsheet can become very complex, especially if you don’t know how to use its functions. In some cases you may need to change an entire column of numeric data from negative to positive. If this is your case, do not worry, we will show you below How can you do this procedure?
What should I do to change negative numbers to positive in Excel?
One of the advantages that excel offers us is that it has some basic formulas and functions that will allow us to change numbers from negative to positive. The best of all is that we will not have to choose figures individually. Since we can select different cells and make the change in all of them without having to repeat the steps again.
Among the multiple options that we can use to change negative numbers to positive or vice versa is the Excel paste special. Another way to do this is by using the ABS function, which will remove the negative sign from the number. In this way it will show its absolute value, that is, without the negative sign, but let’s see in detail how each of these operations is carried out.
ABS function
To make use of the ABS function on a data column with negative signs, the first thing you should do is select the first cell. In this case it will be B3, then you must write the formula =ABS(B3) without the quotes and then hit the ‘Enter’ key. Now, to apply this same formula to the entire column, you need to drag cell B3 to the entire range.
It is important that you know that ABS function will not apply to cells containing text and will give an error as a result. It also doesn’t work in cases where you want to change numbers from positive to negative, since this function will give you the absolute value of the number. Therefore, it will never offer you a negative value as a result.
Use special paste function
To use the special paste to change negative numbers to positive you must follow the following steps. First, you must write in a blank cell the figure -1, then select the cell and use the keyboard shortcuts. And in this case you will press ‘Ctrl + C’ key to copy the cell data and then select all the cells which contain negative numbers.
Now you must right click and this action will show you a menu from which you must select choice ‘Special paste’. We will now have a dialog box on the screen, in which you must mark the ‘All’ option. Then in the ‘Operation’ section you must check the ‘Multiply’ option and click on the ‘OK’ option to finally change the sign.
How can I remove the negative sign from a number in Excel?
In case you want to remove the negative sign from a number or a group of numbers in excel, you may make use of VBA code. To execute this code, we just have to proceed as follows. First of all, we must open the Visual Basic window and for this we will press the shortcut keys ‘Alt + F11’.
This action will show a window on the screen and in it we will select the option ‘Box’ and then ‘Module’. Next we will have to enter the following codes in the way we indicate:
- SubPositive
- Dim Cel As Range
- For Each Cel In Selection
- If IsNumeric(Cel.Value) Then
- Cel.Value = Abs(Cel.Value)
- End If
- Next Cell
- End Sub
To finish and run the code we just entered, just press the function key F5 or you can click on the ‘Run’ option. And in this way we can see how the number or numbers contained in our spreadsheet change from negative to positive. This can be an option, maybe not very fast, but very effective for remove the negative sign.
As you have seen, excel offers you different very useful options that will significantly simplify your tasks. And one of them will allow you change numbers from positive to negative or vice versa by means of some functions. And in a way as simple and fast as converting Arabic numbers to Roman numbers with formulas.
