How to Combine Excel Files With AI and Check for Missing Rows

To combine Excel files with AI, define which files belong in the result, align their columns, and preserve a source reference for every row. Check the combined table before removing duplicates. A correct grand total alone cannot prove that every input record survived.

This guide uses two fictional exports and an independently calculated editorial example. It is not a live test of an AI product. You can recreate the small tables below; no download is needed. An assistant’s ability to open multiple workbooks or return an Excel file depends on the tool and access you have.

1. Decide What “Combine” Means

Append means stacking records from one table below another. Use it for two exports with the same meaning and columns. Merge means matching records by a key to bring related fields together, such as adding customer details to orders. This guide covers appending exports; it does not join customer and order tables.

Microsoft’s append guidance distinguishes these operations. In Power Query, append matches column names, not column positions. If one query lacks a column, its rows receive null in that column. Read the append-query documentation before treating different headers as equivalent.

Write down the reporting period, units, currency, and inclusion rules first. “Amount” and “Amount_USD” might refer to the same field, but their names alone do not establish that. An assistant should propose the mapping and identify uncertainty before applying it.

2. Inventory the Files Before Combining Them

For this fictional exercise, both workbooks contain a table named Orders with text Order_ID and numeric Amount_USD columns. Amounts exclude tax. The owner confirms that these are overlapping exports of the same order system, with one record per order and a genuine zero-value order. The common ID 0102 is the same unchanged record in both exports.

File A.xlsx, table Orders:

File B.xlsx, table Orders:

A1–B3 are trace labels for this exercise, not physical Excel row numbers. Record each workbook name, table or sheet, source row label, expected detail-row count, and subtotal in a manifest. Each file has three detail rows and a subtotal of 150.00. The input inventory therefore contains six rows and 300.00 before overlap is resolved.

For real files, record versions and date ranges too. Exclude temporary files, old output workbooks, and unrelated sheets explicitly. Keep identifiers as text and preserve blank versus zero. If your source started as a PDF, finish the PDF-to-Excel extraction checks before combining its rows with another file.

3. Ask AI to Preserve the Raw Combined Rows

Provide only files you are allowed to share with the chosen assistant. Start with a small representative sample. Ask it to list the workbooks and tables it can actually read; a filename mentioned in a reply does not prove that the table was inspected.

Append the Orders tables from A.xlsx and B.xlsx. Preserve Order_ID as text and Amount_USD as numeric USD excluding tax. Add Source_file, Source_table, and Source_row to every record. Keep all six source rows in RawCombined, including zero amounts and repeated order IDs. Do not guess missing values or remove records yet. Return a file manifest with rows and subtotal per source, a proposed column mapping, and an issue report. Flag repeated IDs and show all conflicting values. If a file, table, or required column cannot be read, report it and label the result incomplete.

After checking the raw result, apply a separate instruction for this sample: “The owner confirms B1 duplicates A2. Keep A2 in Final; record B1 in Exclusions with reason and retained source reference. Make no other exclusions.” This approval depends on the sample’s business rule. Matching amounts or matching IDs alone are insufficient in an order-line dataset.

If the same ID has different amounts, keep both records in review until the owner establishes the authoritative value. Do not silently prefer the last file, average the values, or delete both. Our spreadsheet cleanup example covers conflicting records in more detail.

4. Use a Repeatable Excel Route When Appropriate

For two already imported, aligned queries, use Home → Append Queries → Append Queries as New in Power Query to create a separate combined query. Review the columns and data types before loading it. Preserve source references before appending so that the final rows can be traced back.

For a recurring folder of workbooks with the same structure, supported Excel installations offer Data → Get Data → From File → From Folder. Review the file list, then use the combine-and-transform option and select the intended table in the sample workbook. Keep the generated output outside the input folder. Microsoft’s folder import guide describes the setup and its consistent-schema requirement. The available commands depend on your Excel version and platform.

Inspect the transformation steps and any errors before loading. A skipped file must appear in the manifest as excluded or unresolved; a successful load is not a completeness check. AI can help explain an error or draft a column mapping, while the saved query provides a repeatable way to apply the reviewed steps.

5. Check Counts and Amounts at Every Stage

The raw append must contain A1, A2, A3, B1, B2, and B3 exactly once. After the confirmed overlap is excluded, Final should contain 0101, 0102, 0103, 0104, and 0105. Keep the source reference for A2, the retained version of 0102.

Reconciliation: six raw rows = five final rows + one excluded row; 300.00 = 250.00 + 50.00. The zero-value order 0103 remains in Final. If it disappears, the final subtotal still equals 250.00, but only four records remain.

For this sample, arrange the Final worksheet as A: Order_ID, B: Amount_USD, C: Source_file, D: Source_table, E: Source_row. Put the five records in rows 2–6. On a separate checks sheet:

• =COUNTA(Final!A2:A6) should return 5 nonblank IDs.
• =COUNT(Final!B2:B6) should return 5 numeric amounts, including the zero.
• =SUM(Final!B2:B6) should return 250.
• =COUNTIF(Final!A2:A6,"0102") should return 1 for the retained overlapping order.

These fixed ranges apply only to the five-row example. Inspect the whole sheet for extra records outside the range, and adapt ranges for larger outputs. Compare the source-reference list as well as counts: accidentally repeating one row while omitting another can preserve the row count. Likewise, the two input files have equal subtotals, so importing A twice would still produce six raw rows and 300.00. Only checking the source identities reveals that B was missed.

6. Save the Evidence Alongside the Final Table

Keep the input manifest, untouched sources, RawCombined, Final, Exclusions, and unresolved issues together. Record the rule that allowed B1 to be excluded and point to A2. If any file remains unreadable, label the output partial and list that file rather than calling it complete.

On the next refresh, compare the new file inventory with the approved scope. Recheck headers, row counts, errors, and overlap decisions; a previous duplicate decision does not automatically apply to a new export. Use our Excel formula guide to adapt the checks, and the AI data-analysis guide to choose an assistant for the next analysis task.

Once the combined data is verified, use our Excel chart guide with AI prompts and interpretation checks to turn it into a readable visual. Check the chart range, period coverage, and claims before sharing.

Written September 14, 2026. Microsoft documentation checked on that date. All example records are fictional; counts and totals were independently calculated for this guide.