Excel - Mark Duplicates Over 5 - Episode 1777

794 views · Published 20 August 2013 · 6:31 · Indexed 10 November 2026

Channel: MrExcel.com · 2013 · Education

Watch on YouTube

Microsoft Excel Tutorial: Marking Duplicates in Excel | Conditional Formatting.

Welcome to the MrExcel podcast. In this episode, we will be discussing how to mark duplicates in Excel. Specifically, we will learn how to mark duplicates over 5 in red and duplicates under 5 in blue. This is a common task that many Excel users encounter, and we will show you how to do it without the need for a macro.

The first step is to select the range of cells where you want to mark the duplicates. Then, we will use conditional formatting to apply two different rules. The first rule will check if the value in the current cell is greater than 5, and if it is, it will be marked in red. The second rule will check if the value is less than or equal to 5, and if it is, it will be marked in blue. These rules will help us easily identify the duplicates in our data.

The tricky part is writing the formula for these rules. We need to make sure we use the correct combination of $ signs so that the formula can be copied throughout the entire range. We will also need to adjust the formula if our data starts in a different row. By following the steps carefully, we can ensure that the correct cells are marked as duplicates.

There is also an option to only mark the second, third, or fourth duplicate in each row. This can be useful if you are checking for duplicates in a specific column, such as an invoice number. By making a small change to the formula, we can achieve this result. Just remember to pay attention to where the $ signs are placed in the formula.

Thank you for tuning in to this episode of the MrExcel podcast. We hope you found this tutorial helpful in learning how to mark duplicates in Excel. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time for another netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Marking duplicates in Excel
(00:31) Using conditional formatting rules
(02:11) Setting up the formula
(03:15) Marking both or only one duplicate
(04:09) Editing the rules
(05:11) Subtle change for marking only one duplicate
(06:20) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Applying checks to an invoice
Blue font and fill options
Conditional formatting rules
Duplicates in color
Formula for marking duplicates
Mark duplicates 5 in red, 5 in blue
Marking only one duplicate
Red font and fill options
Starting row for data
Top-left corner cell


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153598/


A question from YouTube on how to write a macro to mark duplicates in each row where duplicates over the value of 5 are marked in red and under 5 are marked in blue. This can be done without a macro, using two formula-based conditional formatting rules. However - the formula will change depending on if you want to mark both halves of the duplicate or just the 2nd occurrence of the duplicate.

More from this channel