Column Harbor
Module 01: Get comfortable in Excel
LESSON 03 / Beginner · ABOUT 15 MIN

Format numbers without confusing display and value

Make decimals, percentages, prices, and fixed-width codes readable while preserving their underlying meaning.

Before you start

Windows desktop: Excel 2016, 2019, 2021, 2024, and Microsoft 365. Mac/web menus and shortcuts differ. No dynamic arrays required.

Useful first: Enter and correct data without changing its meaning

By the end, you can…
  • Separate a stored number from its displayed format.
  • Format a decimal fraction as a percentage correctly.
  • Explain why formatting cannot reconstruct lost source data.
YOUR PRACTICE FILE

Small data. A result you can check.

Data only. Paste at A1 in a fresh sheet using General formatting. Decimal points assume an English-style decimal locale; enter equivalent numeric values manually if needed.

Download practice data
View the raw practice data
Meaning	Value
Measurement	2.375
Rate	0.075
Known five-digit display code	123
Amount	1234.5

01Read the value before choosing its appearance

Create a Formats sheet and paste at A1. B2:B5 are numeric. Click B2: the formula bar contains 2.375. A display format does not ordinarily replace that value.

Select B2, press Ctrl+1, and choose Number with two decimals. It displays 2.38 but remains 2.375 for calculations. Changing visible decimals is different from deliberately rounding a value.

CellNumeric inputRequested display
B22.3752.38, using two decimals
B30.0757.5%, using one percentage decimal
B412300123, using custom 00000
B51234.5A chosen currency symbol and two decimals

02Percentages begin with the correct proportion

Select B3 and choose Percentage with one decimal place in Format Cells. The value 0.075 displays as 7.5%. It represents 7.5 parts out of 100, not a whole-number amount of 7.5.

Enter 7.5 in a General cell and then apply Percentage: it displays 750%. The input is one hundred times too large for an intended 7.5% rate. Confirm the source meaning before correcting it.

03Codes and currency need different decisions

For B4, choose Custom and enter 00000. It displays 00123 but remains numeric 123. Use this only with a documented five-digit display rule; it does not preserve an unknown original text identifier.

For B5, choose Currency and a specific symbol. No exchange-rate conversion occurs. State the currency in the heading. Locale settings can change separators and symbol position.

04Investigate a surprising display before rewriting data

A narrow numeric column can show hashes or an abbreviated value. Widen it and inspect the formula bar before assuming the data disappeared. Long identifiers require special care: changing a format after numeric precision has already removed digits will not restore those digits.

Keep an untouched source. A convincing code display is not evidence that damaged data has been repaired. The related guides separate preserving identifiers from converting genuine text amounts.

PUT IT INTO PRACTICE

Format four existing numeric values and explain what changed.

  1. Paste the sample at A1 and verify the original B2:B5 values.
  2. Apply Number with two decimals to B2; Percentage with one decimal to B3.
  3. Apply custom 00000 to B4 and Currency with two decimals to B5.
  4. Return B4 to General and inspect it again.
Show the worked answer

B2 displays 2.38 but remains 2.375.

B3 displays 7.5% and represents the numeric proportion 0.075.

B4 initially displays 00123 and returns to 123 under General; its stored number was unchanged.

B5 represents 1234.5 in the selected currency display, without converting its amount.

Check your work
  • You can explain the difference between visible decimal places and stored precision.
  • No source value was overwritten just to change appearance.
  • You know the intended unit for each numeric field.
CHECK YOUR UNDERSTANDING

One question before you move on.

A General cell contains the number 0.2. What does Percentage with zero decimals display?

Ready for the next step?

Mark this lesson when you can explain the idea and reproduce the practice result.

Your checklist stays on this browser.
NEXT LESSONSort complete records and filter a focused view

Keep the skill close.

FIELD GUIDEKeep Leading Zeros in Excel CSV Imports and ExportsFIELD GUIDEExcel Changes the Last Digit to Zero: Keep Long IDs IntactFIELD GUIDEImport Decimal Commas in Excel Without Changing the AmountsWORKSPACE TOOLCSV import checker

Reference notes

These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.

Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.