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.
Let's look at how to highlight and label duplicates. We have a table in which we will consider options for working with duplicates.Duplicate data was colored with conditional formatting.
There are two options for selecting cells with the same data. The first option is when all cells with the same data are selected. For example, as in the table (cells A5 and A8). The second option is to select the second and subsequent cells with the same data. And do not select the first cell (select only cell A8). We will consider both options.
First way.
How to highlight duplicate values inExcel.
We need to write the word “Yes” in the adjacent column opposite these cells; if there is a duplicate, write the word “No” for cells with unique data.
In column A we set a filter. How to install and work with the filter, see the article “Filter in Excel”. In cell B2 we write the word “No”.
![](https://i2.wp.com/excel-office.ru/images/stories/dublie2.jpg)
Now let’s filter the data in column A – “Filter by cell color”. It can be by font color, depending on how duplicates are highlighted in the table.
![](https://i2.wp.com/excel-office.ru/images/stories/dublie3.jpg)
![](https://i2.wp.com/excel-office.ru/images/stories/dublie4.jpg)
![](https://i0.wp.com/excel-office.ru/images/stories/dublie5.jpg)
![](https://i1.wp.com/excel-office.ru/images/stories/dublie6.jpg)
Second way.
How to highlight duplicate cells inExcel.
Let's set up a formula in column D so that words are written automatically. The formula is like this. =IF(COUNTIF(A$5:A5,A5)>1,"Yes","No")
Copy the formula by column. It will turn out like this.
![](https://i2.wp.com/excel-office.ru/images/stories/dublie7.jpg)
You can write any words in this formula, numbers, or signs. For example, in column E we wrote the following formula. =IF(COUNTIF(A$5:A5,A5)>1,"Repeatedly","First time")
In column F you wrote the formula. =IF(COUNTIF(A$5:A5,A5)>1;"+";"-") It turned out like this.
![](https://i2.wp.com/excel-office.ru/images/stories/dublie8.jpg)
You can use the formula from column E or F in the table so that when filling out the adjacent column you can immediately see whether there are duplicates in the column or not. For example, let's create a list of last names in column A. In column B we have set a formula.
=IF(COUNTIF(A$5:A5,A5)>1;"+";"-") If there is “+” in column B, then such a surname has already been written.
Third way.
Count the number of identical valuesExcel.
We need to not only highlight repetitions, but also count them, write their number in a cell.
In cell G5 we write the following formula. =IF(COUNTIF(A$5:A$10,A5)>1,COUNTIF(A$5:A5,A5),1) Copy by column.The result is a repeat counter.
![](https://i2.wp.com/excel-office.ru/images/stories/dublie9.jpg)
![](https://i2.wp.com/excel-office.ru/images/stories/dublie9a.jpg)
How to calculate data in cells with duplicates, and then delete them, see the article “How to add and delete cells with duplicates in Excel”.
Fourth way.
Formula for finding identical values inExcel.
We need to highlight duplicates using a formula in conditional formatting. Select the cells. Call up the conditional formatting dialog box. Select the option “Use a formula to determine which cells to format.”
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.
![](https://i2.wp.com/excel-office.ru/images/stories/dublie10.jpg)
Idea.
You can set it in conditional formatting white fill and font. It will turn out like this.
The first cells remained visible, but subsequent repetitions were not visible. When the data in the first column changes, the empty cells also change, depending on where the duplicates are located.
Fifth way.
How to find duplicate lines inExcel.
You need to compare and highlight data in three columns at once. We have such a table.
![](https://i2.wp.com/excel-office.ru/images/stories/dublie11.jpg)
Read about the “CONNECT” function
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.
![](https://i0.wp.com/composs.ru/wp-content/uploads/2017/04/poisk_dublikatov5.jpg)
AllDup to detect duplicates
![](https://i2.wp.com/composs.ru/wp-content/uploads/2017/04/poisk_dublikatov7.jpg)
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.
Select all | Possibility to mark all found duplicates. All files will have only one copy - the bottom one. |
Deselect | The ability to uncheck all found duplicates if they are selected. |
Select type / Remove from type | Ability to check (uncheck) all files of the same type. |
Exclude/Limit/Select Duplicates | The ability to perform the selected action in relation to one of the folders in which the file is located. |
Save report... | Ability to save the report in a text document. |
Open folder | Allows you to open the folder in which this file is located. |
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.
Rate this article: