{"id":531,"date":"2019-04-16T18:05:33","date_gmt":"2019-04-16T15:05:33","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=531"},"modified":"2019-05-06T12:46:53","modified_gmt":"2019-05-06T09:46:53","slug":"learning-about-pivot-tables","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/learning-about-pivot-tables\/","title":{"rendered":"Learning about Pivot Tables"},"content":{"rendered":"\n<p>Pivot tables might as\nwell be Excel\u2019s most powerful feature. A pivot table helps you to summarize and\nanalyze your data, thus extracting the significant information from a large and\ndetailed data set.<\/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\/q3LE1nG6-oY?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\/pivot-tables.xlsx\">pivot tables<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/pivot-tables.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>For this example, we\nwill use a rather small data set, to better understand the concept of pivot\ntables and how they work.<\/p>\n\n\n\n<p>Suppose we want to\nreview the sales per salesman. Excel provides a fast way to do that.<\/p>\n\n\n\n<p>We click on the insert\ntab and then on the pivot table button. We select the cell range we want the\npivot table for, if not already selected.<\/p>\n\n\n\n<p>Finally, we decide if we want the table to appear on this sheet or on a new one. We choose this sheet for this example, and cell G1 specifically.<\/p>\n\n\n\n<p>On the new window we\ncan select the rows, the columns and the values of our table.<\/p>\n\n\n\n<p>Since we want to\nreview the sales per salesman, let\u2019s put the salesman field on the rows area\nand the quantity on the values.<\/p>\n\n\n\n<p>By default, the pivot\ntable generator chose to show us the sum of quantity which is exactly what we\nneed. But if we want we have many more choices.<\/p>\n\n\n\n<p>There is an even\nfaster way. On the insert tab of the ribbon and next to the Pivot Table button\nwe have the recommended pivot tables button. By clicking on it Excel shows us a\nlist of recommended pivot tables. Most of the times the pivot table we need can\nbe found here.<\/p>\n\n\n\n<p>A variation of the\ntable we created exists here. Let\u2019s click it and press ok.<\/p>\n\n\n\n<p>A new sheet was\ncreated with the generated pivot table. This table has an extra field as row.\nThe date field. This means that by clicking the plus sign beside the name of\nthe salesman we can see the sales he did each day.<\/p>\n\n\n\n<p>The versatility of the\ntool can be understood by adding one column to the mix. Let\u2019s add the product\nfield in the columns. In addition to all the previous information we can now\nsee the sales per salesman, per date and per product.<\/p>\n\n\n\n<p>Let\u2019s play with it a\nbit more and add the color field to the filters.<\/p>\n\n\n\n<p>This provides us with\na filtering mechanism for our table.<\/p>\n\n\n\n<p>We filter the results\nto show us the brown products only.<\/p>\n\n\n\n<p>If we need to select\nmore than one values for our filter we just click on this checkbox.<\/p>\n\n\n\n<p>We can see that the\nsame filter button exists on both the rows and columns.<\/p>\n\n\n\n<p>By clicking on them we\ncan further filter our results for certain salesmen or types of products. Let\u2019s\nfilter the results for salesman Simpson and for the product shoes.<\/p>\n\n\n\n<p>So we now know that\nSimpson sold 3 brown shoes in total. <\/p>\n\n\n\n<p>Imagine the time we\nwould need to produce such analytical reports and summarizations by hand\nespecially if we had a massive amount of data to analyze.<\/p>\n\n\n\n<p>Impressive isn\u2019t it.\nAnd this is just the tip of the iceberg. We have just begun to explore Excel\u2019s\ncapabilities in data analysis.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Pivot tables might as well be Excel\u2019s most powerful feature. A pivot table helps you to summarize and analyze your data, thus extracting the significant information from a large and detailed data set.<\/p>\n","protected":false},"author":2,"featured_media":532,"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],"class_list":["post-531","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-analysis","tag-data","tag-pivot-tables"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/pivottables.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-8z","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/531","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=531"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/531\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/532"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=531"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=531"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=531"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}