Excel Text Compare: Formulas and Practical Methods

Formulas & functions · Updated November 18, 2026

To compare text in two Excel cells, use =A2=B2 for a case-insensitive comparison or =EXACT(A2,B2) when capitalization must match. Both formulas return TRUE when the comparison succeeds and FALSE when it does not; the right choice depends on whether differences such as “North” versus “north” matter.

What Excel text comparison means

Excel does not have a single command called “text compare.” Instead, you can compare cell contents with formulas, check whether an item in one list appears in another, or visually flag differences with conditional formatting. These methods answer related but different questions: whether two cells match, whether a value exists somewhere in a list, or which rows need attention.

A comparison generally checks the underlying cell values, not their visual formatting. Two cells that look different because one is bold or uses a different font can still contain identical text. Conversely, two cells that look the same may contain a leading space, a nonprinting character, or a different underlying value.

For a simple row-by-row check, assume A2 contains North and B2 contains South. The formula =A2=B2 returns FALSE. If both cells contain North, it returns TRUE. Excel’s equals operator does not distinguish uppercase from lowercase in text, so North and NORTH count as equal. Use EXACT when that distinction is important.

Compare corresponding cells row by row

When two columns contain values that should match on the same row, put a comparison formula in a third column and fill it down. For example, suppose column A contains the value from the original file and column B contains the value from a revised file. Put the formula in C2, then copy or fill it through the remaining rows. TRUE identifies matching pairs; FALSE identifies pairs to investigate.

With the values North in A2 and South in B2, =IF(A2=B2,"Match","Different") displays Different. If both cells contain North, it displays Match. The IF function is optional: it turns the TRUE or FALSE result into a label that may be easier to scan or filter. You can also use the basic comparison and filter the result column to show only FALSE rows.

To make the comparison case-sensitive, use this version instead. With North in A2 and north in B2, it returns Different:

With the text values to compare in A2 and B2, enter =IF(EXACT(A2,B2),"Match","Different"). For North in A2 and north in B2, the result is Different; for North in both cells, it is Match.

For a long sheet, keep the comparison formula in a separate column rather than replacing either source value. That preserves the original data, makes the comparison auditable, and lets you sort or filter the results without losing the information you are checking.

Compare two lists when order does not matter

A row-by-row comparison is not suitable if the same items may appear in different positions. If you want to know whether each value in one list appears anywhere in another list, COUNTIF can check for a match in a range. This standard text comparison is case-insensitive.

With the value to look up in A2 and the comparison list in D2:D100, enter =COUNTIF($D$2:$D$100,A2)>0. If A2 contains P-104 and the range includes P-101, P-104, and P-108, the result is TRUE. If P-104 is absent, the result is FALSE. The dollar signs keep the lookup range fixed when you fill the formula down.

This check answers whether at least one matching item exists; it does not prove that the lists contain the same items in the same quantities. If a value appears twice in the first list but only once in the second, both occurrences can still return TRUE. For duplicate-sensitive reconciliation, count occurrences on each side and compare those counts as a separate step.

COUNTIF also recognizes the wildcard characters * and ? in criteria. If a value being searched for literally contains one of those characters, the result may behave like a pattern match rather than a literal search. Check such entries carefully or use a different approach when wildcard characters are part of the real text.

Make the comparison case-sensitive

EXACT compares two text strings character by character and is case-sensitive. It returns TRUE only when the strings have the same characters in the same order, including capitalization. With A2 containing FileName.csv and B2 containing filename.csv, =EXACT(A2,B2) returns FALSE. If both contain FileName.csv, it returns TRUE.

Use EXACT for identifiers where capitalization is meaningful, such as codes in a system that treats ABC and Abc as different values. For ordinary names, descriptions, or labels, case-insensitive comparison is often more practical because inconsistent capitalization is common and may not indicate a real discrepancy.

EXACT does not compare formatting. It also does not mean “similar”: a single changed letter, extra character, or punctuation mark makes the result FALSE. If you want to identify near matches such as a misspelled name, you need a separate review method; an exact comparison only tells you whether the strings match under its rules.

Check spaces and hidden characters

A frequent source of unexpected FALSE results is extra whitespace. A trailing space in one cell, for example, makes its contents different from the same word without that space. Leading spaces and repeated spaces can cause similar problems. The spaces may be hard to notice in a narrow column, so inspect the formula bar or test a cleaned version of the text.

TRIM removes ordinary leading and trailing spaces and reduces repeated ordinary spaces between words to one. CLEAN removes certain nonprinting ASCII control characters. They do not remove every character that can look like a space: a nonbreaking space, often introduced when text is copied from a webpage, may need to be replaced explicitly.

With text in A2, enter =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) to replace nonbreaking spaces with ordinary spaces, remove supported control characters, and trim the result. If A2 contains a nonbreaking space followed by North and then extra ordinary spaces, the cleaned result is North. This formula returns a cleaned value; compare the cleaned results from both source cells to see whether the difference was only unwanted whitespace.

Do not clean data automatically if spaces or control characters could be meaningful in your particular dataset. Also note that CLEAN is not a universal remover of every invisible Unicode character. When a comparison remains unexplained, inspect a sample of the original source data and confirm how that data was exported or copied.

Highlight mismatches with conditional formatting

For a visual review, conditional formatting can color cells or rows that fail a comparison. On Excel for Microsoft 365 on Windows, select the range to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula, choose a format, and confirm the rule. On Mac, use Home > Conditional Formatting > New Rule; the dialog may look different, but you can create a rule based on a formula there as well.

With values in columns A and B starting on row 2, select A2:B100 and use =$A2<>$B2 as the rule formula. The dollar signs lock the columns, while the row number remains relative so each row checks its own pair. If A2 contains North and B2 contains South, the rule marks that row; if both cells contain North, it does not.

The equals comparison used in that rule is case-insensitive. If capitalization differences should also be highlighted, use =NOT(EXACT($A2,$B2)) instead, with the same selected range and starting row. This flags North versus north as well as differences in spelling or spacing. Make sure the first row number in the formula matches the first row of the selected range, or the highlighting can be offset.

Compare values on different worksheets

You can compare cells across worksheets directly. If both sheet names contain no spaces, a reference can use the sheet name, an exclamation mark, and the cell address. If a sheet name contains spaces, enclose it in single quotation marks. For example, a value on January Sales can be compared with the corresponding value on February Sales.

With the values to compare in cell A2 on sheets named January Sales and February Sales, enter =EXACT('January Sales'!A2,'February Sales'!A2). If both cells contain Invoice-27, the result is TRUE; if one contains invoice-27, the result is FALSE because EXACT distinguishes case. For a case-insensitive comparison, replace EXACT with the equals operator: ='January Sales'!A2='February Sales'!A2.

Before filling a cross-sheet formula down, check that the rows represent corresponding records on both sheets. If one sheet has been sorted differently, comparing row 2 with row 2 may produce many false mismatches even when the same records exist in both places. In that situation, match records by a reliable identifier first, then compare the fields for the matched records.

Common comparison problems and how to choose

Choose the method based on the question you need answered:

  • Use =A2=B2 to compare corresponding cells without distinguishing letter case.
  • Use =EXACT(A2,B2) when uppercase and lowercase differences count.
  • Use COUNTIF when checking whether a value appears anywhere in a list and its position does not matter.
  • Use conditional formatting when you need to scan mismatches visually across many rows.
  • Clean or normalize text only when unwanted spaces or control characters are known to be the problem.

Check the data types as well as the displayed text. A number stored as a number and the same digits stored as text are not necessarily treated as the same value in a direct comparison. If one source is an imported text field and the other is numeric, decide whether the intended comparison is numeric or textual before converting anything. Conversions can change meaningful details, especially in codes with leading zeros, such as 0042.

Also distinguish a genuine mismatch from a display difference. Number formats can make identical underlying numbers appear different, while formulas may produce text that looks like a manually entered value. If a comparison surprises you, check the formula bar, the cell format, the data type, and any leading or trailing spaces before editing the source.

Frequently Asked Questions

Is Excel text comparison case-sensitive? The equals operator is not case-sensitive for text, so North and NORTH compare as equal. Use EXACT when capitalization must match.

How do I compare two columns and show only differences? Add a comparison formula such as =A2<>B2 in a helper column, fill it down, and filter for TRUE. For a visual result, apply a conditional-formatting formula to the two columns.

Can I compare text from two sheets? Yes. Refer to each worksheet and cell in the formula, such as =EXACT('January Sales'!A2,'February Sales'!A2). Use quotation marks around sheet names that contain spaces.

Why do cells that look identical compare as different? One cell may contain an extra space, a nonbreaking space, a control character, or a different underlying value or data type. Check the source text and test a cleaned copy before changing the original.

Does Excel have a built-in button to compare text? There is no single general-purpose text-compare button. Formulas such as equals and EXACT handle direct comparisons, COUNTIF checks list membership, and conditional formatting helps show differences.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.