skip to Main Content


In My Opinion, There Aren’t Many Things More Exciting Than a New Ribbon Tab Full of Goodies
(And One That I Can Add to Excel 2010 or 2013 – I Hear That’s Important for Some Reason)

Maybe There’s a Future in this “Numbers” Thing…

It does seem like Microsoft has figured out that data is a big deal.  Every time I turn around, I am hearing of a new software development team joining Microsoft’s efforts in the Excel/BI/Overall Data Crunching space.

Often, such teams are merely whispers – shadowy rumors on the wind.  Friends disappear from their familiar roles and reappear in places they can’t talk about.

Other times, someone new to me walks up and just hands me a piece of nearly-finished software.

This is one of those latter cases.

Some Highlights

I don’t have time for a full tour today, and honestly I haven’t even explored all of the functionality yet.  So let’s hit some highlights shall we?

1) It’s another free download

You can go grab it right now.  Everything I am showing in this post, you can also do on your own desktop.

Download it here.

2) It works with 2010 or 2013.

So… those of us sitting tight on 2010 can still use it. You will note that my screenshots come from 2010.  That is no accident – it sure sounds like most of you are staying 2010 for now and I see no reason to blog things you aren’t using.

2) Technically it’s still a preview

A beta, in other words, which in many ways is even better, because we can provide feedback.


You see these buttons?  These are AWESOME.  Use these.  Real, live human beings read what you send, and sometimes even respond.  This is how software should be done:  put something out there, make it easy to provide feedback, engage with the customers when the feedback comes in.

3) Grab data from New Sources,  Like XML, Facebook, & File Folders

Loading Hadoop, Active Directory, and Facebook data into PowerPivot is Now Simple  Loading XML and File/Folder Data into PowerPivot is Now Simple

* = Sorta New.  ** = Really New.


Talk About Timely – This Request for File/Folder Import
Came in On LinkedIn Just Yesterday

4) All of these can be pulled into PowerPivot as refreshable connections

By default in 2010, it will load the data into an Excel sheet, which is not where us PowerPivot Pros want it.

No worries though, when the query pane pops up, turn this setting off:


And then go to the PowerPivot window.  Under Existing COnnections you will find that the Data Explorer queries are available:


Select This and Click “Open”

This lets you “land” the data in PowerPivot:


Mission-Critical Facebook Data:  Now Available in PowerPivot
(Yes, Actually Useful When it’s Your Company’s Facebook Page etc.)

5) More to Come

I haven’t even clicked these highlighted buttons yet.  No time for now, will revisit next week:


Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 19 Comments
  1. Hmm… if the Merge actual does merge multiple source that would be useful. I started using this yesterday and have already gained some great insights about our company Facebook interactions. I think I’ll go data hunting this afternoon 🙂

  2. It seems you cannot use it to import from SQL Server unless it supports encryption. Not sure why that would be since it is not necessary when using Excel’# built in tools.

    1. I’ve asked some folks from the team at MS to come respond to this. I have not used Data Explorer to import from SQL – I just do that in PowerPivot itself, and encryption is not required there.

      1. OK, cool. I already sent a sad face, along with one for getting an error saying that ‘Element’ was an invalid XmlNodeType when trying to import from a workbook containing PowerPivot data.

    2. Hi Jaffa,

      This is Miguel, from the Data Explorer team. Thanks for your feedback, this is a known issue that we will address in one of our next updates… In the future we will let the user decide/specify whether to use encryption or not.

      If you want we can follow up on our Forums, or send us Smile/Frown emails using Data Explorer so it’s easier for us to track the conversation. 🙂


        1. Yeah, and it’s 7:30 am for them. Which makes NO SENSE whatsoever to me. At 7:30 am when I worked in Redmond, I was still in deep REM sleep 🙂

          1. It’s the life of a PM… I have to get to the office early to get stuff done before going into a gazillion hours of meetings for the rest of the day… 🙂

  3. For step 4, it worked for me in an opposite way, i needed to set load to worksheet on in order to be able to see in powerpivot existing connections. Anyway, thank you very much for great tips.

  4. Just discovered this today and I love it… just wondering if there is a way to import a “data feed” into Data Explorer as you can with Power Pivot. We have quite a few Reporting Services reports that I draw from but I can’t see anyway to use the generated .atomsvc as you can with Power Pivot import. I have been waiting for a lightweight ETL tool for ages ….. hooray for the ability to unpivot!!!

  5. I’ve downloaded Data Explorer and run the download query with “load to worksheet” set to off, then gone into PowerPivot existing connections, but cannot see any connection to the Data Explorer query. Any ideas why this might be?

  6. You need to have load to worksheet turned on to see the Data Conn in PP. Once you make the PP query you can turn off the load to worksheet function and it refreshes okay in PP.

Leave a Comment or Question