To compare two aligned lists in Microsoft Excel, put the values in columns A and B, then enter =IF(A2=B2,"Same","Different") in C2 and fill it down. To check whether items in one list appear anywhere in another, use a COUNTIF-based check instead; Conditional Formatting and Power Query are useful for visual comparisons and larger datasets.
Choose the comparison that fits your data
“Compare data” can mean several different things in Excel. You might want to check whether two cells in the same row match, find values that are present in one list but absent from another, identify duplicate entries, or compare two tables using an ID column. The right approach depends on whether the records are aligned row by row and whether order matters.
- Same row, same position: Compare cells directly with a formula or Conditional Formatting.
- Same items, different order: Check whether each value in one list appears anywhere in the other.
- Tables with a shared ID: Match on that ID, then compare the fields you care about.
- Large or regularly refreshed files: Use Power Query to join or compare tables without manually copying formulas.
Before comparing, decide what counts as a match. Should capitalization matter? Are leading or trailing spaces meaningful? Should duplicate entries count separately? These details can change the result even when the cells look alike.
Compare cells in the same rows with a formula
For a direct row-by-row comparison, assume the first list is in A2:A6 and the second is in B2:B6, with each row representing the same record. Enter =IF(A2=B2,"Same","Different") in C2 and copy it through C6. For example, if A4 contains 103 and B4 contains 130, C4 returns “Different”; if both cells contain 104, the result is “Same.” The formula compares the values in each row, not the overall contents of the two lists.
On Microsoft 365 for Windows, select C2 and drag the fill handle—the small square at the lower-right corner of the selection—down the column. You can also copy C2, select C3:C6, and paste. On a Mac, the same formula and fill-handle approach work. Excel adjusts the relative row references as it fills the formula, so the formula in C3 checks A3 against B3.
A direct comparison is often the clearest option for matching exports where row order is supposed to be identical. It does not tell you whether a value from one column appears elsewhere in the other. If one list has been sorted differently, a row-by-row check may report differences even though both lists contain the same values.
Find values that are missing from another list
When list order may differ, check membership rather than comparing corresponding rows. Assume A2:A6 contains the reference list 101, 102, 103, 104, and 105, while D2:D6 contains 101, 103, 105, 107, and 109. With the reference values in A2:A6 and the items to check in D2:D6, enter =IF(COUNTIF($A$2:$A$6,D2)>0,"Found","Missing") in E2 and fill down. E2 returns “Found”; the check for 107 returns “Missing,” as does the check for 109.
COUNTIF counts how many times the D2 value occurs in the reference range. The dollar signs keep that range fixed as the formula is copied down, while D2 changes to D3, D4, and so on. This method answers whether a value appears at least once; it does not check whether both lists have the same number of occurrences. For example, if 103 appears twice in one list and once in the other, a membership check still finds it in both.
To find values in the reference list that are absent from the other list, reverse the ranges: check each value from A2:A6 against D2:D6. Comparing in both directions is important if you want a complete list of differences rather than only the items missing from one side.
Highlight differences with Conditional Formatting
Conditional Formatting lets you spot mismatches without adding a result column. For an aligned comparison, assume the first set of values is in A2:A6 and the second in B2:B6. Select A2:B6, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =$A2<>$B2, choose a format such as a fill color, and confirm the rule. Rows where the two values differ are highlighted; with the example above, the mismatch in row 4 is marked.
The formula uses the row number relative to the top of the selected range, so Excel checks row 2 first and adjusts for each subsequent row. The dollar signs before A and B keep the column references fixed. Conditional Formatting changes the appearance of cells, not their contents, and the highlight can be less useful when printing in grayscale or when the formatting is difficult to distinguish. A separate formula column is easier to filter, count, or export.
For a quick manual scan, Excel’s Find command can also help locate a specific value. Press Ctrl+F on Windows or Command+F on Mac, enter the value, and review the matches. Find is useful for occasional checks, but it is not a substitute for a repeatable comparison across hundreds or thousands of rows.
Compare text, numbers, blanks, and duplicates
Excel’s ordinary equality comparison is not case-sensitive for text: “North” and “north” compare as equal. If capitalization must match, use the EXACT function. Assume A2:A3 contains “North” and “north,” while B2:B3 contains “North” in both rows. Enter =EXACT(A2,B2) in C2 and copy it to C3. C2 returns TRUE; C3 returns FALSE because the capitalization differs. EXACT compares text case-sensitively.
Numbers stored as text can also produce confusing results. A cell containing the number 123 and a cell containing the text “123” may look identical but have different underlying types. Check the cells’ number formats and, when necessary, inspect or convert the source data before comparing. Do not convert values blindly: leading zeros may be meaningful in product codes, account references, or postal codes. In those cases, treating the entries as text may be the correct choice.
Blank cells need attention too. A formula that compares two empty cells can report them as equal, even if your business rule says a missing value should be flagged. Decide whether blank-versus-blank is acceptable and whether blank-versus-filled should count as a mismatch. Similarly, duplicate counts matter when comparing inventories, attendance lists, or transaction records. A simple COUNTIF membership test only establishes that a value exists; it does not prove that duplicate quantities match.
Compare tables by a shared key
When two tables have the same records in a different order, compare them by a stable key such as an order number or employee ID—not by row position. First check that the key is unique in each table. If an ID occurs more than once, a lookup can return one matching record while overlooking other rows, so the comparison may be incomplete.
For a small table, you can use a lookup to bring a field from one table beside the matching ID in the other, then compare the two fields. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Make sure the lookup uses the intended exact-match behavior and decide how missing IDs should be displayed. After matching, compare fields such as status, date, or amount separately; a matching ID does not mean every field in the row matches.
For larger tables or repeat work, Power Query can merge the datasets using the shared key. In Excel for Microsoft 365 on Windows, load each range or table with Data > Get Data > From Table/Range, then use the Power Query Editor’s Home > Merge Queries command and select the key column in each query. A Left Anti join returns rows from the first table that have no matching key in the second; an anti-join in the opposite direction reveals unmatched rows from the other table. A regular merge can bring related columns together for field-by-field comparison. On Mac, Power Query availability and supported actions can vary by Excel version; if the relevant merge options are not available, use formulas or open the workbook in Excel for Windows.
Common reasons comparisons give unexpected results
A reported difference is not always a meaningful data change. Look for invisible spaces, inconsistent date formats, different number types, and text that contains nonprinting characters. Two dates may display in the same format but represent different underlying values, while two values that look different may be equal after formatting. Sorting one list without sorting its paired columns can also break row alignment and make a direct comparison misleading.
- Check the structure: Confirm that each row represents the same record before using a row-by-row formula.
- Check data types: Verify that dates, numbers, and text are stored consistently.
- Check spaces and spelling: Inspect suspicious entries for extra spaces, punctuation, or inconsistent names.
- Check duplicates: Decide whether repeated values are valid and whether their counts must match.
- Check the comparison rule: Confirm whether case, blanks, and formatting differences should count.
Filters can help review the output of a formula column: filter for “Different” or “Missing” to focus on exceptions. Keep an unchanged copy of the source data before cleaning, sorting, or removing duplicates. If a discrepancy matters, verify it against the original source rather than assuming that either spreadsheet is correct.
Which Excel comparison method should you use?
Use a direct equality formula when rows are aligned and you need a clear result for each pair. Use COUNTIF when order differs and your question is simply whether values from one list occur in another. Choose Conditional Formatting for a quick visual review, especially when a separate output column would clutter the sheet. Use Power Query when you need a repeatable comparison between tables, particularly when the data is refreshed regularly or contains many rows.
These methods answer different questions, so they are not interchangeable. A row-by-row formula checks paired positions; a membership formula checks presence; and a table merge matches records using a key. For a complete reconciliation, you may need more than one method—for example, identify unmatched IDs with an anti-join, then compare selected fields for IDs that do match.
Frequently Asked Questions
Can Excel compare two columns and highlight differences?
Yes. Select the aligned range and create a Conditional Formatting rule using a formula such as =$A2<>$B2. The columns and starting row in the rule must correspond to the range you selected.
How do I compare two lists when the order is different?
Use a membership check such as COUNTIF to test each value in one list against the full range of the other. Repeat the check in the opposite direction if you need to find missing values on both sides.
Does Excel treat uppercase and lowercase text as different?
Usually not in ordinary comparisons. Use EXACT when capitalization matters; it returns TRUE only when the text strings match exactly, including case.
Can Excel compare two worksheets?
Yes. If the sheets have matching row order, reference the appropriate cells on each sheet in a comparison formula. If their rows are in different orders, match records by a shared key or use Power Query rather than assuming corresponding row numbers represent the same record.
Why does Excel say two values are different when they look the same?
Common causes include extra spaces, text-versus-number storage, different underlying dates, or small differences hidden by cell formatting. Inspect the source values and types, and confirm that the chosen comparison method matches your requirements.
Related
- Excel Insert Row Shortcut: Add a Row Above or Below
- Meal Plan Excel Sheet Template for Weekly Planning
- Meal Plan Excel Sheet: Plan Meals, Recipes, and Groceries
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Save an Excel File in Read-Only Mode: StepsTo make an Excel workbook open with a read-only prompt, use File > Save As > Browse, then…
- Calculate the Number of Months in ExcelTo count complete months between two dates in Excel, enter the earlier date in A2 and the later…
- How to Insert a Line in Microsoft Word: 5 Easy WaysTo insert a quick horizontal line in Word, type three hyphens on an otherwise blank paragraph and press…
- Append Queries in Power Query: How to Combine TablesTo append queries in Power Query for Excel on Windows, open the Power Query Editor and choose Home…
- How to Delete a Border Line in Excel: Quick Steps and FixesTo delete a border line in Excel for Microsoft 365 on Windows, select the cell or range, then…
- How to Enter a Checkbox in Google Sheets, Step by StepTo enter a checkbox in Google Sheets, select the cell or range where you want it, then choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.