why Excel shows duplicate values when using Conditional formatting
New Microsoft Excel Worksheet (2).xlsx |
---|
|
---|
| B |
---|
3 | |
---|
|
---|
Conditional formatting>Highlight cells rules>Duplicate values shows duplicate even if there is no identical values
Use formula
Also paste as values the whole column. It has space in between so it is showing as duplicate
New Microsoft Excel Worksheet (2).xlsx |
---|
|
---|
| A |
---|
1 | 537053000012963 |
---|
2 | 0464053000010099 |
---|
3 | 822053000003254 |
---|
4 | 537053000012984 |
---|
5 | 5555053000093726 |
---|
6 | 5555053000093749 |
---|
7 | 5555053000093739 |
---|
8 | 0537053000013098 |
---|
9 | 0537053000013101 |
---|
10 | 0537053000013099 |
---|
11 | 5555053000093743 |
---|
12 | 537053000013042 |
---|
13 | 5555053000093705 |
---|
14 | 537053000012992 |
---|
15 | 5555053000093711 |
---|
16 | 537053000013041 |
---|
17 | 5555053000093728 |
---|
18 | 0537053000013210 |
---|
19 | 5555053000093733 |
---|
20 | 537053000012981 |
---|
21 | 537053000013125 |
---|
22 | 0537053000013156 |
---|
23 | 537053000013074 |
---|
24 | 0537053000013160 |
---|
25 | 537053000013112 |
---|
26 | 0537053000013159 |
---|
27 | 537053000013177 |
---|
28 | 5555053000093704 |
---|
29 | 0537053000013178 |
---|
30 | 5555053000093744 |
---|
31 | 5555053000093735 |
---|
32 | 0537053000013108 |
---|
33 | 537053000013136 |
---|
34 | 537053000013157 |
---|
35 | 0537053000013172 |
---|
36 | 0537053000013215 |
---|
37 | 0537053000013185 |
---|
38 | 0537053000013213 |
---|
39 | 0537053000013216 |
---|
40 | 537053000013168 |
---|
41 | 537053000013053 |
---|
42 | 5555053000093719 |
---|
43 | 0537053000013211 |
---|
|
---|
New Microsoft Excel Worksheet (2).xlsx |
---|
|
---|
| A |
---|
1 | 537053000012963 |
---|
2 | 0464053000010099 |
---|
3 | 822053000003254 |
---|
4 | 537053000012984 |
---|
5 | 5555053000093726 |
---|
6 | 5555053000093749 |
---|
7 | 5555053000093739 |
---|
8 | 0537053000013098 |
---|
9 | 0537053000013101 |
---|
10 | 0537053000013099 |
---|
11 | 5555053000093743 |
---|
12 | 537053000013042 |
---|
13 | 5555053000093705 |
---|
14 | 537053000012992 |
---|
15 | 5555053000093711 |
---|
16 | 537053000013041 |
---|
17 | 5555053000093728 |
---|
18 | 0537053000013210 |
---|
19 | 5555053000093733 |
---|
20 | 537053000012981 |
---|
21 | 537053000013125 |
---|
22 | 0537053000013156 |
---|
23 | 537053000013074 |
---|
24 | 0537053000013160 |
---|
25 | 537053000013112 |
---|
26 | 0537053000013159 |
---|
27 | 537053000013177 |
---|
28 | 5555053000093704 |
---|
29 | 0537053000013178 |
---|
30 | 5555053000093744 |
---|
31 | 5555053000093735 |
---|
32 | 0537053000013108 |
---|
33 | 537053000013136 |
---|
34 | 537053000013157 |
---|
35 | 0537053000013172 |
---|
36 | 0537053000013215 |
---|
37 | 0537053000013185 |
---|
38 | 0537053000013213 |
---|
39 | 0537053000013216 |
---|
40 | 537053000013168 |
---|
41 | 537053000013053 |
---|
42 | 5555053000093719 |
---|
43 | 0537053000013211 |
---|
|
---|
Excel highlight that as duplicates, because within the default conditional formatting rule Excel converts text that looks like numbers as numbers.
You can avoid that if you add any non numerical char to the number (at front or end).
C1: ="#"&A1
Now Excel "sees" the text as text, does no conversation and compares the strings.
Similar solution in
Microsoft Forum
- Threads
- 1,226,849
- Messages
- 6,193,321
- Members
- 453,790
- Latest member
- yassinosnoo1