If you are working with large amounts of data, it is not difficult to lose track: data may be missing, in the wrong place, or appear multiple times in the table. These repetitions are sometimes correct, but often they are also the result of an error. In any case, whether they are a mistake or not, it is very useful to be able to see duplicates automatically, since in complex tables it is impossible to mark them one by one manually, and for this you have to know how to show duplicates in Excel..
Typically duplicates in Excel are searched for for one of these reasons:
In another post we tell you how to eliminate duplicates in Excel tables.
Excel can find duplicates for you so you don't have to search cell by cell - the spreadsheet program automatically shows them to you. To do this, you first have to mark the area where you want to search . It can be a whole column or row, but also a specific manually defined area. These areas can be marked by drawing a rectangular box with the mouse. Single cells can also be selected by holding down the [CTRL] key and clicking each one..
Once the area to be analyzed has been selected, Excel can now be ordered to show the repeated values through the menu? Conditional Formatting? (Conditional Formatting), found on the ribbon within ?Style sheets?. In the drop-down menu, the item? Highlight cell rules? (Highlicht Cells Rules) contains the function? Duplicate values ?? (Duplicate Values) where you can decide how you want to mark the duplicates using colors . However, you can also ask the program to do just the opposite: mark unique values , that is, those that only appear once in the table.
Using the function? Duplicate values? You can mark all entries that appear more than once, that is, not only the values that appear twice, but also those that appear three, four or more times . It is also possible to show, for example, only the values that appear three times in the table: simply define a specific rule. The corresponding function is found in the menu? Conditional Formatting ?, where you can set a formula to be used to change the format. In case you want to find values repeated three times, the COUNTIF function is useful:
=CONTAR.SI($A$2:$A$10;A2)=3
You can freely choose the zone you want to register (first parameter of the formula). The first cell in the zone is named in the second parameter: if its content appears three times (= 3), it will be marked. Conditional formatting is automatically applied to all selected cells, which also compares the content of other cells (that is why absolute references have been given in the first parameter and relative references in the second)..
Now, in addition, you can go one step further and completely hide the rest of the cells . This can make working with duplicates much easier, which can then be easily marked using a filter. This filter can only be applied to entire columns, which must be previously selected. The columns can be activated from the menu? Data? (Data), where the corresponding button is located.
When you do this, a drop-down menu will appear in the top cell of the column from which you can set the filter criteria (that is why it is recommended to work with cell titles). To have Excel only show duplicates, it is filtered by color . Since Excel has color-marked all duplicates, these colors can be used for selection. This makes Excel show only duplicates and hide the rest.