How to Compare Data in Excel

Comparing data in Excel is one of those fundamental tasks that can range from a quick visual check to a complex, multi-sheet reconciliation. Whether you're matching two lists of customer names, identifying discrepancies in sales numbers, or trying to see what’s new in an updated report, Excel has a tool for the job. This tutorial will walk you through several methods for comparing data in Excel, starting from the simplest visual techniques and moving up to more powerful formula-based approaches.

Quick Visual Checks: Handling Two Sheets or Workbooks

Sometimes, all you need is a simple side-by-side view to eyeball the differences between two lists. Manually clicking back and forth between sheets or workbooks is inefficient and frustrating. Excel has built-in features to make this easier.

Using "View Side by Side"

If you have two different Excel workbooks open, this is the best way to compare them. It arranges them horizontally or vertically on your screen, taking the guesswork out of resizing windows.

Open both of the Excel files you want to compare.
Go to the View tab in the ribbon of either workbook.
Click on View Side by Side. Excel will automatically tile the two windows horizontally.
A bonus feature, Synchronous Scrolling, will likely be enabled by default. As you scroll through one sheet, the other will scroll with it, keeping the same rows aligned. You can toggle this on or off in the View tab.

Using "New Window" for Sheets in the Same Workbook

What if the data you want to compare is on two different sheets within the same workbook? You can’t view them side-by-side by default, but there’s a great workaround.

With your workbook open, go to the View tab and click New Window.
This will open a second instance of your active workbook. You'll see ":2" added to the file name in the title bar (e.g., "MyReport.xlsx:2"). It's the same file, just a different view.
Now that you effectively have two windows, you can activate the sheet you want to see in each one.
Use the View Side by Side and Synchronous Scrolling features just as you would with two separate files. Any changes you make in one window will instantly reflect in the other, because they are both views of the same file.

Highlighting Differences with Conditional Formatting

A visual side-by-side comparison works for small datasets, but it’s not practical for hundreds or thousands of rows. This is where Conditional Formatting shines. It allows you to automatically highlight cells, rows, or duplicate values based on rules you define.

Finding Uniques or Duplicates Between Two Lists

This is one of the most common comparison scenarios. You have two lists (e.g., an old customer list and a new one) and you want to know which names are new, which were removed, or which appear on both.

Let's assume you have "List A" in column A (A2:A100) and "List B" in column B (B2:B80).

How to Highlight All Values That Are Unique to Each List:

Select the data range for both lists (e.g., A2:A100 and B2:B80). You can do this by first selecting the first list, then holding the 'Ctrl' key (Windows) or 'Cmd' key (Mac) while selecting the second list.
On the Home tab, click Conditional Formatting > Highlight Cells Rules > Duplicate Values...
In the dialog box that appears, change the dropdown on the left from Duplicate to Unique.
Choose your desired formatting (like "Light Red Fill with Dark Red Text") and click OK.

Now, any cell that doesn't have a matching value in the other list will be highlighted.

How to Highlight Only Values That Appear in Both Lists:

Follow the same steps as above.
In the dialog box, leave the dropdown set to Duplicate.
Click OK. Cells that have an identical match in the other designated range will now be highlighted.

Row-by-Row Comparison Highlighting

What if you need to compare entire rows of data for any discrepancies? For example, you have two sales reports and you want to ensure all the details for each transaction ID match up perfectly. We can use a formula-based conditional formatting rule for this.

Let’s say you have your two tables next to each other: Table 1 is in A2:C100 and Table 2 is in E2:G100. We want to check if the rows match.

Highlight the first row of your first table that contains data (e.g., A2:C2).
Go to Home > Conditional Formatting > New Rule.
Select "Use a formula to determine which cells to format."
In the formula box, enter a comparison. For this scenario, you could check if the entry in the first column is the same. To check the whole row, you can concatenate the values: =A2&B2&C2<>E2&F2&G2
(Note: The <>&gt, appears garbled, should be <> for "not equal to".)
Click the Format... button, choose a fill color (like light yellow) to highlight the mismatches, and click OK.
Click OK again to close the rule window. Your first row should (or should not) be highlighted.
To apply this formatting down the entire table, select that first formatted row (A2:C2), then either click the Format Painter (on the Home tab) and apply it to the rest of the rows, or go to Conditional Formatting > Manage Rules... and change the "Applies to" range to cover your entire dataset (e.g., =$A$2:$C$100).

Using Formulas for Flexible Data Comparison

Formulas give you the most power and flexibility. Instead of just highlighting differences, you can create a third column that explicitly states "Match," "Mismatch," "Not Found," or returns specific values.

Method 1: The Simple IF Function for Direct Comparison

Use this when your data is neatly aligned in rows and you just want a simple "yes" or "no" answer for each row about whether two cells match.

Let's say in column A you have "Expected Revenue" and in column B you have "Actual Revenue." In column C, you want to see if they match exactly.

=IF(A2=B2, "Match", "Mismatch")

Drag this formula down from cell C2, and it will instantly check every row. You can then filter for all the "Mismatch" rows to focus on the problem areas.

Method 2: COUNTIF for Comparing Unsorted Lists

This is an incredibly useful formula for comparing two lists of values that are not in the same order. It checks if a value from one list can be found anywhere in a second list.

Let's say Column A has a list of 'All Employees' and Column E has a list of 'Employees Who Completed Training'. You want to find out who from the main list is missing from the training list.

In cell B2 (next to the first employee), you would enter:

=IF(COUNTIF(E:E, A2)>0, "Completed", "Missing")

Breaking it down:

COUNTIF(E:E, A2): This counts how many times the value in A2 appears in all of column E. If it finds a match, the count will be 1 (or more). If not, it will be 0.
IF(...>0, "Completed", "Missing"): The IF function then takes that result. If the count is greater than 0, it means the name was found, so it returns "Completed." Otherwise, it returns "Missing."

Method 3: VLOOKUP & XLOOKUP to Find Matching Data

COUNTIF is great for a yes/no answer, but VLOOKUP (and its modern successor, XLOOKUP) can do more. It can not only find if a value exists but also retrieve other related data from the same row.

Using VLOOKUP

Imagine you have product sales data in two tables. The first table (A1:B100) has ProductID and ProductName. The second table (D1:E50) has ProductID and its SalesQuantity. You want to compare them to bring the SalesQuantity into the first table.

In cell C2 of your first table, you could enter:

=VLOOKUP(A2, D:E, 2, FALSE)

Let's decode this:

A2: This is the ProductID you want to look for.
D:E: This is the table where you are searching (our second table). VLOOKUP requires the lookup value to be in the first column of this table.
2: The column number in the search table from which you want to return a value. In our case, SalesQuantity is the 2nd column of table D:E.
FALSE: This critical part tells VLOOKUP to find an exact match only. If the product ID isn't found, it returns an #N/A error.

Using XLOOKUP (Recommended for newer Excel versions)

XLOOKUP is more intuitive and flexible than VLOOKUP.

The lookup column doesn't have to be the first one.
You select the lookup and return columns separately, which is less error-prone.
It has built-in error handling.

Using the same scenario, the XLOOKUP formula would be:

=XLOOKUP(A2, D:D, E:E, "Not Found")

A2: What to look for.
D:D: The column to search in.
E:E: The column to return the value from.
"Not Found": What to show if there's no match (no more ugly #N/A errors!).

Advanced Comparison with Power Query

For complex, large, or recurring comparison tasks, manually entering formulas or conditional formatting rules can be cumbersome. Power Query (found in the "Get & Transform Data" section of the Data tab) is Excel's powerhouse for data manipulation.

You can use it to connect to two different tables (even from different files) and have it intelligently compare them for you by merging them based on a common key (like ProductID or Email Address).

A "merge" in Power Query is similar to a VLOOKUP but on a much larger and more powerful scale. It can perform different types of "joins" to find matching rows (inner join) or show all data from one table and only the matches from another (left outer join). While it has a steeper learning curve, setting up a Power Query merge means your data comparison can be refreshed with a single click, automating a previously manual process.

Final Thoughts

You now have a wide range of tools within Excel for comparing data, from simple visual tricks and clever highlighting to a set of powerful formulas that can tell you not just if data matches, but how it matches. The key is picking the right technique for your specific task to turn your raw data into clear, actionable information.

As helpful as Excel is, the most time-consuming part of reporting is often the manual process of exporting data from different platforms and wrestling with it in a spreadsheet. This is where modern analytics tools can help automate the heavy lifting. At Graphed target="_blank" rel="noopener"), we tackle this problem by connecting directly to your marketing and sales data sources (like Google Analytics, Shopify, HubSpot, etc.). Instead of manually comparing exports, you can simply ask questions in plain English - like "Compare revenue from Facebook Ads and Google Ads this quarter" - and get a real-time dashboard instantly. This eliminates messy CSVs and complex formulas, giving you back time to focus on strategy instead of report preparation.