What it does: Getting your data into analysis-ready format can be half the battle (or more) when using a spreadsheet. Microsoft Data Explorer Preview for Excel is designed to help by easing data import from a variety of sources. You can then massage the data in different ways, from reshaping to filtering and grouping. It also simplifies merging tables on a common column
The beta runs on all desktop versions of Excel 2010 and 2013.
Note: As of July 2013, the Data Explorer beta became Power Query and does not run on all versions of Excel 2010 and 2013. For Excel 2013, Microsoft Office 2013 Professional Plus, Office 365 ProPlus or Excel 2013 Standalone are required. Microsoft Office 2010 Professional Plus with Software Assurance is needed for Excel 2010.
What's cool: This plug-in handles an impressively large array of data formats such as HTML tables, CSV, XML, Hadoop, Active Directory and, yes, Facebook. It will import from multiple databases including MySQL, PostgreSQL, Oracle and IBM DB2 as well as Microsoft's own SQL Server, Azure and Access; and is also designed to help shape "semi-structured" plain text into a better format for analysis.
Enter a URL and Data Explorer will come back with a list of tables in the Web page; select a table and the data will immediately import into Excel. While you can import HTML tables from "regular" Excel without an add-on, I didn't always find that to be an elegant process. Data Explorer, on the other hand, neatly lists the tables in a column to the left of your document for one-click import.
You can search and replace, filter, query and re-shape the data before import, such as hiding some columns or "promoting" some rows to headers. You can join tables on a common column when importing data(update: that relationship needs to be defined elsewhere, such as SQL Server or PowerPivot), selecting just the columns you'd like to pull into your spreadsheet. Data Explorer keeps track of your data-manipulation steps so you can undo back to a certain point or re-use a query with another data set(features enjoyed by users of OpenRefine, the open-source data-cleaning tool formerly known as Google Refine). In fact, you can even combine queries on a new data source.
There are common date reformats and easy data-type changes as well as pivot-table-style data groupings and filters.
Drawbacks: Documentation is somewhat limited. For example, my Data Explorer help seemed to only show report formatting advice -- font, background color and so on, not my top need when doing data work. Especially if you're not already a user of other Microsoft analysis products, this may be an issue.
One data modeling option within Data Explorer requires the PowerPivot add-on, which in Office 2013 is only available for Office Professional Plus, Office 365 ProPlus or Office 365 Enterprise (PowerPivot also works with Excel 2010).
There's a "write query from scratch" option in Data Explorer but I didn't see any documentation within the application telling me how to construct one; I had to dig up the 114-page Data Explorer Formula Language Specification PDF online. This is a beta product, though; hopefully documentation will improve in future versions.
Alas the query/formula language to used with Data Explorer isn't SQL; it's something new to learn, the "Data Explorer formula Language (informally known as 'M')."
Data Explorer only works on Windows, not Excel for Mac.
Skill level: Advanced beginner to intermediate -- probably on a similar level as PowerPivot or Access, if not a little easier.
Bottom line: While still a bit rough around the edges, Data Explorer is a welcome addition to Microsoft's suite of data tools -- especially for Excel users who would like to do more with data inside that platform.
Examples and learn more: The main Data Explorer Help page has links to various how-to's. It's also worth checking out two tutorials: a simple one showing how to import and query data from a Web page and a more complex tutorial that goes step by step through importing data from multiple sources, joining tables on common columns and grouping, sorting and filtering results.
For more data tools, see my chart of 30+ free tools for data visualization and analysis.
A look at the Data Explorer query window