Data Import & Export

How to Stop Excel from Removing Trailing Zeros in CSV Files

Emma Brown Sep 28, 2026 869 views

Question details

The user needs to prevent Excel from automatically removing trailing zeros when opening or importing CSV files.

How to Stop Excel from Removing Trailing Zeros in CSV Files

Product: Microsoft Excel
Device & OS: not provided
Scenario: Opening or importing downloaded CSV files from external data sources where exact numeric formatting, measurements, or identifiers are required.
Observed behavior: Excel automatically drops trailing zeros from numbers upon opening the file, which alters exact figures and exact string representations.

Before you start

Ensure you have the original, unmodified CSV file saved on your local drive. Avoid double-clicking to open the file directly, as this triggers Excel's automatic formatting rules immediately.

Solution 1Recommended

Import CSV Data as Text via Power Query

Using the Data import wizard allows you to define column data types before Excel applies its automatic numerical formatting.

By importing the data rather than opening the file directly, you retain complete control over how Excel interprets each column.

Open a Blank Workbook

Launch Excel and open a completely new, blank workbook.

Start the Import Process

Navigate to the **Data** tab on the ribbon and select **From Text/CSV**.

Select Your File

Locate the original CSV file on your computer, select it, and click **Import**.

Transform the Data

When the preview window appears, do not click Load immediately. Instead, click **Transform Data** (or **Edit** in older versions) to open the Power Query Editor.

Format Column as Text

Click the data type icon (usually "1.2" or "ABC") in the header of the column containing trailing zeros. Select **Text** from the dropdown menu, and click **Replace current** if prompted.

Load the Data

Click **Close & Load** in the top-left corner to import the data exactly as it appears in the source CSV file.

Formatting Preserved: Your trailing zeros will now remain intact, and a small green triangle may appear in the cell corner indicating numbers are stored as text.

Solution 2

Apply Custom Number Formatting

If you only need the trailing zeros to be visible for display or printing purposes, you can use Excel's custom number formats.

Manage CSV Files Easily

Easily Import CSV Files and Preserve Formatting with WPS Spreadsheet

WPS Spreadsheet provides a straightforward Text Import Wizard that makes it easy to handle CSV files without losing trailing zeros, dropping leading zeros, or unexpectedly altering your important data.

1. Open WPS Spreadsheet: Launch WPS Office and create a new blank spreadsheet.
2. Import Your CSV: Navigate to the **Data** tab, click **Import Data**, choose **Import Data** again, and select your CSV file.
3. Set Column to Text: In the import wizard, highlight the specific column containing your trailing zeros and select **Text** as the Column Data Format before clicking Finish.

Intuitive Text Import Wizard for step-by-step column formatting.Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats.Lightweight, fast, and completely free to use for everyday data management.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel automatically remove trailing zeros from my CSV files?

Excel is designed to recognize number patterns and automatically converts them to standard numerical values to facilitate calculations. Because trailing zeros after a decimal point do not change a number's mathematical value, Excel removes them by default unless the cell is explicitly formatted as text.

Can I permanently stop Excel from formatting numbers automatically?

In newer versions of Excel (Microsoft 365), you can disable automatic data conversions globally. Go to File > Options > Data, and under the 'Automatic Data Conversion' section, uncheck the options that remove leading/trailing zeros or convert text to numbers.

How do I fix a CSV file that has already lost its trailing zeros?

If you have already opened the CSV directly in Excel, lost the trailing zeros, and clicked 'Save', the original data is permanently altered. To fix this, you must download or export the original CSV file again from the source and use the 'Data > From Text/CSV' import method to retain the exact formatting.

Excel & Data Problems