How to Compare Differences in Excel: Cells and Sheets

How-to basics · Updated November 20, 2026

To compare values in the same row, enter a comparison formula such as =IF(A2=B2,"Match","Different") in a spare column and fill it down. To compare lists that may be in a different order, check whether each value appears in the other list; to highlight differences visually, use Conditional Formatting. The best method depends on whether the values line up by row, appear in separate lists, or sit on different worksheets.

What You’ll Need

  • Excel for Microsoft 365 on Windows and the workbook or workbooks you want to compare.
  • A spare column or worksheet for comparison results, unless you plan to use highlighting only.
  • A clear idea of what counts as a difference: for example, different values in the same row, a missing item, or a change in formatting.

If you are comparing two lists, make a copy of the data first if you might sort or edit it. The steps below work in desktop Excel for Microsoft 365. Excel for Mac has the same core formulas and Conditional Formatting options, though some ribbon layouts and keyboard shortcuts differ. Excel for the web supports formulas and conditional formatting too, but some desktop comparison tools may not be available.

Step-by-Step

  1. Choose the kind of comparison. Use a row-by-row comparison when each value in one column is supposed to match the value beside it. Use a list membership check when the same items may be in different positions. For worksheets with the same layout, compare corresponding cells. If you need to find changes in formulas, formatting, or workbook structure, a value formula alone will not show every kind of change.
  2. Prepare the data before comparing. Confirm that the records refer to the same things and that the columns have the same meaning. For row-by-row comparisons, sort both datasets in the same way before comparing; otherwise, a valid record in a different position will be reported as a mismatch. Keep headings out of the comparison range. If one column contains dates and the other contains text that looks like dates, convert them to actual dates first. The displayed format can look identical while the underlying values differ.
  3. Compare cells on the same row. Suppose the two values to compare are in A2 and B2, and column C is available for the result. With the values to compare in A2 and B2, enter =IF(A2=B2,"Match","Different") in C2. The formula returns “Match” when the values compare as equal and “Different” otherwise. For example, if A2 contains 125 and B2 contains 125, the result is “Match”; if B2 contains 130, it is “Different.” Select C2 and drag its fill handle down the rows, or copy and paste it into the rest of column C. Excel adjusts the row references as the formula fills down.
  4. Check for items missing from a differently ordered list. A row-by-row comparison is not suitable when the lists have different orders. If list A is in A2:A100 and list B is in B2:B100, and you want to check each item in A against all of B, enter this in C2. With list A in A2:A100 and list B in B2:B100, use =IF(COUNTIF($B$2:$B$100,A2)=0,"Missing from B","Found"). It returns “Found” if the value in A2 appears in the specified part of column B, and “Missing from B” if it does not. For instance, if A2 is “P-104” and that code appears anywhere in B2:B100, the result is “Found.” Fill the formula down to check the rest of list A. To check the other direction as well, create a second check against column A, starting with a value from column B.
  5. Highlight differences between two columns. If values are paired by row and you want to see mismatches without adding a result column, select the cells in both columns, excluding the headings. For example, with paired values in A2:A100 and B2:B100, select A2:B100. Choose Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format. With the comparison range beginning at row 2, enter =$A2<>$B2 in the rule formula box. Choose Format, select a fill or font style, then confirm the dialogs. The formula checks each row and highlights both cells in a row when they differ. The dollar signs keep the comparison in columns A and B as the rule is applied across the selected range. In Excel for Mac, select the range and use Home > Conditional Formatting to create a formula-based rule; the wording or placement of the rule dialog can vary by build.
  6. Compare corresponding cells on two worksheets. If two sheets use the same layout, put the comparison results on a third sheet or in an unused area. For example, with the cells to compare on worksheets named January Data and February Data, enter =IF('January Data'!A1='February Data'!A1,"","Different") in a result cell. This returns a blank when the two referenced cells are equal and “Different” when they are not. Copy the formula across and down to cover the area you want to inspect. The single quotes around the sheet names are required here because the names contain spaces. If your sheet names have no spaces or special characters, Excel may not need quotes, but using them is safe. Make sure the destination formula points to matching positions on both sheets.
  7. Review the results and decide what a mismatch means. A formula reports whether its comparison test is true; it does not explain why values differ. Check flagged rows against the original records. If the data should match, correct the source or investigate how it was imported. If differences are expected, keep the result column or highlighted view as an audit trail, and note the date or version of each source file so you can interpret the comparison later.

For two open workbooks, you can also arrange their windows for visual inspection using View > View Side by Side in desktop Excel for Windows. This displays the workbooks together but does not automatically identify changed cells. Excel for Mac offers window arrangement options, but the exact command can differ by version. For an automated check, copy comparable data into one workbook or use formulas that refer to the other workbook, then verify any external links before relying on the results.

Common Mistakes

  • Comparing rows that do not represent the same record. A row-by-row formula assumes that row 2 in one column belongs with row 2 in the other. Sort or match records using a unique ID before interpreting differences.
  • Using a row-by-row check for unordered lists. If a code is present in both lists but appears on different rows, a direct comparison calls it different. Use a membership check for unordered lists instead.
  • Assuming “Found” means the lists are identical. COUNTIF confirms that an item appears at least once. It does not compare how many times an item appears. If duplicates matter, compare counts or use a method designed to reconcile repeated records.
  • Missing invisible or type differences. Extra spaces, text stored as numbers, and dates stored as text can produce results that seem unexpected. Also, Excel’s ordinary equality test does not distinguish uppercase from lowercase text. Decide whether these differences matter before cleaning or comparing the data.
  • Including blank rows or headings. A blank in one list can be treated as a match if the other range contains blanks. Limit the comparison to actual records, and make sure your conditional formatting formula starts on the same row as the selected range.

Tips

  • Keep the originals intact. Add results in a new column or worksheet instead of overwriting either source. This makes it easier to check a surprising result and repeat the comparison.
  • Use a stable identifier. An invoice number, product code, or other unique key can help you align records before comparing their details. Names alone may be repeated or spelled differently.
  • Check a small sample first. Before filling a formula through thousands of rows, test a known match, a known difference, and a blank row. Confirm that the results are what you expect.
  • Make the results easy to filter. A result column with labels such as “Match” and “Different” can be filtered from Data > Filter. This can be easier to review than scrolling through a large highlighted range.
  • Be clear about case and duplicates. If uppercase and lowercase should count as different, or if repeated items must occur the same number of times, build that requirement into your method. A basic equality formula and a basic COUNTIF membership check do not cover every reconciliation rule.

Troubleshooting

  • Everything says “Different.” Confirm that the formula references the intended columns and that the two ranges begin on corresponding rows. Look for text-versus-number mismatches, extra spaces, or dates stored as text. Click a cell and inspect the formula bar to check the actual value, not only its displayed format.
  • Some apparent differences are not flagged. Check whether the comparison uses the right rows and whether the values are truly different underneath their formatting. For example, two cells might display rounded values that differ beyond the visible decimal places. Also check whether the formula was filled down far enough.
  • Conditional formatting highlights the wrong cells. Reopen Home > Conditional Formatting > Manage Rules and check both the formula and the Applies to range. If the range starts at row 2, the formula should refer to row 2. Ensure that the formula compares the intended fixed columns, not neighboring cells that shifted when the rule was created.
  • The list check reports an unexpected match. COUNTIF treats the asterisk and question mark as wildcard characters in criteria, so values containing those characters can be interpreted as patterns. Check for duplicate values and blank cells too. If those details affect your results, use a comparison method that handles them explicitly rather than treating the basic membership check as a complete audit.
  • A formula shows as text instead of calculating. Change the result cell’s number format to General, then edit the formula and press Enter. Check that the entry starts with an equals sign and that Formulas > Show Formulas is not turned on. If Excel rejects a formula, confirm that the quotes and parentheses are balanced and that your regional settings use commas as argument separators.

Frequently Asked Questions

Can Excel compare two columns and show only the differences?
Yes. Use a comparison formula in a helper column and filter for “Different,” or use a formula-based Conditional Formatting rule to highlight mismatched rows. The helper column is useful when you want a label you can filter or review.

How do I compare two lists when their items are in a different order?
Check each value in the first list for an occurrence in the second list, then repeat the check in the opposite direction. This identifies items missing from either list, but a basic presence check does not verify duplicate counts.

Does Excel treat uppercase and lowercase text as different?
No. A standard equality comparison is not case-sensitive, so “ABC” and “abc” compare as equal. If case is important, use a case-sensitive comparison method rather than relying on the basic formula shown above.

Can I compare two worksheets in the same workbook?
Yes. If their layouts match, use formulas that reference the same cell position on each sheet, then fill the formula across and down. If rows are in different orders, align the records first or compare by a unique identifier.

Can I compare two Excel files without putting them in one workbook?
You can open both files and use View > View Side by Side for manual inspection. For formula-based checks, create references between workbooks or bring the relevant data into one workbook, then verify that the linked files and ranges are correct.

Will this compare formatting as well as cell values?
No. The formulas compare cell values or formula results, not font, fill, borders, or other formatting. Inspect formatting separately or use a workbook comparison tool if you need to audit structural or formatting changes and that tool is available in your Excel edition.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.