**Question description:**

*This user has given permission to use the problem statement for this blog.*

I have 2 cells containing a % value. The value for both is 9%.
I want to conditionally format these cells as duplicate values but excel only highlights them as duplicate values if the decimal point is exactly the same. How can i correct this?

Solved by S. C. in 40 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
24/05/2018 - 11:54

Hello

User
24/05/2018 - 11:54

hi

Excelchat Expert
24/05/2018 - 11:54

Welcome to excelchat.

Excelchat Expert
24/05/2018 - 11:55

I want to understand the problem clearly.

User
24/05/2018 - 11:55

ok

Excelchat Expert
24/05/2018 - 11:55

Can you please show me the exact values in those cells?

User
24/05/2018 - 11:56

the values in the cells are 8.9% and 9.1%

Excelchat Expert
24/05/2018 - 11:57

And you want those to treat as duplicate even though there are not exactly the same?

User
24/05/2018 - 11:57

yes

Excelchat Expert
24/05/2018 - 11:58

This seems a bit confusing, please tell me more about what you are trying to do and what you have.

User
24/05/2018 - 11:59

I have 2 columns of values in percentages

Excelchat Expert
24/05/2018 - 11:59

It would be better if you can show me the file too.

User
24/05/2018 - 11:59

now and again the same value appears in both columns - for example : 9%

User
24/05/2018 - 12:00

I want excel to highlight these values as duplicates but excel reads to the decimal place and won't highlight them as duplicates because the decimal point is different - eg: 8.9% and 9.1%

Excelchat Expert
24/05/2018 - 12:01

I understand.

User
24/05/2018 - 12:01

goog

User
24/05/2018 - 12:01

good

Excelchat Expert
24/05/2018 - 12:01

And I need to see all values to suggest a solution.

Excelchat Expert
24/05/2018 - 12:02

Can you please send me the file?

User
24/05/2018 - 12:02

can I type out a typical example for you?

Excelchat Expert
24/05/2018 - 12:03

I would need more than a few examples.

User
24/05/2018 - 12:04

I thought there would be quite an easy solution such as telling excel to ignore the decimal points?

Excelchat Expert
24/05/2018 - 12:05

Okay, in the given example, you said the numbers are 8.9 and 9.1 So ignoring decimals would give you 8 and 9 respectively.

Excelchat Expert
24/05/2018 - 12:06

INT function tells excel to ignore decimals. But if we use that in this case, you will get 8 and 9 which are not the same.

User
24/05/2018 - 12:06

Dosen't excel round up to the nearest whole number 8.9 = 9 and 9.1 = 9

Excelchat Expert
24/05/2018 - 12:06

And therefore won't be identified as duplicates.

User
24/05/2018 - 12:06

OK

User
24/05/2018 - 12:07

so is there no other way?

Excelchat Expert
24/05/2018 - 12:07

The automatic rounding in excel only shows you the rounded values but the exact values remain same.

Excelchat Expert
24/05/2018 - 12:07

Yeah there is, that is why I need to see some example.

Excelchat Expert
24/05/2018 - 12:08

I'm again saying it would be better if I can see the file itself.

Excelchat Expert
24/05/2018 - 12:08

making sense?

Excelchat Expert
24/05/2018 - 12:08

If you are sensitive about your data, I do understand that.

User
24/05/2018 - 12:09

yes but you could easily create my scenario without me sending you the file - its confidential

Excelchat Expert
24/05/2018 - 12:10

I understand your feeling and that you data is confidential. Although I can work without the original data, it would require more time and in some cases the solution wouldn't be ideal or efficient.

User
24/05/2018 - 12:12

Its probably more complicated than i thought it would be so I'm not sure it is worth finding a solution - I thought I may be missing something obvious

Excelchat Expert
24/05/2018 - 12:12

Please first try this formula in your cell

Excelchat Expert
24/05/2018 - 12:12

=ROUND(A1,0)]

Excelchat Expert
24/05/2018 - 12:13

=ROUND(A1,0) this will round numbers to nearest whole number.

Excelchat Expert
24/05/2018 - 12:13

And eliminate decimals.

User
24/05/2018 - 12:13

that sounds about right

Excelchat Expert
24/05/2018 - 12:13

Of course you have to change the A1 in the formula accordingly your file.

Excelchat Expert
24/05/2018 - 12:14

And after that you can try the conditional formatting.

Excelchat Expert
24/05/2018 - 12:14

Let me know whether that works or not.

User
24/05/2018 - 12:14

what do I put in, instead of A1?

Excelchat Expert
24/05/2018 - 12:15

The cell reference of the value you want to round to nearest whole number.

User
24/05/2018 - 12:15

I see

Excelchat Expert
24/05/2018 - 12:15

Okay. Let me know if you have anymore questions.

User
24/05/2018 - 12:16

that formula you have quoted is similar to other suggestions I have seen when trying to find a solution to this

User
24/05/2018 - 12:16

Thanks for your help

Excelchat Expert
24/05/2018 - 12:16

This resolves your issue?

User
24/05/2018 - 12:17

Its the best one I've come across - will try it later

Excelchat Expert
24/05/2018 - 12:17

Sounds good.

User
24/05/2018 - 12:17

does this service cost anything?

Excelchat Expert
24/05/2018 - 12:17

if you have any confusion, you can ask me.

Excelchat Expert
24/05/2018 - 12:18

Yeah, we do charge by session. The updated pricing is listed on the website.

User
24/05/2018 - 12:18

my first time here - is this one free?

Excelchat Expert
24/05/2018 - 12:19

I thing so, but if it were to cost you money then you already would have asked to enter your payment details.

User
24/05/2018 - 12:20

well thanks again

Excelchat Expert
24/05/2018 - 12:20

As an expert, my task is to solve user problem. We experts don't always have the latest pricing and others things.

Excelchat Expert
24/05/2018 - 12:21

Thank you for trying out got it pro excel. Have a great day ahead!

User
24/05/2018 - 12:21

you yoo.

User
24/05/2018 - 12:21

you too

Excelchat Expert
24/05/2018 - 12:24

The session will end in 10 minutes unless you opt to end it before. Thanks again for visiting us today.

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*