Format Numbers, Dates, and Currency in Microsoft Excel

Quick answer: Number formats change display without changing the stored value. Use built-in Currency, Percentage and Date formats for common needs, and a custom format for a label such as “units” that must not turn the quantity into text.

Version and platform: Microsoft 365 Excel for Windows desktop; Mac, Excel for the web and Excel 2013 interfaces not covered. Use a disposable workbook for the exercise.

Enter values in a safe order

Use a blank worksheet and prepare these cells:

• A2: enter the number 1234.5.
• A3: enter the number 0.075.
• A4: enter =DATE(2026,10,14), avoiding ambiguous typed slash-dates.
• A5: select the cell, press Ctrl+1, choose Number → Text, and confirm before typing 00123.
• A6: enter the number 12.5.

A5 is an identifier, not a quantity. Formatting an already-entered 123 as Text cannot reconstruct discarded leading zeros. The DATE formula makes A4’s intended day explicit, although the formula’s argument separator can differ with locale.

Apply the built-in display formats

Select A2, press Ctrl+1, open the Number tab and choose Currency. Select two decimal places and the intended currency symbol, then confirm. Expect an amount representing 1,234.50; the symbol and punctuation depend on the selected locale. Currency and Accounting differ in alignment, not in how much the cell contains.

Select A3 and choose Percentage with one decimal place. Expect 7.5%. Select A4 and choose a Date type that clearly identifies 14 October 2026. A different date display should not change the represented day. Leave A5 in Text format with all five digits visible.

In B2 enter =A2*2 and in B3 enter =A3*100. Expect 2469 and 7.5. These calculations check the stored numbers rather than relying on a rounded or decorated display. Do not enable precision-changing calculation settings to make a number look different.

Add a custom unit format

Select A6 and use the Number dialog’s Custom category. In Type, enter 0.0" units" and confirm. The displayed result should be 12.5 units, while the formula bar still shows the number 12.5. The zero placeholders request one displayed decimal; the quoted text is a literal label, not part of the stored value.

In B6 enter =A6*2; expect 25. Change A6 to 12.56. Its display rounds to 12.6 units, but B6 should calculate 25.12, demonstrating that display rounding did not round the stored input. Use a normal numeric display on B6 with enough decimals to inspect that result. Do not type “12.56 units” directly into A6; that would create text instead of applying a format to a number.

Custom codes can also contain separate sections for positive, negative, zero and text values, but a single numeric section is sufficient here. The custom format belongs to the workbook; it is not a universal application setting. Microsoft documents creation of custom formats in desktop Excel, not Excel for the web.

Change a percentage and verify persistence

Change A3 to 0.08. It should display 8.0%, while B3 becomes 8. If a value typed as 8 in a general-format cell later displays 800%, that is the expected percentage representation of the stored 8, not a rounding problem.

Save as XLSX, reopen, and check A5 still reads 00123, A4 still represents 14 October 2026, A6 displays 12.6 units and B6 returns 25.12. CSV is not an archive of these formats. If formatting seems ineffective, inspect whether the source is text; changing display categories does not repair a misparsed import. Widen a column if a date or number cannot be displayed fully rather than assuming the stored value was lost.

Official references

• Format numbers
• Available number formats in Excel
• Create a custom number format
• Review guidelines for customizing a number format

Documentation snapshots retrieved 20 October 2026. These instructions and example checks are documentation-based; no native Excel run or application screenshot is claimed.