How to combine monthly reports with AI without losing track of the numbers
Three monthly spreadsheets arrive. The headings are different, one file includes a correction and another has a total row at the bottom. You want one report, but copying everything into a new tab could count the same work twice.
AI can help inspect the files, propose a common structure and prepare a repeatable way to combine them. The useful part is making the rules visible: what each row means, which records belong together and how the final numbers connect to their sources.
This guide takes you through that process with a worked example and three prompts you can copy. Use an organisation-approved tool and only data you are authorised to share. Work on copies of the files, especially when the assistant can edit them.
Start with the question the report needs to answer
Consider this example: a delivery lead wants to compare how many support requests two teams closed in January and February 2026. Each source contains monthly totals. The required output is one row for each team and month, with a count of closed requests.
That last sentence defines the level of detail. It prevents individual requests, monthly totals and running totals being mixed in the same calculation. A running total includes earlier periods, so adding successive snapshots would count some requests more than once.
Before asking AI to combine anything, agree the measure, reporting period, units and inclusion rules. “Resolved” and “closed” might have different meanings in two systems. A matching column heading cannot establish that the underlying definitions match.
A practical workflow for combining the files
1. Inspect the sources and propose a mapping
Purpose: Understand what you have before changing it.
Ask for an inventory of filenames, sheet names, columns, data rows and reporting periods. A column mapping records which source field corresponds to each field in the new report. It might map “Squad” to “Team”, once someone has confirmed they mean the same thing.
Also check filters, hidden rows, merged headings, formulas and subtotal rows. Some assistants can inspect workbook structure; others only see an extracted table. Ask the tool to state what it actually accessed. A plain chat answer alone does not demonstrate that a workbook was processed.
2. Decide how records should be combined
Purpose: Choose the operation and resolve overlapping versions.
Appending stacks rows with the same structure. Joining adds related columns by matching a key, such as a team identifier. A join can multiply rows if the key appears more than once on either side. In this example, appending the approved monthly records is enough.
A useful record key is the combination of team and reporting month. It identifies where competing records need attention, but does not justify deleting either record automatically. The same count can legitimately occur in two different months.
| Source | Team / month | Closed |
|---|---|---|
| January, row 2 | North / Jan 2026 | 40 |
| January, row 3 | South / Jan 2026 | 30 |
| February original, row 2 | North / Feb 2026 | 45 |
| February original, row 3 | South / Feb 2026 | 35 |
| February correction, row 2 | North / Feb 2026 | 47 |
The data owner confirms that the correction replaces only North’s February count. South’s original February record still applies. Keep that decision with the report. The newest file is not automatically a complete replacement for every earlier file.
3. Create a new table and keep the source trail
Purpose: Make each selected figure traceable.
Retain the source filename, sheet, original row and version beside each output record. This source trail helps a colleague answer “where did 47 come from?” without repeating the whole task.
Keep missing values visible. A blank could mean unknown, not reported or not applicable. Converting it to zero changes the meaning. Likewise, preserve identifiers with leading zeroes and resolve ambiguous dates before reformatting them.
Ask AI to prepare a formula, query or script that can be inspected and rerun. Use the calculation tool to execute it and check the output. For a recurring report, a reviewed repeatable process can reduce the need to reconstruct the rules in a new conversation each month.
4. Reconcile the rows and totals
Purpose: Explain how the inputs became the output.
Reconciliation means checking that the final figures can be accounted for from the inputs and the agreed changes. In the example, five input data rows become four selected records and one superseded record. No rows remain unresolved.
The January total is 40 + 30 = 70. February is 47 + 35 = 82. The two-month total is 152, assuming the team counts do not overlap. Adding all five input rows would give 197, including North’s February work twice.
Keep the original input total of 197 in the reconciliation, then show the excluded superseded value of 45: 197 − 45 = 152. This explains the change instead of forcing the selected result to match an unsuitable input total.
Check totals by team and month, not just one grand total. Two offsetting mistakes can leave the overall figure unchanged. Inspect a few source-to-output records yourself, and check every exception before using the report for a decision.
What if you only have a chart or screenshot?
Ask for the underlying spreadsheet or data export first. A chart may omit exact figures, hide filtered categories or show rounded values. A screenshot also loses formulas and the transformation history.
If an image is the only source, keep any extracted numbers in a separate table and check each against the visible labels. Mark unclear values as unknown. Do not treat estimates based on bar heights as exact measurements or mix them into verified totals without a clear explanation.
Use data quality checks that match the task
The Government Data Quality Framework distinguishes completeness, uniqueness, consistency, timeliness, validity and accuracy. These give you useful questions: are periods missing, are records repeated, do definitions agree, are versions current, are formats valid and do values match the source? A fully populated table can still contain incorrect figures.
For this example, the most useful handover is the combined table, approved mapping, version-selection decision, exceptions log and reconciliation. Add a short note naming who reviewed it and when. A confident AI summary cannot replace those records.
Try it with two small reports
Start with two files whose totals you already know. Use the inspection prompt, approve the mapping and then check whether the combined output matches your expectations. Leave a test blank or competing version in your practice copies and see whether it is surfaced.
Before sharing the report, choose one number and ask a colleague to trace it back to its source. If they can find the record and understand the selection rule, you have a clearer basis for the next reporting cycle.
Source checked 21 October 2026: the Government Data Quality Framework, linked above. The support-report example, calculations and prompts are teaching examples. File-processing capabilities vary by tool; the prompts require the assistant to disclose checks it cannot perform.
You May Also Like
How to write useful user stories: examples and acceptance criteria
6 AI prompts to make everyday tech tasks clearer
