Displaying Lakhs and Crores in Excel

[Radhesh]

I am sharing what i think you be useful for us.

Dear Friends,

Presently in excel if you want to display figure in lakhs you have to
divide by figure by 1 Lakh, in case of thousand or million you have to
simply go to
Format Cell —-> Number ——> Custom, there in Type option you have to
type 0.00, or 0,(single comma for thousand) or 0.00,, or 0,, (double
comma for million) and so on.

There is absolutely no need to divide the figure by million or
thousand the figure remain in full but only thing is that it displayed
or appear in million or thousand.

Now is that similar type of formating possible for displaying figure
in Lakhs through visual basic editor.

Second Option -
Here's a useful Excel tip for users in India and other places that use
number formats like 100,00,00,000 instead of 1,000,000,000:

You may have faced the problem of Excel always displaying amounts in
millions and not in lakhs e.g. 1,000,000 instead of 10,00,000. Here's
a trick to get them to display correctly:

Right-click on the cells which you want to display as lakhs, and click
on Format Cells, then go to the Custom category and enter the below
formats:
For Lakhs (+ve and -ve):

With 2 decimals:

[>99999]##\,##\,##0.00;[<-99999.99]-##\,##\,##0.00;##,##0.00

Without decimals:

[>99999]##\,##\,##0;[<-99999.99]-##\,##\,##0;##,##0

For Lakhs and crores (+ve)

[>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;##,##0.00

For Lakhs and crores (-ve)

[<-9999999](##\,##\,##\,##0.00);[<-99999](##\,##\,##0.00);##,##0.00

There is an easier way to change the global settings from Settings >
Control Panel > Regional Settings > Numbers, but that will affect all
your Excel sheets and the change will be visible only on your system
and not on your files which you send to others.
Happy XL-ing :-)


Thank You

Regards,
Radhesh Bhoot

[Anju Grewal]

Thanks a lot Radhesh....:) we will surely try this.

--
Find eNewsletters of ICSI Mysore at: http://www.icsi.edu/NewsEvents/enewsletters/tabid/1757/Default.aspx  AND www.esnips.com/web/icsimysore

You received this message as you are subscriber. To unsubscribe email to: csmysore-u...@googlegroups.com

--

P Please do not print this email unless it is absolutely necessary. Spread environmental awareness. 

Thanks & Regards

Anju Grewal

[Ashish Aggarwal]

thanx u so much. Thats really going to be helpful. Do post more such tips if you have.

 

regards,

 

Ashish.

On Sat, Jun 4, 2011 at 12:40 AM, Radhesh <radhesh...@gmail.com> wrote:

--
Find eNewsletters of ICSI Mysore at: http://www.icsi.edu/NewsEvents/enewsletters/tabid/1757/Default.aspx  AND www.esnips.com/web/icsimysore

You received this message as you are subscriber. To unsubscribe email to: csmysore-u...@googlegroups.com

--
Ashish Aggarwal

[Madhur Agrawal]

The below i think a more easier option,

Go to control Panel -> Regional & Language Options -> Customize -> Digit Grouping (select 12,34,56,789) -> Currency -> Currency Symbol (type Rs.) -> Apply -> Ok

Not: Existing excel sheet formating wont change.

Regards,

Madhur N. Agrawal

[Radhesh Bhoot]

Dear Madhur,

Yes, you are right in some respect and i am glad that you already knew it but that was not what i meant. 

What i meant was to show Rs. 1,000,000 as Rs. 1.00 million in the cell but in the formula bar it will show full amount i.e 1,000,000

Regards,

Radhesh

Radhesh Bhoot

[Radhesh Bhoot]

Dear Friends,

U are welcome and if i come across some more of such things, will surely post here.

Regards,

Radhesh

--
Radhesh Bhoot