Spreadsheets

Indian number format in Google Sheets: lakh grouping and rupee

Set the spreadsheet locale to India and use a custom number format in Google Sheets to show lakh grouping and the rupee sign, and see how it differs from Excel.

Editorial illustration for Indian number format in Google Sheets: lakh grouping and rupee

In Google Sheets, open File, Settings and set Locale to India; number formats like 1,234,567 then follow Indian grouping where the locale supports it. If you also want a rupee sign or conditional lakh grouping, add a custom number format under Format, Number, Custom number formats.

Step 1: set the locale for this spreadsheet

  1. Open the spreadsheet and choose File, then Settings.
  2. Under Locale, pick India.
  3. Click Save settings. Sheets may reload.
  4. Type 1234567 in a blank cell, apply Format, Number, Number, and look at the result.

If it shows 12,34,567, you are done. Treat that result as the test, because grouping depends on what the locale offers and how your cells are formatted. The locale is stored with the spreadsheet itself, not with your computer, which is the main difference from Excel. Anyone who opens the file sees the same grouping.

The locale also changes a few other things:

  • The default date format (day before month is the usual convention in India).
  • The currency symbol offered under Format, Number, Currency.
  • How some functions treat text dates and decimals, so recheck imported columns after switching.

Step 2: a rupee sign with a custom number format

Go to Format, Number, Custom number formats, type the pattern, and click Apply.

PatternShows for 1234567Use when
#,##012,34,567 (with India locale)Plain amounts
"₹"#,##0₹12,34,567Amount columns
"₹"#,##0.00₹12,34,567.00Totals with paise
[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##012,34,567Locale cannot be changed

Does the Excel-style conditional format work in Sheets?

Sheets accepts conditions in square brackets, like Excel, so the lakh pattern in the last row of the table should work if you cannot change the locale. It carries the same limits: negatives and values from 100 crore upwards are not grouped correctly, and text values ignore the format. Try it on a few test values before using it on a full ledger.

How does Sheets differ from Excel here?

  • Excel follows the Windows regional setting; Sheets follows the spreadsheet's own locale.
  • Changing the locale in Sheets affects only that file, not your other sheets.
  • Excel has Display Units for charts; chart axes in Sheets have their own number format options, and you still need a helper column to scale to lakhs.
  • Sheets also lets you use the same locale on phone and laptop because it travels with the file.

What if the rupee sign shows up in the wrong place or twice?

Do not combine the Currency format with a custom pattern that already has the symbol. Pick one. Format, Number, Currency adds the symbol and grouping from the locale in one step, and it is the easiest option for a single currency. Use the quoted pattern only when you want control, for example a symbol in front of a total but not in front of every row. A symbol typed into the cell by hand turns the value into text, so SUM will skip it; always apply the symbol through formatting.

How do I show crores like 2.45 Cr in Sheets?

Sheets has the same limit as Excel: a number format can add text but cannot divide. Use a helper column with =C2/10000000 and the custom format 0.00" Cr". For a hypothetical ₹2,45,00,000, the cell shows 2.45 Cr while the original column still holds the full amount. Keep the unit in the column heading so nobody reads it as a plain number.

A short checklist

  1. Set Locale to India in File, Settings.
  2. Apply Number or the rupee custom format to the amount column.
  3. Test one large and one small value, plus a negative one.
  4. If your friends use a different locale, ask them to confirm the file looks right on their side.
  5. Keep amounts in a single currency per column, or add a currency column when you track foreign spend.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports