Mister-Office.

This manual explains how to delete repeated values ​​in Excel. You will learn several different methods of searching and removing duplicates, get rid of duplicate lines, detect accurate repeats and partial coincidences.

Although Microsoft Excel is primarily a tool for calculations, its tables are often used as databases to track stocks, compiling sales reports or mailing lists.

A common problem arising from increasing the size of the database is that it appears a lot of repetitions. And even if your huge file contains only a few identical records, these several repeats can cause a lot of problems. For example, it is unlikely to please send several copies of the same document to one person or the emergence of the same data in the report several times.

Therefore, before using a database, it makes sense to check it for duplicate records to make sure that you will not spend time to correct errors.

In several of our recent articles, we discussed various ways to detect duplicates in Excel and the allocation of unrelated cells or lines (see Links at the end of the article). However, there may be situations when you will ultimately eliminate duplicate in your tables. And this is just the topic of this manual.

Removing repetitive strings manually

If you are using the latest version of Microsoft Excel from 2007 to 2016, you have a slight advantage. These versions contain a built-in function to search and remove repeated values.

This tool allows you to find and delete Absolute coincidences (cells or entire lines) as well Partially matching records (having the same values ​​in the column or range).

Important! Since the tool " Delete duplicates » Forever removes identical records, it is recommended to create a copy of the source data before deleting anything.

To do this, follow these steps.

  1. First, select the range in which you want to work. To highlight the entire table, press Ctrl + A,
  2. Specifying the range, go to Top "Data" > And click " Delete duplicates »  .
  1. A dialog box opens. Select columns to check for duplicate and click " OK" .
    • To delete repetitive lines which have absolutely identical data in all columns, leave the checkboxes next to all columns, as in the screenshot below.
    • To delete Partial coincidences Based on one or more key columns, select only them. If there is a lot of columns in your table, the fastest way is to click the "Remove Selection" button. And then mark the ones you want to check.
    • If there is no Headlovkov , uncheck the checkbox My data In the upper right corner of the dialog box, which is usually enabled by default.
  2. If you specify all columns in the dialog box, the string will be deleted only if the values ​​are repeated in all of them. But in some situations it is not necessary to take into account the data located in certain columns. Therefore, for them, uncheck the checkboxes. For example, if each line contains a unique identification code, the program will never find any repeating. Therefore, the checkbox next to the column with such codes should be removed.

Done! All repetitive lines in our range are deleted, and a message is displayed, indicating how many repetitive records have been removed and how many unique remains.

Important note . Repeating values ​​are defined by what is displayed in the cell, and not by the fact that it really is written in it. Imagine that in A1 and A2 contain one and the same date. One of them is represented in format 05/15/2020, And the other is formatted in format 15 May 2020. When searching for repetitive Excel values, it believes that this is not the same. Similarly, the values ​​that are formatted in different ways are considered different, therefore $ 1,209,32 is not at all the same as 1209.32.

Therefore, in order to ensure a successful search and removal of repeats in the table or the data range, it is recommended to apply one format to the entire column. Note. The duplicate removal function removes the 2nd and all subsequent coincidences, leaving all the unique and first instances of identical records.

Deleting duplicates in a smart table.

I think you know that if you convert the range of cells to the table, at our disposal there are many interesting additional opportunities for working with these data. It is for this reason that such an Excel table is called "smart".

Select the area you need, then on the Home tab, select "Format as a Table". Next, you will be asked to specify the desired design option. When you finish, the Designer tab will automatically open.

Select the desired button on the tape, as shown in the screenshot. Then we note those columns in which we will search for repetitions. Well, then there will be the same as described in the previous section.

But, unlike the previously considered removal tool, the operation can be canceled if something went wrong.

Get rid of repetition by copying unique entries to another place.

Another way to delete repeats is to choose all unique entries and copy them to another sheet or another book. Detailed steps follow below.

  1. Select the range or the entire table that you want to process (1).
  2. Click the " Data "(2) And press the" Filter "button (3-4).
  3. In the dialog box Advanced filter "(5) Perform the following steps:
    • Select Switch Copy to another place (6).
    • Make sure that List of ranges The correct range is specified. It should be a range of step 1.
    • In field " Place the result in ... " (7) Enter the range in which you want to copy unique entries (in fact, it is enough to specify its upper left cell).
    • Select only Unique entries (8).
  4. Finally, click OK And unique values ​​will be copied to a new place:

Comment. The advanced filter allows you to copy the filtered data to another location only on the active sheet. For example, select a place at the bottom under your source data.

I think you understand that you can do without copying. Simply select the "Filter List" option, and duplicate entries will be hidden for a while using the filter. They are not deleted, but they will not interfere with you.

How to remove duplicates of rows using formulas.

Another way to delete undesiotic data is to identify them with the help of the formula, then filter, and then then delete too much.

The advantage of this approach lies in universality: it allows you to:

  • find and remove repetitions in one column,
  • find duplicate rows based on values ​​in multiple data columns,
  • Leave the first entry of recurring records.

The disadvantage is that you will need to remember several formulas.

Depending on your task, use one of the following formulas to detect repetitions.

Formulas for searching for repetitive values ​​in one column

Add another column in which we write the formula.

Reints of the names of goods, excluding the first entry:

= If (counted ($ b $ 2: $ b2; $ b2)> 1; "duplicate"; "")

As you can see, when the value occurs for the first time (for example, in B4), it is considered as quite common. But the second appearance (in B7) is already considered to be repeated.

We celebrate all repetitions together with the first advent:

= If (Count $ b $ 2: $ B $ 17; $ b2)> 1; "duplicate"; "unique")

Where A2 is the first, and A10 is the last cell of the range in which you need to find coincidences.

Well, now, to remove the unnecessary, install the filter and in the H column and we leave only the "duplicate". After that, the lines remaining on the screen simply delete.

Here is a small step-by-step instruction.

  1. Select any cell and apply an automatic filter by clicking the "Filter" button on the Data tab.
  2. Filter the duplicate rows by clicking the arrow in the header of the desired column.
  3. And finally, delete repetitions. To do this, select Filtered Rows by dragging the mouse pointer by their numbers, right-click and select " Delete a string " In the context menu. The reason why you need to do is instead of a simple pressing of the "Delete" button on the keyboard, is that this action will delete entire strings, and not just the contents of the cell.

Formulas for searching for repetitive lines.

In the event that we need to find and remove repeated lines (or part of them), we act in the same way as for individual cells. Only the formula is a little change.

We note with the help of the formula, non-unique lines, except 1- GO entry:

= If (countable ($ B $ 2: $ B2; $ B2; $ A $ 2: a2; $ A2; $ C $ 2: C2; $ C2)> 1; "Duplicate"; "")

As a result, we see 2 repeat.

Now the easiest option is to install the filter on the H column and the word "duplicate". After that, just delete all the filtered lines at once.

If we need to exclude all repetitive lines along with their first advent:

= If (countable ($ b $ 2: $ B $ 17; $ B2; $ A $ 2: $ A $ 17; $ A2; $ C $ 2: $ C $ 17; $ C2)> 1; "Duplicate"; "")

Next, we set the filter again and act similarly to the above.

As far as this method is convenient, to judge you.

Well, as you just saw, there are several ways to find repetitive values ​​in Excel and then remove them. And each of them has its strengths and restrictions.

More on the same topic:

How to select repeatable values ​​in Excel? - In this manual, you will learn how to display duplicates in Excel. We will look at various methods for shading in duplicate cells, whole rows or consecutive repetitions using conditional formatting. Previously, we investigated various ... How to calculate the number of repetitive values ​​in Excel? - Why consider duplicates? We can get an answer to many interesting questions. For example, how many customers made purchases, how many managers were engaged in selling, how many times worked with a certain supplier, etc. If a… Checking Excel entry correctly using a function ifHow to check the correctness of data entry in Excel? - Confirm the correctness of the input checkbox. Task: When manually entering data in the table cells, check the correctness of the input in accordance with the existing list of valid values. In the case of proper input in a separate column, put ...

Large Excel tables may contain repeating data, which often increases the amount of information and can lead to errors as a result of data processing using formulas and other tools. This is especially critical, for example, when working with monetary and other financial data.

In this article, we will consider the search and removal methods of duplicate data (duplicates), in particular, the strings in Excel.

Method 1: Deleting duplicate rows manually

The first method as simple as possible and implies the removal of duplicate strings using a special tool on the Tab Tape " Data" .

  1. Completely allocate all cells of the table with data using, using, for example, the left mouse button.
  2. In the tab " Data "In the tool section" Working with data "Find a button" Remove duplicates "And click on her. Tab Data in Excel Table
  3. Go to the settings of the duplicate removal parameters:
    • If the table being processed contains a header, then check the item " My data contain headlines "- He should be marked by a check mark.
    • Below, in the main window, lists the names of the columns on which duplicate will be searched. The system considers the situation in which the values ​​of all column selected in the setting are repeated in lines. If you remove part of the columns from the comparison, the likelihood of increasing the number of similar lines increases.
    • Thoroughly check and press and click OK .Removing duplicates in Excel table
  4. Next, the Excel program will automatically find and delete all duplicate lines.
  5. At the end of the procedure, a corresponding message will appear on the screen with information on the number of found and remote duplicates, as well as about the number of remaining unique lines. To close the window and shut down this feature, press the button OK. .The result of the removal of duplicates in the Special Table

Method 2: Deleting the repetitions with the help of a "smart table"

Another way to remove repeating lines - use " Smart Table ". Let's consider the algorithm step by step.

  1. To begin with, we need to highlight the entire table, as in the first step of the previous section. Selecting table in Excel
  2. In the tab " home "Find a button "Format as a table" (Tools section " Styles "). Click on the arrow down to the right of the button name and choose the color scheme you like. Creating a smart table in Excel
  3. After selecting the style, the settings window will open, which indicates the range to create " Smart Table ". Since the cells were highlighted in advance, you should simply make sure that the faithful data is indicated in the window. If it is not so, then you introduce corrections, check the item " Table with headlines "Was marked with a check mark and click OK . On this process of creating " Smart Table "Completed. Specifying coordinates to create a smart table in Excel
  4. Next, proceed to the main task - finding the hobged rows in the table. For this:
    • We put the cursor on an arbitrary table of table;
    • Switched to the tab " Constructor "(If after creating "Smart Table" The transition was not carried out automatically);
    • In chapter " Tools "We click the button" Remove duplicates ". Deleting duplicates in the smart Excel table
  5. The following steps are completely coincided with the actions described in the method above to remove duplicate lines.

Note: Of all the methods described in this article, this is the most flexible and universal, allowing you to comfortably work with tables of various structures and volume.

Method 3: Using the filter

The following method does not delete repeating lines physically, but allows you to configure the table display mode so that they are hiding when watching.

  1. As usual, allocate all cells of the table.
  2. In the tab " Data "In the tool section" Sorting and filter "We are looking for a button" Filter "(Icon reminds a funnel) and click on it. Turning on the filter in the Special Table
  3. After that, in the line with the names of the table columns will appear icons of inverted triangles (this means that the filter is turned on). To go to the extended settings, click the button " Additionally ", Located on the right of the button" Filter ". Switch to Extended Filter Settings in Excel
  4. In the advanced settings window that appears:
    • As in the previous way, check the address of the range of table cells;
    • We celebrate the checkbox " Only unique entries ";
    • Zhmem. OK .Advanced Filter in Excel
  5. After that, all the reckoned data will stop displaying in the table. To return to the standard mode, sufficiently press the button again. Filter "In the tab "Data" .Turn on and off the filter in the Special Table

Method 4: Conditional formatting

Conditional formatting is a flexible and powerful tool used to solve a wide range of tasks in Excel. In this example, we will use it to select the housing lines, after which they can be deleted by any convenient way.

  1. We highlight all cells of our table.
  2. In the tab " home "Click on the button" Conditional formatting ", Which is in the tool section" Styles ".
  3. The list in which you choose the group will be discharged. Rules for allocation of cells ", And inside it - item" Repeating values ". Conditional Formatting Excel Table
  4. Formatting settings window Leave unchanged. The only parameter that can be changed in accordance with its own color preferences is used to fill the color scheme allocated. Press the button by readiness OK .Selecting repetitive values ​​in Excel Table
  5. Now all the repetitive cells in the table are "highlighted", and you can work with them - edit the contents or delete lines entirely in any convenient way. Search for repetitive values ​​in the Special Table

Important! This method is not so universal as described above, since allocating all cells with the same values, and not only those for which the entire line is coincided. This can be seen on the previous screenshot when the desired jetings in the names of the regions were allocated, but all cells with categories of regions are noted with them, because the values ​​of these categories are repeated.

Method 5: Formula for removing repeating lines

The last method is quite complicated, and there are few people who use, since it is assumed to use a complex formula that combines several simple functions. And to set up a formula for your own table with data, you need a certain experience and skills to work in Excel.

The formula that allows you to search for the intersection within a specific column in general looks like this:

= If utility (index (address_stolbets; search board (0; countdown (address_sapki_stolbts__dlikatov: Address_Shespki_Stolbts_dlikatov (absolute); address_lubets;) +

Let's see how to work with it using the example of our table:

  1. Add a new column at the end of the table specifically designed to display repeating values ​​(duplicates). Search and remove duplicate in Excel table
  2. In the top cell of the new column (not counting the caps) we enter a formula that this particular example will be viewed below and click ENTER := If utility (index (A2: A90; search-receipt (0; counseurs (E1: $ E $ 1; A2: A90) + if (counted (A2: A90; A2: A90)> 1; 0; 1); 0)); "") .Formula for searching and removing duplicates in the Special Table
  3. We allocate to the end a new column for the housing data, the cap does not touch the cap. Next, we act strictly according to the instructions:
    • We put the cursor at the end of the formula string (you need to make sure that this is, indeed, the end of the line, since in some cases the long formula does not fit within the limits of one line);
    • Click the service key F2. on keyboard;
    • Then press the key combination Ctrl + Shift + Enter .
  4. These actions allow you to correctly fill out a formula containing references to arrays, all cells of the column. Check the result. Search and remove duplicates in Excel table with formula

As mentioned above, this method is folded and functionally limited, as it does not imply remove the found columns. Therefore, with other things being equal, it is recommended to use one of the previously described methods, more logically understandable and, often more efficient.

Conclusion

Excel offers multiple tools for finding and removing rows or cells with the same data. Each of the described methods is specific and has its limitations. To the universal version, we, perhaps, will take use " Smart Table "And functions "Remove duplicates" . In general, to fulfill the task, it is necessary to be guided by both the features of the structure of the table and the targeted goals and the vision of the final result.

Duplicate deletion in Excel

Working with Microsoft Excel very often occurs when you need to delete repeating lines. This process can turn into meaningless, monotonous and labor-intensive work, however, there are a number of ways to simplify the task. Today we will analyze several convenient methods for finding and removing repetitive strings in Excel. As an example, take the following table with data:

Duplicate deletion in Excel

Option 1: Command Delete Duplicates in Excel

Microsoft Excel has a built-in tool that allows you to find and delete row duplicates. Let's start with the search for repetitive lines. To do this, select any cell in the table, and then highlight the entire table by pressing Ctrl + A. .

Duplicate deletion in Excel

Click the tab Date. (Data) and then click Command Remove Duplicates. (Delete duplicates), as shown below.

Duplicate deletion in Excel

A small dialog box appears Remove Duplicates. (Delete duplicates). It can be noted that the selection of the first line is removed automatically. The reason for this is the checkbox installed in paragraph My Data Has Headers (My data contains headlines).

Duplicate deletion in Excel

In our example, there are no headers, since the table begins with the 1st line. Therefore, we will remove the checkbox. Having done it, you will notice that the entire table is allocated again, and the section Columns. (Columns) will change with Dulpicate. on the Column A. , B и С.

Duplicate deletion in Excel

Now that the entire table is highlighted, press OK. To remove duplicates. In our case, all rows with repetitive data are deleted, except for one. All deletion information will be displayed in the pop-up dialog box.

Duplicate deletion in Excel

Option 2: Advanced Filter

The second Excel tool, with which you can find and remove duplicates, is Advanced filter . This method also applies to Excel 2003. To apply the filter, it is necessary to highlight the entire table, as before using the key combination Ctrl + A. .

Then go to the tab Data. (Data) in the team group Sort & Filter. (Sorting and filter) click command Advanced (Advanced), as shown below. If you use Excel 2003, call the drop-down menu Data. (Data), select Filters. (Filters) and then Advanced Filters. (Extended filters).

Duplicate deletion in Excel

Now you need to check the checkbox Unique Records ONLY (Only unique entries).

Duplicate deletion in Excel

After clicking OK. All duplicates will be deleted in the document, with the exception of one entry. In our example, two entries remained, since the first duplicate was detected in the string 1. This method automatically determines the headers in the table. If you want to delete the first string, you will have to delete it manually. When in the 1st line there are headlines, not duplicates, there will be only one copy of the existing repetitions.

Duplicate deletion in Excel

Option 3: Replacement

This method is conveniently used when you need to find repeating lines in small tables. We will use the tool Find and Replace. (Search and replace), which is embedded in all Microsoft Office products. First you need to open the Excel table with which it is planned to work.

Opening the table, select the cell, the contents of which you want to find and replace, and copy it. To do this, select the desired cell and press the key combination Ctrl + C. .

Duplicate deletion in Excel

Copying the word you want to find, use the combination Ctrl + H. to call a dialog box Find and Replace. (Search and replacement). Insert the copied word with the field To find , Press Ctrl + V. .

Duplicate deletion in Excel

Press the button Options. (Parameters) to open an optional list of options. Check the Checkbox Match Entire Cell Contents (Entire cell). This must be done because in some cells the sought words are located together with other words. If you do not choose this option, you can unintently remove the cells you want to leave. Make sure that all other settings match the shown in the figure below.

Duplicate deletion in Excel

Now you need to enter a value in the field. REPLACE WITH (Replaced by). In this example we will use the number 1. Entering the desired value, click REPLACE ALL (Replace everything).

Duplicate deletion in Excel

It can be noted that all values Duplicate. In the table cells, will be replaced by 1. We used the value 1Since it is small and stand out in the text. Now you can visually identify lines that have repetitive values.

Duplicate deletion in Excel

To leave one of the duplicates, it is enough to insert the source text back to the string that has been replaced. In our case, we will restore the values ​​in the 1st line of the table.

Duplicate deletion in Excel

Determining the rows with a repeated content, alternately select them by holding the key Ctrl .

Duplicate deletion in Excel

Selecting all the lines that you want to delete, right-click on the header of any of the selected rows and in the context menu, press Delete. (Delete). Do not press the key Delete. On the keyboard, because in this case only the contents of the cells will be removed, and not the strings completely.

Duplicate deletion in Excel

Having done this, you may notice that all the remaining lines have unique values.

Duplicate deletion in Excel

Rate the quality of the article. Your opinion is important to us:

When you work with a huge amount of data in the Excel program, it is very easy to make a mistake and enter the same data again. So the duplicates occur. This, of course, increases the amount of total information, and in some cases confuses the formulas that must sum up and calculate various variables. Manually find duplicates, but why, if there are several ways, as in Excel, delete repeating lines automatically.

How to delete repeating lines in Excel

Method 1: Standard Duplicate Removal

The easiest way, as in Excel, delete repeating lines, is to use the appropriate tool located in the tape.

So you need:

  1. Climbing the left mouse button, select the required range of cells, within which there will be a search and automatic removal of repeating lines.
  2. Go to the "Data" tab.
  3. Press the "Remove Duplicate" tool, which is in the "Working with Data" group.
  4. In the window that appears, put a tick next to "My data contain headlines."
  5. Install checkmarks next to the title of those columns in which the search will be conducted. Take into account that if all the checkboxes are installed, then the rows will be considered a duplicate that are completely repeated in all columns. Thus, if you want to remove duplicates from one column, you must leave only one tick next to its name.
  6. Press "OK."
Excel Delete Rows with Repeating Values

As soon as you press the button, the search for duplicates of the selected area will begin, and then delete them. According to the result, a window will appear in which the report will be demonstrated. Now you know how to remove repeating lines in Excel, but only the first way, the second line.

Method 2: Using the Smart Table

Duplicates can be removed in this way, which takes place in this article. This time, in the story, as in Excel, delete repeating lines, will be used by the "smart table".

To remove repeats in Excel, you need to do the following:

  1. As in the previous way, first highlight the range of cells, where you need to remove duplicates.
  2. Press the "Format as a table" button, which is located on the Home tab in the "Styles" toolbar.
  3. Select from the drop-down menu you like style (any).
  4. In the window that appears, you must confirm the previously specified range of cells, and if it does not coincide, then reassign. Also install a tick next to the "Table with headers", if it is as follows, click the "OK" button.
  5. "Smart Table" created, but that's not all. Now you need to highlight any table of the table so that the "Designer" point appears in the tabs and directly go to this tab.
  6. In the tape of the tool, click the "Remove Duplicate" button.
Delete repeating lines Excel 2010

After that, the Removal String window will appear. It will be similar to what was represented in the first method, so all subsequent actions are carried out in accordance with the first instruction.

Conclusion

So we disassembled two ways, as in Excel delete lines with repeating values. As you can see, there is nothing complicated in this, but using the instruction, and it is possible to check this operation in a few seconds. The example was given in the 2016 version of the program, however, delete the repetitive lines in Excel 2010 and other versions can also be.

Excel How to Delete Repeating Lines

Search and remove repetitions

In some cases, repeating data may be useful, but sometimes they complicate the understanding of the data. Use conditional formatting to search and highlight repetitive data. This will allow you to view the repetition and delete them as needed.

Select cells to be checked for repetitions.

Note: Excel does not support the allocation of repetitive values ​​in the "Value" area of ​​the consolidated table report.

On the tab home Choose Conditional formatting > Rules for allocation of cells > Repeating values .

In the field next to the operator Values ​​S. Select Formatting to apply to repeated values ​​and click OK .

Removing repetitive values

When using the function Duplicate removal Repeating data are permanently removed. To accidentally not lose the necessary information, before removing the duplicate data, it is recommended to copy the source data to another sheet.

Select the range of cells with repeating values ​​to be deleted.

Tip: Before trying to delete repetitions, remove all the structures and intermediate results from your data.

On the tab Data Press the button Remove duplicates and in the section Columns Install or uncheck the flags corresponding to the columns in which you want to remove the repetition.

For example, on this sheet in the "January" column contains price information to be saved.

Therefore, the flag January in field Duplicate removal Need to remove.

Microsoft Excel.

Tricks • Receptions • Solutions

How to remove repeated lines in the Excel table

A long-time point in the list of wishes to Excel was the ability to automatically remove duplicate rows in the sheet. And so, starting with Excel 2007, Microsoft's developers performed this desire.

In fig. 33.1 shows a range of data after it was converted to a table using the command Box ► Tables ► Table . Please note that this table contains some repetitive lines.

Fig. 33.1. The table contains the repetitive strings to be removed

Start by selecting any cell in the table. Then execute the command Designer ► Service ► Delete Duplicates . Excel will display the dialog box shown in Fig. 33.2. All columns are listed here in the table. Select boxes for columns you want to include in the search for duplicates. In most cases, you need to choose all columns - this means that the duplicate will be defined as having the same data in each column.

If your data is not entered in the form of a table, use the command Data ► Data Operation ► Delete Duplicates To display the same name dialog box.

It should be understood that the repetitive values ​​are determined by the value displayed in the cell, and this is not necessarily the value stored in the cell. Suppose two cells contain one and the same date: one date is formatted as 5/15/2010, and the other is like May 15, 2010. When removing Duplicates Excel will consider that these lines are different.

Fig. 33.2. Duplicate Removal - Easy Procedure

Press the button OK. And Excel sends repetitive lines and displays a message about how many duplicates he removed (Fig. 33.3).

Fig. 33.3. Excel informs you about the result of removing repeating lines

How to delete repetitive values ​​in the Excel table

Search and delete manually repeated values ​​in the Excel table, especially in large documents, is a very risky and inappropriate occupation. It is possible that you, checking the visually cells, can skip the dunk. And then, the enormous amount of time is spent on deleting each cell.

In this article, we will look at how to remove duplicate in Excel using various automated standard functions.

Tip! For ease of familiarization with the methods of removing repetitive strings, columns, cells, open the draft template or copy of the project in the draft template or a copy of the project that you work on the error during editing not to lose valuable data and not disrupt the original format.

1. Holding the left mouse button, mark the cursor of the table area of ​​the table (individual lines, columns), where you need to remove repeats. Or highlight the entire project.

2. In the Editor menu, go to the Data tab.

3. In the "Work with Data" block, click the Delete Duplicate button.

4. In the panel that appears, select the columns in which you want to remove the same values ​​by setting the labels. If there are many items, use the "Select All" and "Remove Allocation" to quickly adjust the deletion. Click OK.

5. Upon completion of the cleaning of rows and columns of the table, a message appears on how many unique values ​​remains.

Tip! Repeats Immediately after deletion, you can restore, clicking in the upper left corner Excel "Arrow Left" icon.

1. Click on the editable table.

2. On the Data tab, in the "Sort and Filter" block, click the "Advanced" subsection.

If you need to create a new table, containing only unique source cells:

1. In the "Advanced Filter" panel, the Copy Result to Other Place.

2. Press the button located on the right side of the "Place the result in the range" field.

3. Candle on the free Workspace Excel, where the filtered table will be placed. After clicking, the cell code appears in the field. Close it and go to the filter option.

4. Click only unique entries and click "OK".

5. After performing the filtering in the specified location, a version of the source table without repeats will appear.

To edit the document without creating copies:

  • In the "Advanced Filter" panel, set the processing mode "Filter List on site";
  • Click of mouse Include the add-in "Only unique entries";
  • Clazzo "OK".

1. Click the Home tab.

2. In the "Styles" section, open the "Conditional Formatting" drop-down submenu.

3. Open: Cell allocation rules → Repeating values ​​...

4. In the drop-down menu "Values ​​C", select the most convenient option for the color highlighting of repetitions in the table. Click OK.

5. Remove with selective or groups designated duplicate through the Excel context menu (right-click → delete).

Comfortable work in Excel!

Remove duplicate in Excel

In this article we will analyze How to remove duplicates In Excel 2007, Excel 2010, Excel 2013 and Excel 2016. You will learn several different ways as Find and remove duplicate in Excel With or without the first occurrences, Remove duplicate string , as Remove duplicate in column How to detect absolute duplicates and partial coincidences. The article discusses the following items:

Delete duplicates of strings in Excel using the Duplicate Duplicate feature

If you use the latest Excel 2007, Excel 2010, Excel 2013 or Excel 2016, you have an advantage, because these versions contain a built-in function for searching and removing duplicates - Remove duplicate function .

This feature allows you to find and remove absolute duplicates (cells or entire lines), as well as partially appropriate records (lines that have the same values ​​in the specified column or columns). We will analyze on the example how to use step by step Remove Duplicate Function in Excel .

Note. Since function Remove duplicates Forever removes identical records, it is recommended to create a copy of the source data before removing repeating lines.

  1. First, select the range in which you want Remove duplicates . To select the entire table, press Ctrl + a.
  2. Next, go to the Data tab -> Group "Working with Data" and click " Remove duplicates "

Delete Duplicates in Excel - Remove Duplicate Function in Excel
  1. The dialog box opens Remove duplicates " Select columns to check duplicates and click OK.
  • To Remove duplicate string having fully identical values ​​in all columns, leave flags next to all columns, as shown in the image below.
  • To Remove partial duplicates Based on one or more key columns, select only the corresponding columns. If your table has a lot of columns, it is better to press the "Remove Selection" button first, and then select the columns you want to check for duplicates.
  • If there are no headers in your table, remove the flag from the "My Data contains" field in the upper right corner of the dialog box, which is usually selected by default.

Delete duplicates in Excel - column selection (s) that you want to check for duplicate

Ready! Everything Duplicates string The selected range deleted, and the message indicates how much it was Removed duplicate records and how many unique values ​​remained.

Delete duplicates in Excel - a message about how much duplicates were removed

Function Delete Duplicates in Excel Deletes the 2nd and all subsequent duplicates of instances, leaving all the unique lines and the first instances of the same records. If you want to Remove duplicate string , including the first entry, i.e. If you go to remove all duplicate cells. Or in another case, if there are two or more duplicate lines, and the first of them you want to leave, and all subsequent Duplicates Delete , Using one of the following decisions described in this section.

Remove duplicates by copying unique entries to another place

Another way Remove duplicate in Excel - This is the separation of unique values ​​and copy them to another sheet or in the selected range on the current sheet. We will analyze this method.

  1. Select the range or the entire table you want Remove duplicates .
  2. Go to the Data tab -> Sort and Filter Group and click the "Advanced" button.

Delete Duplicates in Excel - Using an additional filter to remove duplicates
  1. In the Advanced Filter dialog box, follow these steps:
  • Select "Copy the result to another location".
  • Check if the correct range is displayed in the source range. It must be the range selected in step 1.
  • In the Position the result in the Range field, enter the range in which you want to copy unique values ​​(in fact it is enough to select the upper left cell of the destination range).
  • Select only unique entries.

Delete Duplicates in Excel - Duplicate Filter
  1. Finally, click "OK". Excel remove duplicates and copies unique values ​​to a new specified place:

Delete duplicates in Excel - unique entries copied from another location

So you get new data based on the specified but with Remote duplicate .

Please note that the advanced filter allows you to copy filtered values ​​to another location only on the active sheet.

Remove duplicate rows in Excel using formulas and filters

Another way Remove duplicate in Excel - It is to determine them with the help of formula, filter and Remove duplicate string .

The advantage of this approach is universality - it allows you to find and Remove duplicate in column Or duplicate rows based on values ​​in several columns. The disadvantage is that you will need to remember several repetitive formulas.

  1. Depending on your task, use one of the following formulas to search for duplicates.

Formulas for searching duplicates in 1 column

  • Duplicates with the exception of the 1st entry:

= If (counted ($ A $ 2: $ A2; $ A2)> 1; "Duplicate"; "")

  • Duplicates from the 1st entry:

= If (counted ($ A $ 2: $ A $ 10; $ A2)> 1; "Duplicate"; "Unique")

Where the A2 cell is the first, and the A10 is the last cell of the band for the search for duplicates.

Formulas for searching duplicate strings

  • Duplicates of lines, except for the 1st entry. That is, if as a result there are two or more equal strings, then the first of them will be marked as unique, and all subsequent as duplicates:

= If (countable ($ A $ 2: $ A2; $ A2; $ b $ 2: $ B2; $ B2; $ C $ 2: $ C2; $ C2)> 1; »Duplicate string"; "Unique")

  • Duplicates of strings with the 1st entry. In this case, if there are two or more equal lines as a result of the search, then all of them will be marked as duplicates:

= If (countable ($ A $ 2: $ A $ 10; $ A2; $ B $ 2: $ B $ 10; $ B2; $ C $ 2: $ C $ 10; $ C2)> 1; "Duplicate string"; "Unique")

Where A, B and C are columns to be checked for duplicates.

For example, so you can identify Duplicates string , except for the 1st entry:

Remove duplicates in Excel - formula for identifying duplicate rows except for the first cases
  1. Select any cell in your table and apply an automatic filter by clicking the "Filter" button on the Data tab, or "Sort and Filter" -> "Filter" on the Home tab.

Delete Duplicates in Excel - Apply the filter to cells (Data tab)
  1. Filter Duplicates string By clicking the arrow in the column title " Duplicates "And then select the Duplicate Line checkbox.

Delete Duplicates in Excel - Duplicate Filter Row
  1. And finally Remove duplicates of string . To do this, select Filtered Rows, moving the mouse pointer to the rows numbers, right-click on it and select "Delete a string" in the context menu. In this case for Duplicate removal You should not use the "Delete" key on the keyboard, because we need to delete entire strings, and not just the contents of the cells:

Remove duplicates in Excel - filtering and removing duplicate rows

Well, now you have learned several ways as Remove duplicate in Excel . And you can use one of them depending on your situation.

Deleting duplicate rows in Excel

Working with Microsoft Excel very often occurs when you need to delete repeating lines. This process can turn into meaningless, monotonous and labor-intensive work, however, there are a number of ways to simplify the task. Today we will analyze several convenient methods for finding and removing repetitive strings in Excel. As an example, take the following table with data:

Option 1: Command Delete Duplicates in Excel

Microsoft Excel has a built-in tool that allows you to find and delete row duplicates. Let's start with the search for repetitive lines. To do this, select any cell in the table, and then highlight the entire table by pressing Ctrl + A. .

Click the tab Date. (Data) and then click Command Remove Duplicates. (Delete duplicates), as shown below.

A small dialog box appears Remove Duplicates. (Delete duplicates). It can be noted that the selection of the first line is removed automatically. The reason for this is the checkbox installed in paragraph My Data Has Headers (My data contains headlines).

In our example, there are no headers, since the table begins with the 1st line. Therefore, we will remove the checkbox. Having done it, you will notice that the entire table is allocated again, and the section Columns. (Columns) will change with Dulpicate. on the Column A. , B и С.

Now that the entire table is highlighted, press OK. To remove duplicates. In our case, all rows with repetitive data are deleted, except for one. All deletion information will be displayed in the pop-up dialog box.

Option 2: Advanced Filter

The second Excel tool, with which you can find and remove duplicates, is Advanced filter . This method also applies to Excel 2003. To apply the filter, it is necessary to highlight the entire table, as before using the key combination Ctrl + A. .

Then go to the tab Data. (Data) in the team group Sort & Filter. (Sorting and filter) click command Advanced (Advanced), as shown below. If you use Excel 2003, call the drop-down menu Data. (Data), select Filters. (Filters) and then Advanced Filters. (Extended filters).

Now you need to check the checkbox Unique Records ONLY (Only unique entries).

After clicking OK. All duplicates will be deleted in the document, with the exception of one entry. In our example, two entries remained, since the first duplicate was detected in the string 1. This method automatically determines the headers in the table. If you want to delete the first string, you will have to delete it manually. When in the 1st line there are headlines, not duplicates, there will be only one copy of the existing repetitions.

Option 3: Replacement

This method is conveniently used when you need to find repeating lines in small tables. We will use the tool Find and Replace. (Search and replace), which is embedded in all Microsoft Office products. First you need to open the Excel table with which it is planned to work.

Opening the table, select the cell, the contents of which you want to find and replace, and copy it. To do this, select the desired cell and press the key combination Ctrl + C. .

Copying the word you want to find, use the combination Ctrl + H. to call a dialog box Find and Replace. (Search and replacement). Insert the copied word with the field To find , Press Ctrl + V. .

Press the button Options. (Parameters) to open an optional list of options. Check the Checkbox Match Entire Cell Contents (Entire cell). This must be done because in some cells the sought words are located together with other words. If you do not choose this option, you can unintently remove the cells you want to leave. Make sure that all other settings match the shown in the figure below.

Now you need to enter a value in the field. REPLACE WITH (Replaced by). In this example we will use the number 1. Entering the desired value, click REPLACE ALL (Replace everything).

It can be noted that all values Duplicate. In the table cells, will be replaced by 1. We used the value 1Since it is small and stand out in the text. Now you can visually identify lines that have repetitive values.

To leave one of the duplicates, it is enough to insert the source text back to the string that has been replaced. In our case, we will restore the values ​​in the 1st line of the table.

Determining the rows with a repeated content, alternately select them by holding the key Ctrl .

Selecting all the lines that you want to delete, right-click on the header of any of the selected rows and in the context menu, press Delete. (Delete). Do not press the key Delete. On the keyboard, because in this case only the contents of the cells will be removed, and not the strings completely.

Having done this, you may notice that all the remaining lines have unique values.

Добавить комментарий