Data Import & Export

How to Change the Default Format of Imported CSV Data in Excel

Huma Ashraf Ch Oct 12, 2026 871 views

Question details

The user needs a way to prevent or remove the automatic table styling that Excel 365 applies when importing CSV files.

How to Change the Default Format of Imported CSV Data in Excel

Product: Excel 365
Device & OS: not provided
Scenario: Importing a CSV file into a spreadsheet to view and analyze plain data.
Observed behavior: Excel automatically loads the imported CSV data as an Excel table, applying default table styles and formatting that the user does not want.

Before you start

Ensure you have completely finished importing your CSV data before converting it. Note that removing the table formatting may disconnect the data from live external queries if you are using Power Query.

Solution 1Recommended

Convert the Imported Table to a Standard Range

This is the most direct way to remove the unwanted table formatting applied during the import process while keeping your CSV data intact.

When Excel 365 uses the newer 'Get Data' feature, it automatically formats the output as an Excel Table. You can easily strip away this structure by converting it back to a normal range, which allows you to apply your own custom formatting.

Select the Data

Click on any cell inside the imported CSV data table to activate the table tools.

Open Table Design

Navigate to the 'Table Design' tab that appears in the top ribbon menu.

Convert to Range

In the Tools group, click on 'Convert to Range'. A prompt will appear asking if you want to convert the table to a normal range.

Confirm and Reformat

Click 'Yes'. The data will no longer act as a table, and you can now clear the existing formats or apply your preferred styling.

Convert the Imported Table to a Standard Range

Preserve Data Integrity: Converting to a range only removes the structural table features and formatting; your underlying CSV data and values will not be deleted.

Solution 2

Use the Legacy Text Import Wizard

Enable Excel's older import method to bypass the automatic table creation entirely, allowing you to load CSVs directly as plain text ranges.

Free Microsoft Office alternative

Switch to WPS Office for a Hassle-Free Data Experience

If Excel's automatic formatting is disrupting your workflow, WPS Spreadsheet offers a highly compatible and lightweight alternative. It processes CSV files seamlessly as standard text ranges by default, giving you full control over your data without forcing unwanted table styles.

1. Install WPS Office: Download and install the free WPS Office suite from the official website.
2. Open CSV Directly: Launch WPS Spreadsheet, go to Menu > Open, and select your CSV file to view it instantly as plain data.
3. Format Freely: Apply only the formatting you need without fighting built-in table styles.

Fully compatible with Microsoft Excel formats, including .xlsx and .csvOpens CSV files straightforwardly without enforcing automatic table stylesLightweight design ensures incredibly fast loading for large datasetsFamiliar, easy-to-use interface requiring zero learning curve for Excel users

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel 365 automatically format my CSV as a table?

Excel 365 uses Power Query as the default engine for importing data (via Get Data). By design, Power Query loads the processed data into an Excel Table to support dynamic refreshing and better data management.

Will I lose my data if I convert the table to a range?

No. Converting a table to a range only removes the table properties (like auto-expanding rows and built-in filters). All your data, values, and text remain exactly where they are.

How do I clear the table colors after converting to a range?

After converting to a range, the color formatting often remains. You can remove it by selecting the data, navigating to the Home tab, clicking 'Clear' (the eraser icon) in the Editing group, and choosing 'Clear Formats'.

Excel & Data Problems