How to Compare Two Columns in Excel and Find What Is Missing

Find the values in one Excel column that are missing from another with COUNTIF, FILTER, VLOOKUP or a Power Query merge, and fix the keys that never match.

Founder, Glow Reports. , 4 min read
  • Excel
  • Data Quality
  • Reporting Workflows
Two columns of cards linked where they match, with the unmatched cards set apart in an orange tray beside a magnifying glass
On this page
  1. How do you compare two columns in Excel to find what is missing?
  2. How do you flag missing values with COUNTIF?
  3. How do you list the missing values with one formula?
  4. How do you compare two exports every month without redoing the formulas?
  5. Why does Excel say a value is missing when it is in both lists?
  6. What do you do with the list once you have it?
  7. Where does Glow Reports fit?
  8. Common questions

You have two exports, last month's customer list and this month's, pasted side by side, and you need the IDs that sit in one column and not the other. Excel has no "compare columns" button, but three built-in methods do the job: a COUNTIF helper column, one FILTER formula that returns the missing values as a list, and a Power Query merge for files that come back every month. All three fail the same way when the keys don't match character for character, so the first step is cleaning the column you compare on.

How do you compare two columns in Excel to find what is missing?

Count how many times each value of the first column appears in the second. A count of 0 means the value is missing from the second column. COUNTIF does this in a helper column, and FILTER turns the same test into a list of the missing values.

The examples below use one sheet with last month's customer IDs in column A () and this month's in column C (). Customers in A and not in C are the ones who left. Customers in C and not in A are the new ones. Swap the ranges to get the other direction.

Paste both lists on the same sheet, even when they come from two files. Formulas that point at another workbook break as soon as someone renames or moves the file.

How do you flag missing values with COUNTIF?

Put in B2 and fill it down. TRUE marks an ID from column A that never appears in column C. Then filter column B on TRUE to see only the missing rows.

COUNTIF counts the cells in a range that meet one condition Microsoft Support. The dollar signs keep the range fixed while the formula moves down the column. Without them, the range slides one row with each copy and the bottom rows compare against an empty block.

Three details from Microsoft's page matter for list comparison:

  • Criteria aren't case sensitive, so "acme" and "ACME" count as a match.
  • A question mark or an asterisk in the value acts as a wildcard. An ID such as matches more than itself. Put a tilde before the character to match the character itself.
  • Leading or trailing spaces and curly quotes give unexpected counts. Clean them first.

To color the rows instead of adding a column, select and use HomeConditional FormattingNew Rule, choose "Use a formula to determine which cells to format" and enter . Google Sheets has the same COUNTIF with the same wildcards and the same lack of case sensitivity Google Docs Editors Help.

How do you list the missing values with one formula?

In Excel for Microsoft 365, 2021 or 2024, FILTER returns the missing IDs as a list that updates when either column changes. Type the formula once in an empty column and the results spill down.

IDs in column A that are missing from column C
=FILTER(A2:A500,(A2:A500<>"")*(COUNTIF(C2:C500,A2:A500)=0),"None missing")

COUNTIF receives the whole range as its condition, so it returns one count per row. The part drops blank cells, which would otherwise show up as missing. The last argument is the text to show when nothing is missing. Without it, FILTER returns a #CALC! error when no row qualifies Microsoft Support.

Google Sheets accepts the same idea. Its FILTER takes the range and one or more conditions, and returns #N/A when nothing matches Google Docs Editors Help:

Same list in Google Sheets
=IFNA(FILTER(A2:A500,A2:A500<>"",COUNTIF(C2:C500,A2:A500)=0),"None missing")

If you also need a value from the second list, such as the plan or the last order date, use a lookup instead. Its fourth argument sets what to show when there is no match, so returns the plan in column D or the word Missing Microsoft Support. XLOOKUP is not available in Excel 2016 or 2019. There, VLOOKUP does the same with an exact match: returns TRUE for a missing ID, because VLOOKUP returns #N/A when it finds no exact match. The ID must sit in the first column of the range you search Microsoft Support.

How do you compare two exports every month without redoing the formulas?

Load both files in Power Query and merge them with a left anti join. It keeps only the rows of the first table that have no match in the second, and next month you replace the files and refresh.

Microsoft describes the left anti join as one that brings in only rows from the left table that don't have any matching rows from the right table Microsoft Learn. The steps in Excel:

  1. Load each file with DataGet DataFrom File, then choose LoadLoad ToOnly Create Connection.
  2. Open last month's query and select HomeMerge Queries.
  3. Pick this month's query as the second table, then select the ID column in both tables.
  4. Under Join Kind, choose and select OK.

The status line at the bottom of the Merge dialog tells you how many rows of the first table it selected. That number is your count of missing customers before you even load the result. For the new customers, run the same merge with the two tables swapped.

The merge also keeps every other column of the first table, so the result shows the full rows of the customers who left. Their plan, country and last order date come with them. If your monthly files land in one folder, combine them first with Power Query's folder import, then run the comparison on the combined table.

Why does Excel say a value is missing when it is in both lists?

Because the two cells are not the same value. The usual causes are a trailing space, a nonbreaking space from a web page, an ID stored as a number in one export and as text in the other, or a different spelling of the same customer.

Run these checks before you trust a list of missing values:

SymptomLikely causeFix
XLOOKUP or Power Query finds no match at allIDs are numbers in one list, text in the otherConvert both columns to the same type
A few names look missing but are thereExtra or nonbreaking spaces
Matches in COUNTIF, misses in Power QueryLetter case differsLowercase both keys in Power Query before merging
Same customer under two namesNo shared IDCompare on email or customer ID, not on the name

The step-by-step fixes for spaces, text numbers and dates are in how to clean data in Excel. Power Query compares text case-sensitively Microsoft Learn, so TransformFormatlowercase on both ID columns avoids false misses there.

A quick test catches most problems. Pick one ID you know is in both lists, search for it in each column with Ctrl+F, and check that COUNTIF finds it. If it doesn't, the cleaning step is not done.

What do you do with the list once you have it?

Count it, then explain it. The number of IDs missing from this month's list, divided by last month's total, is the customer churn rate for the month. The list itself tells you who to call.

The method for churn rate and the segments to compare are in how to analyze customer churn. The same comparison checks two systems against each other: customers in the CRM with no invoice, or invoices with no CRM record. That case and its pitfalls are covered in connecting CRM and billing data.

Keep the result next to the two source files with the date of each export. When someone asks next quarter why a customer was counted as lost, you can show the two lists that said so.

Where does Glow Reports fit?

The formulas above work in any copy of Excel or Google Sheets, and for a one-off check they are enough. Glow Reports helps when the comparison feeds a report. You upload both files, CSV or Excel, and ask in plain language which customers are in last month's file and not in this month's, and what they were worth.

The AI writes the SQL, builds the charts and drafts the text. The SQL sits under each figure, so you can read how it matched the two files and edit the query when it matched on the wrong column. Reports export to PDF and stay in the workspace. The free plan gives 300 credits once, and a report costs 100 credits.

Common questions

What is the easiest way to compare two columns in Excel?

A COUNTIF helper column: next to the first column, filled down. TRUE marks the values missing from the second column. It works in every version of Excel and in Google Sheets.

How do I compare two Excel files instead of two columns?

Copy both lists onto one sheet and use the formulas above, or load each file in Power Query and merge them with a left anti join. Power Query is the better choice when the files come back every month.

Can I use VLOOKUP to compare two columns?

Yes. returns TRUE when the value in A2 is not in column C. Always set the last argument to FALSE, or VLOOKUP returns the nearest value instead of saying it is missing.

Is COUNTIF case sensitive when comparing columns?

No. COUNTIF treats "acme" and "ACME" as the same value. Power Query merges compare case-sensitively, so lowercase both keys there if case doesn't matter for your data.

Why does FILTER return #CALC! in my comparison?

Nothing in the first list is missing from the second, and FILTER has no value to show. Add a last argument such as "None missing" so the formula shows text instead of an error.

El Mehdi Benane
Founder, Glow Reports

I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.

Upload both files to Glow Reports and ask who is in one and not the other, with the SQL behind each figure.

Keep reading