Quickly Crunching SharePoint Data with PowerPivot Using Only Your Mouse

To paraphrase a wise man, once you’ve got PowerPivot, “all your SharePoint’s are belong to us”. 

I’m going to demonstrate how, using Excel 2013 (which has PowerPivot built right in – you just have to enable it), and SharePoint 2013 you can really easily be crunching some of your SharePoint data, without ever having to know anything more about your data than how to get a good view of it – i.e. no code, no knowledge of OData, you wont even have to know how to use a keyboard (though that may come in handy at some point in the future).  If you have a mouse, Excel (with PowerPivot), and SharePoint, you’re all set.

Note: If you have Excel 2010, you’ll need to add the PowerPivot download (which is free), and the steps won’t necessarily be quite as simple, but you should be able to manage to get to the same point.  Also, the same steps may work for SharePoint 2010, but I haven’t tried it – so again, things may be slightly different.

Basic Ingredients

Starting Point: 2 related pieces of data (such as lists) in SharePoint

In my case, I have a list of issues and a list of clients.  Each issue is assigned to a client, so the relationship between the lists is the “client”.  In our case, we’ve simplified our approach by making the value “client” in our issue list be a single line of text, instead of a lookup – but the same approach should work if you were to relate the data by client “id” instead of client “name”, you’d just need to be sure that your view for your clients contained the “id” column.

Issues list:

Client list:

Obviously for privacy reasons, I’ve had to blur out some details, but as you should be able to identify, each list has a number of different items, some issues are assigned to client 1, some to client 2, and so on.

Note that the more columns you have in your views, the more data you’ll be passing along to excel to be able to slice and dice against.  This is a good thing, so if you want to make a custom view for each list of data, with a whole whack of useful columns, go right ahead.

Export the Data to Excel

Now in each list, we’re going to export the data to Excel (we’ll just put each piece of data into a table in a separate worksheet).

Start by going to the list tab, and selecting Export to Excel.

You can start with either list, it doesn’t matter which on you do first, in my case, I’m going to start with the Client list.  You’ll likely be prompted in your browser as to whether or not you want to open the file, just go ahead with that.

Then, Excel will likely give you a nice warning too.  Click Enable here.

Now you’ve got the same client data in Excel as you had in SharePoint.

If your tab at the bottom looks like “owssvr”, that’s ok, it’s just a name, you can change the name if you’d like (but this would require a keyboard).

Now go back to SharePoint and export your other list.  You’ll get the same Security Notice as above, but then you’ll encounter a new dialog (Import Data), seen below.

In our case, we want to import this data again as a table (that’s what the first set of data came in as), and in a “New worksheet”, which will keep it together in the same workbook as the first set of data.

PowerPivot-ize the Data

Next, we’re going to use PowerPivot to relate the two pieces of data together, so we can make a pretty table.

Go back to your client data (sheet), and from the ribbon, find the PowerPivot tab, and choose “Add to Data Model”.

A “PowerPivot for Excel” window will open, and that same data will be populated into the window.  Switch back to the Excel window, and select your other set of data.  Again, go to the PowerPivot tab, and choose “Add to Data Model”.

You’ll now have two tabs in your PowerPivot window (seen below).

Relating the Data

Now, in the upper right of the ribbon in the PowerPivot window, find and select “Diagram View”.

You’ll now see your tables side by side.

Now we want to add the relationship between Client in the first table, and Client in the second table.  Simply grab the Client column in the left table, and drag and drop it onto the Client column in the right table.

Note: if you get the weird “strikethrough” cursor (below), just ignore it, trust me, you’re doing the right thing – it’ll make sense in a moment.

Below, matching Client with Client.

Finally, when you’re complete, and you’ve made a logical pairing, you get a fancy directional arrow between the two tables.  Admire it – but only for a moment.

Making a Pretty Table

Now, go back to the ribbon (in the PowerPivot Window) and choose PivotTable.

When prompted for where to put the data, choose “New Worksheet”.  You’ll be sent back to the Excel window at this point (we’re now done with the PowerPivot window for this exercise).

Now, in the right hand column, back in your Excel window, you’ll notice a means of adding “PivotTable Fields”.  Check a couple that make sense for your data (seen below).

In my case (below), the PivotTable looks kind of goofy with the “Assigned To” nested under the client.

So all you do is drag and drop the “Assigned To” column from the rows collection, to the values collection.

Voila!  A pretty table, with data counts, and all you used was your mouse.

Hopefully this can help to get you started crunching some SharePoint data on your own, and given how easy it is to get started, you’ll be able to start getting some additional value out of your data unlike ever before!

As an Extra Bonus

The next phase would be to take this same data and turn it into a chart.  Just go into the Insert tab, and select “PivotChart”.

Choose a chart that makes sense to your data.

And voila!  With just a few more clicks, you’re now rockin’ a pie chart!  (Yes, for all you curious folks, the big blue slice at the bottom is that “251” assigned to itgroove)

In Summary

SharePoint + Simple Data Views + Excel with PowerPivot + A Mouse = Pretty Table & Rockin’ Pie.