In this blog post, we’ll see how you can remove duplicated and clean data in excel tables using Data Explorer Add-in.
Problem:
Our Excel Table has following Data:
Month | Month Name |
1 | January |
1 | January |
1 | January |
2 | February |
2 | February |
3 | March |
And we want to remove duplicates to make the data-set look like this:
Month | Month Name |
1 | January |
2 | February |
3 | March |
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.
Solution:
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
Conclusion:
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
Note:
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.
Related articles
- 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)
0 thoughts on “Remove Duplicates in Excel Tables using Data Explorer Add-in:”