How To Find And Remove Duplicates In Microsoft Excel Or Google Sheets
Microsoft Excel and Google Sheets, two leading spreadsheet applications, offer many features to help users work efficiently with their data. One shared challenge spreadsheet that users face is duplicate entries, which can lead to inaccurate results, skewed analyses, and erroneous conclusions. This can be an instant dealbreaker for school and work projects.
These errors can emerge from various situations, such as inaccuracies in data input, merging multiple data sources, or incorporating information from external systems. These repetitions can compromise your data's quality, inflate storage requirements, and detrimentally affect the validity of your work. For instance, duplicate entries can artificially inflate specific data points' frequency, skewing averages, percentages, and other statistical measures. Additionally, duplicates can cause inconsistencies between related data sets, rendering your analyses unreliable.
Whether you are a seasoned spreadsheet expert or a novice user looking to improve your data management skills, it is crucial to know how to remove duplicate entries and why you should. Fortunately, both programs offer built-in solutions to quickly fix these errors and get you back to perfecting your data.
How to recognize and fix duplicates in Microsoft Excel
Now that you understand why addressing copies in your data set is essential, the next step is resolving the issue.
To pinpoint duplicate entries, apply Excel's conditional formatting feature:
- Open the Excel worksheet containing the data in question.
- Select the column or range you want to inspect for duplicates.
- Navigate to the Home tab, and within the Styles group, click Conditional Formatting.
- Opt for Highlight Cells Rules and subsequently Duplicate Values.
- Choose your desired color format for emphasizing duplicates and confirm with OK.
There are several different methods to handle the duplicates, but using Excel's built-in tool is the easiest.
- Select the range containing duplicates.
- Click on the Data tab. Then, click Remove Duplicates.
- Select the columns that might have copies in the dialog box and press OK.
- Excel will eliminate duplicate entries, leaving unique values.
This method might remove duplicates in your data that you'd like to keep, so if this happens, consider manually deleting the data cell by cell. While this might take longer, it's a great way to keep your data intact.
How to recognize and fix duplicates in Google Sheets
If you use Google's cloud-based productivity tools, you can also check your data for copies, just like Excel. The process is mostly the same but requires more technical knowledge of conditional formatting.
- Open the Google Sheets with your data.
- Choose the column or range you want to examine for duplicates.
- Navigate to Format > Conditional Formatting.
- Choose Custom formula under the Format cells dropdown.
- Input the formula: =COUNTIF(A:A, A1)>1
- Select your preferred color format for emphasizing duplicates and confirm with Done.
One of the easiest ways to handle duplicates is by installing the Remove Duplicates extension from the Google Workspace Marketplace.
- Install the Remove Duplicates add-on from the Google Workspace Marketplace.
- In your sheet, click Extensions, then Remove Duplicates, and choose: Find Duplicates or Uniques.
- Follow the steps in the add-on wizard to choose the range and columns you'd like to check for duplicates and configure the removal settings.
While this extension is handy for large data sets, it's always worth checking your data by hand to ensure you don't unintentionally delete any data you'd like to keep.