{"id":673,"date":"2013-03-07T00:06:11","date_gmt":"2013-03-07T07:06:11","guid":{"rendered":"https:\/\/mmman.itgroove.net\/?p=673"},"modified":"2023-02-24T21:48:48","modified_gmt":"2023-02-24T21:48:48","slug":"quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse","status":"publish","type":"post","link":"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/","title":{"rendered":"Quickly Crunching SharePoint Data with PowerPivot Using Only Your Mouse"},"content":{"rendered":"

To paraphrase a wise man, once you\u2019ve got PowerPivot, \u201call your SharePoint\u2019s are belong to us\u201d.  <\/p>\n

I\u2019m going to demonstrate how, using Excel 2013 (which has PowerPivot built right in \u2013 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 \u2013 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\u2019re all set.<\/p>\n

Note: If you have Excel 2010, you\u2019ll need to add the PowerPivot download (which is free), and the steps won\u2019t 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\u2019t tried it \u2013 so again, things may be slightly different.<\/p>\n

Basic Ingredients<\/h2>\n

Starting Point<\/strong>: 2 related pieces of data (such as lists) in SharePoint<\/p>\n

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 \u201cclient\u201d.  In our case, we\u2019ve simplified our approach by making the value \u201cclient\u201d in our issue list be a single line of text, instead of a lookup \u2013 but the same approach should work if you were to relate the data by client \u201cid\u201d instead of client \u201cname\u201d, you\u2019d just need to be sure that your view for your clients contained the \u201cid\u201d column.<\/p>\n

Issues list:<\/h3>\n

\"image\"<\/a><\/p>\n

Client list:<\/h3>\n

\"image\"<\/a><\/p>\n

Obviously for privacy reasons, I\u2019ve 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.<\/p>\n

Note that the more columns you have in your views, the more data you\u2019ll 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.<\/p>\n

Export the Data to Excel<\/h2>\n

Now in each list, we\u2019re going to export the data to Excel (we\u2019ll just put each piece of data into a table in a separate worksheet).<\/p>\n

Start by going to the list tab, and selecting Export to Excel.<\/p>\n

\"image\"<\/a><\/p>\n

\"image\"<\/a><\/p>\n

You can start with either list, it doesn\u2019t matter which on you do first, in my case, I\u2019m going to start with the Client list.  You\u2019ll likely be prompted in your browser as to whether or not you want to open the file, just go ahead with that.<\/p>\n

\"image\"<\/a><\/p>\n

Then, Excel will likely give you a nice warning too.  Click Enable here.<\/p>\n

\"SNAGHTML79d6b57\"<\/a><\/p>\n

Now you\u2019ve got the same client data in Excel as you had in SharePoint.<\/p>\n

\"image\"<\/a><\/p>\n

If your tab at the bottom looks like \u201cowssvr\u201d, that\u2019s ok, it\u2019s just a name, you can change the name if you\u2019d like (but this would require a keyboard).<\/p>\n

\"image\"<\/a><\/p>\n

Now go back to SharePoint and export your other list.  You\u2019ll get the same Security Notice as above, but then you\u2019ll encounter a new dialog (Import Data), seen below.<\/p>\n

\"SNAGHTML7a6211e\"<\/a><\/p>\n

In our case, we want to import this data again as a table (that\u2019s what the first set of data came in as), and in a \u201cNew worksheet\u201d, which will keep it together in the same workbook as the first set of data.<\/p>\n

PowerPivot-ize the Data<\/h2>\n

Next, we\u2019re going to use PowerPivot to relate the two pieces of data together, so we can make a pretty table.<\/p>\n

Go back to your client data (sheet), and from the ribbon, find the PowerPivot tab, and choose \u201cAdd to Data Model\u201d.<\/p>\n

\"image\"<\/a><\/p>\n

A \u201cPowerPivot for Excel\u201d 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 \u201cAdd to Data Model\u201d.<\/p>\n

You\u2019ll now have two tabs in your PowerPivot window (seen below).<\/p>\n

\"image\"<\/a><\/p>\n

Relating the Data<\/h2>\n

Now, in the upper right of the ribbon in the PowerPivot window, find and select \u201cDiagram View\u201d.<\/p>\n

\"image\"<\/a><\/p>\n

You\u2019ll now see your tables side by side.<\/p>\n

\"image\"<\/a><\/p>\n

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.<\/p>\n

Note: if you get the weird \u201cstrikethrough\u201d cursor (below), just ignore it, trust me, you\u2019re doing the right thing \u2013 it\u2019ll make sense in a moment.<\/p>\n

\"image\"<\/a><\/p>\n

Below, matching Client with Client.<\/p>\n

\"image\"<\/a><\/p>\n

Finally, when you\u2019re complete, and you\u2019ve made a logical pairing, you get a fancy directional arrow between the two tables.  Admire it \u2013 but only for a moment.<\/p>\n

\"image\"<\/a><\/p>\n

Making a Pretty Table<\/h2>\n

Now, go back to the ribbon (in the PowerPivot Window) and choose PivotTable.<\/p>\n

\"image\"<\/a><\/p>\n

When prompted for where to put the data, choose \u201cNew Worksheet\u201d.  You\u2019ll be sent back to the Excel window at this point (we\u2019re now done with the PowerPivot window for this exercise).<\/p>\n

\"SNAGHTML7c0ac00\"<\/a><\/p>\n

Now, in the right hand column, back in your Excel window, you\u2019ll notice a means of adding \u201cPivotTable Fields\u201d.  Check a couple that make sense for your data (seen below).<\/p>\n

\"image\"<\/a><\/p>\n

In my case (below), the PivotTable looks kind of goofy with the \u201cAssigned To\u201d nested under the client.<\/p>\n

\"image\"<\/a><\/p>\n

So all you do is drag and drop the \u201cAssigned To\u201d column from the rows collection, to the values collection.<\/p>\n

\"image\"<\/a><\/p>\n

Voila!  A pretty table, with data counts, and all you used was your mouse.<\/p>\n

\"image\"<\/a><\/p>\n

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\u2019ll be able to start getting some additional value out of your data unlike ever before!<\/p>\n

As an Extra Bonus<\/h2>\n

The next phase would be to take this same data and turn it into a chart.  Just go into the Insert tab, and select \u201cPivotChart\u201d.<\/p>\n

\"image\"<\/a><\/p>\n

Choose a chart that makes sense to your data.<\/p>\n

\"image\"<\/a><\/p>\n

And voila!  With just a few more clicks, you\u2019re now rockin\u2019 a pie chart!  (Yes, for all you curious folks, the big blue slice at the bottom is that \u201c251\u201d assigned to itgroove)<\/p>\n

\"image\"<\/a><\/p>\n

In Summary<\/h2>\n

SharePoint + Simple Data Views + Excel with PowerPivot + A Mouse = Pretty Table & Rockin\u2019 Pie.<\/p>\n","protected":false},"excerpt":{"rendered":"

To paraphrase a wise man, once you\u2019ve got PowerPivot, \u201call your SharePoint\u2019s are belong to us\u201d.  I\u2019m going to demonstrate how, using Excel 2013 (which has PowerPivot built right in \u2013 you just have to enable it), and SharePoint 2013 you can really easily be crunching some of your SharePoint data, without ever having to … <\/a><\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[17,21,30,7],"tags":[],"acf":[],"yoast_head":"\nQuickly Crunching SharePoint Data with PowerPivot Using Only Your Mouse - Archive<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Quickly Crunching SharePoint Data with PowerPivot Using Only Your Mouse - Archive\" \/>\n<meta property=\"og:description\" content=\"To paraphrase a wise man, once you\u2019ve got PowerPivot, \u201call your SharePoint\u2019s are belong to us\u201d.  I\u2019m going to demonstrate how, using Excel 2013 (which has PowerPivot built right in \u2013 you just have to enable it), and SharePoint 2013 you can really easily be crunching some of your SharePoint data, without ever having to …\" \/>\n<meta property=\"og:url\" content=\"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/\" \/>\n<meta property=\"og:site_name\" content=\"Archive\" \/>\n<meta property=\"article:published_time\" content=\"2013-03-07T07:06:11+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-02-24T21:48:48+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/mmman.itgroove.net\/wp-content\/uploads\/mmman\/2013\/03\/image_thumb11.png\" \/>\n<meta name=\"author\" content=\"Colin Phillips (Alumni)\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Colin Phillips (Alumni)\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/\",\"url\":\"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/\",\"name\":\"Quickly Crunching SharePoint Data with PowerPivot Using Only Your Mouse - Archive\",\"isPartOf\":{\"@id\":\"https:\/\/regroove.ca\/archive\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/mmman.itgroove.net\/wp-content\/uploads\/mmman\/2013\/03\/image_thumb11.png\",\"datePublished\":\"2013-03-07T07:06:11+00:00\",\"dateModified\":\"2023-02-24T21:48:48+00:00\",\"author\":{\"@id\":\"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b\"},\"breadcrumb\":{\"@id\":\"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/#primaryimage\",\"url\":\"http:\/\/mmman.itgroove.net\/wp-content\/uploads\/mmman\/2013\/03\/image_thumb11.png\",\"contentUrl\":\"http:\/\/mmman.itgroove.net\/wp-content\/uploads\/mmman\/2013\/03\/image_thumb11.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Blog Archive\",\"item\":\"https:\/\/regroove.ca\/archive\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Quickly Crunching SharePoint Data with PowerPivot Using Only Your Mouse\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/regroove.ca\/archive\/#website\",\"url\":\"https:\/\/regroove.ca\/archive\/\",\"name\":\"Archive\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/regroove.ca\/archive\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b\",\"name\":\"Colin Phillips (Alumni)\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/regroove.ca\/archive\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g\",\"caption\":\"Colin Phillips (Alumni)\"},\"url\":\"https:\/\/regroove.ca\/archive\/author\/cphillips\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Quickly Crunching SharePoint Data with PowerPivot Using Only Your Mouse - Archive","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/","og_locale":"en_US","og_type":"article","og_title":"Quickly Crunching SharePoint Data with PowerPivot Using Only Your Mouse - Archive","og_description":"To paraphrase a wise man, once you\u2019ve got PowerPivot, \u201call your SharePoint\u2019s are belong to us\u201d.  I\u2019m going to demonstrate how, using Excel 2013 (which has PowerPivot built right in \u2013 you just have to enable it), and SharePoint 2013 you can really easily be crunching some of your SharePoint data, without ever having to …","og_url":"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/","og_site_name":"Archive","article_published_time":"2013-03-07T07:06:11+00:00","article_modified_time":"2023-02-24T21:48:48+00:00","og_image":[{"url":"http:\/\/mmman.itgroove.net\/wp-content\/uploads\/mmman\/2013\/03\/image_thumb11.png"}],"author":"Colin Phillips (Alumni)","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Colin Phillips (Alumni)","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/","url":"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/","name":"Quickly Crunching SharePoint Data with PowerPivot Using Only Your Mouse - Archive","isPartOf":{"@id":"https:\/\/regroove.ca\/archive\/#website"},"primaryImageOfPage":{"@id":"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/#primaryimage"},"image":{"@id":"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/#primaryimage"},"thumbnailUrl":"http:\/\/mmman.itgroove.net\/wp-content\/uploads\/mmman\/2013\/03\/image_thumb11.png","datePublished":"2013-03-07T07:06:11+00:00","dateModified":"2023-02-24T21:48:48+00:00","author":{"@id":"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b"},"breadcrumb":{"@id":"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/#primaryimage","url":"http:\/\/mmman.itgroove.net\/wp-content\/uploads\/mmman\/2013\/03\/image_thumb11.png","contentUrl":"http:\/\/mmman.itgroove.net\/wp-content\/uploads\/mmman\/2013\/03\/image_thumb11.png"},{"@type":"BreadcrumbList","@id":"https:\/\/regroove.ca\/archive\/2013\/03\/07\/quickly-crunching-sharepoint-data-with-powerpivot-using-only-your-mouse\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Archive","item":"https:\/\/regroove.ca\/archive\/"},{"@type":"ListItem","position":2,"name":"Quickly Crunching SharePoint Data with PowerPivot Using Only Your Mouse"}]},{"@type":"WebSite","@id":"https:\/\/regroove.ca\/archive\/#website","url":"https:\/\/regroove.ca\/archive\/","name":"Archive","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/regroove.ca\/archive\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b","name":"Colin Phillips (Alumni)","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/regroove.ca\/archive\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g","caption":"Colin Phillips (Alumni)"},"url":"https:\/\/regroove.ca\/archive\/author\/cphillips\/"}]}},"_links":{"self":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts\/673"}],"collection":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/comments?post=673"}],"version-history":[{"count":1,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts\/673\/revisions"}],"predecessor-version":[{"id":1967,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts\/673\/revisions\/1967"}],"wp:attachment":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/media?parent=673"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/categories?post=673"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/tags?post=673"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}