Duplicate values in Excel: how to remove duplicates, or simply find and select

Good day!

With the popularization of computers over the past 10 years, the popularization of the creation of reports (documents) in Excel is taking place.

And in any relatively large document, there are duplicate lines, especially if you have collected it from several other tables. These duplicates can greatly interfere with further editing of the table, and therefore they must either be selected or deleted altogether ...

Actually, I have not once or twice been contacted with similar questions, and I decided to arrange the work with duplicates in a separate small article (which you are reading now). I will cite the most frequently encountered tasks and show their solution.

Note: all examples below will be presented in Office 2016/2019 (also valid for Office 2013, 2010, 2007). I recommend that you always use relatively new versions of Office: they are both faster and easier to work with.

Simple auto-deletion of duplicate lines

Let's imagine one of the most popular tasks: there is a list of strings with the names of people, there are many matches in the strings, you need to leave only unique values (by the way, this is just an example, all matches with real people are random).

The task is illustrated in the screenshot below (this is an example: it is clear that 2 lines can be deleted by hand, but in reality there can be 2000 or 20,000 lines, where the "hands" will be powerless ...!).

Example task in Excel

To remove duplicate rows in Excel, you must:

select your plate (rows and columns) with data;
Highlighting a table in Excel
then go to section "Data" and click on the tool "Remove duplicates" (see screenshot below);
Data - Remove Duplicates // Excel
after which a window with settings will appear: if you have headers in the table, I recommend checking the box " My data contains headers " ... After - click the OK button;
Deleting (highlighting titles)
as a result, you will see a message: how many lines were deleted, and how many unique ones remained. An example of a completed task is presented below.
Extra lines deleted!

Copying unique strings (data) to a new location

If you do not want to touch and change the current data (current table), then you can simply copy only unique rows (values) from it to a new table. This is very convenient when you want to visually compare what happened and what happened (and also worry about old data that may still be useful).

How it's done:

first you need to select the plate, then go to the section "Data" and press the button "Additionally" (subsection "Sorting and Filter", see the screenshot below);
Data Section - Advanced (Excel)
then switch the slider to the mode "Copy the result to another location" , in line "Place result in range" - select a cell where the beginning of the table will be; and put a tick in front of the item "Unique values only" ; click OK. An example is shown in the screenshot below;
Copy only unique values to another location
after which you will see how the data appeared in the place you selected, and the rows will remain only unique. Then you can work with them as usual ...
It was - it was

Finding and highlighting duplicate values

In cases where you do not need to delete duplicates or copy unique values to a new location, you can simply find and highlight them (moreover, you can select both unique values and duplicate ones). Usually, this should be done when further adjustments of strings are to be done (they can be found conveniently and quickly).

How to highlight duplicate lines:

first also select all your lines (example below);
Highlighting lines
then you need to open the section "The main" in the top menu of Excel, select the subsection "Conditional formatting", then "Cell selection rules", "Duplicate values" (an example is shown in the screenshot below);
Duplicate values // Home section in Excel
after that, you will have to choose which lines you will select (unique or repeated), and the color of the selection (by default - red-pink).
Choosing a color, how to select the lines