Format Numbers : Excel Currency Format

You can add currency symbol to your numbers in excel.

Open Number Format dialog box using Format Cells option or just press Ctrl+1

1. Click Accounting
2. Choose currency symbol from drop down

In case you do not get your currency symbol from drop down, add your symbol using custom formating option in the same dialogbox
1. Click Custom
2. Type - "Rs "#,##0_);[Red]("Rs "#,##0)

I use "Rs " to show numbers in Indian Currency. You can replace it with your symblol.

Excel Number Format : Indian Style Comma Separation

Yesterday I got a comment asking for custom number format for Indian style comma separation. While I responded to question by providing the custom number format I knew that the answer does not handle the complete range of numbers and can not be applied in all the cases. This forced me to look around for a solution that can be applied for all kind of numbers.

Just for the information of people who are not aware of Indian Style of comma separation, I have produced table below which explains Indian style formatting for various numbers. The basic rule is that first comma separation happens at 3 digits i.e. 1000 then it happens after every 2 digits. Check out table below for better understanding.

Number	Formatted As
10	10.00
100	100.00
1000	1,000.00
10000	10,000.00
100000	1,00,000.00
1000000	10,00,000.00
10000000	1,00,00,000.00
100000000	10,00,00,000.00
1000000000	1,00,00,00,000.00
10000000000	10,00,00,00,000.00
100000000000	1,00,00,00,00,000.00

Soon I realised that there is no single custom format available for handling all kind of number length and we need separate format for it depending upon the number of digits. Doing this manually and accuratly every time is very difficult. This leaves only one option to automate this through a macro. I got a Macro code for doing this and modified and tested that for long numbers.



I am sharing the macro code with you. You will need to select the numbers and run macro to format them as per India style comma sepration.

Sub IndianNumberFormat() For Each c In Selection Select Case Abs(c.Value) Case Is < 100000 c.Cells.NumberFormat = "##,##0.00" Case Is < 10000000 c.Cells.NumberFormat = "#\,##\,##0.00" Case Is < 1000000000 c.Cells.NumberFormat = "#\,##\,##\,##0.00" Case Is < 1000000000 c.Cells.NumberFormat = "#\,##\,##\,##0.00" Case Is < 100000000000# c.Cells.NumberFormat = "#\,##\,##\,##\,##0.00" Case Else c.Cells.NumberFormat = "#\,##\,##\,##\,##\,##0.00" End Select Next c End Sub

For easy application of this code you need to save it as a excel addin or add it to your personal macro book. Adding a custom toolbar for this code will make it easy to apply. You can chage the number of decimal places by changing the number of zeros at the end of custom number format given above.

Excel Number Format : Telephone numbers with leading + sign

This is in continuation of my earlier post Custom Formating - Excel Number Format

Excel does not display the leading + sign entered by users, similarly the leading zeros are also not recoganised by excel. However custom formating trick can help you do this. Refer to the following table, where in Phone number 9971112814 has been formated differently to display different formats for the same cell contents.

91 is country code for India, you can change it to whatever code you want to display the number accordingly.

Format	Displayed as
+91 0000000000	+91 9971112814
+91(0)0000000000	+91(0)9971112814
+91(0)000-000-0000	+91(0)997-111-2814
0091 0000000000	0091 9971112814

Custom Formating - Excel Number Format

Excel stores numbers as normal numbers only, however you can view them as you want based on how do you format the cells.



If you see there are pre defined number format options available when you choose cell format option, however you can use custom format option if available formats does not server your requirement.



There are four sections of format codes. The sections are separated by semicolons ";" First section defines the formats for positive numbers; Second section defines negative numbers; third section defines how to show zero values; and forth one define how to diplay text. If you specify only two sections, the first is used for positive numbers and zeros, and the second is used for negative numbers. If you specify only one section, all numbers use that format. If you want to skip a section, include the ending semicolon for that section.

# displays only significant digits and does not display insignificant zeros.

0 (zero) displays insignificant zeros if a number has fewer digits than there are zeros in the format.

? adds spaces for insignificant zeros on either side of the decimal point so that decimal points align.

? can also be used for fractions that have varying numbers of digits.