Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.
Microsoft Weekly Newsletter Be your company's Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets. Delivered Mondays and Wednesdays Sign up today. Editor's Picks. Linux kernel 5. Windows Do these 11 things immediately.
Top 5 things to do about your tech before you die. If you're not using a kanban board, you're not as productive as you could be. And then, you add up those numbers and check if the sum is greater than 1. As the result, all duplicated items excluding their 1 st occurrences are found and highlighted:. Another possible solution is to add an empty column to the left of your dataset, and combine the above formulas into a single formula like this:.
Where B2 is the top cell with data in the 2 nd column of the target range. Because the IF condition is always TRUE for all the columns other than the first one number of columns is greater than 1 , the formula proceeds in this way:.
If your table contains several columns, you may want to highlight whole rows based on duplicate records in a specific column. As you already know, Excel's built-in rule for duplicates works only at the cell level. But a custom formula-based rule has no problem with shading rows. The key point is to select the whole rows , and then create a rule with one of the following formulas:.
Where A2 is the first cell and A15 is the last used cell in the column that you want to check for duplicates. As you see, the clever use of absolute and relative cell references is what makes a difference. The following screenshot demonstrates both rules in action:. The previous example has demonstrated how to color entire rows based on duplicate values in a specific column. But what if you want to view rows that have identical values in several columns?
Or, how do you highlight absolute duplicate rows, the ones that have completely equal values in all columns? For example, to highlight duplicate rows that have identical values in columns A and B, use one of the following formulas:. The following screenshot demonstrates the result:. As you understand, the above example is for demonstration purposes only.
Sometimes, you may not need to highlight all duplicates in a column but rather show only consecutive duplicate cells, i. To do this, select the cells with data not including the column header and create a conditional formatting rule with one of the following formulas:.
Download - Highlight Duplicates in Excel - xlsx It is damn easy to highlight duplicate values in excel. But if you have any questions regarding this topic or any other topic, feel free to ask it in the comments section below. I would love to interact with excel users like you.
Highlight cells that contain specific text in Excel. Your email address will not be published. Ranking a List of Numbers Counting Number of Cells in a Excel Substring How to Print Formula Syntax in Excel Selecting Cells That Contain Formulas The steps to find and highlight the duplicates in excel by using conditional formatting are listed as follows:.
Alternatively, press the shortcut Shortcut An Excel shortcut is a technique for performing a manual task quickly, and these shortcuts help speed up the work, saving time for the job's secondary tasks.
The succeeding table consists of the invoice numbers and the corresponding amounts. On a selected range, we want to perform both the following tasks:.
The steps to highlight the current and the future duplicate values by using conditional formatting are listed as follows:. With this selection, it will be possible to highlight the new duplicate value entered in the existing list column A in future. Step 3: Select the color in which the duplicate cell values are to be highlighted. Step 4: Four duplicate values are highlighted in green. These are the ones that are occurring more than once.
Step 5: Enter any of the duplicate invoice numbers in row 22 of column A. The new duplicate entry of column A is highlighted automatically, as shown in the succeeding image.
Note 1: Alternatively, select the entire worksheet in step 1 and perform the steps 2 and 3. The results will be the same as those obtained in step 4.
Note 2: There is a difference between selecting a specific column and the worksheet. In the former, the duplicate value entered in a new cell of the same column will be highlighted. In contrast, if a worksheet is selected, the duplicate value entered in any cell of this worksheet will be highlighted. Step 4: The duplicate values are removed from the selected column column A. A message appears stating the number of duplicates deleted and the number of unique values retained.
0コメント