• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 46
  • Last Modified:

Method to determine if certain cells, rows, or columns within Excel have the same value or data

What method or utilities can I use to determine if certain cells, rows, or columns within Excel 2016 have the same value or data?

I have a rather large inventory database stored within an Excel spreadsheet and sometimes need to determine if certain values such as serial numbers are duplicated in different places when such data should be contained in only one place.
IT Guy
IT Guy
  • 2
1 Solution
Ejgil HedegaardCommented:
You could use conditional formatting on duplicates, format with a colour, and then use autofilter to find the coloured cells.

Another method is to use countif or countifs in another column.
Again apply autofilter and select counts above 1.
For very large datasets it could be slow, because of the many calculations.
IT GuyNetwork EngineerAuthor Commented:
Ejgil Hedegaard,

Can you please provide me with the exact steps to do what you have described? It doesn't matter if this is slow.
Ejgil HedegaardCommented:
Select the range you want to format.
On the Home tab, select Conditional Formatting.
New rule.
Select the rule for duplicates.
Select a format.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now