Fix Missing Rows When Importing Excel Data into SharePoint
Question details
Users experience missing or omitted rows when importing Excel workbook data into a SharePoint list due to inconsistent data types or formatting issues.
- Product
- Microsoft Excel / SharePoint
- Device & OS
- not provided
- Scenario
- Importing an existing Excel workbook into a SharePoint list.
- Observed behavior
- SharePoint omits specific Excel rows during the import process. Date fields are frequently imported as raw numbers, and finding the invalid data is difficult in large workbooks.
Ensure you have administrative or edit permissions for the target SharePoint site and make a backup copy of your original Excel workbook before modifying its data types.
Align Excel Data Types with SharePoint Columns
Manually compare and adjust your Excel column formats to perfectly match the destination SharePoint column types, particularly for date and number fields.
SharePoint relies on strict column configurations (e.g., Single line of text, Number, Date and Time). If your Excel file contains mixed data types in a single column or unformatted dates, SharePoint may reject and omit those rows entirely during import.
Open your SharePoint list settings and carefully review the required data types for each column to understand how your Excel data needs to be structured.
Open your Excel workbook, highlight the columns containing the data, right-click the selection, and click 'Format Cells'. Ensure the format exactly matches the SharePoint requirement (e.g., set date columns explicitly to 'Date' rather than 'General').
Scan the columns for any inconsistent entries, such as text inside a number column, and correct them manually rather than deleting the rows blindly.
Save your newly formatted Excel file and attempt the SharePoint import process again.
Export Directly from Excel to SharePoint
Use the built-in 'Export' feature within Microsoft Excel to push a formatted table directly to a SharePoint list, which often handles data type mapping better.
Use WPS Office for Seamless Data Formatting and Management
If you are tired of complex integration errors and subscription fees, WPS Office offers a lightweight, highly compatible alternative for managing your spreadsheets. Easily format data, clean up large workbooks, and save your files in standard formats.
- 1. Download WPS Office: Visit the official WPS website to download and install the free suite.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and easily open your existing Excel workbooks.
- 3. Format and Save: Use the intuitive cell formatting tools to organize your data, then save directly as an .xlsx file.
Frequently Asked Questions
Why are dates from my Excel file appearing as numbers in SharePoint?
Excel stores dates internally as sequential serial numbers. If the destination SharePoint column is not strictly configured as a 'Date and Time' type, or if the Excel column is set to 'General', SharePoint will read and display the raw serial number instead of the formatted date.
Should I delete the rows that SharePoint refuses to import?
No. Blindly deleting rows can cause the import error to simply shift to other preceding rows. Instead, you should inspect the specific data format and correct any inconsistent values in those rows before attempting the import again.
Does the size of the Excel workbook affect the SharePoint import process?
Yes, very large Excel files can time out or make it difficult to locate formatting errors hidden deep within the data. It is recommended to break extremely large workbooks into smaller, manageable batches to isolate problematic rows during import.
