Forum Discussion

Bud4321's avatar
Bud4321
Tin Contributor
Oct 09, 2026

Workflow for importing Excel to List

I'm a novice developing a workflow for importing an Excel spreadsheet to a SharePoint list. Have I missed anything?

Workflow:

  1. Thoroughly clean up the data in Excel before importing to a list.
    • Caution: Excel filters (and list filters) are case-insensitive and ignore superfluous spaces, etc. So we can't rely solely on filter functionality to find messy values. Instead, consider using the EXACT function in Excel like this: =OR(EXACT(A2,"PRIMARY"),EXACT(A2,"SECONDARY")).
      See this Stack Exchange post for more options: https://sharepoint.stackexchange.com/questions/317576/find-list-values-that-arent-in-the-picklist-choices
      Or, as a last resort, we could consider importing to an Oracle table and use a GROUP BY query (SQL) to find incorrect values, since that's dead easy in databases. I don't use Access for this because it has the same filtering limitations that Excel and lists have.
  2. Manually create an empty list. Or import a blank list from Excel and modify it.
    • Caution: When importing from Excel to a list, the list won't honor the Excel column names for the internal list names, even if the Excel names are clean. The list will generate column names like "field_0" through to "field_30" (or whatever applies).
      Google suggests importing from Access is an alternative -- the column names should be honored. https://i.sstatic.net/3KoAsrMl.png
    • Caution: The time zone for my SharePoint sites and personal MS list environment is wrong. It's set to Pacific Time, when it should be Eastern Time. Keep an eye on any list date values to ensure they are correct and haven't been automatically changed to a different day.
  3. Import the Excel rows/data using Power Automate.
    • For what it's worth, any dates that get imported via PA will be correct, despite the time zone, unlike when importing data from Excel using the list UI.
  4. Inspect the imported data, correct the list columns/settings, correct the Excel data, and repeat #3.
    • Do this multiple times -- as many times as it takes.

1 Reply

  • Rob_Elliott's avatar
    Rob_Elliott
    Silver Contributor

    On your #3, if using Power Automate to import the rows (rather than just copying/pasting) you need to change the settings of the List rows present in a table action. By default it will only bring back a maximum 256 rows, so you need to go to settings, turn on the pagination toggle and change the threshold to a number that is more than the number of rows in your excel table.

    Rob
    Los Gallardos, Spain
    Microsoft Power Platform Community Super User
    Principal Consultant, Power Platform, WSP Global (and classic 1967 Morris Traveller driver)