Tracy van der Schyff

#Office365Challenge – Excel Removing Duplicates – As most of our data calculation, manipulation and reporting still happens in Excel, it might be necessary to check for and remove duplicates at times. Seeing as Excel can have over a 1,000,000 rows of data, Doing it fast and easy would be the objective. In this post I will show you exactly how to do that.

I convert a lot of existing Excel spreadsheets to custom lists in SharePoint. As the goal should always be to manage the input of data, you need to apply as much validation as you can to ensure clean and correct data.

For example: My spreadsheet has a column for the location. As this is either a manual input by users, or a report from external systems, I would like to get the exact list of locations that I will use for my choice dropdown in SharePoint. This means that going forward users will not type the location, but choose it from a dropdown. Keep in mind that Removing Duplicates works in Excel – regardless of whether you want to take the lists to SharePoint or not. You might want to remove duplicates to eliminate errors.

In Excel you need to select the column (1) which contains the data you want to clean up. I always copy this column to a new sheet as I don’t want to change the existing data (I only need the dropdown list with no duplicates). Now click on the Data Tab (2), then on Remove Duplicates (3). The settings box will open where you select the columns. As I am only cleaning up one column – it only displays one. Click on OK (4) to perform the function.

Excel will now display the shortened (single value) list. This is a great way to also spot spelling mistakes. In my example Rivonia was misspelt as Rivonias. Click on OK to remove the duplicates after which you can now remove the misspelt locations.
Note: Before taking this ‘clean’ list to SharePoint as a choice dropdown – sort it alphabetically – users struggle finding the correct choice if not displayed A-Z.

I hope this helps you to clean up your data, FASTER. See you tomorrow with some more tips.

Overview of my challenge: As an absolute lover of all things Microsoft, I’ve decided to undertake the challenge, of writing a blog every single day, for the next 365 days. Crazy, I know. And I’ll try my best, but if I cannot find something good to say about Office 365 and the Tools it includes for 365 days, I’m changing my profession. So let’s write this epic tale of “Around the Office in 365 Days”. My ode to Microsoft Office 365.

Keep in mind that these tips and tricks do not only apply to Office 365 – but to the overall Microsoft Office Suite and SharePoint, where applicable.