Displaying Lakhs and Crores in Google Sheets

Question

I would like to use Google Sheets for its convenient global access, but I really need to display numbers in Lakh and Crore (Indian style) format to avoid confusion among managers.

150,000 should display as 1,50,000 and 12,000,000 should display as 1,20,00,000.

I tried setting the format to ##,##,##,000 but my number still displays as 12,000,000. I couldn't find anything in the docs.

Does anyone know of a way to accomplish this?

This question isn't about programming related to Google Apps, so it is off-topic for Stack Overflow. It belongs on [WebApps](//webapps.stackexchange.com/). — Mogsdad, May 02 '16 at 22:01

score 475 · Answer 1 · edited Nov 08 '18 at 00:36

475

This works in Google Sheets for sure. Should work in Excel too.

With the appropriate cells selected, navigate to:

Google Sheets : Format -> Number -> More Formats -> Custom Number Format
Excel : Format -> Cells -> Custom -> [Custom text box]

Enter one of the following and Apply:

A] For Lakhs and Crores with the Rupee symbol and decimals

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

B] For Lakhs and Crores with the Rupee symbol and without decimals

[$₹][>9999999]##\,##\,##\,##0;[$₹][>99999]##\,##\,##0;[$₹]##,##0

C] For Lakhs and Crores without the Rupee symbol and with decimals

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

D] For Lakhs and Crores without both the Rupee symbol and decimals

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

Edit: I've tested these on both Google Sheets and Excel. Negative numbers work as well.

edited Nov 08 '18 at 00:36

Anish Kuriakose

9
4

answered Jan 09 '16 at 07:17

BigDaddy

4,867
2
14
4

21

This should be modified to work with negative values as well. – enkash Jun 14 '16 at 05:04
151

Google in all its glory totally forgot about 17% of world's population... – ADTC Sep 07 '16 at 12:32
13

This formula is not working for negative numbers like -1,00,000. Can anyone expand this answer further for negative numbers – Palani Jun 25 '17 at 03:56
Not Working for More than 100 Cr as well as negative numbers, can somebody please modify this – Anshul Bansal Mar 29 '19 at 11:19
1

for negative numbers, separately formatting formulas are mentioned in this answer: https://stackoverflow.com/questions/36804356/how-to-format-numbers-as-lakhs-and-crores-in-excel-google-spreadsheet-when-the?rq=1 .. if we want both positive & negative, there is a limit of regex patterns u can include ( upto 4 ).. so google treats it as invalid format.. solution is: if you are aware of which columns are negative.. apply negative formatting to them.. for other columns.. apply positive formatting.. – a3.14_Infinity Dec 10 '19 at 05:41
1

Thank you very much. The same problem exists when you search `1,00,000 + 1,00,000` on Google, it'll not show `2,00,000`, but if you search `100,000 + 100,000` it'll show 200,000. They don't seem to care about Indian it seems. – Ganesh Satpute Apr 22 '20 at 12:08
In Google sheets, if I use this, I cannot perform mathematical operations, I get an error saying `Function MINUS parameter 1 expects number values. But '54,00,000' is a text and cannot be coerced to a number.` – Ganesh Satpute Apr 22 '20 at 12:11
We do not need these formats anymore, Google now has inbuild different currency formats. Google Sheets : Format -> Number -> More Formats -> More Currencies – shortduck Aug 07 '20 at 17:23
2

@shortduck The Google Indian rupee format, will only apply the symbol. It will still format the numbers in millions, billions instead of lakhs & crores. – Sumith Nov 27 '20 at 10:16
1

Plus one. Also, make sure, you select custom number format, not custom currency format. – Khushhal Dec 19 '20 at 04:20
Need to display with RED (negative) and GREEN (positive) colors. How do I modify them? – Code Guy Dec 26 '20 at 02:56
@Anish Kuriakose - gr8 job. My sheet looks gr8 now. – Srikumar Krishna Iyer Feb 23 '21 at 12:13
It seems like a deliberate attempt by all major companies like microsoft, apple, google to push everyone to miles, millions and other unscientific American standards. – Gajendra D Ambi May 02 '21 at 00:59
For A} If the number is negative it does not format it correctly. Positive number works fine – Prateek Jun 23 '21 at 11:29
It's not correct, it still display 1 lac as 100,000 but in Indian currency format, it supposed to be 1,00,000 – Jitendra Pancholi Sep 18 '21 at 11:51

Jitendra Pancholi · Accepted Answer · 2021-09-18T11:55:44.217

Indian currency format displays numbers like below

1 - One Rupee
10 - Ten Rupee
100 - One Hundred Rupee
1,000 - One Thousand Rupee
10,000 - Ten Thousand Rupee
1,00,000 - One Lac Rupee
10,00,000 - Ten Lac Rupee
1,00,00,000 - One Crore Rupee

So below formats seems to be correct.

To show positive and negative INR/Rs. up to Lakh

[>99999][$₹]##\,##\,##0.00;[<-99999][$₹]##\,##\,##0.00;[$₹]##\,##0.00

To add support for positive lakhs and crores

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

Add support for negative lakhs and crores

[<-9999999][$₹]##\,##\,##\,##0.00;[<-99999][$₹]##\,##\,##0.00;[$₹]##,##0.00

For Microsoft excel, add the above format at Format -> Cells -> Custom -> [Custom text box] Below screenshot demonstrates above custom format in use:

Reference: https://www.raghunayak.com/2020/07/how-to-show-inrrs-in-lakh-crore-format.html

That's great! One can simply add further instances of `,##\` to each side if they need to support larger numbers. — Adam Starrh, Sep 13 '21 at 18:17

player0 · Answer 3 · 2021-11-14T01:16:53.477

new formula approach:

works with numeric numbers
works with plain text numbers
works with text
works with empty cells
works with negative values
works with zeros
works with scientific notations
works with decimals
works up to Shankh
works up to 10^±50

indian separator system:

=INDEX(IF(IFERROR(N(ABS(A1:A*1)))>0, REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(
 REPT(0, 50)&"×"&TEXT(A1:A, "0"), REPT("(..)", 24)&"(...)$", 
 JOIN(",", "$"&SEQUENCE(25))), "(.*×,?)", ), "-,", "-")&
 IFNA(REGEXEXTRACT(A1:A&"", "(\.\d{1})")), A1:A&""))

indian short currency:

=INDEX(IF((IFERROR(N(ABS(A1:A*1)))>0)+(IF(ISBLANK(A1:A),,IFERROR(A1:A*1, 1)=0)), 
 REGEXREPLACE(TEXT(TRUNC(IFNA(A1:A*(10^-(
 VLOOKUP(LEN(TEXT(INT(ABS(A1:A*1)), "0")), 
 SEQUENCE(8, 1, 6, 2), 1)-1)), A1:A), 1), "0.#"), "(\.)$", )&" "&
 HLOOKUP(LEN(TEXT(INT(ABS(A1:A*1)), "0")), {0, SEQUENCE(1, 8, 4, 2); 
 SPLIT("Rp♦Rp♦L♦Cr♦Arab♦Kharab♦Nil♦Padma♦Shankh", "♦")} , 2), ""&A1:A))

both indian systems combined:

=INDEX(IF((IFERROR(N(ABS(A1:A*1)))>0)+(IF(ISBLANK(A1:A),,IFERROR(A1:A*1, 1)=0)), 
 REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(
 REPT(0, 50)&"×"&REGEXREPLACE(TEXT(TRUNC(IFNA(A1:A*(10^-(
 VLOOKUP(LEN(TEXT(INT(ABS(A1:A*1)), "0")), 
 SEQUENCE(8, 1, 6, 2), 1)-1)), A1:A), 1), "0"), "(\.)$", ), REPT("(..)", 24)&"(...)$", 
 JOIN(",", "$"&SEQUENCE(25))), "(.*×,?)", ), "-,", "-")&
 IFNA(REGEXEXTRACT(A1:A&"", "(\.\d{1})"))&" "&
 HLOOKUP(LEN(TEXT(INT(ABS(A1:A*1)), "0")), {0, SEQUENCE(1, 8, 4, 2); 
 SPLIT("Rp♦Rp♦L♦Cr♦Arab♦Kharab♦Nil♦Padma♦Shankh", "♦")} , 2), A1:A&""))

english demo sheet

non-english demo sheet

score 2 · Answer 4 · answered Nov 10 '21 at 02:52

unfortunately, internal formatting is able to work only with 3 types (see more here) if you need to get more from indian currency system you can use a formula like below either for separator system or short currency system respectively

indian separator system in B2:

=ARRAYFORMULA(REGEXREPLACE(REGEXREPLACE(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(QUERY(
 REGEXEXTRACT(REGEXREPLACE(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(QUERY(IFERROR(
 REGEXEXTRACT(A2:A, REPT("(.)", IF(LEN(A2:A)=3, LEN(A2:A)-4, LEN(A2:A)-3))), "0"), 
 "select "&JOIN(",", "Col"&SORT(SEQUENCE(MAX(LEN(A2:A)-3)), 1, )))),,9^9)), " ", ), 
 "(.{2})", "$1,"), REPT("(.)", IF((LEN(A2:A)-3)+ROUNDDOWN((LEN(A2:A)-3)/2)<1, 1, 
 (LEN(A2:A)-3)+ROUNDDOWN((LEN(A2:A)-3)/2)))),
 "select "&JOIN(",", "Col"&SORT(SEQUENCE(MAX((LEN(A2:A)-3)+
 ROUNDDOWN((LEN(A2:A)-3)/2))), 1, )))),,9^9)), " ", ), "^,", )&","&IFNA(
 REGEXEXTRACT(A2:A, "...$"), IF(A2:A="",,TEXT(A2:A, "000"))), "^0,$", ))

indian short currency in C2:

=ARRAYFORMULA(IFNA(ROUND(A2:A*VLOOKUP(LEN(A2:A), {SEQUENCE(19), 
 {1; 1; 1; 1; 1; 10^-5; 10^-5; 10^-7; 10^-7; 10^-9; 10^-9; 
  10^-11; 10^-11; 10^-13; 10^-13; 10^-15; 10^-15; 10^-17; 10^-17}}, 2, 1), 2)&" "&
 VLOOKUP(LEN(A2:A), {SEQUENCE(19), 
 {"Rp"; "Rp"; "Rp"; "Rp"; "Rp"; "L"; "L"; "Cr"; "Cr"; "Arab"; "Arab"; 
  "Kharab"; "Kharab"; "Nil"; "Nil"; "Padma"; "Padma"; "Shankh"; "Shankh"}}, 2, 1)))

side note: ROUND is set to 2 decimal places. this can be set to 0, or ROUND can be completely removed, or it can be replaced by TRUNC if needed

demo sheet

score 0 · Answer 5 · answered Jul 03 '23 at 21:07

July 2023: Another option is to use formula on cell value:

=CONCATENATE("₹", 
 if(abs(B3)<100000,B3,
  if(abs(B3) <10000000,text(B3/100000,"#.0")&" Lakh",
   if(abs(B3)<=1000000000,text(B3/10000000,"#.0")& "Crore", 
      text(B3/10000000,"#.0")& "Crore"))