Data is the primary ingredient of Formatting and analysis. Handling data properly with a meaningful outcome is crucial for the storage of data, may it be on a small scale or large one.
Excel formatting is used to alter, modify, align and arrange data properly. Duplicacy of data results in the following issues:
- Increase data storage space.
- Requires more management.
- Tasks gets complex
- Unnecessary storage of values and text.
Redundancy in data creates lots of space and also increases the complexities in handling the same. During excel Formatting as well there are chances of the duplicity of data. After that, it becomes more crucial to remove the duplicate data in Excel.
Below is the procedure to find and remove the duplicate data from the excel sheets.
Using Remove Duplicate option present in the workbook ribbon
Step 1: Select/Click the exact cells and sheet number in which you are supposed to find the copied data(duplicate data).
Step 2: Now visit the Home ribbon and select Conditional Formatting. Click the arrow to get the conditioning Formatting dropdown.
Step 3: Now extend Highlight Cell rules and click on Duplicate values
Step 4: A dialogue box will appear. Here select Duplicate values from the drop-down and enter the text you want to compare them with.
Now you know which part of the text consists of duplicate data. Now using remove duplicate users can easily delete the duplicate data.
Step 5: Again select the cells where you found the duplicate data. You can do this using a cursor or using a shift key.
Step 6: Now click on Data and then hit remove duplicates.
Users can check and uncheck columns and rows where he/she wants to sustain the data irrespective of duplicate or not.
Using this procedure one can easily remove the redundant data with few simple clicks.
Note: Make sure you save a dummy copy of your original file before deleting duplicate data. Sometimes there are chances that we want some data back.
- How to Create a Drop-down list in Microsoft Excel
- How to create an Invoice in Excel
- How to Autofit in Excel
- How to Make a Histogram in Microsoft Excel
- How to calculate standard deviation in excel
Manual removal of duplicate data from Excel sheet
The other method is to manually remove the duplicates. Manual removal of duplicates requires time and concentration. But it also gives the advantage of assured removal of data files that are redundant.
Removal of duplicates using the Advance feature
Step 1: Select the column using shift key or cursor used for checking duplicacy.
Step 2: You will be able to see the Advanced Filter at the top of the workbook of excel software. Click on the same.
Step 3: A dialogue box appears.
Step 4: Now check the box of Copy to another location.
Step 5: Verify the location and list string.
Step 6: Make sure you check the box of Unique records only.
Step 7: Click on Ok.
In this way, you can copy the unique records to a new sheet or column. Now users can replace this with the pre-existing column and get the required unique data list.
Duplicates can also be removed using the function COUNTIF.