Duplicate Values in Excel – how can I show them?

Duplicate Values in Excel – how can I shows these highlighted in my spreadsheets?

Duplicate Values in Excel: Excel iconThis week’s hint and tip is about duplicate values in Excel and how you can highlight them in your worksheets. This is covered on our intermediate Excel course, but we also decided to do a hint and tip on it. We are going to go through it now below.


Highlighting Duplicate Values Using Conditional Formatting

This feature is quite useful for identifying duplicate cells either in a column or across columns as the associated video shows.

Uses for this feature include:

  • Identifying where there is no uniqueness in a column e.g., invoice numbers/payroll numbers/NI numbers
  • To identify errors in data entry


How to use it

The steps to do this are found below:

  1. Select the cells you wish to highlight for duplicates
  2. Home Tab, conditional formatting, highlight cell rules, duplicate values
  3. Choose the default colour say of red and click OK (or you can change this is you wish)

Once the data has been identified you can remove the duplicate by choosing Data, Remove Duplicates or by deleting the row(s).

These topics are catered for on our Intermediate Excel course which can be run onsite or virtually. For more information on our virtual course, please visit here and for our onsite course here.

For more information on any of our courses please visit our website or contact John Legge on johnlegge@jplcomputer.co.uk.


The video below shows you how to actually use these functions in a spreadsheet. We hope that you find the video useful and enjoy learning about it!

Take a look below at the video to find out more and then try it out on your own computer!

Click here download the example spreadsheet shown in the video to try it out yourself!


We hope you have enjoyed this hint and tip on duplicate values in Excel. Why not take a look at our previous one on the functions SUMIF and COUNTIF in Excel?

Please visit our YouTube Channel directed from our website or via this link for more videos.

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We use cookies to give you the best online experience.

Please let us know if you agree to all of these cookies.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy