Excel Number Format for Lakhs With Commas

How-to basics · Updated November 19, 2026

To show Indian-style comma grouping in Excel, select the cells, press Ctrl+1, choose Number > Custom, enter #,##,##0 for whole numbers or #,##,##0.00 for two decimal places, and select OK. The format displays values in lakh-style groups without changing the numbers stored in the cells.

What lakh-style comma grouping means

In the Indian numbering system, a lakh is 100,000 and a crore is 10,000,000. After the first group of three digits from the right, later groups contain two digits. So 1,234,567 is written as 12,34,567, and 123,456,789 is written as 12,34,56,789. This differs from the international grouping pattern, which would display those values as 1,234,567 and 123,456,789.

Excel’s custom number format lets you specify that grouping pattern. The format #,##,##0 tells Excel to display a whole number with commas, while #,##,##0.00 adds two decimal places. For instance, applying the first format to the value 1234567 displays 12,34,567; the underlying cell value remains 1234567.

Formatting is useful when a report needs lakh or crore notation but calculations must still use ordinary numeric values. You can continue to add, sort, chart, and reference the cells as numbers. The commas are part of how Excel presents a value, not characters inserted into the stored number.

Apply a custom format in Excel for Windows

In Microsoft Excel for Microsoft 365 on Windows, use the Format Cells dialog to enter the custom pattern:

  1. Select the cells, column, or range you want to format.
  2. Press Ctrl+1 to open Format Cells. You can also right-click the selection and choose Format Cells.
  3. Choose the Number tab, then select Custom in the Category list.
  4. Click the Type box, enter #,##,##0 or #,##,##0.00, and check the Sample preview.
  5. Select OK to apply the format.

On a Mac, select the cells and press Command+1 to open Format Cells; the dialog’s Custom category is where you enter the code. The exact appearance of the dialog can vary slightly by Excel release, but the format code is the same. If you do not see the intended grouping in the preview, check the regional separator settings and the troubleshooting section below.

To reuse the format, select another range and choose the custom format from the list of recently used formats in Format Cells. You can also copy a correctly formatted cell and use Paste Special > Formats on another range. That copies the display format without replacing the destination cells’ values.

Choose decimals, currency, and other variants

Choose a pattern that fits the data rather than applying one format to every kind of number. The digits after the decimal point control how many places Excel displays. For example, #,##,##0.0 displays one decimal place and #,##,##0.000 displays three. A whole-number format rounds the display to a whole number, but does not round or replace the actual stored value.

  • #,##,##0 displays 1234567 as 12,34,567.
  • #,##,##0.00 displays 1234567.5 as 12,34,567.50.
  • ₹ #,##,##0.00 can display a rupee symbol before a value, though currency-symbol rendering and spacing can depend on the font and regional setup.
  • For a currency report, choosing Currency or Accounting in Format Cells and selecting the appropriate symbol can be clearer than typing a symbol into a custom format.

If you need negative values shown with parentheses or a different color, Excel custom formats can define sections separated by semicolons for positive, negative, zero, and text values. For example, a format can give positive and negative numbers different appearances while leaving the numeric value unchanged. Build and preview such a format carefully, because the sections determine how different cell contents are displayed; use a simple grouping format if the report does not need special treatment.

Formatting numbers is not converting them to lakhs

A frequent misunderstanding is that entering #,##,##0 converts a number into a count of lakhs. It does not. A value of 250000 still represents two hundred fifty thousand; it is simply displayed as 2,50,000. If the purpose is to express a measurement in units of lakhs, you must divide the value by 100,000 in a separate calculation or use a display suffix designed for that purpose.

Also distinguish lakh grouping from number scaling. In Excel custom formats, a comma at the end of a format can scale a displayed number by a factor of 1,000. For example, a format ending in a comma can make a large value appear in thousands, which is not the same as inserting Indian-style grouping separators. Do not add a trailing comma when your goal is simply to display 12,34,567 as 12,34,567. If a report needs to show values such as 12.35 lakh, decide whether readers need the original amount, a scaled display, or a separate calculated value.

Similarly, a cell that contains text such as 12,34,567 is not necessarily a number. If commas were typed into the cell as part of text, Excel may not include it in sums or use it as a numeric chart value. Applying a number format is generally preferable when the source data is numeric and only the appearance needs to change.

Regional settings and separator characters

Excel uses regional settings to determine which characters represent decimal and thousands separators. The displayed punctuation can therefore differ between computers, even when the custom format pattern is the same. In Windows desktop Excel, the Use system separators setting is under File > Options > Advanced. When that option is selected, Excel follows the system’s decimal and thousands separators; clearing it lets you specify separators in Excel. Changing these settings affects more than one cell format, so avoid changing them just to fix a single report unless you understand the wider effect.

The pattern #,##,##0.00 expresses Indian-style grouping, but the sample preview is the practical check for a particular installation. The decimal character shown may be a comma rather than a period in a locale that uses comma decimals. That does not mean the cell has become text. When sharing a workbook across regions, confirm both the grouping and decimal display on the recipient’s Excel setup, especially in printed reports and exported files.

If a workbook is opened in an application or locale that does not interpret a custom pattern as expected, it may display separators differently. Keep the underlying data numeric and include a clear heading or note such as Amount (₹) so readers know the unit and currency. A custom display format alone cannot explain whether a number represents rupees, units, or thousands.

Why the format may not appear to work

If commas are missing or the result looks unexpected, check the cell contents and format rather than retyping the values immediately. Common causes include:

  • The cells contain text. A number format does not reliably turn text strings into numeric values. Check whether the values align or behave like numbers in calculations, and convert imported text to numbers if needed.
  • The wrong cells were selected. Confirm that the entire intended range received the custom format. A cell may also have a different format applied later through a pasted range.
  • A trailing comma was added. A comma at the end of a custom code can scale display values; remove it if you only want grouping.
  • The preview uses different separators. Review Windows or Excel regional separator settings and inspect the Format Cells sample before applying the pattern.
  • The data has decimals or large values. Select a format with the required decimal places and test it using representative values, including zero and negative numbers.

To check whether a cell is numeric, try using it in a simple calculation in an empty cell or inspect how it behaves when summed. If converting imported data, keep a copy of the original column until you have verified the converted values. Do not solve a display issue by changing the source values unless a real unit conversion is intended.

Custom formats compared with formulas and text

A custom format is usually the best option when you want normal spreadsheet behavior and lakh-style display. It changes presentation while preserving the original number. A formula that divides values by 100,000 is appropriate only when the desired result is genuinely measured in lakhs; it creates a different numeric value and should be labeled accordingly.

A formula using the TEXT function can create a formatted text result, but text is less suitable for later arithmetic, sorting as a number, or charting. It may be useful for a fixed label or a sentence in a report, but it is not a substitute for formatting a numeric data column. For routine tables, charts, and calculations, keep the cells numeric and apply the custom format.

Excel for the web may preserve and display custom formats created in the desktop app, but the available controls for creating or editing custom codes can differ across web versions. If you cannot find a custom-format entry box in the browser, open the workbook in the Excel desktop app and use Format Cells. Confirm the final display in the environment where the workbook will be used.

Frequently Asked Questions

What is the Excel format code for Indian comma style?
Use #,##,##0 for whole numbers or #,##,##0.00 for two decimal places. Apply it from Format Cells > Number > Custom.

How do I show 10 lakh with commas in Excel?
Enter the numeric value 1000000 and apply #,##,##0. It displays as 10,00,000. The cell still holds the number 1000000.

Can I show rupees with lakh commas?
Yes. Use a rupee symbol with the Indian grouping pattern, or choose a currency format and select the rupee symbol in Format Cells. Check the preview because symbol spacing and separators can vary by regional settings.

Does the comma format change my calculation?
No. A number format changes the display, not the underlying numeric value. A trailing comma in a custom format is a different feature that scales the displayed number, so leave it off for ordinary lakh grouping.

Why does Excel show the number in a different grouping on another computer?
Regional separator settings and the application interpreting the workbook can affect punctuation. Check the Format Cells preview and the recipient’s regional settings, and keep the source values numeric.

Best practice for shared workbooks

Use a custom format on the numeric cells, label the units and currency in the column heading, and test the workbook with a small, representative range before formatting a large report. Include examples around important boundaries, such as 99,999, 100,000, and 1,000,000, to confirm that the display is clear. If the workbook is shared internationally, ask a recipient to verify the view in their Excel environment. These checks help preserve useful calculations while making Indian-style amounts easy to read.

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.