Compare Two Excel Sheets With a Pivot Table

To compare two Excel sheets with a PivotTable, combine their rows into one table, add a column identifying which sheet each row came from, and build a PivotTable that puts that source label in Columns. Put the fields you want to compare in Rows and the numeric measure in Values; the resulting side-by-side totals make differences visible.

What a PivotTable Can—and Cannot—Compare

A PivotTable summarizes data by categories. It can show, for example, sales by product for a prior period beside sales for a current period. That makes it useful for comparing totals, counts, averages, or other summaries when both sheets describe the same kinds of records.

A PivotTable does not automatically match individual rows between two worksheets, identify which cells changed, or prove that the underlying records are identical. It groups records according to the fields you give it. If the question is whether order 1047 has a different status on the second sheet, a row-level lookup or a Power Query merge is more appropriate. If the question is whether each product had higher total sales this month, a PivotTable is a strong fit.

The central requirement is a common structure: the two sets of records need compatible columns, such as Date, Product, Region, and Sales. Once combined, a Source field distinguishes the records from Sheet A and Sheet B.

Prepare the Sheets Before Comparing

Check that each sheet has one header row and one record per row. Column names should mean the same thing on both sheets. For example, avoid using Product on one sheet and Item on the other unless you rename one header so the fields align. Remove merged header cells, blank rows inside the data, and totals rows that could be mistaken for records.

Make sure matching fields use consistent values: North and NORTH may be treated as equivalent text in many summaries, but North, N. and Northern Region are different categories.
Check data types. Dates stored as text may not group like real dates, and numbers stored as text may not sum correctly.
Decide how to handle repeated records. A PivotTable includes every row, so duplicated transactions can inflate totals.
Add a Source column to each dataset, filling its rows with a clear label such as Previous or Current. Use labels that will make sense in the final report.

For example, if both sheets contain Product, Region, and Sales, add Source as a fourth column. On the first sheet, every record might say Previous; on the second, every record might say Current. Keep the labels consistent throughout each set.

Combine the Data Into One PivotTable Source

A PivotTable works best with one rectangular source range. If the sheets have matching columns, append the rows vertically rather than placing one dataset beside the other. The headers should appear once, followed by records from both sheets. Include the Source field so the PivotTable can separate the two sets again in its report.

For a small, one-time comparison, you can copy the records from the second sheet beneath the first sheet’s records, without copying its header row. Include the Source values and convert the combined range to an Excel table with Ctrl+T on Windows. Confirm that the table has headers, then select a cell in it and choose Insert > PivotTable. On Mac, use Command+T to create a table; the PivotTable command is available from the Insert tab, though the dialog layout can differ.

For recurring comparisons, Power Query can append the two tables and preserve a refreshable combined result. Convert each dataset to a table, select a cell in the first table, and choose Data > From Table/Range. In Power Query, check that the Source column is present and that the column types are suitable. Repeat for the second table. In the Power Query Editor, choose Home > Append Queries > Append Queries as New, select the two queries, and confirm. Then use Home > Close & Load To and choose a PivotTable Report if that option is available in the load dialog. Power Query’s ribbon labels and dialog arrangement may vary slightly by Excel build; Excel for the web does not provide the full Windows Power Query authoring experience.

Appending is different from merging. Append places records from one query below the other; merge joins records using matching key fields. For a source-by-source summary, append is usually the right operation.

Build the PivotTable to Show the Difference

After selecting the combined table or query result, choose Insert > PivotTable. In the dialog, confirm the table or range and choose where the report should go. A new worksheet is often easiest to read. Select OK, then arrange fields in the PivotTable Fields pane.

Drag Product or another category to Rows.
Drag Source to Columns, so Previous and Current appear side by side.
Drag Sales to Values. Excel generally uses Sum for a numeric field; check the value field setting if it displays Count instead.
Optionally drag Region or Date to Filters, or add another category to Rows for more detail.

Suppose the combined source contains three rows for Product A: Previous sales of 100 and 150, and Current sales of 180. The PivotTable should show Previous as 250 and Current as 180 for Product A. This comparison indicates that the summarized total is 70 lower in the Current set; it does not identify which individual transaction accounts for the difference.

To make the report easier to scan, use PivotTable Design options for a readable layout and number format. Right-click a value and choose Number Format to apply currency, decimals, or thousands separators. Applying the number format through the value field is preferable to formatting only currently visible cells, because the report may change when refreshed.

Choose the Right Comparison Layout

Rows and Columns determine how the PivotTable groups the records, while Values determines what it calculates. Choose fields according to the comparison question rather than adding every available column.

Compare totals: put a category such as Product in Rows, Source in Columns, and Sales in Values.
Compare record counts: put a reliable identifier such as Order ID in Values and set its summary to Count. Count measures populated entries, not necessarily unique orders.
Compare by time: put Date in Rows and Source in Columns. If Excel recognizes the dates, you can group them into periods such as months; otherwise, correct the date values first.
Compare within regions: put Region above Product in Rows, or use Region as a filter if you want to examine one region at a time.

If a comparison needs a percentage change, a PivotTable’s Show Values As options can display calculations such as difference from or percentage difference from another item, depending on the layout and available fields. The Source field must be arranged so Excel can identify the item to compare against. For a simple report, showing the two totals side by side and calculating the change in a separate, clearly labeled area may be easier to audit.

Refreshes, Updates, and Keeping Results Trustworthy

A PivotTable is a summary of its source, not a live view of every cell. After changing source data, refresh the report with right-click > Refresh or use Data > Refresh All. If the combined source is an Excel table, new rows added to that table are usually included when the PivotTable is refreshed. If the source is a fixed cell range, new rows outside that range may be left out; convert the range to a table or update the source.

For a Power Query workflow, refresh the query as well as the PivotTable when needed. Data > Refresh All is a convenient way to update connections and reports. Check the refreshed row count and a few known totals after major changes. If the sheet structure changes—for instance, a column is renamed—the query or PivotTable may need adjustment.

When sending the workbook to someone else, explain what Previous and Current mean, when the data was refreshed, and whether the numbers represent sums, counts, or another calculation. A clear source label prevents a correct PivotTable from being misread.

Common Problems and How to Fix Them

The PivotTable shows Count instead of Sum: the Sales column may contain text, blanks, or mixed types. Correct the source values, refresh, and set the value field to Sum.
One sheet is missing from the report: verify that its rows were appended and that its Source labels are populated. In a query workflow, confirm both queries were selected.
Categories appear twice: inspect spelling, spaces, punctuation, and inconsistent naming. Standardize the source categories before summarizing.
Totals seem too high: look for duplicate rows, totals copied in as records, or data that should have been filtered out. The PivotTable will aggregate all included rows.
New records do not appear: confirm the source is an expanding Excel table or update the PivotTable source, then refresh.
Dates refuse to group: check for text dates, errors, or blank cells in the date field. Convert the values to genuine Excel dates and refresh.

When Another Method Is Better

Use a PivotTable when you need a summary by category, period, or source. Use a Power Query merge when you need to match records using a key such as Customer ID and find items present in only one dataset or fields that differ between matched records. A lookup formula can also compare a particular field for a known key, but it requires careful handling of missing and duplicate keys.

For a quick visual check, conditional formatting can highlight differences in corresponding cells, but it assumes that the rows and columns are aligned correctly. Sorting two sheets independently can break that assumption. Match records by a stable identifier before comparing cell values; row position alone is not a dependable key.

Excel also has a PivotTable feature called Multiple Consolidation Ranges, but it is limited compared with a normal PivotTable built from a clean combined table. It is not usually the best choice when you need useful row fields such as Product or Region, or a refreshable comparison with clear source labels.

Frequently Asked Questions

Can I compare two worksheets directly in one PivotTable?
Not as two separate ranges in the ordinary PivotTable workflow. Combine the records into one source and include a field that identifies which worksheet each record came from.

Do both sheets need the same column names?
They should have matching field names and compatible data for columns that represent the same information. Rename equivalent headers before appending so the combined source has one consistent field for each attribute.

Can a PivotTable show which rows changed?
A standard PivotTable summarizes groups; it does not report cell-by-cell changes between matched records. Use a key-based comparison, such as a Power Query merge or a lookup-based check, for row-level differences.

Why does my PivotTable count sales instead of adding them?
Excel may interpret the Sales field as text or find nonnumeric entries. Correct the source data, refresh the PivotTable, and use Value Field Settings to select Sum.

Will the PivotTable update when I add rows?
It needs a refresh. Using an Excel table as the source helps new rows become part of the source automatically; after adding them, refresh the PivotTable or choose Data > Refresh All.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.