How to Compare Two Tables in Excel & Google Sheets

This tutorial demonstrates how to compare two tables in Excel and Google Sheets.

 

compare two tables final data

 

Compare Two Tables

In Excel, you can compare two tables and highlight differences using conditional formatting. Say you have the following two tables with the same structure.

 

compare two tables initial data

 

As you can see, there are differences in the two tables (Price and Total Sales for Keyboard, Printer, and Graphic card are changed). Let’s use conditional formatting to highlight these differences in red in the second table.

  1. Select all cells in the second table (B10:F15) where you want conditional formatting, and in the Ribbon, go to Home > Conditional Formatting > New Rule.

 

compare two tables conditional formatting 1

 

  1. In the Formatting rule window, (1) select Use a formula to determine which cells to format for the Rule Type, (2) enter the formula:
=B2<>B10

Then (3) click Format.

This formula goes cell by cell in the selected range. So, in the next cell, it will check if B3 is different from B11, and when it moves to the second column of the range, it will check if C2 is different than C10, and so on. If the condition is TRUE, the cell is formatted according to the rule.

 

compare two tables conditional formatting 2

 

  1. In the Format window, go to Fill tab, choose a color (Red), and click OK.

 

compare two tables conditional formatting 3

 

  1. Back in the New Formatting Rule window, click OK. Here, you can also see a preview of the formatting to be applied to relevant cells.

 

compare two tables conditional formatting 4

 

As a result, all differences between the two tables (cells D10, D13, D14, F10, F13, and F14) are highlighted in red in the second table.

 

compare two tables final data

 

See also…

Compare Two Tables in Google Sheets

You can do the same comparison in Google Sheets.

  1. Select all cells in the second table (B10:F15) where you want conditional formatting, and in the Ribbon, go to Home > Conditional Formatting > New Rule.

 

google sheets compare two tables conditional formatting

 

  1. In the formatting window on the right side, (1) choose Custom formula is for the rule format, and (2) enter the formula:
=B2<>B10

(3) Click the Fill color icon, (4) choose a color (red), and (5) click Done.

 

google sheets compare two tables conditional formatting 1

 

Finally, all differences between the two tables are highlighted in red.

 

google sheets compare two tables conditional formatting 2