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

Question

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

Deepankar 390

I am creating a Microsoft 365 agent where the input consists of two Excel sheets (old status and new status), each around 5 MB in size. The agent’s task is to identify and report the differences added in the new sheet compared to the old one.

The functionality works correctly in principle, but I’ve observed that the agent only iterates through a few rows and then finalizes the response without processing the entire sheet. This makes the comparison incomplete.

Could this be related to token limitations or another constraint in the agent’s execution? How can I handle this scenario or design the use case so that the agent processes all rows in the Excel files without stopping prematurely?

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Sayali-MSFT 6,476 Microsoft External Staff Moderator

Hello Deepankar,
This issue occurs due to token and processing limits—Copilot agents don’t iterate through entire large Excel files and instead process only a subset of data. As a result, comparisons may be incomplete.

To handle this, avoid direct large file comparisons and instead pre-process the data (e.g., using Power Automate, scripts, or backend logic) or split it into smaller chunks, and use Copilot only for summarizing the results.

Lu, Kingsley 0 Reputation points

2026-06-25T06:05:53.2566667+00:00

Hi, I’ve run into similar issues, and I still haven’t found a solution. My sense is that today’s AI or Copilot Agent is better at reasoning and calculation than at accurate mapping. In real use cases, though, an Agent needs to read the relevant documents to produce a useful summary or suggestion.

Answer 2

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:

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.
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.
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.
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.
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.
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: