Data Explorer for Microsoft Excel 2010

There’s a new add-in for Excel called Data Explorer. Currently it’s in preview (beta), so use at your own discretion and risk.

What is it?

Well the name really does say it all – it lets you explore data. Think of the current mechanisms we have in Excel for getting data (that doesn’t require complex VBA code or 5 days of research). Have you heard the term “self-service BI”? That’s basically what it is. Take what we currently have for data connections in Excel, give it some really cool stuff under the hood, give it a face-lift, and what you get is Data Explorer. Microsoft announced it on their MSDN blog (here).

Rob Collie blogged about it last week (here). Yes I’m late to the game. It’s been on my radar since it came out, it just went into the queue. I’ve been able to play a little with it, but not explore all of the functionality yet. So far it’s pretty cool, although it needs a little work.

How much does it cost?

Nothing. It’s a free download. Some of you may be thinking, “wasn’t PowerPivot free to start with in 2010, and then scaled back to ProPlus SKU’s only?” You would be correct, and while we can’t see the future, I’m holding out hope this one will not only stay free, but work its way into the client default install.

How do I get it?

Here is the download link:

http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx

Please be sure to install the right version, as there are two. If you have 32-bit Office, you need to install the 32-bit version of Data Explorer. Likewise, if you have 64-bit Office, you’ll need the 64-bit version of the add-in.

What does it do?

There are some cool new features. Let’s take a look at the ribbon.

As you can see there are some familiar faces, like From Web, From Database, From Other Sources, etc. I happen to have Excel 2010 installed side-by-side with 2013, and that gets the add-in when installed too.

Why does Data Explorer get reversed with PowerPivot in the two versions? I have no idea, it’s weird for sure, thought it was worth the mention though.

Get External Data

Here are the menus from the Get External Data group.

From File

The first thing I noticed here was “From Folder”. Description says it imports files from a folder. Wow, very cool. In testing, it kinda-sorta does this. First you must browse for a folder.

Clicking Browse brings up a pretty standard folder browse dialog box.

By clicking OK you will see that path in the text box. Click Apply to initiate the query.

The query will look something like this:

There’s a lot of information there. Basically it brings back a query table of the file attributes of all files in all subfolders. Pretty handy!

If you notice the Content and Attributes fields of data look different, that’s because those are hyperlinks. If you click on the Binary link you’ll see a list of the data sources. In this case I only have a single table in each of the files, one sheet per book.

Again, the data table has a link. Clicking on that will get you the query of your data.

You can adjust this query by filtering or sorting any field. Also, you can set query parameters by clicking the data sheet icon in the upper-left corner of the query data.

With these options you can set to use the first row as headers or not, manually insert columns (fields), as well as keep or remove certain ranges. When finished, click the Done button in the lower-right corner.

You’ll then see your data in your worksheet (as a table). It will get a new table name which you won’t be able to change (the option is grayed out on the ribbon). However, you will notice when you select a table now, instead of a single tab on that ribbon, you have an additional tab labeled QUERY.

The Refresh button will obviously refresh your data. When it’s actively refreshing your table color will gray out temporarily.

INFO

In the preview, after refreshing the table style will go back to a default of ‘Table Style Medium 7’, regardless of any style you have applied, and also regardless of any default (table) style you have set.

I’m hoping the From Folder feature gets more work in the future. It’s a very cool part of the tool, but has a way to go before it becomes extremely useful, or the next big thing you can’t live without.

From Database

From Other Sources

There are some new faces in this menu. While I haven’t had time to explore all of these, the From Facebook really got my attention. The red circled item, Write Query, does not show by default. To show that you must click Settings and check the box that says Enable Advanced Query Editing.

To start with, you must enter your username and what connection you want to retrieve. For this example I’m using my page, and going to just grab the feed (which is very small, since I just started it last month – and you can Like it if you want, I won’t stop ya).

At this time in the preview, not all connections work for me. I’ve only tested with my page, not my personal profile. You’ll have to login first.

After logging in, clicking the Apply button will get you your feed.

One this is done, really all you need to do is refresh the query to get the latest feed. You can data mine all you want off this queried table. Makes it quite nice to run your own analytics on it!

Right-clicking any field (column header) will yield you some options.

Once you have your query into Excel, there are some things to take note of:

First (1) is the new ribbon tab we talked about earlier (2), as well as the query settings (3), which you can toggle off in the View group of the TABLE TOOLS

QUERY tab. One important thing to note in the Query Settings task pane is the Load to data model button. If you click that, once complete, you won’t get any fancy message boxes, just a simple text display.

What’s the benefit of this? Well you get all of this data in its glory into PowerPivot!

Update!

Since this is a preview, expect updates. In fact, there is already an update available (blogged about here). A super nice feature built into this add-in is the update feature. If you look at the DATA EXPLORER ribbon tab, look to the Options group, there is a button that says Update. If that is grayed out, no update is available. The only time that becomes active is if there is an update available. So really the only thing you need to do is ignore that button until it’s enabled. You’ll be taken to the website for downloading the new version (currently as an MSI install file). You will have to quit all Excel sessions to install it.