Microsoft office is undoubtedly the most important software/application for any laptop or PC. Microsoft office serves different purposes through its categories such as Microsoft Word, Microsoft Excel, and Microsoft PowerPoint etc.
Microsoft Excel is among the most used applications in the world. Being familiar with this application is absolutely significant in this day and age. You would certainly be asked about your knowledge regarding Microsoft Excel during most job interviews as it advocates your computer literacy.
There is a lot to learn about Excel as the application serves so many purposes. Whether you are working on a large Excel worksheet or compiling small spreadsheets into a large one, you will likely face the issue of duplicates. Finding duplicates in Excel is one of the areas that need to be addressed.
1. Finding Duplicates
1. Open your file on Microsoft Excel.
2. Select the range of cells from which you want to identify the duplicates. Click on a single cell and then press Ctrl + a to select all.
3. You will see Conditional Formatting on the Excel’ Home Tab. Click it and then click Highlight Cell Rules.
4. Click on Duplicate Values from the options and you will see a popup box.
5. Just click the OK option on popup box and your duplicates value will be identified.
Additionally, you can also isolate the duplicated values just to clear any confusion. For that, follow these steps.
1. First, you will have to right-click on the duplicated cells.
2. After the right click, choose the Filter option
3. Then click on Filter by Selected Cell Colors.
Now your list will just show the duplicated values. You can copy them to another worksheet to eradicate any confusion. Moreover, Excel also allows you to remove Conditional Formatting from your sheet. Just click the Conditional Formatting on Excel’s home tab. Click on the Clear Rule option and then Clear Rule from Entire Worksheet.
You need to understand that if you are working on Microsoft Excel 2003 or the versions before, you won’t be able to utilize Filter by Color option. Filter by Color option started from the 2007 version of Excel.
Furthermore, the above-mentioned procedure of Conditional Formatting is only applicable to the columns in excel. If you need to highlight the duplicates rows, then you need to do it through your own generated formula (depending on the number of rows and columns).
2. Finding Duplicates
Moving on, Microsoft Excel offers another method through which you can find the duplicates and delete them as well. The second method is comparatively easy, but some professionals believe that it misses the trick sometimes. Anyway, here is a step by step guideline on how this second method works.
1. Open the Microsoft Excel file.
2. Next, you need to select the range of cells from which you need to find the duplicate data.
3. The easiest way to do is to click on a cell and then press ctrl-a to select all the cells.
4. Once done, click on the Data tab from the toolbar.
5. Now you will see various options which include Text to Columns, Remove Duplicates, Data Validation, Consolidate, and What-If Analysis.
6. Click on the Remove Duplicates option.
7. A customized window will popup when you will click Remove Duplicates option.
8. Click Select All to ensure all your columns have been selected.
9. The default setting will check all your columns, but you can also check each column separately just by clicking at the top of that column.
Beware that if your data has headers. Your first entry in the column could be a header, and therefore you need to click My Data has Headers option. It will omit headers from the deletion process.
Just click OK in the popup box when you are satisfied that you have figured out your duplicates.
Do not forget to re-save your file as closing it without saving won’t save the changes.
In some cases, the program may tell you that there are no duplicates to be deleted. If that happens, then repeat the procedure by checking every column individually. Scanning each and every column separately will remove any duplicates that were missed initially.