Highlight identical values ​​in excel. How to Find Duplicates in Excel

Let's consider how to find duplicate values ​​inExcel, how to highlight identical meanings with words and signs, count the number of identical values, we'll find out formula for finding identical values ​​in Excel, etc.
In Excel, you can not only select and delete duplicate data, but also work with it - count duplicates before deleting, designate duplicates with words, numbers, signs, find repeating rows consisting of several cells, etc.

There are two rows left in the table with duplicates. In the top cell of the filtered column B, write the word “Yes”. Copy by column.

We've highlighted cells with the word "Yes" using conditional formatting. Instead of words, you can put numbers. It will turn out like this.

In column F you wrote the formula. =IF(COUNTIF(A$5:A5,A5)>1;"+";"-") It turned out like this.

In the line “Format formula to determine formatted cells,” write the following formula. =COUNTIF($A:$A;A5)>1 Set the format if you need to select a different cell color or font.

You need to compare and highlight data in three columns at once. We have such a table.

Deleting identical files (copies of the same file) or folders can free up free space disk space and reduces unnecessary trash in the system, which has a positive effect on speed systems. Sometimes duplicates are created by the user himself (for example, the same photos are copied to different folders, located inside the same physical disk), in some cases they remain after using different software. Search function unnecessary copies run many applications, most of which are distributed as free ( for free). Let us describe the operation of several such applications.

Removing duplicates using Total Commander

We use Dupeguru

This software is designed solely to scan the system for copies and does not perform other functions.

AllDup to detect duplicates

The design of the program is made in a beautiful and easy-to-use style. Immediately after launch, a window appears with a brief management for finding duplicates. You can search anywhere physical disk entirely by checking the required local drives.

Or add individual folders by clicking on the arrow next to the " Source folders" and selecting " Add folders».

Let's try to find duplicates in the Games folder in local disk"WITH:\"

Activate the tab " Search method", set up the criteria. By default, the option to search for duplicates only by similar names, if necessary, tick the required settings. It is advisable to enable search by extensions, otherwise the software may show files with similar names even if they are not copies of each other.

Afterwards, press the button start search.

Once completed, a new window will open with results, in which we tick unnecessary files , right-click on file, which is supposed to be cleared of duplicates (open context menu) and select " Delete all other files from this group permanently" to delete copies. In this case, the original itself will remain untouched, only duplicates will be deleted.

How to use DuplicateCleaner Free

A very thoughtful application for finding and removing duplicates on your computer. In this case, you can perform search all files in normal mode, search for copies of images or music, or identical folders.

Launch the application. First of all, we set up the search criteria, for this in the “ Additional options » mark the setting « With the same name" and go to the tab " Scan path»

Select a folder, press the button additions selected folder into included paths and click the button " Scan».

We wait for the end of the scan, which will display statistics detected duplicates in a separate window. We close the window.

In the " Duplicate Files» the list is displayed identical files, mark unnecessary ones and press the menu “ View", select the item " Deleting files»

In the window that opens, click “ Delete file(s)" In this case, it is advisable to include deletion in Cart so that you can restore the necessary files.

Very convenient function V CCleaner application is to find duplicate files. Very often, there are files on your computer that are identical in date, size and name. Of course, some of them are necessary, and some may have been created accidentally or downloaded several times from the Internet. All these files eventually accumulate, free space becomes less and less, and, as a result, the computer begins to slow down. Therefore, from time to time, you need to get rid of such files. If you are an advanced PC user, then you will not have any difficulty finding the files CCleaner duplicates which ones to delete, but if you are new to this matter, then we will help you figure it out.

What files should not be deleted

Before we start searching for duplicates and deleting them, let's look at whether it is possible to delete duplicate files using Cycleaner? To begin with, I would like to note that the program will not allow you to delete absolutely all copies of a file. One of them must remain untouched. Further, we do not recommend deleting system files. It is quite normal for them to have duplicates. Typically, system files are located on drive C in the Windows folder.

Files that can be deleted

Typically, a computer consists of several partitions (disks). The amount of information that is stored on each of them is most likely impressive. There are pictures, music, videos, photographs, and much more. Some of the duplicate files could have been duplicated by the user by mistake, for example, due to forgetfulness, the file was saved in different sections. Some files may have been downloaded from the Internet several times, etc. And when the program finds such files, you can safely delete them from your computer.

Find duplicates

In the " Service"there is a section" Search for duplicates».

In this section, at the user's discretion, you can set search criteria. You can search for duplicates by any one search parameter: by size, by date, by name and by content, or by several parameters at the same time, marking them with checkboxes.

You can also define the files that need to be skipped. There are several options here:

• Zero size files;
• Files that are read-only;
• Hidden files;
• System files;
• Files whose size does not exceed the megabyte size you specify;
• Files larger than the specified megabyte size.

In the " Inclusions» you can specify the places where the search will be carried out. To scan a specific folder, you must select " Add" The following window will appear

Click on the button " Review" and select the desired folder, indicating the path to it.

In the " Exceptions» you can specify those folders that should not be affected during the search.

You can add them similarly to the “ Inclusions" Click " Review" and select this folder.

After setting all the search parameters, click on the button “ Find».

Duplicate search results

After the search, the results will be shown in table form.

It will indicate the file names, locations where they are located, their sizes and creation dates.

To remove duplicates, check the boxes next to them. If you right-click on any file, a context menu will appear.

After you have selected all the duplicates that you want to delete, click on the “ Delete».

A common question is how to find and remove duplicates in Excel. Suppose you downloaded a monthly report from your accounting system, but in the end you need to understand which counterparties generally interacted with the company during this period - leave the list of counterparties without repetition. How to select unique values?

Is it possible to remove doubled, tripled, etc. values ​​in Excel across multiple columns?

It is possible, and very simple. For this there is special function. Pre-select the range where you want to remove duplicates. On the ribbon, go to Data - Remove duplicates (see the picture at the beginning of the article).

Select the first column

It is important to understand that if you select only the first column, then all data in the unselected columns will be deleted if it is not unique.

Very convenient!

2. How to select all duplicates in Excel?

Have you already heard about? Yes, this is where it will help! Select the column in which you want to mark duplicates, select Main in the menu - Conditional Formatting— Rules for highlighting cells — Repeating values…

In the Repeating values ​​window that opens, select which cells to select (unique or repeating), as well as the selection format, either from those suggested, or create a Custom format. The preset format will be red fill and red text.

Click OK if you do not want to change the formatting. Now all the data for the selected conditions will be colored.

I note that the tool is applied only to the selected one (!) column.

By the way, if you need to see unique ones, then in the window on the left select - unique.

3. Unique values ​​using pivot tables

To be honest, I once did not suspect the existence of the ability to “remove duplicates” and used pivot tables. How did I do it? Select the table in which you need to find unique values ​​- Insert -

If you are working with large quantities 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 range of data in the desired table. On the “Home” tab, click on the button "Conditional Formatting", select from menu “Rules for highlighting cells” – "Duplicate values".

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

IN Excel example I highlighted in pink all the same information. As you can see, the data is not compared row by row, but highlighted identical cells in columns. Therefore, the cell “Sasha V.” is highlighted. . There may be several such students, but with different last names.

How to count

If you need to find and count the number of repeating values ​​in Excel, we will create a summary for this Excel spreadsheet. Add the “Code” column to the original one 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, the value in the Code column will be incremented by one each time.

Select everything along with the headings, 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 following the link.

In the next window, the cells of the range are already indicated; use a marker to mark “To a new sheet” and click “OK”.

On the right side, drag the first three headings into the area "Line titles", 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 repeating values ​​in the source table - how many times this row was repeated in it.

For convenience, let's highlight all the values ​​in the column “Amount by field Code”, 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 rows of the table or only for selected columns.