{"id":311,"date":"2017-06-07T09:05:44","date_gmt":"2017-06-06T23:05:44","guid":{"rendered":"https:\/\/www.dynamicwebtraining.com.au\/blog\/?p=311"},"modified":"2022-11-02T15:18:52","modified_gmt":"2022-11-02T04:18:52","slug":"understanding-power-pivot-excel","status":"publish","type":"post","link":"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel","title":{"rendered":"Understanding Power Pivot in Excel"},"content":{"rendered":"\n

Excel\u2019s reputation as an analysis tool grows with each successive upgrade. While data analysis was originally confined to data in a worksheet, Excel is now being used for analysing data from a variety of sources including databases and larger corporate data sources. Whilst this can be done using Excel itself, Power Pivot is an add-in that consolidates the steps towards achieving this.<\/p>\n\n\n\n\n\n\n\n

Lists, Tables, and Relational Databases<\/strong><\/h4>\n\n\n\n

Excel, as do most other spreadsheet applications, has always provided a way of sorting, filtering, and querying data organised into rows and columns. Earlier, this type of data organisation was referred to as a list, but it is now known as a table. Once data is organised in this way you can use some of the data tools such as Pivot Tables<\/strong>, Pivot Charts<\/strong>, and Pivot View<\/strong> reports to drill down and analyse what is going on in the data.
However, a single table, irrespective of how many rows (records) or columns (fields) it may contain is a rather simplistic entity and doesn\u2019t really provide much business grunt. Larger systems store data in many tables, and then relate the data in these tables to one another using a common linking field referred to as a primary key. This is what a relational database is all about \u2013 it may sound complicated but it really is just a common-sense way of organising things.<\/p>\n\n\n\n

The Excel Data Model<\/h4>\n\n\n\n

In Excel you can model data in just this way. One way to do this would be to create a workbook where each table would be placed into a separate sheet (for convenience and ease of maintenance). You can then use the Relationships<\/strong> command on the Data<\/strong> tab on the ribbon to relate the tables together \u2013 providing they have been created with appropriate fields. In this sense a data model would be created in the worksheets of a workbook.
The problem here is that many organisations already have a system that allows users to input data. They may have an Access or SQL<\/a> database system and are only really looking to Excel<\/a> as a way of analysing that data. In this case it would be a waste of time to import that data into a worksheet to work on it.<\/p>\n\n\n\n

Power Pivot and The Data Model<\/h4>\n\n\n\n

Imagine being able to use Excel\u2019s powerful analysis tools such as PivotTables and PivotCharts on large amounts of data. Well, that\u2019s what Power Pivot<\/strong> is all about.
Power Pivot<\/strong> provides a way of managing an Excel data model, either one that exists within the worksheets of a workbook, or one that is created from external data. Power Pivot<\/strong> is a separate add-in application that when installed appears as a tab on the ribbon.<\/p>\n\n\n\n

\n
\"Understanding
Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n

The Manage<\/strong> command in the Data Model<\/strong> group on the Power Pivot<\/strong> tab actually opens a window titled Power Pivot and it is here where you can get external data, filter data, create relationships between tables (if they don\u2019t already exist) and create calculated fields. This is also where you can create Pivot Tables and Pivot Charts from the data model.
Some of the things you can do with data in Power Pivot<\/strong> (such as importing external data and creating relationships between tables) can be done in a worksheet. So you can work on your data model either in a worksheet or in a Power Pivot<\/strong> data-model window. The main difference, however, is that the Power Pivot<\/strong> window allows you to do much more sophisticated work with and on your data model.
By the way, the data in Excel and in the Power Pivot<\/strong> window is stored in an analytical database within the Excel workbook. Since the data is stored within the Excel workbook it becomes available to the analytical tools of Excel. According to Microsoft, Power Pivot supports files up to 2GB in size and will work with up to 4GB of data in memory.<\/p>\n","protected":false},"excerpt":{"rendered":"

Excel\u2019s reputation as an analysis tool grows with each successive upgrade. While data analysis was originally confined to data in a worksheet, Excel is now being used for analysing data from a variety of sources including databases and larger corporate data sources. Whilst this can be done using Excel itself, Power Pivot is an add-in…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-311","post","type-post","status-publish","format-standard","hentry","category-microsoft-excel"],"yoast_head":"Understanding Power Pivot in Excel - Dynamic Web Training Blog<\/title>\n<meta name=\"description\" content=\"View and Share Dynamic Web Training Blog Archives. This is a great source of articles and posts on Computer and IT training, tutorials and insights\" \/>\n<meta name=\"robots\" content=\"index, follow\" \/>\n<meta name=\"googlebot\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<meta name=\"bingbot\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Understanding Power Pivot in Excel - Dynamic Web Training Blog\" \/>\n<meta property=\"og:description\" content=\"Understand how to use Power Pivot for managing an Excel data model, either within existing worksheets, or one that is created from external data.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel\" \/>\n<meta property=\"og:site_name\" content=\"Dynamic Web Training Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DynamicWebTraining\/\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/DynamicWebTraining\/\" \/>\n<meta property=\"article:published_time\" content=\"2017-06-06T23:05:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-11-02T04:18:52+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2017\/06\/Understanding-Power-Pivot-Excel.png\" \/>\n<meta name=\"twitter:card\" content=\"summary\" \/>\n<meta name=\"twitter:creator\" content=\"@dynamicwebtrain\" \/>\n<meta name=\"twitter:site\" content=\"@dynamicwebtrain\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#organization\",\"name\":\"Dynamic Web Training\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/\",\"sameAs\":[\"https:\/\/www.facebook.com\/DynamicWebTraining\/\",\"https:\/\/www.linkedin.com\/company\/dynamic-web-training\",\"https:\/\/twitter.com\/dynamicwebtrain\"],\"logo\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#logo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2016\/02\/logo.png\",\"width\":361,\"height\":109,\"caption\":\"Dynamic Web Training\"},\"image\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#logo\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#website\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/\",\"name\":\"Dynamic Web Training Blog\",\"description\":\"The Ultimate Training Experience.\",\"publisher\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/?s={search_term_string}\",\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel#primaryimage\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2017\/06\/Understanding-Power-Pivot-Excel.png\",\"width\":1021,\"height\":201},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel#webpage\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel\",\"name\":\"Understanding Power Pivot in Excel - Dynamic Web Training Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel#primaryimage\"},\"datePublished\":\"2017-06-06T23:05:44+00:00\",\"dateModified\":\"2022-11-02T04:18:52+00:00\",\"description\":\"Understand how to use Power Pivot for managing an Excel data model, either within existing worksheets, or one that is created from external data.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\",\"name\":\"Training Blog\"}},{\"@type\":\"ListItem\",\"position\":2,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\",\"name\":\"Training\"}},{\"@type\":\"ListItem\",\"position\":3,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\/microsoft-excel\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\/microsoft-excel\",\"name\":\"Microsoft Excel\"}},{\"@type\":\"ListItem\",\"position\":4,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel\",\"name\":\"Understanding Power Pivot in Excel\"}}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel#webpage\"},\"author\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#\/schema\/person\/c94653aed4a6decc8e357af0a1082233\"},\"headline\":\"Understanding Power Pivot in Excel\",\"datePublished\":\"2017-06-06T23:05:44+00:00\",\"dateModified\":\"2022-11-02T04:18:52+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel#webpage\"},\"publisher\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-power-pivot-excel#primaryimage\"},\"articleSection\":\"Microsoft Excel\",\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#\/schema\/person\/c94653aed4a6decc8e357af0a1082233\",\"name\":\"Dynamic Web Training\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#personlogo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0a14e92e62ad4eee0843f5cf7da3a00e1df4c9763922d4d20ba3ed2402a6896d?s=96&d=mm&r=g\",\"caption\":\"Dynamic Web Training\"},\"description\":\"Dynamic Web Training is Australia's leading provider of instructor led software training. We offer training courses in Adobe, Web Design, Graphic Design, Photoshop, InDesign, Dreamweaver and many more.\",\"sameAs\":[\"https:\/\/www.facebook.com\/DynamicWebTraining\/\",\"https:\/\/www.linkedin.com\/company\/dynamic-web-training\",\"https:\/\/twitter.com\/dynamicwebtrain\"]}]}<\/script>","_links":{"self":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts\/311","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/comments?post=311"}],"version-history":[{"count":10,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts\/311\/revisions"}],"predecessor-version":[{"id":1650,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts\/311\/revisions\/1650"}],"wp:attachment":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/media?parent=311"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/categories?post=311"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/tags?post=311"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}