Excel Changed My CSV Numbers (How to Stop This)

You open a CSV file in Excel. Your data looks perfect.

You close and save. Reopen the file.

Wait... where did all the leading zeros go? Why is "1-2-3" now "Jan 2, 2003"? Why is that long number showing as 1.23E+15?

Excel "helped" by auto-formatting your data. Without asking. And now it's ruined.

Let me show you how to stop this madness and keep your data exactly as it is.

What Excel Auto-Changes

Here's what Excel does when you open a CSV:

Leading Zeros Disappear

Your data: 007, 00123, 00456
Excel shows: 7, 123, 456

Why this sucks: Product codes, ZIP codes, employee IDs all broken.

Numbers Become Scientific Notation

Your data: 123456789012345
Excel shows: 1.23E+14

Why this sucks: Order IDs, tracking numbers, large serial numbers now unreadable.

Text Becomes Dates

Your data: 1-2-3 (aisle-shelf-position), 3-5 (product version)
Excel shows: Jan 2, 2003, Mar 5, 2024

Why this sucks: Your warehouse codes are now random dates.

Phone Numbers Get Reformatted

Your data: 5551234567
Excel shows: 555-123-4567 or 5.55E+09

Why this sucks: Format changed or became scientific notation.

Fractions Become Dates

Your data: 1/2 (half an inch)
Excel shows: Jan 2 (current year)

Why this sucks: Measurement data destroyed.

Why Excel Does This

Excel tries to be "helpful" by guessing data types:

• Looks like a number → Make it a number
• Looks like a date → Make it a date
• Number looks big → Simplify with scientific notation

The problem: Excel guesses wrong. Often.

And there's no undo once you've saved and closed the file.

The Right Way: Import, Don't Open

Never double-click CSV files with important number formatting.

Method: Use Text Import Wizard

Step 1: Start with blank Excel

Don't open the CSV directly. Open Excel first (blank workbook).

Step 2: Import the CSV

Excel for Windows:

• Data → Get Data → From Text/CSV

Excel for Mac:

• Data → Get External Data → Import Text File

Step 3: Select your CSV file

Step 4: In the preview window:

• Don't click "Load" yet
• Click "Transform Data" or "Edit"

Step 5: Set columns to Text format

This is the crucial step:

1. Click the column with leading zeros (or dates that aren't dates)
2. Right-click column header
3. Change Type → Text

Do this for every column that needs to stay exactly as-is.

Columns to usually set as Text:

• ZIP codes (05401, not 5401)
• Product codes (007, not 7)
• Large IDs (long numbers)
• Version numbers (1-2-3, not Jan 2)
• Phone numbers
• Account numbers

Step 6: Load the data

Now click "Close & Load"

Your data loads into Excel with formatting preserved.

Quick Fix: Add Apostrophe

If you're creating the CSV (not just opening it), add an apostrophe before numbers you want to keep as text.

In Excel before saving:

'007
'00123
'5551234567

The apostrophe tells Excel "this is text" and gets hidden when displayed.

Result in CSV file:

'007

When someone opens this CSV: Excel sees the apostrophe and keeps it as text. Leading zero preserved.

Downside: Apostrophes are visible if you open the CSV in text editor or import to database.

Already Lost Data? Recovery Options

If You Haven't Saved Yet

Stop! Don't save!

1. Close without saving (click "Don't Save")
2. Reopen properly using Import method above

If You've Saved and Closed

Bad news: The original data is gone. Excel overwrote it.

Your options:

1. Re-download from source If the CSV came from a system (CRM, database, export), regenerate it.

2. Restore from backup Check your backup drives, cloud storage, previous versions.

3. Check "Previousversions" Windows:

• Right-click file → Restore previous versions
• See if system had a restore point

4. It's gone If no backup exists, the data is lost. Sorry.

This is why you backup before opening.

Preventing Data Loss

Rule 1: Never Double-Click CSVs with Important Data

Always use the Import method. Takes 30 extra seconds. Saves hours of data recovery.

Rule 2: Keep the Original

Before opening/editing:

1. Make a copy
2. Rename to original_backup.csv
3. Work on the copy

If you mess up, you still have the original.

Rule 3: Verify Before Saving

Before clicking Save:

1. Scroll through your data
2. Check for:
   • Missing leading zeros
   • Unexpected dates
   • Scientific notation
   • Reformatted phone numbers

If you see problems, Don't Save. Close and reopen properly.

Rule 4: Save As Excel Format First

If you're editing data:

1. Import CSV properly (formats preserved)
2. Save as Excel Workbook (.xlsx)
3. Work in Excel format
4. Only export to CSV when done

Excel format preserves your formatting choices. CSV doesn't.

For People Sharing CSVs: How to Protect Your Data

If you're creating a CSV for others:

Option 1: Add Quotes

Wrap fields that need to stay as text:

"007","00123","5551234567"

Some versions of Excel respect quotes and keep data as text.

Option 2: Use Different Format

If possible:

• Share as Excel file (.xlsx) instead of CSV
• Or share as Google Sheet (link)
• Both preserve formatting better than CSV

Option 3: Include Instructions

Include a README.txt:

IMPORTANT: Do not double-click this CSV file.

To open properly:
1. Open Excel (blank workbook)
2. Data → From Text/CSV
3. Select this file
4. Set columns A, C, and F to Text format
5. Then load

This preserves leading zeros and prevents date conversion.

Option 4: Use Text Format for Everything

When exporting from your system:

• Force all columns to export as text (quoted)
• Add leading apostrophe to number columns
• Convert dates to text before export

Makes the CSV uglier but safer.

Special Cases

Case 1: ZIP Codes

Problem: 05401 becomes 5401

Fix before import: Set as Text Or: Store with leading apostrophe '05401 Or: Format as ZIP Code style (Excel has this)

Case 2: Large Order IDs

Problem: 123456789012345 becomes 1.23E+14

Fix before import: Set column to Text Or: Split into chunks separated by dash (prevents number interpretation)

Case 3: Version Numbers

Problem: 1-2-3 becomes Jan 2, 2003

Fix before import: Set as Text Or: Use different separator: 1.2.3 or v1-2-3 Or: Quote in CSV: "1-2-3"

Case 4: Phone Numbers

Problem: Various formatting issues

Best practice:

• Store with dashes or parentheses: (555) 123-4567
• Or with country code: +1-555-123-4567
• Prevents Excel from treating as pure number

Alternative: Use Google Sheets

Google Sheets is better at preserving CSV data as-is.

Advantages:

• Doesn't auto-format leading zeros as aggressively
• Less likely to convert text to dates
• Easier to force text format

How:

1. Go to sheets.google.com
2. File → Import
3. Upload CSV
4. Choose "Import" (not "Replace")
5. Data usually preserved better than Excel

Then export:

• File → Download → CSV
• Opens cleaner in Excel

When Excel's Auto-Formatting Is Actually Helpful

Fair question: Is Excel's behavior ever useful?

Yes, when:

• You have actual dates you want as dates
• You have actual numbers you want to calculate with
• You want pretty formatting for viewing (not data processing)

No, when:

• Text that looks like numbers (codes, IDs)
• Numbers that shouldn't be calculated (ZIP, phone)
• Data being passed to other systems
• Exact preservation of source data required

Rule of thumb: If the data goes anywhere else (database, system import, sharing), preserve as text. If it's just for viewing/calculations in Excel, auto-formatting is fine.

The Bottom Line

Excel changes CSV numbers because:

• It guesses data types automatically
• It assumes you want formatting help
• There's no way to disable this when double-clicking

How to prevent:

1. Import, don't open (use Data → From Text/CSV)
2. Set problem columns to Text format
3. Backup original before opening
4. Verify data before saving

How to protect CSVs you create:

1. Add quotes around text fields
2. Use apostrophe prefix for numbers-as-text
3. Include opening instructions
4. Consider sending .xlsx instead

Already lost data?

• Check backups
• Redownload from source
• Check file previous versions
• Or accept it's gone and prevent next time

Pro tip: For critical data, never use CSV if you can avoid it. Use Excel format (.xlsx), database, or another format that respects data types. CSV is great for compatibility, terrible for preserving exact formatting.

---

Working with CSV files? HappyCSV's tools process your data without changing it - no auto-formatting, no data loss, keeps everything exactly as is.