How to use the compare two excel sheets
- Choose the first workbook (A) and the second workbook (B), and pick the sheet in each.
- Optionally type a key column, a heading both sheets share or a column letter, so rows are matched by key. Leave it empty to compare row by row.
- Tick Ignore capital letters if A and a should count as the same.
- Read the summary, the list of changed cells with their old and new values, and the rows that were added or removed, and download the differences as CSV.
Formula
The first row of each sheet is the heading, and columns are matched by heading text
With a key column, rows with the same key are compared cell by cell. Keys only in A are removed rows, and keys only in B are added rows.
Without a key, row 2 is compared with row 2, row 3 with row 3, and so on
Cells are equal when their text is the same after trimming spaces at the ends
A key must be unique in each sheet. If a key appears twice, the page stops and tells you which value, because the rows could not be matched fairly.
Two versions of an order sheet
Sheet A has 12 orders with the key Order from 1001 to 1012. In sheet B the order 1009 was deleted, the order 1013 was added, the Rep for 1002 changed from Ben to Bo and the Amount for 1004 changed from 300 to 310. Matching on Order reports 1 removed row (1009), 1 added row (1013), 2 changed rows and 2 changed cells, with 9 rows unchanged.
Comparing the same sheets row by row, without a key, reports many more differences, because deleting one row moves every row below it up by one place. This is why a key column matters for lists that can lose or gain rows. Matching on Region instead is refused, because the same region appears in several rows.
Choosing how to match rows
Use a key when each row has an identifier such as an order number, an employee ID or an email address. The comparison is then independent of the order of the rows, and added and removed rows are clear. Use row-by-row comparison when the sheets have the same layout and the same rows in the same order, such as two exports of the same report.
To compare CSV files in the same way, see the CSV diff tool. To compare plain text line by line, use the diff checker.
What counts as a difference
The tool compares what the cells show: text, numbers and dates. Numbers are compared as numbers, so 1.50 and 1.5 are the same, and dates are compared as dates. Formulas are compared by their last calculated value, and formatting, comments and hidden rows are ignored. If a column exists in only one sheet, it is reported, and the other columns are still compared.
To inspect a workbook for hidden sheets, formulas and other content, use the XLSX inspector. Nothing is uploaded.
Assumptions and limits
- Both files are XLSX workbooks, and the first row of each chosen sheet is the heading.
- A key column holds unique values in each sheet.
- Columns are matched by heading text. Headings that appear in only one sheet are reported.
- The page shows the first 500 differences of each kind. The CSV lists all of them.
Frequently asked questions
How do I compare two Excel sheets?
Choose both workbooks, pick the sheets and name a key column. The page lists the added rows, removed rows and changed cells.
What if the rows are in a different order?
Use a key column. Rows are then matched by key, and the order does not matter.
Does it compare formulas?
It compares the value each formula last calculated, not the formula itself.
Can I save the differences?
Yes. Download them as a CSV file with one line for each changed cell, added row or removed row.