Data Import & Export

How to Set Data Types and Formats in Excel 365

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to understand how to manage data types and cell formatting in Excel 365, particularly when importing CSV data from financial institutions where text, numbers, and dates may be interpreted incorrectly.

Product: Excel 365
Device & OS: not provided
Scenario: Opening or importing plain-text CSV files and attempting to set strict data types for columns.
Observed behavior: Excel does not use strict database column types. Instead, it relies on cell formats for display and automatically infers underlying data types when opening CSV files, which sometimes leads to incorrect interpretation of text, currency, or dates.

Before you start

Before importing your CSV file into Excel, open the file in a basic text editor like Notepad to verify the raw data structure and check if numerical IDs or dates are formatted with special characters.

Solution 1Recommended

Import CSV using Get Data to Control Data Types

Using the Power Query import method prevents Excel from automatically guessing data types, allowing you to explicitly define whether a column is text, date, or a specific number format.

Since CSV files contain plain text without built-in data types, opening them directly by double-clicking forces Excel to guess the format, which often drops leading zeros or ruins date formats.

Open the Data Tab

Launch Excel 365, open a blank workbook, and navigate to the Data tab on the top ribbon.

Import from Text/CSV

Click on 'Get Data' > 'From File' > 'From Text/CSV', then locate and select your CSV file.

Transform Data

In the preview window, do not click Load. Instead, click 'Transform Data' to open the Power Query Editor.

Set Column Types

Click the small data type icon (like 'ABC' or '123') in the header of any column you want to change, and select the correct type (e.g., Text or Date).

Load the Data

Once all column types are correctly defined, click 'Close & Load' in the top left corner to bring the formatted data into your spreadsheet.

Preserving Leading Zeros: Setting an account number column's data type to 'Text' in Power Query before loading ensures that leading zeros are never removed.

Solution 2

Convert Inferred Formats using Text to Columns

If you have already imported the data and Excel inferred the wrong data type (such as treating dates as text), you can force Excel to re-evaluate or set a strict data format for an entire column.

Solution 3

Apply Custom Number Formats for Display Purposes

You can change how values are displayed in cells using Excel's formatting features without altering the underlying raw data type.

Efficient Data Processing

Easily Manage CSV Imports and Data Types with WPS Spreadsheet

WPS Spreadsheet provides intuitive tools for importing CSV files, formatting cells, and managing underlying data types. Its smart data import wizard allows you to explicitly enforce text or number formats, ensuring your financial records remain accurate.

1. Open WPS Spreadsheet: Launch WPS Office and open a new Spreadsheet document.
2. Access the Import Tool: Navigate to the Data tab and select 'Import Data', then choose your CSV file.
3. Configure the Wizard: Use the File Conversion dialog to preview how your data is separated by delimiters.
4. Specify Data Types: In the import wizard, explicitly set the column formats to 'Text' or 'Date' to prevent automatic format inference.
5. Finish the Import: Click 'Finish' to successfully load the accurately formatted data into your worksheet.

Free and lightweight alternative for powerful spreadsheet processingSeamless compatibility with Microsoft Excel (.xlsx, .csv) file formatsIntuitive Text to Columns and data import wizards to prevent auto-formatting errorsAdvanced formatting options for handling complex financial and date data

Frequently Asked Questions

Why does Excel remove leading zeros from my CSV file?

Because CSV files are plain text, Excel automatically infers numerical data upon opening. It converts text strings like '00123' to the number '123'. To preserve leading zeros, import the CSV using 'Get Data' and define the column format as Text before loading.

How can I check the true underlying data type in an Excel cell?

You can use built-in functions like =ISTEXT(A1) or =ISNUMBER(A1) to determine the actual underlying data type Excel has stored, regardless of the display format currently applied to the cell.

Can I assign a permanent data type to an entire column like in SQL databases?

No, Excel does not strictly enforce database-style data types on a per-column basis. While you can format an entire column as 'Text', individual cells can still contain other data types if manually overridden. For strict data entry rules, you should use Excel's Data Validation feature.

Why do dates change format when I open a CSV downloaded from my bank?

Excel attempts to match the date strings found in the CSV with your operating system's regional settings. If the bank provides dates in DD/MM/YYYY but your system is set to MM/DD/YYYY, Excel will infer them incorrectly. Using the Text to Columns wizard helps you explicitly define the date structure during import.

Excel & Data Problems