If an Excel equality formula is not working, first check that it is entered as a formula and that both cells contain the same kind of value; a basic comparison such as =A2=B2 returns TRUE when the values match and FALSE when they do not. Hidden spaces, numbers stored as text, tiny decimal differences, and calculation settings are among the most common reasons a result is unexpected.
How Excel’s equal formula works
In Excel, the equals sign has two jobs: it starts a formula and, inside a formula, it can compare two values. When it compares cell contents, the result is a logical value: TRUE or FALSE. The comparison does not change either cell or make their values match; it only reports whether Excel considers them equal.
For example, with the numbers 24 in A2 and 24 in B2, enter the following in C2. The result is TRUE.
With the values being compared in A2 and B2: =A2=B2
To show a word instead of TRUE or FALSE, with the same layout, use this formula in C2. It returns Match if the values compare as equal and Different otherwise.
With the values being compared in A2 and B2: =IF(A2=B2,"Match","Different")
Check that the formula begins with an equals sign and that the cell references point to the cells you intend to compare. A formula such as =A2=B2 is different from typing A2=B2 without the first equals sign: the latter is ordinary text, not a working comparison formula.
Check whether Excel is treating the formula as text
If a cell displays the formula itself instead of TRUE or FALSE, Excel may be treating the entry as text. This commonly happens when the cell was formatted as Text before the formula was typed, or when an apostrophe was entered before the equals sign. A leading apostrophe is not part of the visible cell value, but it tells Excel to store the entry as text.
To correct it in Excel for Microsoft 365 on Windows, select the cell, choose Home > Number Format and select General, then edit the cell and press Enter. You can press F2 and then Enter on Windows to re-enter the existing formula. On a Mac, use the formula bar or edit the cell and confirm the entry; the exact keyboard shortcut for editing can differ by keyboard and Excel settings. Merely changing the number format may not convert a formula that Excel has already stored as text, so re-enter it after changing the format.
If formulas appear throughout the worksheet rather than their calculated results, check whether Show Formulas is on. On the ribbon, choose Formulas > Formula Auditing > Show Formulas to switch the view. On Windows, Ctrl+` (the grave accent key) toggles this view; the Mac shortcut may differ, so the ribbon command is the reliable option. This setting changes what is displayed, not the underlying formula results.
Look for numbers stored as text
Two cells can look identical while holding different data types. For example, A2 might contain the numeric value 125, while B2 contains the text string “125,” often because the value was imported from a text file or entered with a leading apostrophe. Number formatting can also make values look alike: a number may be displayed with no decimal places even though the stored value has decimals.
First, inspect the source cells and their number formats. A small green triangle may indicate a number stored as text, although it is not shown in every situation. If both entries are intended to be numbers and are convertible, VALUE can convert text representations before comparing them. With numeric-looking entries in A2 and B2, this formula returns TRUE for text “125” in A2 and the number 125 in B2.
With numeric values or numeric text in A2 and B2: =VALUE(A2)=VALUE(B2)
Do not use this conversion blindly on arbitrary text: VALUE returns an error when a string cannot be interpreted as a number under the workbook’s regional settings. Dates and decimal separators can also be interpreted differently depending on settings. If one entry is supposed to be text, preserve that meaning rather than converting it just to force a match. A comparison should reflect the data you actually intend to validate.
Remove extra spaces and imported characters
Text that looks the same can contain leading spaces, trailing spaces, repeated spaces, or characters that are hard to see. These are frequent causes of failed comparisons after copying data from websites, reports, or other systems. For ordinary spaces, TRIM removes spaces at the start and end and reduces repeated internal spaces to a single space. With text in A2 and B2, the formula below returns TRUE for “North” and “ North ”.
With text entries in A2 and B2: =TRIM(A2)=TRIM(B2)
TRIM does not remove every invisible character. A nonbreaking space, often brought in from web pages, is different from a standard space. SUBSTITUTE can replace the nonbreaking space represented by CHAR(160), after which TRIM can clean up ordinary spaces. With imported text in A2 and B2, use:
With imported text entries in A2 and B2: =TRIM(SUBSTITUTE(A2,CHAR(160)," "))=TRIM(SUBSTITUTE(B2,CHAR(160)," "))
This changes the comparison logic, not the source cells. If the cleaned values will be used repeatedly, consider cleaning the data in a helper column and checking the results before replacing or deleting original data. Avoid removing spaces automatically when they are meaningful, such as in an identifier or a string where spacing distinguishes valid values.
Decide whether capitalization should matter
Excel’s ordinary equality comparison for text is not case-sensitive. Thus, the words “Excel” and “EXCEL” normally compare as equal with the equals operator. That behavior is useful for many lists, but it may be wrong for case-sensitive codes, passwords, or identifiers.
For a case-sensitive comparison, use EXACT. With the two text entries in A2 and B2, the formula returns FALSE for “Excel” and “EXCEL,” but TRUE when capitalization and characters match exactly.
With text entries in A2 and B2: =EXACT(A2,B2)
EXACT does not remove spaces or normalize imported characters, so it will still distinguish entries with extra spaces. If your rule is “same letters regardless of case, but ignore accidental outside spaces,” combine the appropriate cleanup with the comparison. Choose the rule deliberately: making a comparison more forgiving can hide genuine differences in data that should be exact.
Account for decimal precision and rounding
Numbers displayed with the same number of decimal places are not necessarily equal underneath. For example, cells formatted to show two decimal places can display 3.14 even if one stores 3.141 and the other stores 3.144. The display format controls appearance; it does not round the stored values used in a comparison.
For calculations involving decimal fractions, Excel stores numbers using binary floating-point representation, which can produce tiny differences in results. If your business rule is to compare values rounded to two decimal places, round both sides in the comparison. With calculated values in A2 and B2, this returns TRUE when both round to 0.30.
With calculated values in A2 and B2 and a two-decimal comparison rule: =ROUND(A2,2)=ROUND(B2,2)
Use the number of decimal places that matches the actual requirement; two places is not automatically appropriate for every dataset. In some cases, a tolerance is more suitable than rounding. With values in A2 and B2 and an allowed difference of less than 0.01, this comparison returns TRUE when their absolute difference is below 0.01:
With numeric values in A2 and B2 and a tolerance of 0.01: =ABS(A2-B2)<0.01
That formula deliberately treats a range of nearby numbers as equivalent. Choose a tolerance that has a meaningful basis, and do not use it to conceal errors in measurements or calculations.
Check calculation settings and cell references
A correct formula can appear out of date if workbook calculation is set to Manual. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. The calculation options are also available in Excel for Mac, though the ribbon layout can differ slightly. With Automatic selected, Excel recalculates dependent formulas when relevant inputs change. If the setting is Manual, recalculate the workbook or switch back to Automatic when appropriate.
Also verify that the formula compares the intended cells. If you copy a formula down a column, relative references change with each row. For example, a formula in C2 comparing A2 and B2 will normally become a comparison of A3 and B3 when copied to C3. If one reference must stay fixed, use dollar signs to make it absolute; test the copied formula on a few rows to confirm the references still match your layout.
When a formula returns an unexpected result, click the result cell and inspect the formula bar. Confirm the formula has no accidental spaces or missing references, and check the values in the referenced cells directly. Formatting can make a date, number, or percentage appear different from its stored value, so compare the underlying data rather than relying only on what is visible.
A quick troubleshooting sequence
Work through these checks in order, changing one thing at a time. That makes it easier to identify the cause instead of masking it with a formula that is too permissive.
- Confirm the formula begins with
=and the references point to the right cells. - If the formula text is displayed, check for Text formatting or a leading apostrophe, then re-enter the formula.
- If many formulas are displayed, turn off Show Formulas from the Formulas tab.
- Check whether the entries are both numbers, both text, or a mix, and convert only when the data’s meaning supports it.
- Inspect text for extra spaces or imported characters; use TRIM or a targeted replacement only if those characters are unwanted.
- Decide whether capitalization matters, and use EXACT if the comparison must be case-sensitive.
- For calculated decimals, compare rounded values or use an explicitly chosen tolerance if that matches the requirement.
- Check the calculation mode and verify that the formula recalculates after inputs change.
As a practical example, suppose a product code in A2 appears to match a code in B2 but the result is FALSE. First check if either cell contains an extra space or nonbreaking space. If the codes differ only in capitalization, ordinary equality should still return TRUE; EXACT would return FALSE. If one code has a genuinely different character, cleanup should not make it match. The right fix depends on what counts as a valid match in the underlying data.
Frequently Asked Questions
Why does Excel show FALSE when two cells look identical?
They may contain different data types, hidden spaces, nonbreaking spaces, or different underlying numbers hidden by formatting. Inspect the values and use a cleanup or conversion formula only if it matches the comparison rule you need.
Is Excel’s equals comparison case-sensitive?
No. The standard equals comparison treats text that differs only by capitalization as equal. Use EXACT when uppercase and lowercase letters must match.
Why does Excel display my equal formula instead of TRUE or FALSE?
The cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be enabled for the sheet. Check the cell format and the Formulas tab, then re-enter the formula if it was stored as text.
Can I compare numbers after rounding them?
Yes. ROUND both values inside the comparison when your rule is based on a specific number of decimal places. Remember that rounding changes the comparison rule, so choose precision that is appropriate for the data.
Why does an equal formula not update when I change a value?
Check Formulas > Calculation Options and select Automatic if the workbook is set to Manual. Also confirm that the formula references the cell you changed; a comparison will not respond to edits in unrelated cells.
Related
- Excel Data Validation Simple List: Create and Manage It
- Excel Data Validation List: Can It Show Two Columns?
- Excel Data Validation List With a Blank Option
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Add M to Number Format: Display Millions or SuffixYes. In Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, choose Number > Custom, and…
- Excel Freeze Panes Not Working? Fix the SelectionIf Excel is not freezing the rows or columns you selected, click the cell immediately below the rows…
- Excel Merged Cells Error: Fix the Size Mismatch FastThe Excel message “To do this, all the merged cells need to be the same size” means the…
- Excel File Not Opening? Fix Corruption and Recover DataIf an Excel file will not open and Excel reports corruption, make a copy first, then try File…
- Excel Not Working on Your Laptop? Causes and FixesIf Excel is not working on your laptop, restart the laptop, open Excel by itself, and check whether…
- Find Tool Not Working in Excel? Fix Find and ReplaceIf Excel’s Find tool is not working, press Ctrl+F, open Options in the Find and Replace dialog, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.