Converting Numbers to negative

les.61

New Member
Joined
Nov 25, 2009
Messages
4
Do you know if you can use the cell formatting to change the sign of a number? For example, the cell contains 123.45, but I want it to show as -123.45. I don’t want to use a formula to multiply by -1, because the data is being extracted from a database and I have to do a fair amount of work on the report with the formulas to do that.

I can format the number and it shows a minus but will not use the minus if I then use it in a calcualtion. I could use a calcualtion with a *-1 in it but for ease of use by other users who may take part of the Excel report it would be better if I could format the number to negative.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Formatting will do exactly what is says, format it so no amount of formatting will actually change the number for other formulas to pick up as a negative.

I would suggest you write a quick macro to rip through and multiply by -1, it will be quick and you will not have to mess with formulas.
 
Upvote 0
thanks Blade Hunter.

I thought that might be the case. I have not run many macros so how would I run a macro that changes all lines in a particular column?
 
Upvote 0
if it's just a one time thing, it's probably easier to

type -1 in a cell
copy that cell
highlight the cells you want to convert
right click - paste special - values - multiply
 
Upvote 0
Records a macro and choose to save in your personal.xls

Click the stop button as soon as you start.

Hold Alt and press F11

Find Module1 in personal.xls and paste this code in there.

From then on Alt-F8 will bring up the list of macros and you can simply double click inverse.

Code:
Sub Inverse()
Dim X As Long
Application.ScreenUpdating = False
For X = 1 To Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & X).Formula = Range("A" & X).Value * -1
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Very nice, I never knew about that :)
Yes, that's a very usefull trick.
You can obviously see all the other options, Add/Subtract/Divide etc..

Probably the most usefull thing about that little trick is converting "numbers stored as text" to actual numbers

Copy a blank cell
Paste Special - Values - Add

Adds 0 to all cells, converting "Numbers stored as text" to actual numbers.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top