{"id":549,"date":"2019-04-16T18:29:34","date_gmt":"2019-04-16T15:29:34","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=549"},"modified":"2019-05-06T12:49:35","modified_gmt":"2019-05-06T09:49:35","slug":"using-the-powerpivot-add-in-with-excel","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/using-the-powerpivot-add-in-with-excel\/","title":{"rendered":"Using the PowerPivot add-in with Excel"},"content":{"rendered":"\n<p>Although <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/learning-about-pivot-tables\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"Pivot tables (opens in a new tab)\">Pivot tables<\/a><\/strong> are very powerful, in the professional editions of excel there is one optional add-in more powerful and versatile than any other. <\/p>\n\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/OGx-1rFfoko?version=3&#038;rel=0&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-large-font-size\"><strong>Practice File:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/power-pivot-basic.xlsx\">power pivot basic<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/power-pivot-basic.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Transcript<\/strong><\/p>\n\n\n\n<p>It is called Power\nPivot and in order to use it you have to first enable it.<\/p>\n\n\n\n<p>Let\u2019s do just that.<\/p>\n\n\n\n<p>We click on the file\nTab of the ribbon and then on options. There we select Add-ins. From the\ndropdown control we choose COM add-ins and we are presented with a list. If it\nis installed in our version of office, we should see the Microsoft Power Pivot\nfor Excel on that list. We select it and click on OK.<\/p>\n\n\n\n<p>A new tab appeared on\nour ribbon called Power Pivot. We click on it. <\/p>\n\n\n\n<p>To be able to use\nPower Pivot we have to add some data to the data model.<\/p>\n\n\n\n<p>Data model is a group\nof tables from a lot of different sources that can be combined by creating\nrelationships. <\/p>\n\n\n\n<p>So let\u2019s add some data\nto our data model.<\/p>\n\n\n\n<p>In the current sheet\nwe have two different tables. We will add them both in the data model. We can\nadd tables from different workbooks and different sources to our model. For\nsimplicity reasons in this example we have both our data sources on the same\nsheet.<\/p>\n\n\n\n<p>We select the first\ntable and in the power pivot tab we click on \u201cadd to data model\u201d button.<\/p>\n\n\n\n<p>Our table has headers\nand Power Pivot opens showing it to us. Nothing impressive till now.<\/p>\n\n\n\n<p>Let\u2019s jump back to\nexcel.<\/p>\n\n\n\n<p>We add the second\ntable to the data model<\/p>\n\n\n\n<p>The second table\nappears in Power Pivot. We can see the previous table on the other tab.<\/p>\n\n\n\n<p>We click on the button\n\u201cDiagram view\u201d. We see a diagram of our two tables.<\/p>\n\n\n\n<p>Let\u2019s create our first\nrelationship. <\/p>\n\n\n\n<p>Relationship between\ntables means that one field from one table is related with one field from\nanother, thus the records with the same value on that field on each table are\njoined to each other.<\/p>\n\n\n\n<p>The common fields, and\nso the most likely to relate, are the product fields. We drag the field from\ntable1 to the field on table2. The relationship is created.<\/p>\n\n\n\n<p>We can create a\nrelationship without using the diagram view through a button of the design tab\ncalled \u201cCreate relationship\u201d.<\/p>\n\n\n\n<p>Let\u2019s remove the\nrelationship first. We right click on the line and select remove.<\/p>\n\n\n\n<p>We now click on the\ncreate relationship button and select the product field on both tables. <\/p>\n\n\n\n<p>We click ok and the\nrelationship is there again.<\/p>\n\n\n\n<p>The button manage\nrelationships shows us a list of all our relationships and gives us the ability\nto edit or delete them.<\/p>\n\n\n\n<p>But what is the\npractical use of all these.<\/p>\n\n\n\n<p>We click on the home\ntab and then on pivot Table button. We have various choices. Let\u2019s choose the\nfirst one. <\/p>\n\n\n\n<p>The popup asks us\nwhere to place our pivot table. We choose the same sheet and after we click on\nok we see the familiar environment of pivot tables.<\/p>\n\n\n\n<p>With a small addition.\nThe field list now consists of the fields in both our tables.<\/p>\n\n\n\n<p>We select the price\nper item for columns and the product field for rows from table2. The sum of\nquantity, form table 1, is our values and the pivot table is ready.<\/p>\n\n\n\n<p>As far as pivot tables\nare concerned this is not a so useful one. We used it for simplicity to show\nthe steps needed in creating it.<\/p>\n\n\n\n<p>The important thing\nhere is that in our pivot table we depicted data from two different sources. By\nfollowing the same steps, we can create a pivot table that gets data from\nnumerous data sources related between them in various ways.<\/p>\n\n\n\n<p>Power Pivot truly has no limits, other than our imagination and the amount of the data we feed it. We\u2019ll dig in a little deeper on the next lesson and still we will have barely scratched the surface of its abilities.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Although Pivot tables are very powerful, in the professional editions of excel there is one optional add-in more powerful and versatile than any other. <\/p>\n","protected":false},"author":2,"featured_media":551,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[38,11,37,44],"class_list":["post-549","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-analysis","tag-data","tag-pivot-tables","tag-powerpivot"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/powerpivot.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-8R","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/549","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/comments?post=549"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/549\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/551"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=549"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=549"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=549"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}