+/- In a Number Format
Showing a minus sign in a cell is easy. You just format the cell as General and negative numbers will display the minus sign. For a plus sign, you’ll need a custom number format, like the one shown below.
Remember that custom number formats have up to four semi-colon separated values; positive number format, negative number format, zero format, and text format.
Thanks, Jeff, for the good question.
Lot’s of info about custom cell formats here:
Using Formatting to Change the Way Numbers, Dates, Times, and Text Appear in Microsoft Excel
http://support.microsoft.com/default.aspx?scid=/support/excel/content/formats/default.asp
Jamie.
–
thanks, i ran across this need a few years ago…i run some golf tournaments, and have needed to show “E” or “Even” as a zero (when there’s a push in a match)…
+0;-0;”E” or +0;-0;”Even” works great…i wish i was reading this column a few years ago…would have saved me time
Mike
You can also override the pos;neg;zero;alpha parts of the custom number formats by entering your own conditions in the formula. For example, to show a different number of decimal digits for larger and smaller numbers, use
[>100]0;[>1]0.0;0.00
To show items in color, use a format like this:
[>100][blue]0;[>1][red]0.0;[green]0.00
- Jon
Custom formats like this are a great way to hide chart data labels that you don’t want to show. To hide zero values, only provide a format for nonzero numbers:
0;-0;;
To hide numbers greater than 1000 (maybe useful for a customized axis labels):
[>1000]“”;General
If you don’t use “” here, it will default to General even though you’ve specified a condition.
- Jon
Dear Sir,
How to use custom number formats in MS Excel to get the following output in Indian currency :
12258 as 12,258 (i.e, Twelve Thousand Two Hundred & Fifty Eight).
125458 as 1,25,458 (i.e, One Lakh Twenty Five Thousand Four Hundred & Fifty Eight).
1125458 as 11,25,458 (i.e, Eleven Lakh Twenty Five Thousand Four Hundred & Fifty Eight).
Please advice.
Sandeep
Calcutta, India
Sandeep: Try here
http://www.google.com/groups?&threadm=umdRB2laDHA.1940%40TK2MSFTNGP10.phx.gbl
I’ve written a simple equation, but wheneber the result is negative i.e. ($12,500) I want it to come up as ‘-’.
Any suggestions?
Bill,
Try this numberformat:
0;-;0
Cheers,
Rob
what is the system to convert number into lakh format, i.e 12,12,54,566.00.
Lots of information about this subject here. http://www.dicks-blog.com/archives/2004/07/30/in-a-number-format/#comments
All about number formatting in excel is covered.
Lots of information about this subject here.
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=414
All about number formatting in excel is covered.
Thanks for the tip. I was neeeding to do this for a price sheet I was making. You rock!
good help but couldnt access
http://www.dicks-blog.com/archives/2004/07/30/in-a-number-format/#comments
pl give another address
I need to convert number in to lakhs in excel
i.e if I type 1 in a cell than the cell should automaticaly show 100,000.00
Can any one help
I need to convert number in to lakhs in excel
i.e if I type 1 in a cell than the cell should automaticaly show 100,000.00
Can any one help please
Dear Sir,
How to use custom number formats in MS Excel to get the following output in Indian currency :
12258 as 12,258 (i.e, Twelve Thousand Two Hundred & Fifty Eight).
125458 as 1,25,458 (i.e, One Lakh Twenty Five Thousand Four Hundred & Fifty Eight).
1125458 as 11,25,458 (i.e, Eleven Lakh Twenty Five Thousand Four Hundred & Fifty Eight).
Please advice.
Archana
Panchkula,INDIA.
Archana,
Try this. May help
[>=10000000] ##\,##\,##\,###.00;[>=100000]##\,##\,###.00;##,###.00
CA Kanwaljit Singh Dhunna
Punjab
Archana,
You might find this of some use. Here is a event procedure to automatically perform the Indian-style of formatting numbers, up to a total of 15 significant digits (which is the total number of digits making up the whole number and decimal portion of the value), that I posted not too long ago to a blog run by Yogesh Gupta at this link…
http://www.yogeshguptaonline.com/2009/10/excel-number-format-indian-style-comma.html#comments
Right click the tab at the bottom of the worksheet that is to have this functionality, select View Code from the popup menu that appears and then copy/paste the following into the code window that appears…
Dim R As Range
Set R = Range("A:A,C3:E10")
If Not Intersect(Target, R) Is Nothing Then
With Target
.NumberFormat = Trim(Replace(Format(String(Len(Int(.Value)) - 1, _
"#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@"), _
" \,", "")) & "0.00"
End With
End If
End Sub
Just change the Range address in the Set statement to cover the range of cells you want to have this functionality. My sample above is set to apply the Indian-style Number Format to all of Column A plus just the cells in range C3:E10. After entering any number within this range of cells, it will be reformatted into the Indian style for a number.