How to remove duplicate strings in Excel from the table? How to find and delete repetitive values \u200b\u200bin the Excel editor?
Detailed manual for removing duplicates from Excel tables in several ways.
Navigation
Editor Excelwhich is included in the office application package Microsoft Office.Today, it is considered the best tool for working with various kinds of tables. I use it both at home and in organizations engaged in trade, audit and other activities.
Most often B. Excel Tables are processed with extremely large volumes and sometimes the user needs to find and remove repeated values. Search and delete duplicates manually in such tables may take a huge amount of time and even if it has a user, then there is no guarantee that it will not miss some kind of repetitive value, which will be almost impossible to find.
But fortunately editor Excel It has extensive functionality, thanks to which users can make it easier for themselves. In our article you will find a few simple ways to find and remove duplicate values \u200b\u200bfrom the table in Excel.
Image 1. Deleting the same lines to Excel.
How to remove duplicate strings in Excel from the table? How to find and delete repetitive values \u200b\u200bin the Excel editor?
Before moving to familiarize yourself with the search and removal of lines, columns and values \u200b\u200bin cells, it is highly recommended to pre-create a test table or a copy of the existing working project to avoid the loss of valuable data and not spoil the original format in case of any error. After creating a test table or copies of the available work, open them in the editor Microsoft Excel. And go to the next stage of the article.
Search and remove duplicate values \u200b\u200bin Excel Table: First method
Step 1.
- After you have opened the test table in the editor, hold down the left mouse button and select the columns and lines where you want to remove repeats.
- If necessary, select the entire table in the same way or using the key combination. Ctrl + A.».
Image 2. Allocation of the workspace.
Step 2..
- After selecting the required cells at the top of the editor window on the toolbar, open the tab " Data"And in the section" Working with data»Click on the" button " Remove duplicates».
Image 3. Selecting a tool to work with the table.
Step 3..
- In the window that opened on the screen should be highlighted by checkboxes from which you want to remove repeated values. If a large number of items are displayed in the window, you can use the Quick Selection buttons.
- To complete, click the "button" OK».
Image 4. Select columns and start the filtering process.
Step 4..
- For a while, the editor will search and delete duplicate in the selected columns. As soon as the process is completed, the window appears on the screen with a notification of how many duplicate values \u200b\u200bwas removed and how many unique remains.
Image 5. Window with finite result.
Step 5..
- Check the document and make sure everything happened the way you wanted. If you made a mistake and removed not the same values, then you can cancel the last action by pressing the reverse arrow button at the top of the editor window.
Image 6. Cancel the last action taken.
Search and delete duplicate values \u200b\u200bin the Excel table: second way
Step 1.
- Select the editable table, at the top of the main window editor, open the tab " Data"And on the toolbar in the section" Sorting and data»Click on the button" Additionally».
Image 7. Transition to additional data filters.
Step 2..
- If you need to make a separate table, in which it will be contained exclusively cells with unique values \u200b\u200bof the source table, then in the window that opens " Advanced filter»Mark the marker line" Copy the result to another place", After which on the right side of the field" Place the result in the range»Click the Place Selection Button.
Image 8. Setting the data filter.
Step 3..
- In the next step, you will need to select the free work space area where you want to add a filtered table. After selecting the area, you must close the field with the code code and switch back to " Advanced filter».
Image 9. Select a place to insert the table.
Step 4..
- Next you will need to mark the marker " Only unique entries»To extract them from the source table and click on the button" OK».
Image 10. Completion of the filter setting and the beginning of the process of its operation.
Step 5..
- As soon as the filtering is completed, the version of the initial table will appear in the specified area exclusively with unique values.
Image 11. The resulting end result.
Step 6..
- If you do not want to create a copy of the table on a separate workspace in Excel, then in the window " Advanced filter»Instead of a line" Copy the result to another place»Mark the marker line" Filter list».
- Next, you should activate the filter setting " Only unique entries"And click on the button" OK" In this case, your table will remain columns and rows exclusively with unique values \u200b\u200bwithout creating a copy.
Image 12. Deleting duplicates without creating a copy of the table.
Search and delete duplicate values \u200b\u200bin Excel Table: Third Method
Step 1.
- On the main tab of the editor Excel It is necessary to go to the section " Styles"And there to reveal the context menu" Conditional formatting».
- Next, hover the cursor to the item " Rules for allocation of cells"And in the next list, select Location" Repeating values».
Image 13. Transition to the stylization tool of cells.
Step 2..
- A small window will appear in front of you, where in the line " Values \u200b\u200bS."You must select the most suitable color to highlight the cells with repeating values.
- Select the appropriate color scheme and click the "button" OK».
Image 14. Setting the style of duplicate cells.
Step 3..
- Your table will come about the same way as the screenshot below. Manually remove all the backlit duplicas, highlighting them in one or groups.
Image 15. Manual removal of tinted cells with dubs.