How to format numbers as lakhs and crores in excel google spreadsheet when the number could be negative too

0 votes

I have to format numbers in the Indian formatting system with commas showing up for lakhs(1,00,000) and crores (1,00,00,000).

This answer works for positive numbers

Displaying Lakhs and Crores in Google Sheets

But negative numbers show the commas for millions and billions only. How do I make one format work for both positive and negative?

Oct 31, 2022 in Others by Kithuzzz
• 28,900 points
1,428 views

1 answer to this question.

0 votes

Excel formatting, in my opinion, can only accept a maximum of two requirements, plus a default if those conditions are not met. Conditional formatting may be your only escape from this, in my opinion. I'd format this in a cell using one of the examples from your linked solution.

[$₹][>9999999]##\,##\,##\,##0.00;[$₹][>99999]##\,##\,##0.00;[$₹]##,##0.00

Then in conditional formatting, you would need to have a condition if cell<0 and apply the conditional format of:

[<-9999999][$₹]-##\,##\,##\,##0.00;[<-99999][$₹]-##\,##\,##0.00;[$₹]##,##0.00

If you want to change decimals, symbols, etc you should be able to work it out from the linked solution, and this one.

answered Oct 31, 2022 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
0 answers
0 votes
1 answer

I have filtered my Excel data and now I want to number the rows. How do I do that?

Solution Filter your data. Select the cells you want ...READ MORE

answered Nov 14, 2022 in Others by narikkadan
• 53,520 points
128 views
0 votes
1 answer
0 votes
1 answer

Thousand and million formatting for negative numbers (excel/ googlesheets)

Its not possible. What you could do is ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 53,520 points
163 views
0 votes
1 answer

How to split text values by a delimiter?

The Split function is what you are looking for: =Split(A1, ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 53,520 points
155 views
0 votes
1 answer

Currency conversion:number to words excel

Try looking for javascript solutions to use ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 53,520 points
90 views
0 votes
1 answer

How to remove gridlines on select cells in Google Sheets?

The google sheets don't have an exact ...READ MORE

answered Nov 20, 2022 in Others by narikkadan
• 53,520 points
79 views
0 votes
1 answer

How to format numbers in excel in crores or lacks

Try this: =TEXT(A1/10000000,"#,### \cr") READ MORE

answered Nov 6, 2022 in Others by narikkadan
• 53,520 points
113 views
0 votes
1 answer

How to change the default number format in Excel?

Excel Tips, Learn Excel \ Raghu R Setting ...READ MORE

answered Dec 17, 2022 in Others by narikkadan
• 53,520 points
125 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP