Microsoft Lists import help: fix CSV and Excel failures

Microsoft Lists can create a new list from an Excel table or a CSV file, but the importer is less forgiving than the spreadsheet you started with. Most failures become much easier to diagnose when you separate three different jobs:

creating a new list from Excel or CSV;
adding rows to an existing list; and
building a repeatable integration that imports data on a schedule.

They are not the same feature, and they do not have the same limits.

The current Microsoft limits that matter

Microsoft's current create a list from a spreadsheet guidance says:

an Excel source must contain a table, or the wizard will prompt you to create one;
tables with up to 5,000 rows can be used to create a list;
both From Excel and From CSV are available in the modern list-creation flow; and
a file uploaded from your device is copied to the site's Site Assets library, where other people with access to the site can see the original data.

That 5,000-row import limit is not the maximum size of a SharePoint list. Microsoft says a list can contain up to 30 million items, subject to other practical limits and good large-list design. The familiar 5,000-item list view threshold is also a query and view constraint, not a claim that a list can hold only 5,000 items.

The old version of this article quoted a 200 MB CSV limit from a different Power Apps import feature and a 20,000-row figure from Microsoft Q&A. Neither is the right primary source for this Microsoft Lists wizard, so those claims have been removed.

Fast route: create a new list from Excel or CSV

1. Work on a copy

Keep the source untouched and prepare a separate import copy. If an attempted clean-up changes dates, identifiers or leading zeroes, you need an original to compare against.

For sensitive data, also decide where the source is allowed to live. Microsoft explicitly warns that a locally uploaded Excel or CSV file is saved in Site Assets. Check the site's membership and your retention rules before uploading it.

2. Give every column a simple, unique header

Use one header row. Remove blank headers, duplicates and line breaks. Keep the names short and meaningful.

The original Collab365 support thread behind this page traced one failed import to a hidden line break in a heading. That is a useful troubleshooting clue, not a universal explanation for every import error.

If a heading looks suspicious, click the cell and inspect the formula bar. In desktop Excel, Find and Replace can search for a line break by placing the cursor in Find what and pressing Ctrl+J. Test the change on a copy.

Simple source headers also make later Power Automate and API work easier. They do not need to become user-unfriendly display labels; you can adjust the visible column names after the list exists.

3. Make Excel data a real table

For an .xlsx source, select the data and use Home > Format as Table. Confirm that the table has headers and that its range contains only the rows you intend to import.

Check for:

totals or notes below the actual dataset;
hidden rows or columns;
formulas returning errors;
mixed types in the same column; and
empty rows inside the table.

Do not assume that a worksheet containing data is automatically an Excel table.

4. Remove ambiguous values before the first test

Start with a small representative sample—perhaps ten rows—not the entire file. Include the awkward cases deliberately.

Pay particular attention to:

dates such as 03/04/2026, which depend on locale;
identifiers with leading zeroes;
numbers containing currency symbols or thousands separators;
Yes/No values expressed in several different ways;
formula errors such as #N/A; and
text containing embedded line breaks.

Where a date could be misread, use a clearly defined format and verify the preview. Do not convert everything to plain text automatically; that can create a different clean-up job after import.

5. Create the list and inspect the preview

In Microsoft Lists or SharePoint, choose New list, then From Excel or From CSV. Select the table or file and review the proposed column types.

Do not click through on trust. Check several values in every column, especially dates, long numbers and identifiers. If the inferred type is wrong, correct it in the wizard where possible or go back and make the source unambiguous.

Microsoft notes that importing a spreadsheet is also a way to create a list without the default Title column. The Title field is not a primary key, and you do not have to pretend that it is one.

If the error says “data doesn't match the column type”

That message describes a category of failure, not the bad cell. Use a controlled narrowing process:

Try only the header plus ten rows.
If that works, double the number of rows until it fails.
Split the failing block in half.
Repeat until you isolate the row.
Compare each value in that row with the proposed column type.

This binary-search approach is faster and safer than repeatedly changing the whole workbook.

If the ten-row sample fails, simplify the columns instead. Try the first few columns, then add columns back until the failure returns. That will tell you whether the problem is a row value or a column definition.

Complex columns: create first, enrich second

People, lookups, managed metadata, multi-value fields and attachments are not ordinary CSV text. For a dependable migration, import simple source values first and resolve complex relationships in a controlled second stage.

Examples:

keep a person's email address in a staging text column, then resolve it to a Person field;
keep the source key for a lookup, then map it to the target item's ID;
migrate attachments as files with an explicit link to their destination item; and
log values that could not be resolved instead of silently leaving them blank.

Do not promise that an email address will produce a “100% match”. The account may not exist, may be blocked from the site, or may not be resolvable by the connection being used.

Add rows to an existing list

The Excel/CSV wizard creates a new list. For a modest one-off append to an existing list, use Edit in grid view.

Microsoft's grid-view instructions confirm that you can paste several items at once from an Excel range or Word table. They also say:

position the cursor in the first blank column;
make sure every value matches its destination column type; and
attachments cannot be added in grid view.

Before pasting:

create a view containing only the destination columns in the same order as the source;
exclude the spreadsheet's header row;
paste a few rows first;
verify saved values, dates and people fields; and
only then paste the next controlled batch.

Grid view is excellent for tens or a few hundreds of straightforward rows. It is not an auditable recurring integration.

For a recurring import, design an integration

A weekly CSV append needs more than split() on commas. CSV values can contain quoted commas, quotes and line breaks, so a naive line-and-comma split can corrupt valid data.

A production import should define:

the unique business key for each row;
whether a matching row is updated, skipped or rejected;
how deletions are handled;
batch size and concurrency;
a checkpoint so a failed run can resume;
an error file or list with the source row and reason; and
who is alerted when some rows fail.

If you use Power Automate, prefer a source it can parse reliably, such as a controlled Excel table, or use a CSV parser that correctly handles quoted fields. Create or update items in measured batches and respect SharePoint throttling. For large or business-critical migrations, assess Microsoft Graph, PnP PowerShell, Dataflows or a specialist migration tool rather than forcing a browser import to do integration work.

Large lists: plan the view before loading the data

A list can hold far more than 5,000 items, but users can still hit the list view threshold when a query asks SharePoint to process too much at once.

Before a large load:

index the columns used for filtering;
make the default view return a bounded set;
avoid grouping or sorting an unfiltered huge result;
minimise unique item-level permissions; and
test the actual views and flows at expected volume.

Use Microsoft's SharePoint Online limits and large-list guidance as design inputs. A capacity ceiling is not a performance target.

A clean import checklist

I am creating a new list, not trying to append through the creation wizard.
The Excel source contains a real table with no more than 5,000 rows.
Every column has one unique, single-line heading.
The first test contains a small but representative sample.
Dates, identifiers and numbers survive the preview correctly.
I have checked whether the uploaded source may be stored in Site Assets.
Complex people, lookup and attachment fields have a second-stage plan.
Large-list views and indexed filters are ready before the full load.
A recurring import has keys, checkpoints and an error log.

Official sources

For practical Microsoft Lists and SharePoint administration help, join the SharePoint & Teams Admins Space.