In this blog post, we’ll see how you can remove duplicated and clean data in excel tables using Data Explorer Add-in.
Our Excel Table has following Data:
And we want to remove duplicates to make the data-set look like this:
In real world data-sets, we wouldn’t have few rows but lot’s of rows and doing it manually wouldn’t be the wisest option. With that in mind, let’s look for a few-clicks solution that can help us remove duplicates.
If you haven’t already, download the Data Explorer add-in preview available for Excel 2010 & 2013. It can do a lot more than removing duplicates – it’s a great add-in and it’ll save you lots of time especially if your job involves discovering, cleaning and combining data for analysis purposes. After you’re done installing the add-in, use the steps below to remove duplicates in an excel column:
1. Open Data in Excel. Switch to Data Explorer Tab
2. For the purpose of the demo, I am assuming that you already have the data in excel file. If not, you can connect to other sources via the add-in.
3. Data Explorer add-in > Excel Data> From Table
4. After you’ve clicked on the From Table, a query editor will pop up:
5. Select both columns
(you can select both columns by: select first column > hold down the ctrl key and then click on second column)
6. Right click > Remove Duplicates
7. click on done if you see that the duplicates have been removed correctly
In this blog post, we saw how to remove duplicates and clean data in Excel using the Data Explorer Preview add-in.
If you’ve not downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx
1) URL to download the add-in may change in future
2) The steps that I described may also change because as of today the ad-in is in “preview” stage and things may change in future.
- PASS Business Analytics Conference Keynote Day #1 (parasdoshi.com)
- Data Explorer for Excel: An experiment with Sachin Tendulkar (romitmehta.com)
- Found something interesting by exploring a “List of companies by revenue” Data Set: (parasdoshi.com)