To compare two Excel lists and find items that do not match, use COUNTIF to check whether each value in one list appears in the other. With List A in A2:A100 and List B in B2:B100, enter this in C2: =IF(A2="","",IF(COUNTIF($B$2:$B$100,A2)=0,"Only in A","Found in B")). Fill it down, then repeat the check in the other direction to find items only in List B. For values that should match on the same row, use a direct row-by-row comparison instead.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The formulas in this guide also work in recent desktop versions of Excel and Excel for the web.
- Two lists in a workbook. For the examples, List A is in A2:A100 and List B is in B2:B100, with headers in row 1. Adjust the ranges to fit your data.
- A clear idea of what “different” means for your task: different values on the same row, or items that are missing from one list regardless of order.
- Optionally, a spare column for results. Save a copy first if you plan to sort or edit the original data.
Step-by-Step
- Decide which kind of comparison you need. A row-by-row comparison checks whether A2 matches B2, A3 matches B3, and so on. Use it when the rows are aligned records, such as this month’s figures beside last month’s figures. A membership comparison checks whether each value in one list appears anywhere in the other, so it still works if the lists are in a different order. Use membership checks to compare customer IDs, product codes, or two exports where rows may have been sorted differently. If you are unsure, compare both ways: an item can be missing from A, missing from B, or simply in a different position.
- Prepare and inspect the lists. Put the two lists in separate columns and add descriptive headers, such as “Old list” and “New list.” Check that each column contains the same kind of information. A column of IDs should be compared with another column of IDs, not with names or descriptions. Remove extra spaces if they are not meaningful, and check for blank rows within the data. Do not delete duplicates automatically: repeated values may be important, and a simple membership check does not count how many times an item appears. Keep the original columns unchanged while you work in helper columns.
- Compare values on matching rows, if row order matters. With List A in A2:A100 and List B in B2:B100, enter this formula in C2 to leave rows blank when both cells are blank and label the rest:
=IF(AND(A2="",B2=""),"",IF(A2=B2,"Match","Different")). Press Enter. Select C2 and use the fill handle—the small square at the lower-right corner of the selected cell—to copy the formula down beside all rows. “Match” means the two cells on that row are equal under Excel’s normal comparison rules; “Different” means they are not. If one list has extra rows, extend the formula and inspect those rows too, since a blank on one side and a value on the other counts as a difference. - Find items in List A that are absent from List B. With List A in A2:A100 and List B in B2:B100, enter this in C2:
=IF(A2="","",IF(COUNTIF($B$2:$B$100,A2)=0,"Only in A","Found in B")). Fill it down through the rows in List A. The dollar signs keep the search range fixed as the formula is copied, while A2 changes to A3, A4, and so on. “Only in A” identifies a value with no match in the checked portion of List B. “Found in B” means at least one match exists; it does not mean the lists contain the same number of copies. - Check in the reverse direction. To find values in List B that are absent from List A, with List A in A2:A100 and List B in B2:B100, enter this formula in D2:
=IF(B2="","",IF(COUNTIF($A$2:$A$100,B2)=0,"Only in B","Found in A")). Fill it down beside List B. Looking both ways is important: the first check only reports missing items from A’s perspective. Filter the results column using Data > Filter or the filter arrows in the headers, then show only “Only in A” or “Only in B” to review the differences quickly. Clear the filter when you want to see the full list again. - Highlight differences without adding result labels. To highlight values in A that do not appear anywhere in B, select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With A2:A100 selected and A2 as the active cell, use this rule formula:
=COUNTIF($B$2:$B$100,A2)=0. Choose Format, select a fill or font style, and confirm the dialogs. The relative A2 reference adjusts for each selected row, while the B range stays fixed. Repeat for B2:B100 using the reverse-range formula if you want to highlight items missing from A. Conditional formatting marks cells visually but does not change their values. - Check whether duplicates affect the answer. The membership formulas above answer “Does this value occur at least once in the other list?” If quantities matter, an extra copy can be a difference even when the value exists on both sides. With List A in A2:A100 and List B in B2:B100, enter this in C2 to flag occurrences in A that exceed the number of matching occurrences in B:
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>COUNTIF($B$2:$B$100,A2),"Extra occurrence in A","Matched occurrence")). Fill it down. For example, if A contains three copies of “P17” and B contains two, the third occurrence in A is labelled “Extra occurrence in A.” Repeat the logic for B if you also need to find excess copies there. - Review the results before acting on them. Filter or sort the helper-column labels and check a few flagged values in the original data. Confirm that the searched ranges include every data row and do not include unrelated notes or totals. If the lists are identifiers, consider checking the cell formats and stored values as well as what is displayed. Once you are satisfied, you can copy the results and use Paste Special > Values if you need fixed labels, or keep the formulas so the comparison updates when the source lists change.
Common Mistakes
Using the wrong comparison method: A direct formula such as =A2=B2 tests only the two cells on the same row. It will report differences if the same values are present but sorted differently. Use the COUNTIF membership method for lists that are not aligned.
Checking only one direction: “Only in A” does not tell you what is missing from A. Run the reverse check too if you want a complete comparison.
Ignoring duplicates: A value appearing once in each list is considered present, even if one list contains extra copies. Use an occurrence-count comparison when the number of copies matters.
Comparing inconsistent data: A number stored as text may not behave as expected beside a numeric value. Extra spaces can also make text values appear different. Check the underlying values and clean the data consistently before interpreting results.
Overlooking the range endpoints: If a formula checks B2:B100 but the list continues to row 120, values in rows 101–120 will be missed. Update the ranges when the data grows, or convert each list to an Excel table so its structured range can expand with added rows.
Tips
- Keep a copy of the source lists and put comparison labels in separate columns. This makes it easier to verify a flagged result and undo accidental edits.
- Use clear headers and consistent formatting. Format both ID columns as text before pasting codes that may contain leading zeros, such as 0042; once Excel has removed a leading zero, changing the display format may not restore the original code.
- Excel’s ordinary equality comparison and COUNTIF are not case-sensitive. If “abc” and “ABC” should count as different, use an exact, case-sensitive check. With A2 as the item to test and B2:B100 as the comparison list, enter this in C2:
=IF(SUMPRODUCT(--EXACT(A2,$B$2:$B$100))=0,"Only in A (case-sensitive)","Found in B"). Fill it down. EXACT distinguishes letter case; this version checks whether any exact-case match exists, not how many duplicates there are. - COUNTIF treats the characters
*and?as wildcards in its criteria. If your values literally contain those characters, check the results carefully or escape the characters in the criterion before relying on the match. - For a quick review, turn on filters for the headers and show only the difference labels. You can also apply conditional formatting to make differences stand out while leaving the underlying list intact.
- On Excel for Mac, the formulas and Conditional Formatting options are substantially the same. Keyboard shortcuts differ: for example, use Command+D rather than Ctrl+D to fill down where that shortcut is supported. Excel for the web supports these formulas too, though some ribbon controls and dialog layouts may look different.
Troubleshooting
The formula returns “Found in B” for something that looks absent. Check for leading or trailing spaces, invisible characters, and a value that exists farther down the checked range. Compare the underlying cell contents in the formula bar, and make sure the full list is included. If the source data was imported, clean both lists in the same way rather than cleaning only one side.
A matching item is labelled “Only in A.” Confirm that both cells contain the same type of value. For instance, 42 stored as a number and “42” stored as text can create unexpected results in some comparisons. Also check whether one value includes leading zeros or extra spaces. Make a cleaned copy of both lists if you need to standardize them, and preserve the raw data for reference.
The result shows as a formula instead of a label. Make sure the cell is not formatted as Text. Change the format to General, then re-enter the formula by selecting the cell, pressing F2, and pressing Enter. Also check that the formula begins with an equals sign and that Show Formulas is not turned on; in Windows, Ctrl+` toggles formula display.
Some rows have no result or the highlight stops early. The formula may not have been filled down far enough, or the conditional formatting rule may apply to only part of the list. Extend the fill handle through the final row. For highlighting, inspect the rule’s Applies to range in Home > Conditional Formatting > Manage Rules and correct its boundaries.
COUNTIF gives an unexpected result for codes containing asterisks or question marks. Those characters act as wildcards in COUNTIF criteria. Where they are part of the literal code, use a case-sensitive EXACT-based check or escape the wildcard characters before using COUNTIF. Test the approach on a few known examples before applying it to a large list.
Frequently Asked Questions
How do I compare two Excel columns row by row?
Place the columns side by side and use the row-by-row formula in a third column. Fill it down to label each pair as “Match” or “Different.” This only compares values in corresponding rows.
How do I find values that are in one list but not the other?
Use COUNTIF to check each list against the other, then run the check in both directions. Filter for “Only in A” and “Only in B” to see what each list is missing.
Can Excel compare lists that are in a different order?
Yes. The COUNTIF formulas search the whole comparison range, so the matching item can be on any row. A row-by-row equality formula is not suitable when the order differs.
Does the comparison distinguish uppercase and lowercase?
Not with the standard equality or COUNTIF formulas; they are not case-sensitive. Use the EXACT and SUMPRODUCT formula in the Tips section when letter case matters.
Will these formulas work if my lists have duplicates?
The basic membership check reports whether a value appears at least once, so it does not detect extra copies. Use the occurrence-count formula in Step 7 if the number of duplicates matters.
What if my lists are longer than row 100?
Change each formula range to include the last data row, such as B2:B500. For lists that regularly grow, format each list as a table with Insert > Table and consider using table references so new rows are included in future comparisons.
Related
- How to Change Data to Lowercase in Excel
- How to Change Cell Color in Excel
- Excel Drop-Down Colors: Change the Cell, Not the List
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel: Copy Subtotal Rows Only Without Detail RowsYes. In Excel for Microsoft 365 on Windows, collapse the outline to level 2, select the range you…
- Excel Power Query: Turn a JSON List Into a TableIn Excel for Microsoft 365 on Windows, choose Data > Get Data > From File > From JSON,…
- Excel Keyboard Shortcuts for Selecting Cells QuicklyIn Excel for Microsoft 365 on Windows, use Shift+Arrow to extend a selection one cell at a time,…
- Excel Packing List Template: What to Include and How to UseAn Excel packing list template is a reusable worksheet for recording what is packed, how many packages or…
- How to Schedule an Email in Outlook with Viva InsightsTo schedule an email in Outlook with Viva Insights, compose your message, add the recipient, and use the…
- Conditional Dropdowns in Google Sheets: How They WorkTo make a conditional (dependent) dropdown in Google Sheets, create a named range for each group of choices,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.