How to Compare Data in Google Sheets

Comparing data is a fundamental task whether you're tidying up a contact list, reconciling sales figures, or trying to find differences between two inventory reports. Google Sheets provides several powerful, built-in ways to compare data, from simple visual highlights to complex formula-driven analysis. This guide will walk you through the most practical methods for comparing columns, lists, and entire sheets, so you can stop manually eyeballing rows and start getting answers fast.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

How to Find Duplicates in a Single Column

The most basic data comparison is finding duplicate entries within a single list. This is useful for cleaning up email lists, identifying double-ordered products, or just getting a sense of data repetition. The quickest way to do this is with Conditional Formatting, which highlights cells based on rules you create.

Let's say you have a list of email addresses in column A and you want to flag any that appear more than once.

1. Select the entire column (or the specific range you want to check) by clicking the column letter "A".
2. Go to the menu and select Format > Conditional formatting. A sidebar will open on the right.
3. Under the "Format rules" section, find the "Format cells if..." dropdown. Choose Custom formula is.
4. In the text box that appears below, enter the following formula:

=COUNTIF(A:A, A1)>1

Here's what this formula does:

• COUNTIF(A:A, A1): This part counts how many times the value in cell A1 appears within the entire A column (A:A).
• >1: This part checks if that count is greater than one. If it is, the condition is true, and the formatting is applied.

Google Sheets is smart enough to apply this rule to every cell in your selected range. For cell A2, it will check COUNTIF(A:A, A2)>1, for A3 it will check COUNTIF(A:A, A3)>1, and so on.

Finally, under "Formatting style," you can choose how you want the duplicates to be highlighted. A light red or yellow background is usually a good choice. Click "Done," and all your duplicate emails will instantly change color.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

How to Compare Two Columns in Google Sheets

A more common scenario is comparing two separate lists to find what they have in common or what's unique to each. Imagine you have a list of people who registered for an event in column A and a list of people who actually attended in column B. Let’s look at a few ways to compare them.

Method 1: Highlight Unique or Matching Values with Conditional Formatting

This is a great visual method for spotting differences or similarities at a glance. We can use a similar approach to finding single-column duplicates, but with a slightly modified formula. We'll highlight cells in column A that do not appear anywhere in column B.

1. Select column A. This is the column where we want to apply the highlights.
2. Navigate to Format > Conditional formatting.
3. Set the "Format cells if..." dropdown to Custom formula is.
4. Enter this formula:

=COUNTIF($B:$B, $A1)=0

Let's break it down:

• COUNTIF($B:$B, $A1): This counts how many times the value in cell A1 appears in column B. The dollar signs ($) lock the B:B range, so it doesn't shift as the formula is applied down column A.
• =0: This checks if the count is zero. If true, it means the name from column A was not found in column B, identifying someone who registered but didn't attend.

You can then reverse this process. To find the matches (people who are in both lists), just change the formula slightly:

=COUNTIF($B:$B, $A1)>0

This will highlight every name in column A that also appears in column B.

Method 2: Use the VLOOKUP Formula to Find Matches

Conditional formatting is great for visual checks, but sometimes you need a more explicit "Match" or "No Match" label. The VLOOKUP formula is perfect for this. It searches for a value in one list and returns a corresponding value from another.

Let's create a "Status" column in column C. In cell C1, type the header "Status". In cell C2, enter this formula:

=IF(ISNA(VLOOKUP(A2, B:B, 1, FALSE)), "No Match", "Match")

Drag the small blue box in the bottom-right corner of cell C2 down to apply this formula to the rest of the column. Now, let’s understand how it works piece by piece:

• VLOOKUP(A2, B:B, 1, FALSE): The core of the function.
• When VLOOKUP finds a match, it returns the value itself. If it doesn't, it returns an #N/A error. We need to handle that error cleanly.
• ISNA(...): This function checks if the result of the VLOOKUP is an #N/A error. It returns TRUE if there's an error (no match) and FALSE if there isn't (a match was found).
• IF(...): The final wrapper. The IF statement checks the result of ISNA. If ISNA is TRUE (meaning no match was found), it displays "No Match." Otherwise, it displays "Match."

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

How to Compare Two Sheets for Differences

Sometimes your lists aren't side-by-side in columns, they're on entirely different sheets. The principle is the same, but the formulas need to reference the other sheet properly.

Imagine you have your master customer emails on Sheet1 in column A, and a list of unsubscribed customers on Sheet2 in column A. You want to highlight the users on your master list who have unsubscribed.

1. Go to your master list in Sheet1 and select column A.
2. Open Format > Conditional formatting.
3. Choose Custom formula is and enter:

=COUNTIF(INDIRECT("Sheet2!A:A"), A1)>0

The key here is the INDIRECT function. While you can sometimes reference other sheets directly in formulas (like =Sheet2!A1), conditional formatting rules work best with INDIRECT. It takes a text string ("Sheet2!A:A") and treats it as a cell reference.

This formula checks each email in Sheet1's column A and sees if it exists in Sheet2's column A. If it finds a match, it highlights the cell. This is a very fast way to flag unsubscribes, reconciled accounts, or any record that exists on a second "do not contact" or "completed" list.

Comparing Entire Rows for Duplicates

Comparing single columns is one thing, but what if you need to find duplicate rows? For example, in an order sheet, an identical row could mean a duplicate order was placed. Simply matching the customer name isn't enough, you need to see if the customer, product, date, and price are all the same.

The easiest way to do this is with a "helper column."

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method: Concatenate and Count

Concatenation means joining pieces of text together. We're going to create a new column that combines all the values in a row into a single, unique string. Then, we can simply find duplicates in that helper column.

Assume your data is in columns A through D (e.g., Customer, Product, Date, Price).

1. In cell E1, add a header like "Unique Row ID".
2. In cell E2, enter this formula:

=A2 & "|" & B2 & "|" & C2 & "|" & D2

The ampersand (&) is Google Sheets' operator for joining text. We also add a separator like a pipe character (|) to prevent false matches (e.g., a customer named "Apple" and a product named "Pie" would merge into "ApplePie", the same as a customer "ApplePie" and product "-"). The separator solves this.

1. Drag this formula down for all your rows. Now you have a column where every value represents an entire row.
2. Apply the first method we learned: use Conditional Formatting on column E to highlight duplicates with the formula:

=COUNTIF(E:E, E1)>1

This will instantly highlight every row ID that appears more than once, showing you exactly where the duplicate orders are.

Final Thoughts

As you can see, Google Sheets gives you a complete toolkit for comparing data. From simple visual checks with conditional formatting to more robust formulas like VLOOKUP and helper columns for matching records across columns or entire sheets, you can handle most data reconciliation tasks without ever leaving your spreadsheet.

While these spreadsheet methods are powerful, spending hours exporting CSVs and wrestling with formulas can become a bottleneck, especially when comparing data from multiple sources like Google Ads, Shopify, and Salesforce. We built Graphed to solve this problem by providing a central hub for all your data. After a one-click connection to your business apps, you can ask questions in plain English - like "compare my ad spend to Shopify sales this month" - and get an interactive, real-time dashboard instantly. This eliminates the meticulous manual work of data comparison, freeing you up to focus on the insights.