Connecting to SharePoint and Excel

Analogy categoryAnalogies

SharePoint Lists as a Data Source

SharePoint lists are one of the most common starting points for Power Apps because many organizations already track business data — vacation requests, equipment inventories, help-desk tickets — in a list before ever opening Power Apps Studio. When you add a SharePoint connection, you point it at a specific site and then a specific list, and Power Apps reads the list's column definitions (Single line of text, Choice, Person, Lookup, Yes/No) to infer the schema used in galleries, forms, and formulas. Unlike Dataverse, SharePoint has no concept of a formal relationship beyond its own Lookup column type, so multi-list data models require careful manual joins in Power Fx.

🏏

Cricket analogy: Starting an app from an existing SharePoint list is like a scoring app being built on top of a club's existing paper scorebook format rather than designing a data model from a blank page, inheriting whatever columns the scorer already used.

powerfx

// Filter a SharePoint list called 'HelpDeskTickets' and sort by created date
SortByColumns(
    Filter(HelpDeskTickets, Status.Value = "Open" && Assigned.Email = User().Email),
    "Created",
    SortOrder.Descending
)

Excel as a Data Source

Excel workbooks can be used as a Power Apps data source only when the data lives in a properly defined Excel Table (Insert > Table, not just a range of cells with headers) and the workbook is stored in OneDrive, SharePoint, or another supported cloud location — a local desktop file is not usable. Because Excel has no native choice, lookup, or person column types, every column effectively behaves as free text or number, meaning an app built on Excel loses SharePoint's basic input validation and Dataverse's strong typing entirely, pushing more validation logic into the app's own Power Fx formulas.

🏏

Cricket analogy: Requiring a real Excel Table object instead of a loose range is like a scorecard only being officially recognized by the umpires once it's filled in the standardized format, not scribbled on a random sheet of paper.

Delegation and File-Locking Limits

SharePoint has moderate delegation support — Filter and Sort work on most standard column types, but formulas involving certain Choice or Lookup column comparisons, or functions like StartsWith combined with other conditions in some configurations, can fall outside delegation and silently cap at 2,000 (or up to the app's configured Data row limit) records. Excel, by contrast, has essentially no delegation: every Filter, Sort, or Search against an Excel table is pulled into local memory first, so Excel-backed apps are only appropriate for genuinely small datasets, and because Excel Online does not support true concurrent multi-user writes as reliably as SharePoint or Dataverse, simultaneous edits from multiple users can occasionally overwrite each other.

🏏

Cricket analogy: Excel having no delegation is like a scorer manually re-adding every run from the entire tournament's printed scorecards each time someone asks for the current total, instead of a live database query giving an instant answer.

Never point Power Apps at an Excel workbook stored on a local hard drive or a mapped network drive — only OneDrive, SharePoint, and a small set of other cloud locations are supported, and using an unsupported location causes the connection to silently fail to refresh for other users.

Choosing Between SharePoint and Excel

As a rule of thumb, SharePoint is the better choice whenever an app needs multiple users editing concurrently, any meaningful data validation (Choice, Person, Yes/No columns), or attachments and versioning, while Excel is acceptable only for prototypes, single-user tools, or genuinely static reference data such as a lookup table of country codes that rarely changes. Both sources should be treated as a stepping stone rather than a permanent architecture for any app expected to grow past a handful of users or a few thousand records — migrating to Dataverse later means rebuilding forms and re-pointing data sources, so it's worth evaluating expected scale honestly before building extensively on either.

🏏

Cricket analogy: Treating SharePoint or Excel as a stepping stone is like a young cricketer starting in club-level cricket before graduating to first-class and international cricket — a valid starting point, not the final destination.

SharePoint lists infer their schema from column types like Choice, Person, Lookup, and Yes/No, but have no formal cross-list relationships beyond Lookup columns.
Excel requires data to be defined as a real Excel Table stored in OneDrive or SharePoint; local files are not supported.
Excel columns have no native typing, so validation logic that Dataverse or SharePoint provide must be rebuilt in Power Fx.
SharePoint has moderate delegation support; some Choice/Lookup comparisons can fall outside delegation.
Excel has essentially no delegation — all filtering happens after pulling data into local memory, capping practical scale.
Excel Online's weaker concurrent-write handling can cause simultaneous edits from multiple users to overwrite each other.
Both sources are best treated as a starting point; apps expected to scale should plan a path to Dataverse.