It’s just been a couple of days since this add-in has been released and I can’t seem to stop using it. I guess I haven’t been this excited about a Microsoft offering since SSRS 2008 R2 (well, I am really passionate about Tabular and PowerPivot now but I wasn’t that hooked onto those technologies when they were released). As a BI consultant, blogger and speaker, I traverse through lots and loads of open data – sometimes trying to get that extra piece of open data which will add value to my clients’ existing data or to get some interesting stuff for my blogs and presentations. Data Explorer might not be the perfect solution, but it sure does a splendid job of reducing my efforts in searching, shaping and preparing my data. I thought of sharing my experiences in the form of a quick introduction to Data Explorer Preview for Excel. 
1) What is Data Explorer Preview for Excel and where can I get it from? Microsoft “Data Explorer” Preview for Excel is an add-in for Excel 2013 which provides an intuitive user interface for data discovery, data transformation and enrichment. You can download the add-in from here. 2) Basic Tutorial for Data Explorer You can import data into excel from a wide variety of sources using Data Explorer and the complete list of sources is given here. For the purpose of this demo, I am going to be connecting to my favourite source which is the ‘web page’ source. a) After installing the add-in, you should be able to see the Data Explorer tab in Excel (if not, go to File—>Options—>Add-Ins—>Com Add-Ins and enable the Data Explorer add-in). Click on the From Web option and enter the url – http://www.nuforc.org/webreports/ndxloc.html as shown below. 
b) Now click on Table 0 in the Navigator pane and you should be able to see the UFO sightings by states. The table has some non-US data and so to filter them, click on the dropdown in the reports column and unselect them. Click on OK when you are done. 
c) Rename the columns as States and UFO Sightings respectively. Notice that the Steps pane on the right shows the modifications you are making and you can expand the pane. 
d) Click on Done and now you can see the data in Excel. This data can be used as a source for your charts / tables and in the image below, I have used the “Geographic Heat Map” Office app to visualize the same. Looks like the aliens have taken a liking for California! 
3) Interesting Features a) Online Search:- I spend a lot of time trying to search for open datasets and this feature is surely going to reduce that time. You can search for data right from Excel by clicking on the Online Search button. For eg, if I search for ‘richest states’, the results as shown below. 
Just scroll your mouse over the results and click on Use to add the data. b) Filter & Shape Data:- The ability to filter and transform your data is what makes this so useful. You can just click on the Filter & Shape button to start the process as shown below. 
For eg, if you just want to show the income for 2011, you can hide the rest of the columns as shown below. 
You can also do a lot of other operations like Splitting columns, removing duplicates, replacing values, changing types, group-by, etc. 
You can read the complete list from here. Hopefully, there will be more additions to this in the future (I could definitely use a Unpivot / Pivot option). c) Merging & Appending from Multiple Sources:- This is another killer feature in Data Explorer. You can merge or append from multiple sources. For eg, if we have to merge the two sheets that we just created, click on the Merge button and then select the primary and secondary tables from the dropdown. 
Then select the matching columns from both the tables and click on Apply. 
Now, to display the additional columns, click on the expand icon on the New Column and select the UFO sightings. Click on OK. 
Now you have combined both the sources and can use it for your visualization. I have visualized the same data using GeoFlow below. 
4) Further Reading There’s lot of interesting stuff you can do with Data Explorer and if this post caught your interest, make sure to check the below ones too
1) Data Explorer Team – Announcing Microsoft “Data Explorer” Preview for Excel
2) Chris Webb – Importing Data From Multiple Log Files Using Data Explorer
3) Chris Webb – Calling A Web Service From Data Explorer, Part 1
4) Jamie Thomson – Traversing the Facebook Graph using Data Explorer
5) Matt Masson – Access the Windows Azure Marketplace from Data Explorer
6) Jake Smillie – Best Oscar winning Film? My first Data Explorer adventure…
7) Ian Morrish – SharePoint OData and the Excel Data Explorer
8) Data Explorer Help
Updates
Hey Jason,
Great article! Regarding your comment about "traverse through lots and loads of open data – sometimes trying to get that extra piece of open data which will add value to my clients’ existing data or to get some interesting stuff for my blogs and presentations.". I'd love to introduce our DaaS platform to you, http://www.quandl.com
Quandl has over 4 million datasets that can be pulled in through DE, or other tools and packages. We're currently trying to figure out how it can integrate best, but for you and any readers looking to try it out, any of our datasets can be used with it right now. As well, if anyone has any thoughts on how it could be done best, would love to hear them.