Excel - Mark Duplicates Over 5 - Episode 1777
794 views · Published 20 August 2013 · 6:31 · Indexed 10 November 2026
Channel: MrExcel.com · 2013 · Education
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920