When you’re working in Excel, formulas are designed to calculate numerical values. If you manually enter symbols like a currency sign (Rs., $, €) or measuring units (Ltr., Kg) directly into the cell, Excel treats the entire entry as text instead of a number. Text values cannot be used for calculations, which is why your formulas might not work.
How to Fix This Issue:
- Don’t Enter Symbols with Numbers Directly: Avoid typing values like 50 Rs. or 30 Ltr. directly into cells.
- Use Excel’s Format Cells Feature Instead: Format the cell to display the desired symbol or unit without converting the number to text.
Steps to Format Cells:
- Select the Cells: Highlight the cells where you want to add a symbol or unit.
- Right-click and Choose “Format Cells”: Alternatively, press Ctrl+1 on your keyboard.
- Choose the Category: In the “Format Cells” dialog box, go to the Number tab and select Custom.
- Enter Your Custom Format:
- For currency: Type “Rs.” 0.00 (without quotes).
- For units: Type 0.00 “Ltr.” or replace Ltr. with your unit.
- Click OK: Your numbers will now display with the symbol or unit, but remain usable in formulas.
Example:
If you enter 50 into a cell formatted with “Rs.” 0.00, it will display as Rs. 50.00. If you use a formula like =A1 + B1, Excel will calculate correctly because the value is still a number.
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.
