Number Formatting - Change display, preserve value
Show INR amounts, percentages, decimals, and dates appropriately while understanding the value that calculations actually use.
By the end, you can
- Apply a format appropriate to the measurement.
- Distinguish display rounding from a changed value.
- Avoid percentage and leading-zero mistakes.
Before you start: Know numeric values versus text. Steps use Windows desktop Format Cells.
In this lesson
A numeric value can have several displays
Number formatting controls how a value appears: rupees, percentages, decimal places, or a calendar date. It normally does not change the value used in calculations. This matters when a report displays a rounded amount or a percentage but later formulas still use the underlying precision.
Before and after display choices
1250.5| A | B | |
|---|---|---|
| 1 | Field | Stored value |
| 2 | Expense amount | 1250.5 |
| 3 | Completion proportion | 0.125 |
| 4 | Measured hours | 2.346 |
| 5 | Date serial, 1900 system | 46303 |
₹1,250.50| A | B | C | |
|---|---|---|---|
| 1 | Field | Display | Underlying value still used |
| 2 | Expense amount | ₹1,250.50 | 1250.5 |
| 3 | Completion proportion | 12.5% | 0.125 |
| 4 | Measured hours | 2.35 | 2.346 |
| 5 | Date serial, 1900 system | 08-Oct-2026 | 46303 |
Choose each format in Excel
- Select the expense amount, press Ctrl+1, and open the Number tab of Format Cells.
- Choose Currency, select the ₹ symbol, set two decimal places, and confirm. Regional grouping/separators may follow your locale.
- Select the completion value 0.125. Choose Percentage with one decimal place to display 12.5%.
- Select the measured-hours value. Choose Number with two decimal places to display 2.35.
- Select the date serial. Choose Custom and enter dd-mmm-yyyy, then confirm the displayed date.
- Select each cell again and inspect its formula-bar value to distinguish underlying data from display.
Currencies, identifiers, and precise calculations
- Currency adds a monetary symbol; Accounting also aligns symbols and decimals in columns. Choose a consistent report convention.
- Use ROUND when the calculated value itself must be rounded. Merely reducing displayed decimals does not do that.
- For text identifiers such as 0042, choose Text before entry. A numeric custom format 0000 can display 42 as 0042, but the value remains numeric 42.
- Use a date format with a named month to reduce ambiguous day/month interpretation. Converting imported text dates is still a separate step.
Common mistakes and troubleshooting
The custom engine does not provide the full Excel number-format system, and no existing challenge grades these formatting steps. Use Microsoft Excel for the walkthrough.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.