Expert guide
Connect Excel to a SharePoint List with Power Query
A refreshable report on top of the list your team updates, with lookup and person columns handled
Call us at +1 (385) 386-3860
Short answer: in Excel choose Data → Get Data → From Online Services → From SharePoint Online List, paste the site URL (for example https://company.sharepoint.com/sites/Ops), sign in with your work account, pick the list, then in Power Query keep only needed columns, expand lookup and person fields, set data types and load. Refresh pulls the latest items. Power Query is read-only; write back with Power Automate. For broader integrations, see Excel database integration.
SharePoint lists have become the default place for many teams to track requests, assets, incidents and projects, because they are easy to share and edit in Teams. Reporting on them in SharePoint is limited. Excel with Power Query turns any list into a refreshable source for PivotTables, charts and dashboards, combined with data from other systems.
Step-by-Step Connection
1. Get the site URL
Open the list in the browser and copy the address up to the site name, e.g. https://company.sharepoint.com/sites/Ops. Do not include /Lists/...
2. Connect
Excel → Data → Get Data → From Online Services → From SharePoint Online List. Paste the site URL. Choose the 2.0 implementation if offered, which is faster and returns columns as displayed.
3. Sign in
Choose Microsoft account / Organizational account and sign in.
4. Pick the list
Select it in the Navigator and click Transform Data.
5. Shape and load
Choose columns, expand complex fields, set types, then Close & Load to a table or the Data Model.
Handling SharePoint Column Types
| SharePoint column | Arrives in Power Query as | What to do |
|---|---|---|
| Single line / number / date | Plain values | Set data types |
| Choice | Text (single) or list (multi-select) | Extract values with a delimiter for multi-select |
| Lookup | Record | Expand to Title (and ID if needed) |
| Person or Group | Record or list of records | Expand to Title and Email |
| Managed Metadata | Record | Expand to Label |
| Created / Modified / Author | System fields | Keep for audit and ageing |
Expanding a person column in M:
#"Expanded AssignedTo" = Table.ExpandRecordColumn(Source, "AssignedTo",
{"Title", "Email"}, {"Assigned To", "Assigned Email"})
Multi-select choice to text:
#"Categories as text" = Table.TransformColumns(#"Expanded AssignedTo",
{{"Category", each Text.Combine(_, "; "), type text}})AI assistants are good at writing these expansion steps when you paste your query; see writing Power Query M with AI.
Making Refresh Fast and Reliable
Remove unneeded columns immediately after the Source step; SharePoint lists include many system fields.
Filter early (for example, Modified in the last 12 months) to reduce rows.
Index the columns you filter on in the SharePoint list settings.
Rename columns only once, near the end, so changes in SharePoint display names are easy to fix.
If the workbook is in SharePoint or OneDrive, refresh settings and credentials apply per user; each user needs access to the list.
Reports That Work Well on List Data
Request or ticket ageing by status and owner.
Asset register by location with warranty expiry.
Incident counts by category and month.
Project tracker summaries with overdue tasks by person.
Age in days (custom column in Power Query): = Duration.Days(DateTime.LocalNow() - [Created])
Writing Back: Options
| Need | Use |
|---|---|
| Users edit individual items | The SharePoint list itself or a Power Apps form |
| Bulk add rows from Excel | Power Automate flow reading an Excel table and creating items |
| Two-way sync with another system | Power Automate or a custom integration via Microsoft Graph / SharePoint REST |
| Heavy relational data | Consider SQL Server or Dataverse instead of lists |
Lists are convenient for simple tracking but have limits as data grows; the trade-offs for Access users are covered in Access with SharePoint lists, and platform choices in Access vs Dataverse vs SQL Server. For combining list data with other sources in one report, see Excel data integration.
Combining a SharePoint List With Other Data
The real power of the Power Query connection is joining list data with data that lives elsewhere. A few common combinations:
Asset list in SharePoint plus purchase invoices from accounting exports: warranty and cost per asset in one table.
Request list plus an employee table from HR: requests by department and manager.
Project list plus timesheet exports: hours and cost per project against budget.
Incident list plus a calendar table: incidents per week, with weeks of zero shown correctly.
Merge the list with another query in Power Query:
= Table.NestedJoin(SPRequests, {"Department"}, HRDepartments, {"Department"},
"HR", JoinKind.LeftOuter)
then expand the HR column to the fields you need.Load the combined result to the Data Model for larger reports, as described in our Excel Data Model guide, so PivotTables can slice by any column from either source.
FAQ
Excel and SharePoint lists FAQ
Connecting, column types and write-back