Microsoft Office

Microsoft Excel How To Compare Two Columns

To compare two columns in Microsoft Excel, use an IF formula for values on the same row, COUNTIF to check whether an entry appears anywhere in another list, or conditional formatting to highlight results. Choose the method based on whether row order matters and whether.

Which licences fit your PC setup?

Prices are an AUD snapshot, not a live quote. Confirm variant and total on the listing.



Choose the right comparison

Two columns can contain the same entries in a different order. A row comparison will flag those entries as different, while a list comparison will find them. Decide which question you need to answer before entering a formula.

Your question Method Result
Do the values on each row agree? IF with A2=B2 A match or difference label
Is each entry in column A present anywhere in column B? COUNTIF in a helper column A found or missing label
Which cells need attention? Formula-based conditional formatting A colour applied to matching or differing cells

The examples below use headings in row 1 and data in A2:A100 and B2:B100. Replace these example ranges with your actual data ranges; the two lists do not need to be the same length.

Compare values on the same row

Use this approach when each row represents the same record in both columns, such as an expected amount beside an actual amount. If the records are in a different order, align them by a reliable identifier first or use a list comparison.

  1. Enter a heading such as Comparison in C1.
  2. In C2, enter =IF(A2=B2,"Match","Different").
  3. Press Enter, then fill the formula down to the last row of your data.
  4. Select your data and headings, choose Data > Filter, and filter column C for Different to review discrepancies.

The formula compares cell values. Changing a font or fill colour does not create a difference. Both empty cells return Match, while a blank cell and a zero can also compare as equal. Check blanks separately if an empty entry has a different meaning from zero.

Make a text comparison case-sensitive

The ordinary equals comparison ignores letter case. For text where capitals matter, use =IF(EXACT(A2,B2),"Match","Different"). For example, ABC and abc return Different. Microsoft's EXACT function documentation explains that this comparison is case-sensitive and ignores formatting differences.

Find entries missing from another column

Use a list comparison for customer names, stock codes or reference numbers whose row positions may differ. A match means an entry exists in the other list; it does not establish that both lists contain the same number of copies.

  1. Enter a heading such as In column B? in C1.
  2. In C2, enter =IF(A2="","",IF(COUNTIF($B$2:$B$100,A2)>0,"Found","Missing")).
  3. Fill the formula down alongside column A.
  4. Filter column C for Missing to see non-empty entries in A that are absent from B.

The dollar signs keep the search range fixed as you fill down. The A2 reference changes to A3, A4 and so on. The outer IF leaves the result empty when the entry being checked is blank.

Check the other direction

Finding everything in A within B does not prove the lists contain the same entries: B could have additional values. In D2, enter =IF(B2="","",IF(COUNTIF($A$2:$A$100,B2)>0,"Found","Missing")), then fill down alongside B.

This second check identifies entries present only in B. Neither formula removes repeated entries. If a missing code appears several times in the source list, it will be flagged several times.

COUNTIF ignores letter case and treats * and ? as wildcards. For literal occurrences, use criteria such as "~*" or "~?"; a tilde escapes the following wildcard. Microsoft's COUNTIF guidance explains these matching rules.

Highlight differences with conditional formatting

Use a formula rule when you want colour to identify discrepancies. These steps follow the Excel desktop ribbon; menu wording can vary. Microsoft's conditional formatting instructions describe formula-based rules and formatting options.

  1. Select A2:B100, starting with A2 as the active cell.
  2. Choose Home > Conditional Formatting > New Rule.
  3. Select Use a formula to determine which cells to format.
  4. Enter =$A2<>$B2.
  5. Choose Format, select a fill colour, and confirm the rule.

Both cells on each differing row will be highlighted. The column references stay fixed, while the row changes throughout the selected range.

To highlight entries in A that are missing anywhere in B instead, select A2:A100 and use =AND(A2<>"",COUNTIF($B$2:$B$100,A2)=0). Change =0 to >0 to highlight entries found in B.

Understand the Duplicate Values shortcut

Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values identifies repeated values within the selected range. Selecting both columns can also highlight a value repeated entirely within one column. It therefore does not prove that a value occurs in both lists. Use a formula rule when that distinction matters.

Check unexpected results before changing data

  • Check for leading or trailing spaces in imported text. Clean a copy of the data before comparing again.
  • Keep identifiers consistent, particularly codes with leading zeros. Converting a code to a number can lose those zeros.
  • Check underlying values when displayed numbers look identical. Rounded displays can hide small differences.
  • Extend search ranges when new records are added. Entries outside the referenced range will not be checked.
  • If Excel rejects a formula, check whether your regional settings require semicolons instead of commas between arguments.

A Missing result is a reason to investigate, rather than an instruction to delete a record. Keep a copy of the original worksheet before making corrections.

For related setup help, the Microsoft Office guides include articles on finding an Office product key using Command Prompt and activating Office using CMD.

Start with a copy of your worksheet, apply the comparison to a few known examples, and check one expected match and one expected difference before filling down.

Other editions for separate workloads

Other editions for separate workloads; prices in AUD, confirm the total on the listing.

How to check this worked

  1. Open Settings, System, Activation and confirm the edition shown.
  2. Enter the key and run activation; expect a green tick within a minute.
  3. Reboot and re-check activation survives the restart.

FAQ

What else should you know before buying?

What do I need before starting?
Read the requirements and confirm the edition first; the steps assume the machine is compatible.
How do I know the licence is genuine?
Activate on Microsoft's servers and check Settings, System, Activation. A working activation is the test.
Is this a subscription?
Keys sold here are one-time lifetime licences. Microsoft 365 plans are billed by Microsoft instead.
What if the key fails?
Email hi@ms.codes with the order number and a screenshot. Replacement or refund applies within 30 days.

Check who prepared and reviewed this guide

Written by
MS.Codes editorial
Reviewed by
Support team

Last verified: (source review; see test status above).

Recent Post