Excel Dropped the Trailing Zeros From My Numbers
The quick answer: As numbers, 1.5 and 1.50 are the same value — the extra zero is display only, so Excel dropping it is a formatting choice, not lost data. For any column you do arithmetic on, it is purely cosmetic: apply a custom format of 0.00 and it looks right again. It only truly matters when the field is really text that must keep its exact shape — a price shown as 1.50, a version string like 1.10, a measurement whose precision is meaningful, or a code. In that case you have to store the column as text, because a number format alone does not reliably survive a save to CSV.
Does it actually matter?
Most of the time, no. If the column holds quantities, prices you will total, rates, or anything you feed into a formula, then 1.5 is mathematically identical to 1.50 and your results are unaffected. The moment the value is really a label — something a person reads, or another system parses character-for-character — the missing zero can break the meaning. Use this to decide:
| Your column is… | Does 1.5 vs 1.50 matter? | What to do |
|---|---|---|
| A number you will calculate with | No — cosmetic | Apply custom format 0.00 |
| A price or label shown to people | Yes | Store the column as text |
A version string like 1.10 | Yes | Store the column as text |
| A fixed-precision measurement | Depends | Store as text if the zeros are meaningful |
A version number is the clearest example: 1.10 is a later release than 1.9, but read as a number it collapses to 1.1 and sorts before 1.9. No display format will save you there — the value has to be text.
Why Excel drops the zero
A CSV file stores exactly whatever each cell holds, and a cell formatted as General or Number holds a mathematical value, not the characters you typed. Type 1.50 into a General cell and Excel parses it as the number one-and-a-half; the trailing zero has no arithmetic meaning, so it is not stored. When Excel then writes the CSV, it writes that stored value, and out comes 1.5. The zero was never in the file to begin with — it lived only in the moment of typing.
This is the same root cause behind Excel treating identifiers as numbers and behind leading zeros vanishing from ZIP and SKU columns: in every case, a value that should be exact text got interpreted as a quantity. The fix is always the same shape — keep it as text.
How to keep the literal 1.50
Store the column as text, so the exact characters are what gets saved. There are three reliable routes, depending on where your data is coming from:
- Before you type: select the column, open Format Cells → Text, then enter
1.50. Excel keeps every character and the cell sits left-aligned as text. - On import: bring the file in through Data → From Text/CSV and set the relevant column’s type to Text in the preview, rather than double-clicking the file open.
- For numbers already in the sheet: in a helper column use
=TEXT(A2,"0.00"), then Copy and Paste Special → Values back over the original. TheTEXTresult is genuine text, so the zeros are now part of the stored value.
Whichever route you take, save with File → Save As → CSV afterwards. Because the cells now hold text, the file contains the literal 1.50. Opening that CSV carefully next time — see opening a CSV in Excel without breaking it — keeps it intact on the round trip.
While you’re here: fix the other things in the file
Trailing zeros are a store-as-text job (above) — no tool can add a 0 that means nothing to a number, and we won’t pretend otherwise. But if the same file also lost its leading zeros, has numbers stuck in scientific notation, or a broken encoding, our free browser tools repair those locally, with nothing uploaded.
Why the 0.00 format does not stick
Honest gotcha: a custom number format changes only the screen, not the saved file. Applying 0.00 makes the cell display 1.50, which is perfect when the value is a real number you just want to look tidy. But the cell still stores the plain number 1.5, and when Excel writes the CSV it writes the stored value — not the format — so the file usually still gets 1.5. This trips people up constantly, because the sheet looks correct right up until they reopen the exported file.
So the rule is simple: use 0.00 when you only need it to look right and the data stays inside Excel; store the column as text when the literal 1.50 has to exist in the CSV itself. Do not rely on formatting surviving the CSV round-trip — it is display-only by design, a point echoed repeatedly by long-time Excel users on the MrExcel community boards.
Frequently asked questions
Why does Excel remove trailing zeros?
A General or Number cell stores a mathematical value, not the characters you typed. As a number, 1.50 and 1.5 are identical, so Excel keeps 1.5 and writes that to the CSV. The trailing zero was part of the display, never part of the number, so nothing was corrupted.
Are 1.5 and 1.50 different numbers?
As numbers, no — they are exactly the same value, and any calculation gives the same result either way. They differ only as text, where 1.50 has one more character. That matters when the field is a label, code, or version rather than a quantity.
How do I keep trailing zeros in a CSV?
Store the column as text. Set Format Cells → Text before typing, import the column as Text through Data → From Text/CSV, or convert existing numbers with =TEXT(A2,"0.00") then Copy and Paste Special → Values. Once the cell holds text, saving to CSV writes the literal 1.50.
Does formatting a cell as 0.00 save the zeros to the CSV?
Not reliably. A custom format like 0.00 only changes what you see on screen; the cell still stores 1.5. Excel writes the stored value to the CSV, not the format, so the file usually still gets 1.5. Store the column as text to guarantee 1.50 in the file.
Sources: The display-versus-stored-value distinction is standard Excel behaviour, discussed by users on the MrExcel forums. Microsoft documents the careful import and export path in Import or export text (.txt or .csv) files.