Why Excel Changes Your Data: Leading Zeros, Dates and Big Numbers

This is the most common complaint about any JSON-to-spreadsheet conversion, and almost none of it is the converter's fault. Excel applies type inference to every value it imports as text. It decides what each cell "means", converts, and discards the original characters. By the time you notice, the information is gone from the workbook.

The good news is that every case has a deterministic cause and a specific fix. Here they are, roughly in order of how often they cost people an afternoon.

1. Leading zeros disappear

What you see:"00123" becomes 123. Postcode 01234 becomes 1234. A German phone number loses its 0.

Why: Excel sees a string of digits, concludes it is a number, and numbers do not have leading zeros. The zeros are not hidden by formatting — they are gone from the stored value.

This is the single most damaging case, because the affected fields are almost always identifiers: postcodes, account numbers, SKUs, employee IDs, ISO country codes with numeric prefixes. A truncated identifier does not look wrong. It just quietly fails to join against anything.

Fix, if you control the conversion: write XLSX rather than CSV, and mark the column as text.

import pandas as pd

df = pd.read_json("data.json")
df["postcode"] = df["postcode"].astype(str)

with pd.ExcelWriter("out.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, index=False, sheet_name="Data")
    book = writer.book
    sheet = writer.sheets["Data"]
    text = book.add_format({"num_format": "@"})
    sheet.set_column("C:C", 14, text)   # column C stays text

The @ number format is Excel's "Text" format. Applied at write time, it survives opening the file.

Fix, if you are importing a file someone sent you: do not double-click it. Use Data → From Text/CSV, click Transform Data, and set the column type to Text in Power Query before loading. Setting the format after the import is too late — the zeros were dropped during it.

2. Long numbers become scientific notation — then lose digits

What you see: a 17-digit transaction ID displays as 1.23457E+16. Widening the column shows the full number again, so it looks like a display problem.

It is not. Excel stores numbers as IEEE 754 doubles and keeps only 15 significant digits. Digit 16 onwards is replaced with zeros permanently. A credit card number, an IMEI, a Twitter/X status ID, a Snowflake key — all longer than 15 digits, all silently truncated.

Original in JSON:  "12345678901234567"
Stored by Excel:    12345678901234500
                                   ^^ overwritten with zeros

Widening the column will show 12345678901234500. The trailing digits are not hidden; they no longer exist. This is why financial and telecoms data must never be round- tripped through a naive CSV import.

Fix: the same one as leading zeros — the column has to be text before the value is parsed. Numbers you will never do arithmetic on are identifiers, not numbers, and should be typed as text from the start.

A useful rule when designing the JSON in the first place: if you would never add two of these values together, quote it as a string in the API response. Most well-behaved APIs already return large IDs as strings for exactly this reason.

3. Anything that resembles a date becomes one

What you see: the version string "1.10" becomes 1 October. The ratio "3/4" becomes 4-Mar. The gene name "SEPT1" becomes 1-Sep.

That last one is not a joke — Excel's autoconversion corrupted enough genomics data that in 2020 the HUGO Gene Nomenclature Committee renamed several human genes to symbols Excel would leave alone. If a standards body changed the names of genes to work around this behaviour, it is worth taking seriously in your own data.

Why: Excel's date parser is aggressive and locale-dependent, which adds a second failure mode: 03/04/2026 is 3 April in the UK and 4 March in the US. The same file opened in two offices produces two different datasets, with no error in either.

Fix: ISO 8601 (2026-09-06) is unambiguous in every locale and is the only date format worth putting in an interchange file. For values that merely resemble dates but are not, force the column to text at import.

Converting real ISO timestamps into proper Excel dates, deliberately:

import pandas as pd

df = pd.read_json("data.json")
df["created_at"] = pd.to_datetime(df["created_at"], utc=True, errors="coerce")
df["created_at"] = df["created_at"].dt.tz_localize(None)   # Excel has no time zones

df.to_excel("out.xlsx", index=False)

errors="coerce" turns unparseable values into NaT instead of raising, so one malformed row does not abort the job. The tz_localize(None) line is required — Excel has no concept of a time zone, and openpyxl refuses to write a timezone-aware datetime at all. Convert to UTC first, then strip, and note in the header that the column is UTC.

4. Booleans stop being booleans

JSON true becomes TRUE in Excel, which is a real boolean and behaves correctly. But export that sheet back to CSV and you get the string TRUE, which is not valid JSON — the spec requires lowercase. Round trip a few times and you accumulate a mixture of TRUE, true, 1 and VERDADERO, because Excel localises boolean display names.

If the file is going to be read by a program, write 1 and 0, or the literal strings "true" and "false" in a text-formatted column. Neither gets localised.

5. null, empty string, and zero all look identical

JSON distinguishes null (no value), "" (empty value), and 0 (a value that is zero). Excel has one empty cell, and it displays zero as 0. After conversion you cannot tell "we never collected this" from "we collected it and it was blank" — and if a downstream SUM treats missing as zero, your averages are wrong.

Where the distinction matters, make it explicit rather than hoping:

df = df.where(df.notna(), "N/A")            # nulls become a visible marker
df = df.replace("", "(blank)")              # empty strings become another

Losing this distinction is the least visible failure on this list and often the most consequential, because it changes aggregate numbers rather than individual cells.

6. Text starting with =, +, - or @

Excel treats those four leading characters as the start of a formula. A product name like -40C Coolant becomes a broken formula and shows #NAME?. Worse, a field containing user-submitted text can execute as a formula when someone else opens the file — a genuine vulnerability class known as CSV injection.

Fix: when generating CSV from data you did not write yourself, prefix any value starting with those characters with a single quote or a tab. When generating XLSX, write the cell with an explicit text type. Do not rely on the recipient's Excel settings.

7. Accented and non-Latin characters turn to gibberish

Names with accents, or any Cyrillic, Greek, Arabic or CJK text, arrive mangled — the classic symptom is an Ã appearing in the middle of a European name.

Why: the file is UTF-8, and Excel on Windows defaults to the system code page unless it finds a byte order mark.

Fix: write CSV with encoding="utf-8-sig", or avoid the problem entirely by writing XLSX, which stores its text as UTF-8 by specification and has no ambiguity to resolve.

8. Numbers that are text, and text that is numbers

A column that arrives left-aligned with a little green triangle in the corner is text that looks numeric. SUM over it returns 0, sorting is alphabetical (10 before 9), and charts ignore it.

This usually happens when JSON quoted its numbers — {"price": "19.99"} rather than {"price": 19.99}. It is a common shape in APIs that serialise decimal types as strings to avoid floating-point rounding, which is a defensible choice on their end and an annoyance on yours.

Convert explicitly before writing:

df["price"] = pd.to_numeric(df["price"], errors="coerce")

Anything that will not parse becomes NaN, which you can then find with df["price"].isna() and inspect. That is a much better outcome than a column that is quietly half text.

The rule that prevents most of this

Write XLSX, not CSV, whenever a human will open the file. CSV has no type information at all, so every value is re-guessed at import time on a machine whose locale and settings you do not control. XLSX stores the type with the value. The guessing never happens.

That is the whole reason our converter outputs XLSX by default. When you do need CSV — because the destination is another program — see converting JSON to CSV for the escaping and encoding rules that keep it intact.

Symptom lookup

Only the last row on that list is cosmetic. Everything above it is data loss, and none of it raises an error.