Issue with M365 Agent Comparing Excel Sheets – Stops After Few Rows

The behavior is caused by limits on how much data an agent can process from a file in a single run, not just by token limits. For Excel- or table-like workloads, Microsoft agents do not guarantee full-sheet processing when the dataset is large; they typically work on a subset of the data.

For Finance-style reconciliation and comparison scenarios, there are explicit limits on rows, file size, and mapping complexity that affect how much of each sheet can be reliably processed:

• Maximum rows per sheet supported for reconciliation: 1,000,000
• Maximum file size: 15 MB per source file (for autonomous/Copilot Studio agents)
• Maximum number of mapping key pairs: 20 (for autonomous agents)

Even when the files are within these limits, agents may still only operate on a partial extract of the data unless the scenario is designed to respect these constraints and the agent is fully published and configured.

In addition, there is a known behavior in Copilot Studio agents where, if the agent is not published, it only processes a small subset of the rows (around 10) from a connected file. This matches the symptom of “only a few rows” being processed.

To handle this scenario and get complete comparisons:

1. Ensure the agent is published
   • In Copilot Studio, publish the agent and test it via the published surface (e.g., Teams, web, or the test canvas after publish). Unpublished agents may only read a limited extract of the data (about 10 rows), which explains why processing stops after a few rows.
2. Keep within supported file and structure limits
   • Ensure each Excel file is:
     • ≤ 15 MB (for autonomous/Copilot Studio agents)
     • ≤ 1,000,000 rows per sheet
     • Using a reasonable number of key columns for comparison (well under the 20 mapping key pair limit for autonomous agents).
   • If files are close to these limits, consider splitting them into logical chunks (for example, by date range or category) and running the agent per chunk.
3. Design the comparison around key columns, not all columns
   • Reconciliation limits are measured on the columns used as mapping keys, not on all columns in the source data. For large sheets, define a small set of key columns (IDs, dates, statuses) that the agent uses to detect differences.
   • This reduces complexity and helps the agent stay within its operational limits.
4. Use a staged or batched approach
   • If the sheet is large or complex, design the use case so the agent:
     • Processes subsets of the data (for example, one table or one filtered segment at a time).
     • Writes intermediate results (e.g., differences for a segment) back to a separate sheet or file.
   • Then aggregate these partial results in Excel or another downstream process.
5. Validate that the source is accessible and shared correctly
   • For agents using SharePoint/OneDrive-based knowledge or file connections, ensure the underlying folder/files are shared with all intended users. If the agent cannot fully access the file, it may behave as if only a small portion is visible.
6. Consider using Excel-native logic for full-sheet comparison
   • For strict, row-by-row comparisons of large sheets, combine:
     • Excel formulas or Power Query to compute the raw differences across all rows.
     • The agent to summarize, explain, or classify those differences rather than to perform the entire comparison itself.

By publishing the agent, staying within the documented file and row limits, minimizing mapping keys, and (if needed) batching the data, the comparison scenario can be made reliable and avoid stopping after only a few rows.

References:

• Finance Reconciliation agent limits
• Known issues for the Finance Agent for Excel add-in
• Copilot Studio Agent will not read entire data set in Excel or markdown - Microsoft Q&A
• Copilot Studio Agent doesn't recognize the docs in the Knowledge Base. - Microsoft Q&A