To show numbers with Indian lakh-style digit grouping in Excel, select the cells, press Ctrl+1, choose Custom, and enter #,##,##0 for whole numbers or #,##,##0.00 for two decimal places. This changes how the number is displayed, not its stored value; it does not convert the number into lakhs.
What “in lakhs” can mean
People use “format in lakhs” to describe two different things. The first is grouping digits according to the Indian numbering system: for example, displaying 12345678 as 1,23,45,678. The second is expressing a value as a count of lakhs: displaying 12345678 as approximately 123.46 lakh. These are not the same operation.
A custom number format can control the appearance of digits, separators, decimals, and some text around a value. Indian grouping is commonly represented by #,##,##0. To express an amount in lakhs, however, the number must be divided by 100,000, or the data must otherwise be converted to that unit. A format that merely adds the word “lakh” does not do that arithmetic.
One lakh is 100,000, and 100 lakhs make one crore, or 10,000,000. So 1,23,45,678 is 123.45678 lakhs, while its Indian-style digit grouping remains 1,23,45,678. Keeping that distinction clear helps prevent reports from showing a number in the wrong unit.
How Indian digit grouping works
In the international grouping pattern often used in Excel, digits are separated into groups of three from the right: 12,345,678. In the Indian pattern, the final three digits form one group, and digits to their left are grouped in pairs: 1,23,45,678. The same number is being represented; only the placement of the separators differs.
The custom format #,##,##0 is a commonly used pattern for Indian-style whole-number grouping. The zero at the end means that a digit is shown in that position, including a zero when the value is zero. The # symbols are optional digit placeholders, so they do not force extra leading zeroes. For two decimal places, use #,##,##0.00; for a format that omits unnecessary trailing decimal zeroes, use #,##,##0.##.
For example, a cell whose stored value is 12345678 can appear as 1,23,45,678 with the whole-number pattern, or 1,23,45,678.50 if its value is 12345678.5 and the two-decimal pattern is applied. Decimal precision shown by the format does not necessarily match the precision stored in the cell.
Apply a custom format in Excel for Windows
In Microsoft Excel for Microsoft 365 on Windows, use the Format Cells dialog to enter a custom code:
- Select the cells, column, or range to format.
- Press Ctrl+1 to open Format Cells. You can also right-click the selection and choose Format Cells.
- Open the Number tab and select Custom in the Category list.
- Click the Type box, enter
#,##,##0or another appropriate code, and select OK.
For two decimal places, enter #,##,##0.00. To include a currency symbol, you can try a pattern such as ₹ #,##,##0.00, provided the rupee symbol is supported by the selected font and the regional settings. In financial workbooks, it is often clearer to use the Currency or Accounting category, or select the currency through the number-format controls, rather than rely on a symbol typed into a custom code.
On Excel for Mac, select the cells and press Command+1 to open Format Cells, then choose Custom and enter the code. The dialog layout can differ slightly by version, but the custom format is entered in the Type field. Excel for the web may not provide the same full controls for creating and editing custom codes as desktop Excel; if you cannot enter the format there, open the workbook in the desktop app.
Choose a format for the way you report numbers
The right code depends on what readers need to see. Consider whether decimals matter, whether the values are counts or money, and whether negative or zero values should be visually distinct. Examples include:
#,##,##0— Indian-style grouping with no decimals.#,##,##0.00— grouping with exactly two decimal places.#,##,##0.##— grouping with up to two decimal places, without forcing both decimal digits.#,##,##0;[Red]-#,##,##0;0— positive values, negative values in red with a minus sign, and zero displayed as 0.
In a custom number format, semicolons separate sections. A format can have sections for positive numbers, negative numbers, zero, and text, in that order. The example with three sections applies one pattern to positive values, another to negative values, and a third to zero. Use such extras only when they make a table easier to read; a minus sign and clear headings are often more accessible than color alone.
To display a label beside every number, you can add quoted text to a format, for example #,##,##0.00" units". This appends the word “units” to the displayed value but leaves the cell numeric. A format can also include a currency sign or other literal characters, but literal text should be quoted where needed so Excel treats it as text rather than a formatting instruction.
Show the amount as a number of lakhs
Excel’s comma-based scaling in custom formats divides a displayed value by 1,000 for each scaling comma placed at the end of the numeric format. For example, a trailing comma can show a value in thousands. It does not provide a single scaling step for 100,000, so adding commas to an ordinary format is not a reliable way to convert a raw amount into lakhs. A display that says “lakh” should be based on the amount divided by 100,000.
If the original amount is in A2 and you want the result in lakhs in B2, enter =A2/100000 in B2, then format B2 with 0.00" lakh". For example, if A2 contains 12345678, the formula returns 123.45678, which displays as 123.46 lakh when rounded to two visible decimal places. The formula result remains numeric, so it can still be used in calculations. The original amount in A2 is unchanged.
This approach creates a separate converted value. That is generally preferable to overwriting the source data, because the workbook retains both the original amount and the reporting unit. Label the column clearly—for example, “Amount (lakh)”—and make sure totals are calculated from values expressed in the same unit. Adding a value in rupees to one already converted to lakhs would produce a meaningless total.
Formatting changes appearance, not the underlying value
Custom number formats are useful because they preserve the underlying number. If a cell contains 12345678 and you apply #,##,##0, a formula that refers to that cell still uses 12345678, not the characters “1,23,45,678.” You can still sum, sort, chart, and compare the value as a number.
This also means a custom format cannot change how a calculation works. It does not divide the value by 100,000, alter the source precision, or turn a numeric cell into text. If you need the number itself to be expressed in lakhs, use a calculation such as the division shown above and retain the source amount if it is needed for audit or reconciliation.
For the same reason, formatting a cell to show two decimal places does not round the stored value. It only rounds the display. A sum may therefore use more precision than is visible in the individual cells. If a business rule actually requires rounding the calculated value, use an appropriate rounding formula separately and confirm the required precision for the task.
Regional settings and common display problems
Excel’s number separators and custom-format behavior can depend on the workbook’s locale and the regional settings of the computer. As a result, a code that displays Indian grouping on one system may not look identical on another. In some settings, Excel may show different separator characters, or the pattern may appear as ordinary three-digit grouping. Test the result with a value such as 12345678, rather than assuming that the code has worked because it was accepted.
- The value still looks like 12,345,678: Check the selected cells and confirm that the intended custom format is active. Regional settings or the way Excel interprets grouping can affect the result.
- The value shows ####: The column may be too narrow for the displayed number. Widen it or use AutoFit; the number is not necessarily damaged.
- Separators look wrong: Check Excel’s and Windows’ regional number settings. Comma and period can have different separator roles in different locales.
- The value is left-aligned or will not calculate: It may have been imported as text. Number formatting does not convert text into a numeric value; convert the data to numbers before applying the format.
- The word “lakh” appears but the amount is unchanged: A literal label only changes the display. Divide by 100,000 if the displayed quantity is meant to be a count of lakhs.
When sharing a workbook, remember that a custom format is stored with the cell, but the recipient’s Excel version and regional configuration can influence the way separators appear. If a report must be unambiguous across systems, include a descriptive heading such as “Amount (₹)” or “Amount (lakh)” and provide a sample or written convention.
Custom format versus changing the data
Use a custom format when the values should remain in their original units and you only want a different visual grouping. Use a formula or a separate converted column when the values should actually be reported in lakhs. These choices have different effects on totals and downstream calculations.
For example, if a worksheet stores rupee amounts and a chart should still calculate totals in rupees, Indian digit grouping is a display choice. If the report’s scale is lakhs, create a lakh-based result and make the chart and its axis labels clear about that unit. Do not apply a “lakh” label to an unscaled number: 12,345,678 labelled as lakh would be misleading because the actual value is about 123.46 lakh.
Text functions or concatenation can create labels such as a number followed by “lakh,” but those results are text, not numbers, and may not sort or calculate like numeric cells. For most worksheets, keep the calculated result numeric and use a custom number format to append a unit label. This preserves more spreadsheet functionality while keeping the unit visible.
Frequently Asked Questions
What is the custom number format for Indian commas in Excel?
Try #,##,##0 for whole numbers and #,##,##0.00 for two decimals. If Excel does not show Indian grouping, check the system’s regional settings and test the code on a known value.
How do I show a number in lakhs in Excel?
Divide the original value by 100,000 in a separate cell, then format the result as needed. For example, a value of 12345678 becomes 123.45678 lakhs, which displays as 123.46 with two decimal places.
Does a custom format change the actual number?
No. A custom number format changes the display only. The stored value remains available to formulas and calculations. Use a formula or another data transformation if the underlying quantity must change units.
Can I show the word lakh after a value?
Yes. A format such as 0.00" lakh" appends the label, but it does not divide the value. Apply it to a value that has already been converted to lakhs, not to an unscaled rupee amount.
Why does Excel show the number in thousands grouping instead?
Custom grouping can be influenced by the locale and regional settings used by Excel and the operating system. Confirm the format code, test a sample number, and check the regional number format if the separators do not appear as expected.
Related
- Close Group Excel Shortcut: Close Workbooks and Rows
- Close Excel Shortcut: Windows and Mac Keys
- Checklist Template Excel: Set Up, Track, and Reuse Tasks
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel: Expand Print Area to Fit a PageTo make an Excel printout fit a page, select the cells you want, choose Page Layout > Print…
- How to Share a Calendar in Microsoft OutlookTo share a calendar in Microsoft Outlook, open Calendar, select Share or Share Calendar, choose the calendar and…
- Excel Data Validation List From Another Sheet: StepsTo create an Excel data-validation dropdown from another worksheet, put the choices on that sheet, define a workbook-level…
- Google Sheets Unix Timestamp to Date Conversion GuideTo convert a Unix timestamp in Google Sheets, divide seconds by 86,400 or milliseconds by 86,400,000, then add…
- Excel Workbook Statistics: What It Counts and How to Use ItExcel’s Workbook Statistics feature gives you a quick count of items in the active worksheet or the whole…
- Dropdown Slicer in Excel: What It Is and How to Use OneExcel does not provide a native slicer that turns into a dropdown list: a slicer displays filter choices…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.