Indian Number Format in Excel: Apply Lakhs and Crores

How-to basics · Updated November 20, 2026

To show a number in Indian grouping in Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, choose Number > Custom, and enter #,##,##0.00 in the Type box. Select OK. For whole numbers, use #,##,##0. This changes how numeric values display—so, for example, 1234567.5 appears as 12,34,567.50—without changing the underlying value used in calculations.

What You’ll Need

  • An Excel workbook with the values you want to format. The cells should contain numbers, not text that only looks like numbers.
  • Excel for Microsoft 365 on Windows for the steps below. Excel for Mac has a similar Format Cells dialog, while Excel for the web may offer fewer options for creating custom formats.
  • Optionally, a rupee symbol or a preferred number of decimal places if you want the display to include currency or paise.

Step-by-Step

  1. Check that the values are numbers. Click one of the cells and look at the formula bar. A number such as 1234567.5 should be stored as a numeric value, not as text containing commas. By default, numbers are often right-aligned and text left-aligned, though alignment can be changed, so do not rely on alignment alone. If the cell contains a leading apostrophe or an imported text string such as “12,34,567.50,” see the troubleshooting section before applying the format.
  2. Select the cells to change. Drag over a range, click a column heading to select a whole column, or click a single cell. To select non-adjacent ranges, hold Ctrl while selecting each range on Windows. Formatting a whole column is convenient for future entries, but avoid applying it to a very large worksheet if you only need a small range.
  3. Open Format Cells. Press Ctrl+1 on Windows, or right-click the selection and choose Format Cells. On Mac, select the cells and press Command+1, or use Format > Cells. In Excel for the web, open the Home tab and use the Number Format dropdown; if you cannot find a Custom format entry or a Type box, choose Open in Desktop App to create the format in desktop Excel.
  4. Choose a custom number format. In the Format Cells dialog on Windows, open the Number tab and select Custom in the Category list. Click the Type box. If a format code is already there, select it and replace it; do not add the new code after the existing one. The format code controls the separators and decimal places shown, not the stored value.
  5. Enter the Indian grouping pattern. For two decimal places, enter #,##,##0.00. The first comma separates the final three digits from the rest, and the next grouping repeats in pairs of digits. This is the lakh-and-crore pattern: 1234567.5 displays as 12,34,567.50, while 123456789 displays as 12,34,56,789. For whole numbers with no decimals, enter #,##,##0.
  6. Choose decimals that suit the data. In a custom format, each zero after the decimal point requires a digit to display. Use #,##,##0.0 for one decimal place, #,##,##0.00 for two, or #,##,##0.000 for three. If you want up to two decimal places without showing unnecessary trailing zeroes, use #,##,##0.##. The format rounds what is displayed; it does not round or alter the underlying value.
  7. Apply and inspect the result. Select OK and check a few cells, including a small value, a value over one lakh, and a value with decimals. You should see commas in the Indian grouping pattern, for example 9,876; 1,23,456; and 1,23,456.78. If you see the international pattern 123,456 or 1,234,567 instead, revisit the custom code and confirm it was entered exactly as shown.
  8. Optionally include the rupee symbol. To show rupees with two decimal places, use ₹ #,##,##0.00 in the Custom Type box. For example, 1234567.5 displays as ₹ 12,34,567.50. If the symbol is missing or appears as a box, use a font that supports ₹ or select a rupee currency format from the Number tab and check the result. Currency options and symbol placement can vary with your regional settings. Do not add a currency symbol to values that are counts or measurements.
  9. Reuse the format where needed. Apply the same custom format to other ranges by selecting them and repeating the steps. You can also use Format Painter on the Home tab to copy the display formatting from a correctly formatted cell: select that cell, choose Home > Format Painter, then select the destination cells. Format Painter copies other formatting too, such as font and fill, so use it only when that is appropriate.

Common Mistakes

Confusing formatting with conversion: a custom format changes only how a numeric value appears. It does not convert international comma grouping into Indian grouping in the underlying data, nor does it change the value. A cell displaying 12,34,567 still contains the number 1234567.

Applying the format to text: if a cell contains the characters “12,34,567” as text, a number format cannot regroup them. Convert the text to a number first, then apply the custom format. This commonly happens with CSV imports, copied web tables, or values entered with a leading apostrophe.

Using the wrong grouping code: #,##0 normally shows three-digit international grouping, such as 1,234,567. Use the extra grouping comma in #,##,##0 to request Indian-style display. Check the actual preview or a large value after applying the code.

Expecting the format to change calculations: changing the displayed decimal places does not change the actual precision. If a value is 12.345 and you display two decimals, Excel may show 12.35 while calculations still use 12.345. Use a rounding formula only when you deliberately need to change the calculated value.

Tips

  • Keep the source data numeric. Store 1234567 as a number and let the cell format show 12,34,567. This keeps sorting, totals, charts, and arithmetic working normally.
  • Use separate formats for different purposes. A plain number format is suitable for counts; a rupee format is suitable for money. A decimal place in a currency display can represent paise, but choose it based on what your data means.
  • Consider negative values. The basic code #,##,##0.00 will format negative values too, usually with a minus sign. If you want a particular style for negatives or zeroes, use Format Cells > Number > Custom and set up the appropriate format sections carefully; test positive, negative, and zero values before using the format throughout a report.
  • Save a reusable template. If you regularly prepare Indian-style reports, save a workbook with the needed number formats already applied. New entries in a preformatted range will usually retain the cell format.
  • Remember that readers may have different regional settings. Excel can display separators according to application and system settings. When sharing a workbook, check how it appears on the recipient’s version of Excel, especially if the custom code displays differently on another computer.

Troubleshooting

The commas do not change. First confirm that the selected cells contain numbers. If they are text, and the text is in A2:A20 with comma-separated values such as 12,34,567.50, put this formula in B2 and fill it down:

=VALUE(SUBSTITUTE(A2,",",""))

For example, if A2 contains the text 12,34,567.50 and your regional settings use a period for decimal values, B2 returns the numeric value 1234567.5. Apply #,##,##0.00 to the results. If your source includes a rupee symbol or uses a comma as the decimal separator, clean or adapt the imported text to match your Excel regional settings before converting; the formula above only removes commas.

The grouping still looks international. Reopen Format Cells and verify the Type box contains #,##,##0 or #,##,##0.00, without extra characters. Check the displayed result with a value of at least seven digits. If Excel still uses a different grouping, your regional settings or Excel build may be affecting custom separators. Try the same code in a new blank workbook, or use the Windows regional number settings configured for India, then reopen Excel and check again.

The rupee sign is not displayed correctly. Change the cell font to a common font that supports the Indian rupee symbol, such as Aptos or Arial, and confirm the symbol was entered in the custom format. If the symbol remains unavailable, select a currency format with the rupee symbol from Format Cells and check whether that format groups digits as required.

The values change when pasted or opened on another device. CSV files do not preserve Excel cell formatting, so a custom format will not travel with a CSV export. Save the workbook as an Excel file such as .xlsx to retain cell formats. When importing a CSV, use Excel’s import options and verify the decimal and thousands separators before loading the data.

Frequently Asked Questions

What is the Indian number format in Excel?
It groups the rightmost three digits, then groups digits in pairs to the left. For example, 12345678 displays as 1,23,45,678, or one crore, twenty-three lakh, forty-five thousand, six hundred seventy-eight.

What custom format code shows lakhs and crores?
Use #,##,##0 for whole numbers or #,##,##0.00 for two decimal places. Add ₹ at the start if you also want the rupee symbol to display.

Will changing the format affect formulas or totals?
No. Number formatting changes only the appearance. Excel continues to use the underlying numeric value in calculations, so 12,34,567 is still the number 1234567.

Can I use Indian number formatting in Excel for the web?
You can select cells and check Home > Number Format > More Number Formats. If the web version does not show a Custom category or let you enter a format code, open the workbook in desktop Excel, apply the code there, and save it.

Why does Excel keep showing my imported number with the wrong commas?
The imported value may be text, or Excel may have interpreted the decimal and thousands separators using different regional settings. Convert text to a numeric value, confirm the decimal separator, and then apply the Indian custom format.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.