Why Excel Says Two Values Are Different When They Look the Same
The Problem: Same Appearance, Different Value
If you have worked with Microsoft Excel for any length of time, you have probably encountered one of those frustrating moments where two cells look exactly the same, yet Excel insists that they are different.
You compare them visually.
They appear identical.
You copy one value and paste it next to the other.
Still, Excel says they don’t match.
This can become a serious problem when working with data analysis, reporting, lookups, PivotTables, dashboards, customer records, financial data, or database exports.
The good news is that Excel is usually not “wrong.”
The problem is that what you see is not always what Excel is actually storing inside the cell.
In this article, we will explore why this happens, how to identify the hidden differences, and practical ways to clean your data so Excel can correctly recognise matching values.
1. Floating-Point Precision Errors (Most Common with Numbers)
Excel stores numbers using the IEEE 754 double-precision floating-point format. Many decimal fractions cannot be represented exactly in binary (for example, 0.1 or results of division). Tiny rounding differences accumulate and become invisible once Excel rounds the display to a certain number of decimal places.
Example Cell A1 contains the formula =1/3 (displayed as 0.333333). Cell B1 contains the value 0.333333 typed or pasted. =A1=B1 may return FALSE even though both cells show the same digits.
The actual stored values differ by a tiny amount such as 0.0000000000000001. Excel’s default number format simply hides this difference.
How to detect
- Increase decimal places (Home → Number → Increase Decimal) until extra digits appear.
- Use =A1-B1 — a non-zero result confirms a precision difference.
- Apply the formula =A1=B1 or =EXACT(A1,B1).
Fixes
- Round both values explicitly: =ROUND(A1,6)=ROUND(B1,6).
- Use =ABS(A1-B1)<0.000001 for a tolerance-based comparison.
- Convert to text with a fixed number of decimals if exact display matching is required.
2. Hidden Characters or Non-Printing Characters
Copied data from web pages, PDFs, other applications, or external systems often contains invisible characters: leading/trailing spaces, non-breaking spaces (CHAR(160)), tabs, line breaks, or zero-width characters. These do not change the visible appearance but make the cells unequal.
Classic symptoms
- =A1=B1 returns FALSE.
- =LEN(A1) and =LEN(B1) return different numbers.
- TRIM does not fully clean the cell because TRIM only removes regular spaces (CHAR(32)), not non-breaking spaces.
How to detect
- =LEN(A1) versus =LEN(B1).
- =CODE(MID(A1,n,1)) to inspect individual characters.
- Use the formula =CLEAN(TRIM(A1)) and compare lengths before and after.
Fixes
- =TRIM(CLEAN(A1)) (or apply to both cells).
- For stubborn non-breaking spaces: =SUBSTITUTE(A1,CHAR(160),””).
- Paste Special → Values after cleaning, or use Power Query’s “Trim” and “Clean” transformations for bulk data.
3. Numbers Stored as Text vs. True Numbers
One cell contains a real number; the other contains the same digits stored as text. This commonly happens after importing CSV files, copying from web pages, or when cells are pre-formatted as Text.
Symptoms
- Numbers aligned left (text) versus right (numbers).
- A small green triangle in the corner of the cell.
- Formulas such as =A1+B1 may produce #VALUE! or unexpected results.
- =A1=B1 returns FALSE; =A1+0=B1 or =VALUE(A1)=B1 may succeed.
How to detect
- =ISTEXT(A1) and =ISNUMBER(B1).
- Look at alignment and the error indicator.
Fixes
- Multiply by 1 or add 0: =A1*1.
- Use =VALUE(A1).
- Text to Columns (Data tab) with “General” format.
- Paste Special → Multiply by 1 (enter 1 in a cell, copy it, select the text numbers, Paste Special → Multiply).
4. Formatting Differences That Do Not Affect the Value
Two cells can hold identical underlying values yet look different because of number formats, date formats, or custom formats. Equality checks still work on the stored value, but visual comparison misleads users.
Get Adeyemi Adenuga’s stories in your inbox
Join Medium for free to get updates from this writer.
Examples
- 0.5 formatted as 50% versus 0.50.
- A date serial number shown as “17-Aug-2026” versus “8/17/2026”.
- Currency symbols, thousands separators, or scientific notation.
The values themselves are equal; only the display differs. Use =A1=B1 (it should return TRUE) or change both cells to the General format to confirm.
5. Other Less Common Causes
- Trailing or leading zeros that are significant only in text (e.g., product codes “00123” vs “123”).
- Case sensitivity — Excel’s = operator is case-insensitive for text, but EXACT is case-sensitive.
- Array or spilled formulas that produce slightly different intermediate results.
- Regional settings (decimal comma vs. decimal point) when data is shared across locales.
- Very large numbers that exceed Excel’s precision (approximately 15 significant digits).
Practical Diagnostic Checklist
- Select both cells and increase the number of displayed decimals.
- Check =LEN(A1) and =LEN(B1).
- Test =ISTEXT(A1) / =ISNUMBER(A1).
- Compute =A1-B1 (for numbers).
- Apply =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),””))) and re-compare.
- Change both cells to General format and re-evaluate.
Prevention Tips
- When importing data, use Power Query and explicitly set data types.
- Avoid copying formatted values from external sources; prefer Paste Special → Values.
- For critical comparisons, always round or apply tolerance rather than relying on exact equality of floating-point results.
- Document any intentional use of text-formatted numbers (IDs, codes, etc.).
The Key Lesson
Before Analysing your Excel data, check and clean it first.
Remember:
If Excel says two values are different, don’t just look at them, check what’s actually inside the cells.
Sometimes the difference is invisible to you, but very real to Excel.
Need Help Fixing Your Excel Data?
Struggling with Excel errors, mismatched values, messy datasets, or formulas that just won’t work?
I can help you clean, troubleshoot, and analyse your Excel data so you can work with confidence.
