UNIQUE function is not working in this case

Question

UNIQUE function is not working in this case

Ahmed142 20

Hello everyone,

I noticed that in Excel the Unique function is not working as expected, the explanation of this is illustrated in the following image, as I will insert a link to download the file by OneDrive at the end of this post:

User's image When I apply the UNIQUE function on the A1:A2 range it not works as expected it shows two times 13.62! while using UNIQUE function after using TRIM function is giving the wanted result, but the problem is that neither the cell A1 or A2 has any spaces, so what is the effect of TRIM function in this case?

so isn't this considered a problem that exist in UNIQUE function in Excel? or how to solve this problem without retyping the content of the cells A1 and A2 manulally or using TRIM function?

I use Windows and I use Microsoft Office Home and Business 2024

Here is the link to download the file:
https://1drv.ms/x/c/3b862aaec36e5e46/IQBjdaZuVt4pRLFh0g14TcxiAURKtlR4Y8rMISxT80JI4To?e=C9xqam

Viorel 127K Reputation points

2026-06-18T02:34:17.0033333+00:00

The first value, as well as C67, does not seem to be exactly equal to 13.62. It is rounded. How was this file created?

To deal with such values, try this formula: =UNIQUE(ROUND(A1:A2,2)).

2 answers

Your answer

Viorel 127K Reputation points

2026-06-18T02:34:17.0033333+00:00

The first value, as well as C67, does not seem to be exactly equal to 13.62. It is rounded. How was this file created?

To deal with such values, try this formula: =UNIQUE(ROUND(A1:A2,2)).

Answer 1

Ashish Mathur 102.2K Volunteer Moderator

Hi,

Use this formula in cell C2

=UNIQUE(REGEXREPLACE(A1:A2,"\s+",""))

Hope this helps.

User's image

0 comments

Answer 2

UNIQUE compares the underlying values in the array, not the formatted display. If the two cells in A1:A2 contain values that are numerically different (for example, one is a number and the other is text, or they differ beyond the displayed decimal places), UNIQUE will treat them as two distinct values and will therefore return both.

TRIM works only on text. When TRIM is applied to the cells, Excel converts the values to text strings and removes any leading/trailing spaces inside those strings. After TRIM, the resulting text values become identical, so UNIQUE sees only one distinct value and returns a single result.

This behavior is by design and not a bug in UNIQUE. To avoid retyping, normalize the data before calling UNIQUE, for example by:

Converting both cells to the same type and precision (e.g., using a helper column with a formula that rounds or converts to text), and then running UNIQUE on that normalized range.

References: