Import Excel Data Into a SharePoint List: Best Settings for Microsoft 365

You want to move data from an Excel table into a SharePoint list without losing formatting or breaking column types. The default import wizard in SharePoint often misidentifies column types or fails to preserve key values such as dates and numbers. This article explains exactly which settings to choose in the import wizard, how to prepare your Excel table, and what to do when the import produces unexpected results.

By following the steps here you will create a SharePoint list that mirrors your Excel table structure, keeps data types correct, and avoids common errors like truncated text or missing hyperlinks.

How SharePoint Imports Excel Data: What Happens Behind the Wizard

When you use the built-in Import spreadsheet command in SharePoint, the system reads the first row of your Excel table as column headers. It then attempts to detect the data type for each column based on the first few rows of data. This detection is not perfect. For example, a column that contains mostly numbers but has one text value may be treated as a single line of text, or a date column formatted as text in Excel may not become a real date column in the list.

The import creates a new SharePoint list with the same name as your Excel file. All rows become list items. The original Excel file is not modified or linked to the list after import. If you update the Excel file later, the list does not update automatically.

Column Type Mapping Rules

SharePoint maps Excel data types to its own column types as follows:

• Text cells map to Single line of text (limit 255 characters). Longer text is truncated.
• Numbers map to Number (no currency symbol).
• Currency formatted cells map to Currency.
• Dates map to Date and Time (date only, no time).
• Hyperlinks in Excel (HYPERLINK function) map to Hyperlink.
• True/False values map to Yes/No.

If your Excel column contains mixed data types, the wizard defaults to Single line of text for the entire column. This is the most common cause of import failures.

Steps to Import Excel Data with the Correct Settings

1. Prepare your Excel table
   Open the Excel file and select the data range. Press Ctrl+T to convert the range into a named table. Ensure each column has a unique header in the first row. Remove blank rows and columns. Save the file to a SharePoint document library or OneDrive for Business.
2. Open the SharePoint site where you want the list
   Navigate to the site. Click the gear icon (Settings) and select Add an app. Alternatively, go to Site contents > New > List.
3. Start the import wizard
   In the Create a list panel, choose From Excel. A file picker opens. Locate and select your Excel file. Click Open.
4. Review column types in the wizard
   SharePoint displays a preview of the table. Below each column header you see the detected data type. Click the dropdown arrow next to each type to change it. For example, if a column should be a number but shows as text, change it to Number. Set Date and Time for date columns. Set Hyperlink for URL columns.
5. Set the list name and description
   At the top of the wizard, type a list name. Optionally add a description. The list name becomes the URL path for the list.
6. Click Create
   SharePoint processes the file and creates the list. The process may take a few seconds to a minute depending on the number of rows. After creation, the list opens in the browser.
7. Verify column types and data
   Click the gear icon and select List settings. Under Columns, review each column type. If a column is still incorrect, edit it and change the type. Note that changing a column type after creation may cause data loss if the existing values cannot be converted.

Common Problems When Importing Excel Data to a SharePoint List

Text is truncated after import

The default Single line of text column holds up to 255 characters. Any text beyond that is cut off. Before importing, change the column type in the wizard to Multiple lines of text (plain text or rich text). If the list already exists, you must create a new list or use Power Automate to move data into a column with the correct type.

Dates show as numbers or text

If Excel stores dates as text strings, SharePoint cannot detect them as dates. In Excel, select the column and press Ctrl+Shift+# to apply the Date format. Then save the file and re-import. Alternatively, after import, edit the column in list settings and set the type to Date and Time. Existing text values may not convert automatically.

Hyperlinks are lost or show as plain text

The wizard only recognizes hyperlinks created with the HYPERLINK function in Excel. Plain URLs typed as text are imported as text. To preserve them, use the HYPERLINK formula in Excel before importing. After import, you can also add a new Hyperlink column manually and paste the URLs using the Edit in grid view option.

Imported data does not update when Excel changes

The import is a one-time copy. To keep the list in sync, use Power Automate. Create a flow that triggers when the Excel file is modified. Use the List rows present in a table action to read Excel data, then use Update item or Create item actions to write to the SharePoint list. For large tables, schedule the flow to run hourly or daily.

Import Settings Comparison: Default vs Recommended

Setting	Default Behavior	Recommended for Best Results
Column type detection	Auto-detect based on first rows	Manually verify each column type in the wizard
Text column length	Single line of text (255 characters)	Change to Multiple lines of text if data exceeds 255 characters
Date format	Date only, no time	Use Date and Time if time values matter
Hyperlink handling	Only HYPERLINK function recognized	Use HYPERLINK in Excel or add Hyperlink column after import
Data sync	One-time import only	Use Power Automate flow for recurring updates

Now you can import Excel data into a SharePoint list with confidence. The key is to prepare your Excel table as a named table with consistent data types. During the wizard, manually check each column type and change it if needed. After creation, verify the list settings to confirm column types match your original data. For ongoing synchronization, build a Power Automate flow that reads the Excel table and updates list items automatically. This approach saves time and prevents data corruption from repeated manual imports.