To compare values in the same row, put this formula in a spare column and fill it down: with corresponding values in A2:A100 on Sheet1 and Sheet 2, enter =IF(A2='Sheet 2'!A2,"Match","Different") in Sheet1 cell B2. It returns Match when the two cells agree and Different when they do not. If rows may be in a different order, use a lookup or count-based check instead of comparing row by row.
Choose a comparison that fits your data
“Compare columns” can mean several things. You might need to check whether corresponding rows contain the same value, find values in one sheet that are absent from another, identify differences in a long list, or compare two lists that contain duplicates. The right method depends on which question you are trying to answer.
- Same item, same row: Compare each pair of cells directly. This is best when both sheets use the same row order and each row represents the same record.
- Same list, possibly different order: Test whether each value in one column appears anywhere in the other. This is useful for reconciling exports or checking membership lists.
- Find every difference visually: Use a helper formula, then filter its results, or use conditional formatting for a quick scan.
- Check duplicates and counts: Compare how many times each value appears, not merely whether it appears at least once.
Before choosing, confirm what counts as a match. Excel’s ordinary equality comparison treats text with different capitalization, such as “North” and “north,” as equal. A comparison may also need rules for extra spaces, blank cells, numbers stored as text, or repeated values. Decide those rules first so the result answers the real question rather than just producing a column of labels.
Compare corresponding rows with a formula
When each row on one sheet corresponds to the same row on the other, a direct formula is the simplest approach. In the opening example, the formula in B2 compares Sheet1!A2 with ‘Sheet 2’!A2. The single quotes around the second sheet name are required because it contains a space. Sheet names without spaces or certain special characters can be written without quotes.
After entering the formula, use the fill handle—the small square at the lower-right corner of the selected cell—to copy it down the comparison range. In row 2, if both cells contain 125, the result is Match; if Sheet1!A3 contains 130 and ‘Sheet 2’!A3 contains 128, the result in the next row is Different. The formula’s relative row references adjust as it is filled down.
For a shorter result, the formula can return TRUE or FALSE instead of words. With the same layout, enter =A2='Sheet 2'!A2 in Sheet1 cell B2 and fill down. TRUE means the values compare as equal; FALSE means they do not. This is handy when another formula, filter, or summary will use the comparison result.
To make the comparison case-sensitive, use EXACT. With the same row layout, enter =EXACT(A2,'Sheet 2'!A2) in Sheet1 cell B2. It returns FALSE for “North” versus “north,” even though an ordinary equals comparison treats those text values as equal. EXACT is useful for codes or identifiers where capitalization carries meaning, but it does not remove spaces or otherwise standardize the contents.
Find values regardless of row order
A direct row comparison gives misleading results if the same records appear in a different order. For example, an ID of 104 may be in row 8 on one sheet and row 21 on the other. In that situation, test whether each value from the first list appears anywhere in the second list.
With IDs in Sheet1!A2:A100 and Sheet 2!A2:A100, enter =IF(ISNUMBER(XMATCH(A2,'Sheet 2'!$A$2:$A$100,0)),"Found","Missing") in Sheet1 cell B2, then fill down. XMATCH searches the second range for the value in A2; the final 0 requests an exact match. ISNUMBER identifies a successful position, and IF displays Found or Missing. XMATCH is available in Microsoft 365 and Excel 2021 or later.
For example, if A2 contains ID 104 and that ID occurs in Sheet 2!A21, the formula returns Found even though the rows differ. If ID 105 does not occur in the specified range, it returns Missing. Use dollar signs on the search range so it stays fixed when the formula is filled down; A2 remains relative so it changes to A3, A4, and so on.
COUNTIF is another option for a straightforward membership check. With the same ID ranges, enter =IF(COUNTIF('Sheet 2'!$A$2:$A$100,A2)>0,"Found","Missing") in Sheet1 cell B2 and fill down. It returns Found if the value occurs one or more times. COUNTIF criteria recognize wildcard characters such as * and ?, so a value containing those characters can behave as a pattern rather than a literal ID. For exact matching with identifiers that may include wildcards, prefer XMATCH with match mode 0.
Highlight differences and filter the results
A helper column is often more useful than highlighting alone because it labels each row and can be filtered. After filling a comparison formula down, select the data and use Data > Filter. Open the filter arrow on the result column and select Different, Missing, or FALSE to isolate the records that need attention. On Windows, Ctrl+Shift+L toggles filters for a selected data range; on Mac, the shortcut is Command+Shift+F in many Excel configurations, and the Data tab is a reliable alternative.
For a quick visual scan of cells in the same worksheet, select the cells to format and choose Home > Conditional Formatting > Highlight Cells Rules. However, conditional-formatting formulas that refer directly to another worksheet can be restricted. A dependable approach is to calculate the comparison in a helper column on the current sheet and apply conditional formatting to that result, or use a formula-based rule that refers to a named range. Avoid assuming that selecting two separate columns and choosing a built-in highlight rule will compare them row by row; many built-in rules compare values to fixed thresholds instead.
Color is useful for spotting patterns, but it should not be the only record of a reconciliation. A labeled result column can be filtered, copied, counted, or reviewed later, while colors may be lost when data is copied or may be difficult for some readers to distinguish. If you use formatting, keep the explicit Match or Different result as well.
Duplicates, blanks, and exact matching
A membership test answers whether a value appears at least once; it does not prove that two lists contain the same number of each value. Suppose one sheet contains ID 104 twice and the other contains it once. XMATCH and COUNTIF membership checks report Found for both occurrences, so they do not reveal the count discrepancy.
To compare duplicate counts, count each ID in both lists. With IDs in Sheet1!A2:A100 and Sheet 2!A2:A100, enter =COUNTIF($A$2:$A$100,A2)=COUNTIF('Sheet 2'!$A$2:$A$100,A2) in Sheet1 cell B2 and fill down. It returns TRUE if that ID has the same frequency in both ranges. For ID 104 appearing twice in Sheet1 and once in Sheet 2, the result is FALSE. This check compares frequencies for values in the first list; to find values found only in the second list, perform the check from the second sheet as well.
Blank cells need deliberate treatment. In a direct comparison, two blank cells return a match, which may or may not be meaningful. A blank on one sheet and a populated cell on the other returns a difference. If blank rows should not appear in the results, add an explicit blank check. With corresponding values in A2 and ‘Sheet 2’!A2, enter =IF(AND(A2="",'Sheet 2'!A2=""),"Both blank",IF(A2='Sheet 2'!A2,"Match","Different")) in Sheet1 cell B2. This labels two empty cells separately rather than treating them as an ordinary matching record.
Spaces and data types can create surprising mismatches. “ID7” and “ID7 ” look alike but are different text values because one has a trailing space. Similarly, a number stored as text may not behave like the same number stored as a numeric value in every operation. Inspect the source data, and decide whether to clean it or compare it exactly. TRIM can remove ordinary leading and trailing spaces from text, but it does not fix every kind of imported whitespace, such as nonbreaking spaces.
Compare two worksheets with Excel tools
For most worksheet-to-worksheet comparisons, formulas are easy to audit and update. Excel does not have one universal button that reconciles any two columns while automatically inferring whether rows correspond or whether duplicates matter. Formulas make the comparison rule visible, and a helper column gives you a reusable result. If you only need a quick visual check, you can also place the sheets side by side using View > New Window and View > View Side by Side, then scroll through matching rows. This helps with manual inspection but does not detect differences automatically.
Microsoft Spreadsheet Compare is a separate comparison tool included with certain editions of Microsoft Office, rather than a standard worksheet formula or a feature available in every Excel installation. It compares workbook contents and can be useful for reviewing broader workbook changes, but availability depends on the Office edition and installation. For two columns with a clearly defined matching rule, a formula is usually more direct. Excel for the web supports common formulas such as IF and COUNTIF, but some desktop comparison tools and ribbon options may not be available there. On Mac, formula syntax is generally the same; ribbon layout and keyboard shortcuts can differ.
Common problems and how to troubleshoot them
- The formula shows an error: Check sheet spelling and punctuation. A sheet name containing spaces needs single quotes around it, as in ‘Sheet 2’!A2. Also confirm that the formula begins with an equals sign and uses commas between arguments in the standard English-language syntax.
- Every row says Different: Verify that the lists really align row by row. If the order differs, use a lookup or membership check. Look for hidden spaces, inconsistent data types, or a reference to the wrong column.
- Some missing values appear Found: Review the search range and its boundaries. COUNTIF uses wildcard criteria, so values containing * or ? can produce unexpected matches; XMATCH with exact match mode 0 is a better choice for literal identifiers.
- Matches appear despite different capitalization: Ordinary equality and common lookup comparisons are not case-sensitive. Use EXACT for a case-sensitive, same-row text comparison.
- Results change after filling down: Lock the lookup range with dollar signs, such as $A$2:$A$100, while leaving the value being checked relative, such as A2.
When data is imported from another system, check for nonprinting characters as well as visible spaces. A cell can look identical while containing a different character. Compare a few suspicious values in the formula bar, test a representative record, and avoid applying a cleanup formula to an entire source column until you understand what the characters mean. Keep an untouched copy of important source data before making bulk changes.
Frequently Asked Questions
How do I compare two columns in Excel and find what is missing?
Use a membership formula such as the XMATCH example above to test each value in the first column against the second. Filter the result for Missing. To identify values present only in the second list, run the same check in the opposite direction.
Can Excel compare two sheets when the rows are in a different order?
Yes. Search for each value anywhere in the other sheet with XMATCH or COUNTIF rather than comparing the same row number. If duplicate counts matter, add a frequency comparison because a simple found-or-missing check does not account for repetitions.
How do I compare two cells and return TRUE or FALSE?
For corresponding rows, use =A2='Sheet 2'!A2 in the result column and fill it down. It returns TRUE when Excel considers the values equal and FALSE otherwise. Use =EXACT(A2,'Sheet 2'!A2) when text capitalization must match.
Why does Excel say two values are different when they look the same?
Check for leading or trailing spaces, nonprinting characters, different capitalization when using a case-sensitive formula, and numbers stored as text. Imported data can also contain characters that look like ordinary spaces but are not the same character.
Does COUNTIF compare values exactly?
COUNTIF counts cells that meet its criteria, but * and ? in the criterion act as wildcards. For literal exact-match searches, XMATCH with its match-mode argument set to 0 is less likely to misinterpret an identifier containing wildcard characters.
Which method should I use for a large comparison?
Use a helper column with XMATCH for differently ordered lists, or a direct formula for aligned rows, then filter the results. Keep ranges limited to the actual data where practical, and use a defined Excel Table when rows will be added regularly so formulas and ranges are easier to maintain.
Related
- Circular Cell Reference in Excel: Causes and Fixes
- Circular Reference Settings in Excel: How They Work
- Change the Print Area in Excel: Steps and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF in Excel: Count Empty Cells and Blank Cells EasilyIf your cells are in A2:A20, use =COUNTIF(A2:A20,"") to count empty cells. COUNTIF with an empty-string criterion counts…
- VLOOKUP Excel Example: Look Up Data Across Two SheetsTo look up a value from one Excel sheet in another, use VLOOKUP with the source sheet in…
- Excel Formula Line Break: How to Add One in a CellTo put a line break in an Excel formula, join the text with CHAR(10), then turn on Wrap…
- SUMIF Example Formula: Syntax, Uses, and Common FixesWith product names in A2:A7 and sales amounts in B2:B7, use =SUMIF(A2:A7,"Apples",B2:B7) to add the sales for Apples;…
- XLOOKUP Between Two Sheets in Excel?Yes. To look up a value on one Excel sheet and return a related value from another, use…
- Excel VLOOKUP Yes or No: How to Return a Match ResultYes. In Excel for Microsoft 365, you can make VLOOKUP return “Yes” or “No” by wrapping an exact-match…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.