When working with financial reports, sales data, or dashboards in Excel, you often deal with large numbers. Writing them in full form (like 36,731,702) makes reports look bulky and harder to read.
Instead, you can convert them into short formats like Cr (Crore), L (Lakh), and K (Thousand). This makes your data clean, readable, and professional.
Formula to Convert Amount in Short Format
=IF(B2>=10000000, ROUND(B2/10000000,2) & " Cr",
IF(B2>=100000, ROUND(B2/100000,2) & " L",
IF(B2>=1, ROUND(B2/1000,2) & " K", B2)))
Step-by-Step Explanation of the Formula
- IF(B2>=10000000, …) → If the amount is 1 Crore or more, divide by 1,00,00,000 and show result in Cr.
- IF(B2>=100000, …) → If the amount is less than 1 Crore but ≥ 1 Lakh, divide by 1,00,000 and show result in L.
- IF(B2>=1, …) → If the amount is less than 1 Lakh but ≥ 1, divide by 1,000 and show result in K.
- B2 → If the amount is less than 1, it will just display the number as it is.
Example Data
Why Use This Formula?
- Makes your Excel reports clean and professional.
- Easy to read and understand for managers and clients.
- Saves space in dashboards and summaries.
By using this simple IF condition formula, you can instantly convert large numbers into short formats like Cr, L, and K. It’s an excellent trick for anyone who prepares financial reports, sales summaries, or dashboards in Excel.
Join Our WhatsApp Group
Nazim Khan (Author) 📞 +91 9536250020
[MBA in Finance]
Nazim Khan is an expert in Microsoft Excel. He teaches people how to use it better. He has been doing this for more than ten years. He is running this website (TechGuruPlus.com) and a YouTube channel called "Business Excel" since 2016. He shares useful tips from his own experiences to help others improve their Excel skills and careers.
