To import CSV data into Excel for Microsoft 365 on Windows, open a blank workbook, select Data > From Text/CSV, choose the file, review the preview and delimiter, then select Load or Transform Data. Importing this way gives you more control over how Excel reads dates, numbers, text, and character encoding than simply opening a CSV file.
What a CSV file contains
CSV stands for comma-separated values. It is a plain-text format for storing rows and columns: each line typically represents a row, and separators divide the fields. Despite the name, a CSV file does not always use commas. A system configured for certain regional settings may export fields separated by semicolons, and other text exports can use tabs or another delimiter.
A CSV stores values and separators, not the full set of features in an Excel workbook. It does not preserve worksheet formatting, formulas as formulas, multiple sheets, charts, or other workbook features. When you save a workbook as CSV, Excel saves the active sheet’s displayed values in a text format; other sheets and workbook features are not included. Keep an .xlsx copy if you need to retain them.
CSV files can include quoted fields. For example, a field containing a comma may appear as "Portland, Oregon" so the comma remains part of that field rather than splitting it into two columns. Quotes can also enclose text containing line breaks. A proper importer recognizes these conventions; splitting every line at every comma by hand can corrupt the layout.
Import a CSV with the Data tab
- Open Excel and create or open the workbook that should receive the data.
- Select Data > From Text/CSV. Browse to the CSV file, select it, and choose Import.
- In the preview window, check that the columns line up as expected. If they do not, choose the correct delimiter, such as comma or semicolon.
- Choose Load to bring the data into a worksheet. Choose Transform Data if you want to inspect or change the data in Power Query before loading it.
The preview is important: a file can open without an error and still be divided into the wrong columns. Check a few recognizable records, including rows with punctuation, dates, or blank values. If the first row contains field names, confirm that Excel treats it as the header row rather than ordinary data.
In Excel for Mac, look for Data > From Text/CSV in a current Microsoft 365 build. The exact availability and interface can vary by version. If that command is not present, check for updates or use the file-opening route described below, then inspect the imported columns carefully.
Choose between importing and opening
Double-clicking a CSV file or using File > Open is quick, and Excel may automatically place its contents into a worksheet. The trade-off is that Excel has to make decisions about delimiters, character encoding, and data types. Those automatic choices can be wrong, especially for identifiers, dates, or files produced by another country’s software.
Use Data > From Text/CSV when the content matters more than speed, when you need to set the delimiter, or when you expect to repeat the import. The preview offers a chance to catch problems before loading, and Power Query can preserve repeatable transformation steps. Opening a small, simple file is reasonable if you verify the result and do not need those controls.
CSV files can also open in Excel for the web after being uploaded to OneDrive or SharePoint, but the browser experience and available import controls differ from desktop Excel. For a large or sensitive import that needs explicit control over column types or encoding, use the desktop app where the full import workflow is available.
Set delimiters, encoding, and data types
The delimiter tells Excel where one field ends and the next begins. Select comma for a conventional comma-separated file and semicolon if the preview shows each line as one long field or divides it incorrectly. The preview should show meaningful columns, not simply a plausible number of columns: check fields that themselves contain punctuation or quoted text.
Encoding determines how text characters are represented. If names or symbols appear as question marks or garbled characters, the file may have been read using the wrong encoding. In the import preview, check the file-origin or encoding option if it is offered, and select the encoding that matches the source. UTF-8 is common for modern exports, but the correct choice depends on how the file was created. Re-import the original file rather than trying to repair already corrupted text in the worksheet.
Data types deserve special attention. Excel may interpret a field as a number or date when the source intended it to be text. Examples include product codes such as 004812, postal codes, account identifiers, and dates whose day and month order is ambiguous. In Power Query, select Transform Data, select the relevant column, and use Transform > Data Type to choose an appropriate type, such as Text, Whole Number, or Date. Then select Home > Close & Load.
Setting a worksheet column to Text after Excel has already removed leading zeros does not restore them. Preserve such values by setting the column to Text during import or in Power Query before loading. If a field is truly numeric, a number format can change how it looks, but that is different from preserving an identifier as text.
Clean and refresh data with Power Query
Power Query is useful when you need to make the same adjustments every time a CSV is imported. It can remove unwanted columns, rename headers, filter rows, change types, and reorder fields before the result reaches the worksheet. The steps are saved as a query, so a later refresh can repeat those transformations against an updated source file.
For a recurring report, import the CSV through Data > From Text/CSV, choose Transform Data, make the necessary changes, and use Home > Close & Load. Keep the file at a stable path if you plan to refresh the query. If the file is moved or renamed, Excel may not find the source until you update the query’s source location. A query refresh reads the source again; it does not automatically mean that every manual change made to the loaded worksheet will be retained.
Power Query is also helpful for combining files with consistent layouts, but a basic one-time import does not require it. For one CSV that needs no cleanup, loading directly is simpler. For repeat imports or transformations, a query reduces the need to redo the same manual steps and makes those steps visible.
Common CSV import problems and fixes
- Everything appears in one column: Reopen the import and choose the delimiter actually used by the file. Check whether it is comma, semicolon, or tab-separated.
- Leading zeros disappear: Import the affected field as Text. Formatting the column afterward cannot recover zeros that Excel has already discarded.
- Dates are reversed or unexpected: Check the source’s date convention and the locale used to interpret it. If necessary, set the column type deliberately in Power Query rather than accepting an automatic guess.
- Accented characters look wrong: Re-import and select a matching encoding, such as UTF-8 when that is how the source was saved. Do not assume every CSV uses the same encoding.
- Long numbers display in scientific notation or change: If the values are identifiers rather than quantities, import the column as Text. Excel has finite numeric precision, so storing long identifiers as numbers can alter their digits.
- Columns shift on particular rows: Look for embedded delimiters, quotes, or line breaks in the source. Quoting should be handled by a CSV-aware importer; manual splitting can misread valid quoted fields.
If the preview itself is malformed, check the source file and the system that exported it. The file may have inconsistent row structures, an unexpected delimiter, or a header that does not match later records. A text editor can help inspect the raw file, but avoid editing it in a way that changes its encoding or quoting unintentionally.
Save the imported data safely
After importing, save the working file as an Excel workbook if you want to retain the query, worksheet formatting, multiple sheets, or other Excel features. Use File > Save As and choose Excel Workbook (*.xlsx). Saving as CSV is appropriate when another system specifically needs a CSV, but it exports plain text rather than preserving the workbook structure.
Before sending a CSV onward, check which worksheet is active and review the output. Excel may show a warning that some workbook features cannot be saved in CSV format; that warning reflects the format’s limitations. If the receiving system expects a particular delimiter, encoding, header row, or date format, confirm those requirements with its documentation. A CSV that looks correct in Excel can still be interpreted differently by another application.
Frequently Asked Questions
How do I import a CSV into Excel without losing leading zeros? Use Data > From Text/CSV, then choose Transform Data and set the identifier column to Text before loading. For a one-time import, the key point is to make Excel treat the field as text before it converts the values to numbers.
Why does Excel put my CSV data in one column? Excel may be using a delimiter that does not match the file. In the import preview, select the delimiter used by the source, often a semicolon or tab instead of a comma.
Can I open a CSV in Excel by double-clicking it? Yes. Excel can open many CSV files directly, but it may automatically choose the encoding, delimiter, and data types. Use the Data tab import route when you need to check or control those choices.
How do I stop Excel from changing CSV values into dates? Import the relevant column as Text before loading, or set its type to Text in Power Query. If Excel has already converted the value, re-import the original CSV with the type set correctly.
Does importing a CSV keep formulas and formatting? No. A CSV is plain text and does not retain Excel formatting, formulas as working formulas, or multiple worksheets. Save the finished workbook as .xlsx when you need to keep those features.
Related
- Excel Heatmap Table: How to Create and Read One
- Excel: Hide Column Headers Without Hiding Data
- Excel Heat Mapping: How to Create Useful Color Scales
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Dropdown Equals: Formulas and Rules ExplainedTo check whether a Google Sheets dropdown equals a particular choice, compare the dropdown cell with that value…
- How to Unhide Rows in Excel All at Once on WindowsTo unhide every hidden row on an Excel worksheet at once, click the Select All button at the…
- How to Insert an Image in an Excel Cell and Keep It in PlaceTo put a picture directly inside an Excel cell in Microsoft 365 for Windows, select the cell and…
- Excel PivotTable Testing: Checks, Steps, and Common FixesTo test an Excel PivotTable, compare its results with a small source dataset whose totals you can verify…
- How to Delete a Specific Page in Word on Windows and MacTo delete a specific page in Word, click in its text, use Go To to select that page,…
- How to Lock Cells in Excel for the Web and Protect DataTo lock cells in Excel for the web, unlock any cells people should still be able to edit,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.