Free Microsoft Excel 2013 Quick Reference

convert negative numbers to positive numbers and vice versa

I have in column A formulas e g :

=VLOOKUP(D53,AN54:AO4000,2,FALSE) result -45
=VLOOKUP(D54,AN54:AO4000,2,FALSE) result -67
=VLOOKUP(D55,AN54:AO4000,2,FALSE) result 78
=VLOOKUP(D56,AN54:AO4000,2,FALSE) result -4
=VLOOKUP(D57,AN54:AO4000,2,FALSE) result 49
=VLOOKUP(D58,AN54:AO4000,2,FALSE) result 23
=VLOOKUP(D59,AN54:AO4000,2,FALSE) result - 34

how can I convert negative numbers to positive numbers ?

regards bill
bill gras

Post your answer or comment

comments powered by Disqus
I have a columns of numbers. How can I quickly change the negative numbers to positive numbers and the positive numbers to negative numbers?

Thank you.

I am wanting to be able to enter in a number in one cell and then have it's negative equivalent appear in another cell or enter a negative number in a cell and have its positive equivalent appear in another cell. Example: I enter the negative number (11.5) in F4 and I want the positive number 11.5 to then also appear in F10.

Or I enter the positive number 2.5 in F5 and I want the negative number (2.5) to appear in F12.

I can attach a sample worksheet if that would help.


I have the following code to delete all rows containing "Payment" in column D and transposing negatives to positives and vice verca in column H, however it it seems to fall over on the highlighted line with an error 13.

    Application.ScreenUpdating = 0 
    With Sheets("Inputs Analysis") 
        With .Range("d5", .Cells(Rows.Count, "d").End(xlUp)) 
            .Offset(, 1).AutoFilter 1, "Payment" 
            Set Rng = .Offset(, 4) 
            [COLOR=#ff0000]mystr = Replace(Replace("|" & Join(Application.Transpose(Rng), "|"), "|-", "$"), "|", "$-") 
            [/COLOR]        Rng.Value = Application.Transpose(Split(Mid(mystr, 2, Len(mystr)), "$")) 
            If Application.DecimalSeparator = "." Then Rng.Replace ",", ".", xlPart 
        End With 
    End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any Help would be much appreciated, thanks.


Is there a way to change a long column of negative numbers to positive numbers without editing each individual cell?

Thank you

Hello everybody,

I'm looking for a formula (not VBA nor "Paste Special/Transpose") that would
transpose columns to rows (or vice versa) by simple dragging of the cell.
Are you aware of any, hopefully not too complex?

Thanks a lot for your help on this,

If I need to quickly change an entire column (800 rows) of negative numbers
to positive numbers, what is the easiest way. I tried the paste special
multiply by -1, but it didn't work, I'm probably doing it wrong.

Hello everybody,

I'm looking for a formula (not VBA nor "Paste Special/Transpose") that would
transpose columns to rows (or vice versa) by simple dragging of the cell.
Are you aware of any, hopefully not too complex?

Thanks a lot for your help on this,

Hi all,

Ok here's my problem:

I have a dump of information (from a application called Vision) which dumps information from a databse from predetermined filters etc. So there's a couple of things I want to do with that info...

The titles are: Date, Invoice Number, Amount, Description

1) Change all the negatives numbers to positives and vice versa, but only in cells that contain numbers in a specified range
2) The download just dumps the data so therefore I need the invoice number to be displayed as number format, however i've tried the general way of highlighting and changing the format but because it's a dump of info you have to F2 in the cell and enter for it to then be seen as Number rather than General (I hope that makes sence) - the reason for the change in format is because i'm using a =LOOKUP formula to search the invoice number from that array to another...
3) Delete the full row with "PAYMENT" in the description column, but only the cells that actually contain info as in number 1) above

This seems like quiet a lot but I've got some of them working but need it so it's a just a one button job rather then several...

I can send a sample if needed...

Many thanks in advance.



I was hoping someone would be kind enough to help me out with a small dilemma.

Is there a formala or macro which would enable me to convert an 8-bit binary number to decimal and vice versa.

Example 1. 11111111 =256
Example 2. 00000001 =1
Example 3. 01010101 =85

Basically i want to be able to input a number in to a cell and then select either of two buttons. Button 1 would convert to binary, whilst button 2 would convert to decimal.

It is only necessary to have up to 8 bits of data, although anything above that would be a great asset too.

Just some help with binary. I am placing the headers below.

128 64 32 16 8 4 2 1

Thanks for your help.

In an excel column listing monthly payments, I cannot report a negative value . I need to "carry forward" the negative value by automatically adding it to the next positive cell in the column. This process should loop through the column leaving no negative numbers, leaving a zero in any cell where the negative number was originally and balance out to be the same amount as the sum of the original listing of monthly payments.

Thank you so much for your help!

How do I convert a negative number to a positive number? (without editing
every cell)

grateful for any advices on converting negative numbers with decimals to
positive; if you multiply by -1 you loose the decimals (well i do!)
with kind regards


Hi Everyone!

I have a question and I hope someone can help me.

I have a spreadsheet with several columns working together. In one of those, sometimes I get negative numbers so I need to change those numbers into Zero. I already did that with this code (where a=2, is the number of the row) and it work perfect:


Sub NegativetoZero()
Dim a As Integer
Dim LastCell As Long
a= 2
LastCell = Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row

On Error Resume Next
While a


can anyone help me to copy specific cell contents from one sheet to another automatically and vice versa? please see file attached.



Hi all,

I'm looking to establish the most effective way of telling Excel where a value is negative, replace it with 0.

I've achieved this with a custom format but obviously this does not work where the data is required for formulas.

Equally i'm aware that I can make use of an IF() statement to decide if the data is less than 0 and use the appropriate value accordingly. However where i'm using values aggregated using a simple Sum() from other workbooks this quickly creates a very bloated formula, equally I dont want to have to replicate all my Worksheets that have negative numbers to contain a version where negatives are replaced with 0's.

I appreciate this sounds's a reporting requirement unfortunately however. I'm trying to maintain a balance between keeping accurate data and generating reports with the negative numbers replaced (and replaced in any calculations).

Suggestions appreciated.



I have a chart with the alpabet in the first column in the next four columns are numbers that correspond to the letters in the 1st column. How can I use MS Word to reference my chart and convert these numbers to letter and vise versa. Also since I have 4 seperate columns of numbers. I need to be able to specify which column of numbers I want to be used to deciper my alphbet. Does anyone have any ideas on the best way to do this? Basically it is making 4 seperate decoder wheels/rings but just automated so it will decipher what is typed in Word (alpha or numeric). Thanks for you help.

How can use a formula to convert negative numbers to zero?

Is it possible to use more than one formual in a cell, such as this formual for cell D33:

=SUM(D30-D32), =IF(D33

I've been searching around and can't seem to figure out how to write a
formula that will round a negative number to 0 correctly. I used the
(MAX(0,A1) and it seemed to work from that cell but another cell pulls the
value from that cell and it was still using the negative number to calculate.

I need a formula that will round the negative number to Zero and then use
the Zero from that roundup when other cells are referring to it.

Does that make sense?

Need help!!


I would be grateul if someone could help me please:

I am trying to change the sign (+/-) of a large table of data because it has been entered incorrectly.

Do you know how I can change all the negative values to positive and vice versa?

Thank you

Thanks for help on this issue.

I need to populate a cell with a total from another cell.

If that total from another cell is a negative number I need it to be displayed as a positive number and vice versa, if it is a positive number I need it to be displayed as a negative number.


A1 is ($300)

I need A2 to diplay $300

Thanks for any help with this.

I have few huge sheets with thousands of rows of data (and lots of columns). It contains both negative and positive numbers. I would like to convert negative nubmers to positive before I can start creating pivot table on this data. Any suggestions?

Thank you,

I have downloaded a spreadsheet and need to convert a column of negative numbers to positives.

From the format cells tab, I could not locate a feature that would allow me to convert to an absolute value.

Please provide any hints or advice. Thanks,

So I have the following cells:
1 0.2
-2 0

Assuming the second line is row E, I used =MAX(0,E2/5) to round my negative results to zero. I also want to add to this formula and use it on all of my values so that positive numbers will be rounded up. For example above, assuming the first row is D, I would like the answer to be 1 instead of 0.2 (the result of =MAX(0,D2/5). I want it to be
=ROUNDUP(D2/5,0), which would equal 1.

How can I combine these to work together to round my negative values to zero, but also round my positive numbers up?

Sorry if this is confusing, as I am an excel amateur.


Is there a way to highlight a column or a row and change all of the numbers
from negative to positive or vice versa without doing the change cell by
cell? Thanks

No luck finding an answer? You could always try Google.