To show numbers in Excel’s Indian grouping system, select the cells, press Ctrl+1, choose Custom, and enter #,##,##0 for whole numbers or #,##,##0.00 for two decimal places. This changes how numbers look—not their underlying values—so you can still calculate with them.
How Indian number grouping works
Most number formats place commas after every three digits from the right: 1,234,567. Indian grouping places a comma after the final three digits, then after each pair of digits to the left. The same number is therefore written 12,34,567. For a larger example, 12345678 appears as 1,23,45,678.
This grouping is commonly used with the Indian place-value terms lakh and crore. One lakh is 100,000, and one crore is 10,000,000. Thus 12,34,567 is twelve lakh thirty-four thousand five hundred sixty-seven, while 1,23,45,678 is one crore twenty-three lakh forty-five thousand six hundred seventy-eight. Excel stores these as ordinary numeric values; the format only changes the displayed separators.
Indian grouping is not a different kind of arithmetic or a special Excel data type. It is a number-display convention. A cell containing 12345678 remains that number whether it is displayed as 12,345,678, 1,23,45,678, or ₹1,23,45,678.00.
Apply an Indian format in Excel for Windows
In Microsoft Excel for Microsoft 365 on Windows, apply a custom number format from the Format Cells dialog:
- Select the cells, column, or range you want to format.
- Press Ctrl+1 to open Format Cells. You can also use Home > Number and select the small dialog box launcher in that group.
- Choose the Number tab, then select Custom in the Category list.
- Enter
#,##,##0in the Type box for whole numbers, or#,##,##0.00if you want two decimal places. - Select OK and check a few values in the sheet.
For example, a cell with the value 1234567 should display as 12,34,567 with the whole-number format, and as 12,34,567.00 with the two-decimal format. A value such as 12345678 should display as 1,23,45,678. The actual cell values remain available for formulas, sorting, and charts.
On a Mac, select the cells and press Command+1 to open Format Cells, then use the Number tab and Custom category. If you are using Excel for the web and cannot create or edit the custom format you need, open the workbook in the desktop app; web features can differ from desktop Excel.
Choose a format for decimals, negatives, and zeros
The format code determines how many decimal places Excel displays. The code #,##,##0 rounds the display to a whole number, but it does not round or remove decimal places from the stored value. For example, a stored value of 1234.56 may display as 1,235, yet a formula still uses 1234.56. Use #,##,##0.00 when two visible decimal places matter, such as in a report showing rupee amounts.
You can use optional digit placeholders when you do not want Excel to show unnecessary zeroes after the decimal point. For example, #,##,##0.## shows up to two decimal places, but omits them when the value is a whole number. A value of 1234.5 would display as 1,234.5, while 1234 would display as 1,234. Confirm the result with representative values before using a format in a formal report.
Custom formats can also specify separate displays for positive numbers, negative numbers, and zero values. This is useful when a report needs negatives in red or wants zero shown as a dash. Such formats can be harder to read and maintain, so begin with a simple grouping code unless you have a clear reporting requirement. Keep the original values intact when presentation rules change.
Display rupees and other currency amounts
For a rupee amount, you can add a currency symbol to an Indian grouping format, for example ₹ #,##,##0.00. The spacing and exact appearance of the symbol can depend on the font and regional settings. For a more consistent currency display, open Format Cells and use the Currency or Accounting category, then choose an appropriate symbol and decimal-place setting if it is available.
Number format and currency are related but not identical. A custom format that adds a rupee symbol is still only a display choice; it does not convert another currency into rupees. If the value represents dollars, euros, or another currency, changing its symbol alone would make the display misleading. Currency conversion requires an exchange rate and a calculation, not just a number format.
When sharing a workbook, check that the symbol and grouping appear as intended on the recipient’s computer. Regional settings, fonts, and Excel versions can affect how formats are interpreted or displayed. For documents that must look identical across systems, consider exporting a final copy to PDF after reviewing it.
Formatting numbers is different from converting text
A custom format works on numeric values. If a cell contains the text string “1234567” rather than the number 1234567, applying #,##,##0 may not add grouping because Excel is treating the contents as text. Text can result from importing a CSV file, copying data from a website, or including an apostrophe before a value.
To check, select a cell and look at its alignment and the formula bar, but do not rely on alignment alone: formatting can change alignment, and Excel may display imported data in different ways. A small warning indicator may also offer a Convert to Number option. Before converting a large range, make a copy or test a few cells, especially if the text contains commas, decimal points, spaces, or currency symbols.
One option is to use Data > Text to Columns and complete the wizard, checking how Excel interprets the data before selecting Finish. You can also use Excel’s Convert to Number prompt where it appears. The safest method depends on the source and your regional settings. Once the cells are genuine numbers, apply the Indian format and verify that totals and other calculations behave as expected.
Regional settings and compatibility
Excel’s display can be influenced by Windows regional settings and the workbook’s number-format code. In Windows, Excel commonly follows system choices for decimal and thousands separators unless the application is configured to use different separators. A comma in a custom format may therefore be interpreted or shown differently in some environments. If the format does not behave as expected, check the regional settings on the computer and test the workbook in the version and locale where it will be used.
Custom format codes may be stored with locale-related details, and Excel can adapt their presentation when a workbook is opened under another regional setup. That is why a workbook can look different across computers even when its numeric values are unchanged. If a file is shared widely, test it on a second computer or ask the recipient to confirm the display before relying on it for a published report.
Excel for the web, desktop Excel for Windows, and Excel for Mac may not expose every formatting control in exactly the same place. The desktop Format Cells dialog is the most direct route for entering a custom code. If a format already exists in a workbook, it may still display in another version even if that version provides fewer options for editing it.
Common problems and how to fix them
- The commas still appear in groups of three: Confirm that you entered the Indian pattern, such as
#,##,##0, under Custom rather than selecting a standard thousands-separated format. - Nothing changes: Check whether the cells contain numbers or text. Convert text values to numbers before applying a numeric format.
- Decimals seem to disappear: A format ending in zero decimal places hides decimals in the display. The stored value is usually unchanged; choose a format with decimal placeholders to show them.
- A number appears rounded: The display may be rounded to the selected number of decimal places. Increase the displayed decimal places to inspect the value, or select the cell and review the formula bar.
- The currency symbol is wrong: Formatting does not perform currency conversion. Choose the correct symbol, and ensure the underlying amount is actually in that currency.
- The layout changes after sharing: Check regional settings and the recipient’s Excel version. For a fixed visual copy, save or export a reviewed PDF.
A common misconception is that commas inserted by a number format become part of the value. They do not: Excel still stores a numeric value, so SUM and other calculations can use it normally. Conversely, if commas are typed into a cell and Excel stores the entry as text, calculations may not treat it as a number. Keeping values numeric and using formatting for presentation avoids that problem.
When to use Indian grouping instead of Western grouping
Use Indian grouping when the intended audience reads amounts or counts using lakh and crore conventions—for example, an India-focused budget, sales report, invoice, or population summary. Use Western grouping, such as 12,345,678, when that is the expected convention for the audience or when a reporting specification requires it. Neither format changes the quantity; the choice is about readability and consistency.
For a workbook used by people in multiple regions, agree on the display convention before building charts, printouts, and dashboards. You may keep one numeric data table and create separate presentation sheets with different formats for different audiences. Avoid manually inserting commas into the data: it makes later sorting, calculations, and currency changes more error-prone than applying a number format.
Frequently Asked Questions
What is the Excel format code for Indian commas?
For whole numbers, use #,##,##0. For two decimal places, use #,##,##0.00. Enter the code in Format Cells > Number > Custom and confirm that it displays correctly in your Excel environment.
How do I show 1 lakh as 1,00,000 in Excel?
Enter the numeric value 100000, then apply the custom format #,##,##0. Excel should display 1,00,000 while retaining 100000 as the underlying value.
Can Excel display rupees with Indian commas?
Yes. Apply an Indian grouping format and use a rupee currency format or add the ₹ symbol to a custom format, then check the result. This changes the appearance only; it does not convert the amount into rupees.
Why is my Indian number format not working?
The cells may contain text, the format code may have been entered in the wrong category, or regional settings may affect separators. Check that the value is numeric, enter the Indian grouping code under Custom, and test a known value such as 1234567.
Will Indian number formatting change my calculations?
No. A number format changes the display, not the stored number. Excel can still use the value in sums, comparisons, charts, and other calculations. Text that merely looks like a number is a separate issue and may need converting first.
Related
- Excel Dependent Drop-Down Lists With Spaces: Setup Guide
- Excel Dependent Drop-Down List: 3 Levels
- Excel Dependent Drop-Down Lists with Dynamic Ranges
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Day Hour Minute Second Format: Display DurationsTo display an elapsed time in Excel as hours, minutes, and seconds, apply the custom number format [h]:mm:ss.…
- Proper Excel Formatting: Clear, Reliable Spreadsheet RulesProper Excel formatting makes a spreadsheet easier to scan, understand, update, and print. In Microsoft Excel for Microsoft…
- SUMIF Negative Values in Google Sheets: What to CheckNegative numbers do not make Google Sheets’ SUMIF function fail: to add every value below zero, use a…
- Company Invoice Template in Excel: Setup and TipsYes. You can make a reusable company invoice template in Excel by laying out your business and customer…
- Calculate Age in Excel From a Birth Date, Step by StepTo calculate someone’s age in completed years in Excel, put their birth date in A2 and enter =DATEDIF(A2,TODAY(),"Y")…
- How to Set Out of Office in Outlook on the WebTo set an out-of-office reply in Outlook on the web, open Settings, find Automatic replies, turn them on,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.