When working with a table or database with a large amount of information, it is possible that some rows are repeated. This increases the data array even more. In addition, if there are duplicates, incorrect calculation of results in formulas is possible. Let's figure out how in the program Microsoft Excel find and remove duplicate lines.

It is possible to find and delete table values ​​that are duplicated in different ways. In each of these options, finding and eliminating duplicates are links in the same process.

Method 1: simple removal of duplicate rows

The easiest way to remove duplicates is to use a special button on the ribbon designed for this purpose.

Method 2: remove duplicates in the smart table

Duplicates can be removed from a range of cells by creating a smart table.

This method is the most versatile and functional of all those described in this article.

Method 3: applying sort

This method is not entirely duplicate removal, since sorting only hides duplicate records in the table.

After that, duplicate entries will be hidden. But their display can be turned on at any time by pressing the button again. "Filter".

Method 4: conditional formatting

You can also find duplicate cells using conditional table formatting. True, they will have to be removed with another tool.

After that, cells with duplicate values ​​will be selected. You can then manually delete these cells in a standard way, if you wish.

Attention! The search for duplicates using conditional formatting is performed not for a row as a whole, but for each cell in particular, so it is not suitable for all cases.

Method 5: applying the formula

In addition, you can find duplicates by applying a formula using several functions at once. With its help, you can search for duplicates for a specific column. General form of this formula will look like this:

IFERROR (INDEX (column_address; SEARCH (0; COUNTIF (duplicate_column_header_address): duplicate_column_header_address (absolute); column_address;) + IF (COUNTIF (column_address);); 0 ;; COUNTIF (column_address); 0) ;; column_address); 0 ;;

After these steps in the column "Duplicates" duplicate values ​​are displayed.

But, this method is still too complicated for most users. In addition, it only involves searching for duplicates, but not deleting them. Therefore, it is recommended to use simpler and more functional solutions described earlier.

As you can see, Excel has many tools for finding and removing duplicates. Each of them has its own characteristics. For example, conditional formatting involves searching for duplicates only for each cell separately. In addition, not all tools can not only search, but also remove duplicate values. The most versatile option is to create a smart table. Using this method you can customize the search for duplicates as accurately and conveniently as possible. In addition, they are removed instantly.

Effective general cleaning of the computer's disk space, along with the use of programs for automatic cleaning windows, still requires manual work to remove unnecessary files and duplicate files. To track unnecessary files, there are special programs- disk space analyzers, they help to filter the contents of computer disks according to certain criteria (in particular, by weight), so that the user can make a decision about deleting files or leaving them. To find duplicate files there is also special software- either as separate programs or small utilities, or as part of an integrated software for cleaning and Windows optimization... Below we will consider five programs for finding duplicate files. The top five entered completely free programs with Russian language support.

When searching for duplicate files on system disk It is better to indicate not the entire section as a whole, but only separate folders where custom files are stored. Duplicates found in workers Windows folders, you cannot delete. If you find on the C drive individual weighty folders or files with unfamiliar names, it is recommended to get help about them on the Internet.

1. AllDup

AllDup is equipped with many different options for fine tuning search for duplicate files. Among the customizable options - the choice of the comparison method, search criteria, priority of checking duplicates, the use of exclusion filters, the inclusion of the contents of archive files, etc. It is even possible to change the themes and individual interface settings. The program is good, however, with a somewhat ill-conceived interface. On its toolbar, all tabs - at least basic operations, at least additional functions- are listed as equivalent. And to help the user who launched AllDup for the first time to master the specifics of the program, its creators equipped the interface with a floating widget in the form quick guide, which step for which to take. The "Source folders" tab specifies the search area - disk partitions, connected devices or individual user folders on the system drive C.

Next, the search criteria are set in the "Search method" tab. Here, the search for duplicates can be specified and the extension, size, content, etc. can be added to the preset search criteria by file names.

Duplicate search results can be sorted by size, path, file modification date, etc. The detected files can be deleted by opening their location in Windows Explorer, apply other actions provided by the program.

To return to the analysis of the results of the search for duplicates performed by AllDup, later, but not wait for the completion of the scan, the current results can be saved to a program format file or exported to TXT files and CSV.

AllDup has portable version that does not require installation into the system.

2. Duplicate Cleaner

Another one functional program for a flexible search for duplicate files - Duplicate Cleaner. Duplicate Cleaner comes in two editions - paid Pro and free Free... Although the latter is limited by the inaccessibility of some functions, its capabilities will be sufficient for efficient duplicate search. Duplicate Cleaner Free allows you to set search criteria by name, content, size, date of creation of files. Provides additional search criteria for audio file data, as well as filtering by content type and file extension. All these points are configured in the first tab of the program "Search criteria".

In the second tab of the program - "Scan path" - the search area is selected.

In the search results window, duplicates can be sorted, deleted, opened in Explorer, and other program options can be applied to them.

Duplicate Cleaner search results can be exported to CSV tabular data format file. Data export is carried out both for the entire list of duplicates and for only files marked by the user. Benefits of Duplicate Cleaner - user-friendly interface and thoughtful organization.

3. DupeGuru

DupeGuru is the simplest search engine for duplicate files for those who do not have the time or desire to master all the subtleties of functional highly specialized programs such as those discussed above. At the bottom of the small window of the program, the search area is selected and scanning starts.

Duplicate search results can be sorted by location path and file size. Context menu search results DupeGuru contains only operations necessary for working with found duplicates.

Search results are saved to a program file or exported to HTML.

DupeGuru is a cross-platform program, but only older versions are adapted for Windows. The program installer for Windows 7 offered on the official website is also suitable for system versions 8.1 and 10.

4. CCleaner

At one of the stages of its improvement, the most popular Windows cleaner CCleaner received the function of finding duplicate files. You can use this function in the "Service" section. To search for duplicates, search criteria are available by name, creation date, file content, and their size.

Like the previous reviewer, CCleaner program the duplicate search results environment is not particularly rich in functionality, but basic operations are present. This, in particular, sorting search results and deleting files.

5. Glary Utilities 5

Comprehensive program for cleaning and optimizing Windows Glary utilities 5 in the arsenal contains a utility for finding duplicate files. The same utility, if desired, can be downloaded separately from the official website of the program, if the full power of the software package is not required.

The duplicate finder in Glary Utilities 5 is simple, but convenient and customizable. Scanning for duplicates can be started immediately by selecting only the search area - disk partitions, removable devices or individual folders. Or you can narrow down your search by clicking the "Options" button.

The options are used to configure the search criteria for duplicates - by name, by size, by the time the files were created. In some cases of thorough cleaning of disk space, you can change the preset search selection only among common file types to scan all types.

The search results provide only the necessary operations for working with duplicates, in particular, deleting and opening the location path in Windows Explorer.

For the convenience of the user, Glary Utilities 5 catalogs the found duplicates by content type - documents, pictures, videos, programs, etc. The total weight of the found files is displayed for each type.

Finding duplicates in Excel is one of the most common tasks for any office worker. To solve it, there are several different ways... But how quickly how to find duplicates in Excel and highlight them with color? To answer this frequently asked question, let's look at a specific example.

How to find duplicate values ​​in Excel?

Suppose we are engaged in the registration of orders received by the company via fax and e-mail. A situation may arise that the same order was received by two channels of incoming information. If you register the same order twice, certain problems may arise for the company. Below we will consider a solution using conditional formatting.

To avoid duplicate orders, you can use conditional formatting to help you quickly find duplicate values ​​in an Excel column.

Example of a daily order journal for goods:

To check if the order log contains possible duplicates, we will analyze by customer names - column B:

As you can see in the figure with conditional formatting, we were able to easily and quickly implement a duplicate search in Excel and detect duplicate cell data for the order history table.

Example of the COUNTIF function and highlighting duplicate values

The principle of the formula for finding duplicates with conditional formatting is simple. The formula contains the function = COUNTIF (). This function can also be used when searching for duplicate values ​​in a range of cells. In the function, the first argument is the data range to be scanned. In the second argument, we indicate what we are looking for. Our first argument has absolute references, since it must be immutable. And the second argument, on the contrary, must change to the address of each cell of the viewed range, therefore it has a relative reference.

The fastest and simple ways: find duplicates in cells.

The function is followed by an operator comparing the number of found values ​​in the range with the number 1. That is, if there is more than one value, then the formula returns the value TRUE and conditional formatting is applied to the current cell.

If you work with large numbers information in Excel and regularly add it, for example, data about school students or company employees, then duplicate values ​​may appear in such tables, in other words - duplicates.

In this article, we will look at how to find, select, delete and count the number of duplicate values ​​in Excel.

How to find and highlight

You can find and highlight duplicates in a document using conditional formatting in Excel. Select the entire data range in the desired table. On the "Home" tab, click on the button "Conditional Formatting", select from the menu "Cell selection rules" – Duplicate Values.

In the next window, select from the drop-down list "Recurring", and the color for the cell and text in which to paint over the found duplicates. Then click "OK" and the program will search for duplicates.

IN Excel example highlighted in pink all the same information. As you can see, the data is not compared line by line, but the same cells in the columns are highlighted. Therefore, the cell "Sasha V." ... There may be several such students, but with different surnames.

How to calculate

If you need to find and count the number of duplicate values ​​in Excel, we will create an Excel Pivot Table for this. Add “Code” to the original column and fill it with “1”: put 1, 1 in the first two cells, select them and drag them down. When duplicates are found for rows, each time the value in the "Code" column will increase by one.

Select everything together with the headers, go to the "Insert" tab and press the button "Pivot Table".

To learn more about how to work with pivot tables in Excel, read the article by clicking on the link.

In the next window, the cells of the range are already indicated, with a marker we mark "On a new sheet" and click "OK".

On the right side, drag the first three headings to the area "Line names", and drag the "Code" field to the "Values" area.

As a result, we will get a pivot table without duplicates, and in the “Code” field there will be numbers corresponding to the repeated values ​​in the original table - how many times this row has been repeated in it.

For convenience, let's select all the values ​​in the column "Amount for the Code field", and sort them in descending order.

I think now you can find, select, delete and even count the number of duplicates in Excel for all table rows or only for selected columns.

Microsoft Excel is quite rich in functions for analyzing data ranges, earlier we considered how it can, how it can be used for two data ranges, as well as visualization of statistical information with the addition of a function.

Today we'll talk about how to find duplicate values ​​in Excel tables. The method presented in the article will be based on the application of conditional formatting. In fact, there will be two ways - one general, which will help you better understand the basic principles of conditional formatting, and the second is simple.

See also the video version of the article.

The first part of the method.

Let's look at an example of finding duplicate values.

To find duplicate values, you must perform the following step-by-step algorithm of actions:

1. Select original range (A1: E8)
2. Run command: Home tab / Styles group / Conditional Formatting / Create Rule

1. In the dialog box, select: "Use a formula to determine the formatted cells", while the dialog box will slightly change its appearance, then enter the following formula: = COUNTIF ($ A $ 1: $ E $ 8; A1)> 1

   after entering the formula, you must select the format that will be applied to the cells that meet the condition (in the example, the orange fill is selected).

1. After pressing the "OK" button, you can immediately observe the result of the operation.

The entered formula compares the value of each individual cell with cells from the range and, if the cell is not unique, then formatting is applied to it, in our case, the cell is filled with orange color.

The second part of the method.

Sometimes it becomes necessary to search not duplicate cells, but whole rows.

1. The main idea of ​​finding non-unique, or, conversely, unique strings, is to make one of all the strings in the range by concatenation (joining), and then look for non-unique values ​​in the new range. By the way, you can also concatenate strings in more than one way, for example, the concatenation sign "&" is perfect, as well as a function.

1. The next step will be to search for non-unique rows among the new column, highlighting the cells of which will show duplicate rows in the original table. The search, as in the first part of the method, could be performed with the construction of a formula, but it can be made simpler.

In the window for constructing rules in MS Excel, the developers have already provided for the most frequent scenarios for using this tool, so you can not enter a formula, but select the item " Format only unique or duplicate values»

1. After clicking "OK", the result will not be long in coming.

Finally, it should be mentioned that conditional formatting works dynamically, i.e. if certain values ​​in non-unique strings are changed in such a way that the strings become unique, then the formatting will automatically change. The converse is also true.