How to Compare Two Data Sets in Google Sheets

Trying to find the differences between two lists in Google Sheets can feel like a game of spot-the-difference, but it doesn’t have to. You can easily compare two sets of data to find duplicates, unique values, or discrepancies without manually checking line by line. This article will show you several straightforward methods to compare data sets, ranging from simple visual highlights to powerful formulas that do the heavy lifting for you.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why Compare Two Data Sets in the First Place?

Before jumping into the "how," it's helpful to understand the common scenarios where comparing data is a game-changer. This isn't just a technical exercise, it's a practical skill for solving real-world business problems.

• Reconciling Lists: Compare an event registration list against the final attendee list to see who didn't show up.
• Sales and Marketing Analysis: Find out which customers on your email list have actually made a purchase by comparing your email marketing platform export with your sales data from Shopify.
• Data Cleaning: Identify duplicate entries between two spreadsheets before merging them to ensure your final dataset is accurate.
• Inventory Management: Match a list of sold items against your supplier's invoice to spot discrepancies.
• Financial Auditing: Compare a downloaded bank statement with your internal expense tracking sheet to ensure everything lines up perfectly.

In all these cases, Google Sheets has the tools you need to get the job done quickly and accurately.

Method 1: Conditional Formatting to Visually Highlight Differences

Sometimes, all you need is a quick visual comparison. Conditional formatting is perfect for this, as it colors your cells based on rules you create. It won't create a new list, but it's great for flagging values directly in your existing dataset.

Highlighting Duplicates (Values in Both Lists)

Let's say you have two lists of email subscribers in Column A and Column B, and you want to highlight the emails that appear in both lists.

1. Select the first list of data (e.g., range A2:A100). This is the list where you want the duplicates to be highlighted.
2. In the menu, go to Format > Conditional formatting. A sidebar will appear on the right.
3. Under the "Format rules" dropdown, select Custom formula is.
4. In the formula input box that appears, type the following formula:

=COUNTIF($B$2:$B$100, A2)>0

1. Under "Formatting style," choose a fill color (like light green) to highlight the matching cells.
2. Click Done.

How This Works: The COUNTIF formula counts how many times each cell in your selected range (starting with A2) appears in the second list (B2:B100). If the count is greater than zero, it means the value exists in both lists, and the cell gets highlighted.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Highlighting Unique Values (Values in Only One List)

What if you want to find the people who are in your first list but not in your second? Modifying the formula is simple. This is useful for finding new leads who haven't been contacted yet.

1. Follow the same steps as above, selecting range A2:A100 and opening the conditional formatting menu.
2. Again, select Custom formula is.
3. This time, enter the formula with an equals zero at the end:

=COUNTIF($B$2:$B$100, A2)=0

1. Choose a different fill color (perhaps a light red or yellow) to distinguish unique values.
2. Click Done.

Now, any email in Column A that does not exist in Column B will be highlighted, instantly showing you the unique entries.

Method 2: Using Formulas to Create a "Status" Column

Visual highlighting is great, but often you need to create a new column that explicitly labels each item as a "Duplicate," "Unique," or "Match." This makes your data easier to filter, sort, and analyze further. For this, we can use a helper column.

The VLOOKUP Method

VLOOKUP (Vertical Lookup) is a classic function for finding a value from one list in another. It’s perfect for checking if a customer ID from a sales sheet exists in a master customer list.

Imagine your customer IDs are in Column A, and you want to see if they exist in a master list located in Column B.

1. In a new column (let's say Column C), click into cell C2.
2. Enter this formula:

=IF(ISNA(VLOOKUP(A2, $B$2:$B$100, 1, FALSE)), "Unique to List A", "Found in Both")

1. Press Enter and drag the fill handle (the small square in the bottom-right corner of the cell) down to apply the formula to the rest of the column.

How This Works:

• VLOOKUP(A2, $B$2:$B$100, 1, FALSE) tries to find the value from A2 within the range B2:B100. If it finds a match, it returns the value itself. If it doesn't, it returns an #N/A error.
• ISNA(...) checks if the VLOOKUP resulted in an #N/A error. It returns TRUE if there's an error (no match) and FALSE if there isn't (a match was found).
• The IF function then uses that TRUE or FALSE result to return a custom text string: "Unique to List A" when no match is found, and "Found in Both" when a match exists.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

The COUNTIF Method (as a Formula)

The same logic from our conditional formatting example can be used in a helper column, and it's often more intuitive than VLOOKUP for simple comparisons.

1. In cell C2, enter the following formula:

=IF(COUNTIF($B$2:$B$100, A2)>0, "Duplicate", "Unique")

1. Press Enter and drag the formula down.

This formula achieves the exact same result as the VLOOKUP method but is often simpler to read and understand. It counts occurrences, and if the count is greater than 0, it labels the item as "Duplicate," otherwise, it's "Unique."

Method 3: Creating New Lists with the FILTER Function

The previous methods were great for marking up your existing data. But what if you want to generate a brand new, clean list of only the differences or only the matches? The FILTER function is your best friend here.

FILTER works on an entire range at once, so you don't have to drag any formulas down. It dynamically creates a new list that meets your criteria.

To Get a List of Values in List A but NOT in List B

This is extremely useful for generating a list of, say, products that were in last month's inventory (List A) but aren't in this month's (List B).

Click an empty cell where you want your new list to start, and enter this formula:

=FILTER(A2:A, ISNA(MATCH(A2:A, B2:B, 0)))

To Get a List of Common Values (Found in Both Lists)

Want to see a clean list of everyone who attended both of your recent webinars? If attendees for webinar 1 are in Column A and attendees for webinar 2 are in Column B, use this formula:

=FILTER(A2:A, NOT(ISNA(MATCH(A2:A, B2:B, 0))))

How This Combination Works:

• MATCH(A2:A, B2:B, 0) tries to find the position of each item from the range A2:A inside the range B2:B. It returns a number for matches and #N/A for non-matches.
• ISNA(...) checks for that familiar #N/A error.
• FILTER then goes through every item in range A2:A and only keeps the ones where the ISNA(MATCH(...)) condition is met.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

A Quick Note on Data Cleaning

All of these methods fail if your data isn't clean. The most common reason a comparison doesn't work is due to invisible problems like extra spaces, inconsistent capitalization, or numbers formatted as text.

• Extra Spaces: "Apple " will not match "Apple". Use the TRIM function on both your columns in a separate helper column before comparing. For example: =TRIM(A2).
• Inconsistent Casing: "Apple" won't match "apple". To fix this, convert everything to the same case using LOWER() or UPPER() before comparing. Example: =LOWER(A2).
• Data Types: The number 123 is not the same as the text string "123". Ensure your data types are consistent across both sets.

A good practice is to create clean "helper" columns using =TRIM(LOWER(A2)) and then run your comparisons on those cleaned-up columns.

Final Thoughts

From visual highlighting with conditional formatting to powerful dynamic lists with the FILTER function, Google Sheets gives you a variety of ways to compare data sets. Choosing the right method depends on your goal: a quick visual check, an organized "status" column for filtering, or a completely new list of the differences or duplicates.

Comparing two lists from different spreadsheets is often the first step, but the real challenge arises when you need to compare data from entirely different platforms, like Google Analytics versus Shopify or Facebook Ads versus your CRM. That usually involves hours of exporting CSVs and manual cleanup. At Graphed, we solve this by connecting directly to all your data sources. Instead of wrestling with spreadsheets, you can just ask questions in plain English, like "Show me which Facebook campaigns drove the most sales in Shopify last month," and get an interactive dashboard instantly. It automates the painful reporting process, giving you back time to focus on the insights themselves.