How to Compare Excel Files With Different Columns or Headers

To compare Excel files with different columns or headers, first identify the same record on both sides, then map fields that mean the same thing. A moved column, a renamed field and a genuinely new field need different decisions. Matching column letters alone can compare unrelated values.

Separate moved, renamed and added columns

Do not assume Net Amount and Gross Total are the same measure merely because both contain numbers. Check definitions, units and key types. Mapping is an explicit comparison decision; it does not convert currencies, remove tax or normalize the underlying data.

Open the two tables and choose their record IDs

Open Compare Files and choose supported XLSX or CSV exports. Select the worksheet from each workbook if needed. Keep the earlier export first so that added and removed have a consistent direction.

Check the suggested ID. If the names differ or no key is selected, open Comparison basis and use the first and second ID controls to choose the matching columns. The ID can occupy different positions in the two files. Use multiple key columns when a single column does not uniquely identify a record.

Missing or duplicate IDs are not a field-mapping shortcut: they remain uncertain matches. Resolve the key definition before deciding which amounts changed.

Map renamed fields in Comparison basis

When column names differ, the tool asks you to review them. Open Comparison basis, then expand Field mappings to inspect each First field and Second field pair. Match the renamed counterpart using its actual column position and name. Use Add field mapping when another pair is needed.

If a renamed field initially appeared as two one-sided entries, first exclude the redundant entry for its counterpart, then select that counterpart in the pair you are keeping. Each non-ID column must participate at most once. Otherwise the tool cannot produce a valid comparison. Do not keep both “Amount → Total” and a separate added Total entry.

Repeated header labels also need explicit choices by position. Two columns called Amount might represent different measures; do not choose one just because the label matches.

Worked example: renamed IDs and amounts, plus a new field

The earlier table has columns A: Customer ID and B: Amount. The later table has A: Region, B: Total and C: Client No. For this example, Amount and Total represent the same measure in the same units.

1. Choose Customer ID in the first file and Client No in the second as the record key.
2. Exclude the one-sided Total entry; keep the entry containing the first file’s Amount.
3. In that remaining entry, pair Amount with the second file’s Total so that Total is compared only once.
4. To compare only the shared amount measure, also exclude Region. Expect one changed and two unchanged records, with no records added or removed.
5. To include the new field instead, keep or add a pair with First field set to Field absent and Second field set to Region. All three matched records count as changed because the selected field exists only in the later table.

The blank Region for C002 does not make the column exist in the earlier table. An absent field and an existing blank cell are different comparison facts. The two results above answer different questions, so neither should be shared without stating the chosen field scope.

When a field really was added or removed

Keep a one-sided mapping with Field absent on the missing side if you want the report to include that schema difference. If the suggested mappings correctly represent additions and removals, the review’s Compare these as added or removed fields action confirms that interpretation.

Do not use that confirmation to stand in for pairing a renamed field. Treating Amount as removed and Total as added can mark records changed even when their values agree. Map equivalent fields first, then decide whether genuinely new or removed fields belong in the result.

Download the report and check what it includes

After the mappings are valid, inspect the totals and each source-row reconciliation line. Download the separate XLSX report with Summary, Records, Cell changes and Needs review worksheets. Check its compared-fields summary and a known change against both original files.

Compare works with stored typed values and saved formula results. It does not recalculate formulas, compare formula text or audit formatting. Field mapping does not rewrite either source workbook or merge the second file’s columns into the first. If you need that kind of output, comparison is only the review step.

For the broader two-version workflow, see comparing workbook versions like Word’s Compare feature. For different record totals as well as different columns, use the unequal-row-count example.