{"id":414,"date":"2017-12-20T10:34:10","date_gmt":"2017-12-20T09:34:10","guid":{"rendered":"http:\/\/it-merge.com\/?p=414"},"modified":"2017-12-20T10:36:25","modified_gmt":"2017-12-20T09:36:25","slug":"combination-of-a-table-and-pivot","status":"publish","type":"post","link":"https:\/\/it-merge.com\/?p=414","title":{"rendered":"Combination of a table and pivot"},"content":{"rendered":"<h2>Basis is the table<\/h2>\n<p>The basic for an efficient pivot usage is the consequent use of the table.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-416 alignnone\" src=\"http:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimple0-300x275.png\" alt=\"\" width=\"300\" height=\"275\" srcset=\"https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimple0-300x275.png 300w, https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimple0.png 394w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/p>\n<p>Define the table and convert it to a table.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-417 alignnone\" src=\"http:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimple1-300x220.png\" alt=\"\" width=\"300\" height=\"220\" srcset=\"https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimple1-300x220.png 300w, https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimple1.png 381w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/p>\n<p>If you specifiy already the table capture ensure the checkbox is tagged. If not Excel will create automated column captures for you.<\/p>\n<p>If you use a lot of tables, it might be good to define a clear table name. It will help later.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-418 alignnone\" src=\"http:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimple2-300x252.png\" alt=\"\" width=\"300\" height=\"252\" srcset=\"https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimple2-300x252.png 300w, https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimple2.png 389w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/p>\n<h2>Adding the pivot<\/h2>\n<p>Next step is, to define the pivot.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-426\" src=\"http:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimple3-1-300x277.png\" alt=\"\" width=\"300\" height=\"277\" srcset=\"https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimple3-1-300x277.png 300w, https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimple3-1.png 375w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/p>\n<p>As we will use a table a source, the pivot automaticlly select the data based on the table boundaries.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-420 size-large\" src=\"http:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimplex_pivot1-1024x450.png\" alt=\"\" width=\"1000\" height=\"439\" srcset=\"https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimplex_pivot1-1024x450.png 1024w, https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimplex_pivot1-300x132.png 300w, https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimplex_pivot1-768x338.png 768w, https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimplex_pivot1.png 1103w\" sizes=\"auto, (max-width: 1000px) 100vw, 1000px\" \/><\/p>\n<h2>Reflect table changes to the pivot<\/h2>\n<p>The pivot will everytime reflect the current table strucutre when it will be updated. As in the example below, I add a new column &#8220;d&#8221; and update the pivot afterwards.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-421 size-large\" src=\"http:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimplex_pivot2-1024x488.png\" alt=\"\" width=\"1000\" height=\"477\" srcset=\"https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimplex_pivot2-1024x488.png 1024w, https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimplex_pivot2-300x143.png 300w, https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimplex_pivot2-768x366.png 768w, https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimplex_pivot2.png 1111w\" sizes=\"auto, (max-width: 1000px) 100vw, 1000px\" \/><\/p>\n<p>The pivot will now reflect the last table change.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-422 size-large\" src=\"http:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimplex_pivot3-1024x429.png\" alt=\"\" width=\"1000\" height=\"419\" srcset=\"https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimplex_pivot3-1024x429.png 1024w, https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimplex_pivot3-300x126.png 300w, https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimplex_pivot3-768x322.png 768w, https:\/\/it-merge.com\/wp-content\/uploads\/2017\/12\/Tablesimplex_pivot3.png 1083w\" sizes=\"auto, (max-width: 1000px) 100vw, 1000px\" \/><\/p>\n<h2>Pivot and table a must<\/h2>\n<p>For me one of the most important features of Excel it the combination of tables and pivots. Not to use this combination will make a lot of manual work when changing tables and afterwards updating the pivots.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Basis is the table The basic for an efficient pivot usage is the consequent use of the table. Define the[&#8230;]<\/p>\n","protected":false},"author":2,"featured_media":417,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,30],"tags":[],"class_list":["post-414","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","category-tools"],"_links":{"self":[{"href":"https:\/\/it-merge.com\/index.php?rest_route=\/wp\/v2\/posts\/414","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/it-merge.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/it-merge.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/it-merge.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/it-merge.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=414"}],"version-history":[{"count":6,"href":"https:\/\/it-merge.com\/index.php?rest_route=\/wp\/v2\/posts\/414\/revisions"}],"predecessor-version":[{"id":428,"href":"https:\/\/it-merge.com\/index.php?rest_route=\/wp\/v2\/posts\/414\/revisions\/428"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/it-merge.com\/index.php?rest_route=\/wp\/v2\/media\/417"}],"wp:attachment":[{"href":"https:\/\/it-merge.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=414"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/it-merge.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=414"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/it-merge.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=414"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}