{"id":192603,"date":"2015-12-15T09:00:16","date_gmt":"2015-12-15T17:00:16","guid":{"rendered":"http:\/\/www.microsoft.com\/?p=192603"},"modified":"2024-08-30T13:35:40","modified_gmt":"2024-08-30T20:35:40","slug":"learn-how-to-unpivot-static-tables-in-excel-2016","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/","title":{"rendered":"Learn how to unpivot static tables in Excel 2016"},"content":{"rendered":"<p>With the new Get &amp; Transform capabilities in Excel 2016, you can now import, transform and combine data from different sources\u2014thanks to the integrated Power Query technology in Excel. Today we are going to focus on one of the most useful capabilities that Get &amp; Transform offers\u2014the ability to unpivot to transform ANY table in your workbook to make it ready for powerful analysis using PivotTables and PivotCharts.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-192633\" src=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-1024x209.png\" alt=\"Learn how to unpivot static tables in Excel 2016 1\" width=\"780\" height=\"159\" srcset=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-1024x209.png 1024w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-300x61.png 300w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-768x157.png 768w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1.png 1520w\" sizes=\"(max-width: 780px) 100vw, 780px\" \/><\/p>\n<p style=\"text-align: center\"><a href=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/uploads\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-192643\" src=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-2.png\" alt=\"Learn how to unpivot static tables in Excel 2016 2\" width=\"75\" height=\"35\" \/><\/a><\/p>\n<p style=\"text-align: center\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-192653\" src=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-3.png\" alt=\"Learn how to unpivot static tables in Excel 2016 3\" width=\"550\" height=\"345\" srcset=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-3.png 550w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-3-300x188.png 300w\" sizes=\"(max-width: 550px) 100vw, 550px\" \/><\/p>\n<p style=\"text-align: center\"><em>Transform a static table into a PivotTable ready for deep analysis.<\/em><\/p>\n\n<div class=\"wp-block-msxcm-cta-block\" data-moray data-bi-an=\"CTA Block\">\n\t<div class=\"card d-block mx-ng mx-md-0\">\n\t\t<div class=\"row no-gutters\">\n\n\t\t\t\t\t\t\t<div class=\"col-md-4\">\n\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" width=\"150\" height=\"150\" src=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2019\/09\/Excel_150x150.png\" class=\"card-img img-object-cover\" alt=\"Excel icon\" srcset=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2019\/09\/Excel_150x150.png 150w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2019\/09\/Excel_150x150-100x100.png 100w\" sizes=\"(max-width: 150px) 100vw, 150px\" \/>\t\t\t\t<\/div>\n\t\t\t\n\t\t\t<div class=\"d-flex col-md\">\n\t\t\t\t<div class=\"card-body align-self-center p-4 p-md-5\">\n\t\t\t\t\t<h2>Microsoft Excel<\/h2>\n\n\t\t\t\t\t<div class=\"mb-3\">\n\t\t\t\t\t\t<p>Turn data into insights.<\/p>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t<\/div>\n\n\t\t\t\t\t<\/div>\n\t<\/div>\n<\/div>\n\n\n\n<p>Let&#8217;s start with a common scenario. You have an Excel workbook that is dedicated for manual data entry to record the hours you put in for each relevant project:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-192753\" src=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Chart.png\" alt=\"Chart\" width=\"600\" height=\"195\" srcset=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Chart.png 683w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Chart-300x98.png 300w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/p>\n<p>But analyzing this data is not easy and sometimes you wish you could easily create charts on this data, or even better\u2014PivotCharts.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-192663\" src=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-4-1024x337.png\" alt=\"Learn how to unpivot static tables in Excel 2016 4\" width=\"640\" height=\"211\" srcset=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-4-1024x337.png 1024w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-4-300x99.png 300w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-4-768x253.png 768w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-4.png 1726w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<p>Here is a common, yet challenging scenario\u2014challenging, because it would take a lot of effort to get to the point you can gain insights from this data in the current format.\u00a0Fortunately, through the new <strong>Get &amp; Transform<\/strong> section in the <strong>Data<\/strong> tab of Excel 2016, the challenge is over.<\/p>\n<p>Simply select any cell inside an existing table or range and in the <strong>Get &amp; Transform<\/strong> section, click <strong>From Table<\/strong>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-192673\" src=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-5.png\" alt=\"Learn how to unpivot static tables in Excel 2016 5\" width=\"560\" height=\"336\" srcset=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-5.png 622w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-5-300x180.png 300w\" sizes=\"(max-width: 560px) 100vw, 560px\" \/><\/p>\n<p>You will see a preview of your data inside the <strong>Query Editor<\/strong> window. From this window, you can start transforming your data in powerful, yet simple ways. Each change you make is recorded as a transformation step that is saved with your workbook. All the steps are kept as a sequence that can be performed again and again each time you refresh your data.<\/p>\n<p>In the Query Editor, you will usually need the help of the <strong>Unpivot<\/strong> transformation to change your table into a format that can be used by PivotTable.<\/p>\n<p>In the example below, I can select the first column that includes project names and click <strong>Unpivot Other Columns<\/strong> to transform my data from columns of <strong>Project Name \/ 2010 \/ 2011 \/ 2012\u2026<\/strong> to the desired column structure of <strong>Project \/ Year \/ Duration<\/strong>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-192683\" src=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-6.png\" alt=\"Learn how to unpivot static tables in Excel 2016 6\" width=\"600\" height=\"615\" srcset=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-6.png 659w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-6-292x300.png 292w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/p>\n<p>And here are the results in the <strong>Query Editor<\/strong>:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-192693\" src=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-7.png\" alt=\"Learn how to unpivot static tables in Excel 2016 7\" width=\"475\" height=\"683\" srcset=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-7.png 514w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-7-209x300.png 209w\" sizes=\"(max-width: 475px) 100vw, 475px\" \/><\/p>\n<p>I can now rename the columns to <strong>Project<\/strong>, <strong>Year<\/strong> and <strong>Duration <\/strong>inside the Query Editor and then load the data into a PivotTable or PivotChart.<\/p>\n<h3>Learn more<\/h3>\n<p>We recently published a series of blog posts on our TechNet blog, beginning with <a href=\"http:\/\/blogs.technet.com\/b\/gilraviv\/archive\/2015\/08\/27\/unpivot-data-with-excel.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">Get &amp; Transform Data in Excel 2016<\/a>, that walk you through a variety of scenarios and magical transformations that can take ANY kind of a table or range in Excel and unpivot it into the desired structure for PivotTables and PivotCharts.<\/p>\n<p>If you are familiar with the Unpivot functionality and want to test your skills, read <a href=\"http:\/\/blogs.technet.com\/b\/gilraviv\/archive\/2015\/09\/14\/the-definitive-guide-to-unpivot-with-power-query-in-excel-part-2.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">this post<\/a>. Even simple tables such as a class schedule may require some cunning approach.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-192703\" src=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-8.png\" alt=\"Learn how to unpivot static tables in Excel 2016 8\" width=\"550\" height=\"354\" srcset=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-8.png 550w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-8-300x193.png 300w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-8-200x130.png 200w\" sizes=\"(max-width: 550px) 100vw, 550px\" \/><\/p>\n<p>The most common scenario includes nested tables with hierarchical date information such as years, quarters and months. Read <a href=\"http:\/\/blogs.technet.com\/b\/gilraviv\/archive\/2015\/09\/27\/transform-nested-table-to-pivottable.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a> how to transform these tables.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-192633\" src=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-1024x209.png\" alt=\"Learn how to unpivot static tables in Excel 2016 1\" width=\"780\" height=\"159\" srcset=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-1024x209.png 1024w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-300x61.png 300w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-768x157.png 768w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1.png 1520w\" sizes=\"(max-width: 780px) 100vw, 780px\" \/><\/p>\n<p>And if you wish to apply your magic on ANY nested table, read <a href=\"http:\/\/blogs.technet.com\/b\/gilraviv\/archive\/2015\/10\/18\/transform-any-nested-tables-to-pivot-table.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">this post<\/a> that will first show you how to unpivot three levels of nested rows and columns. It is so simple.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-192713\" src=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-9-1024x222.png\" alt=\"Learn how to unpivot static tables in Excel 2016 9\" width=\"780\" height=\"169\" srcset=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-9-1024x222.png 1024w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-9-300x65.png 300w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-9-768x166.png 768w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-9-1259x273.png 1259w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-9.png 1260w\" sizes=\"(max-width: 780px) 100vw, 780px\" \/><\/p>\n<p>On many occasions your data contains comma-separated values, like a table of events in which all participants are listed on a single column. You can learn <a href=\"http:\/\/blogs.technet.com\/b\/gilraviv\/archive\/2015\/11\/08\/split-and-unpivot.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a> how to split such data and unpivot it.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-192833\" src=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-comma-separated-values.jpg\" alt=\"Learn how to unpivot static tables in Excel 2016 - comma-separated values\" width=\"640\" height=\"315\" srcset=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-comma-separated-values.jpg 956w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-comma-separated-values-300x147.jpg 300w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-comma-separated-values-768x378.jpg 768w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<p>Finally, you can learn <a href=\"http:\/\/blogs.technet.com\/b\/gilraviv\/archive\/2015\/10\/18\/unpivot-any-nested-table-and-load-to-power-bi-desktop.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a> how to transform and unpivot all these challenging tables in Power BI Desktop, and build amazing dashboards in Power BI.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-192723\" src=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-10-1024x446.png\" alt=\"Learn how to unpivot static tables in Excel 2016 10\" width=\"780\" height=\"340\" srcset=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-10-1024x446.png 1024w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-10-300x131.png 300w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-10-768x335.png 768w, https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-10.png 1599w\" sizes=\"(max-width: 780px) 100vw, 780px\" \/><\/p>\n<p>\u2014Gil Raviv, senior program manager for the Excel team<\/p>","protected":false},"excerpt":{"rendered":"<p>With the new Get &amp; Transform capabilities in Excel 2016, you can now import, transform and combine data from different sources\u2014thanks to the integrated Power Query technology in Excel. Today we are going to focus on one of the most useful capabilities that Get &amp; Transform offers\u2014the ability to use Unpivot to transform ANY table in your workbook to make it ready for powerful analysis using PivotTables and PivotCharts.<\/p>\n","protected":false},"author":3368823,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ep_exclude_from_search":false,"_classifai_error":"","footnotes":""},"content-type":[4035],"product":[4047,4036],"audience":[4081,4101,4102],"tags":[],"coauthors":[4437],"class_list":["post-192603","post","type-post","status-publish","format-standard","hentry","content-type-tips-and-guides","product-excel","product-microsoft-365","audience-enterprise","audience-personal-and-family","audience-small-business","review-flag-1680288349-139","review-flag-1680288349-963","review-flag-new-1680288347-871"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v23.9 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Learn how to unpivot static tables in Excel 2016 | Microsoft 365 Blog<\/title>\n<meta name=\"description\" content=\"With the new Get &amp; Transform capabilities in Excel 2016, you can now import, transform and combine data from different sources\u2014thanks to the integrated Power Query technology in Excel. Today we are going to focus on one of the most useful capabilities that Get &amp; Transform offers\u2014the ability to use Unpivot to transform ANY table in your workbook to make it ready for powerful analysis using PivotTables and PivotCharts.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Learn how to unpivot static tables in Excel 2016 | Microsoft 365 Blog\" \/>\n<meta property=\"og:description\" content=\"With the new Get &amp; Transform capabilities in Excel 2016, you can now import, transform and combine data from different sources\u2014thanks to the integrated Power Query technology in Excel. Today we are going to focus on one of the most useful capabilities that Get &amp; Transform offers\u2014the ability to use Unpivot to transform ANY table in your workbook to make it ready for powerful analysis using PivotTables and PivotCharts.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/\" \/>\n<meta property=\"og:site_name\" content=\"Microsoft 365 Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-15T17:00:16+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-08-30T20:35:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-1024x209.png\" \/>\n<meta name=\"author\" content=\"The Microsoft 365 Marketing Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"The Microsoft 365 Marketing Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 min read\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/\"},\"author\":[{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/author\/the-microsoft-365-marketing-team\/\",\"@type\":\"Person\",\"@name\":\"The Microsoft 365 Marketing Team\"}],\"headline\":\"Learn how to unpivot static tables in Excel 2016\",\"datePublished\":\"2015-12-15T17:00:16+00:00\",\"dateModified\":\"2024-08-30T20:35:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/\"},\"wordCount\":586,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-1024x209.png\",\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/\",\"name\":\"Learn how to unpivot static tables in Excel 2016 | Microsoft 365 Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-1024x209.png\",\"datePublished\":\"2015-12-15T17:00:16+00:00\",\"dateModified\":\"2024-08-30T20:35:40+00:00\",\"description\":\"With the new Get & Transform capabilities in Excel 2016, you can now import, transform and combine data from different sources\u2014thanks to the integrated Power Query technology in Excel. Today we are going to focus on one of the most useful capabilities that Get & Transform offers\u2014the ability to use Unpivot to transform ANY table in your workbook to make it ready for powerful analysis using PivotTables and PivotCharts.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#primaryimage\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-1024x209.png\",\"contentUrl\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-1024x209.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Learn how to unpivot static tables in Excel 2016\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#website\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/\",\"name\":\"Microsoft 365 Blog\",\"description\":\"Microsoft 365 brings together Office 365, Windows 10, and Enterprise Mobility + Security. It delivers a complete, intelligent, and secure solution to empower people.\",\"publisher\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#organization\",\"name\":\"Microsoft 365 Blog\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2019\/08\/ms-logo-amp.png\",\"contentUrl\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2019\/08\/ms-logo-amp.png\",\"width\":279,\"height\":60,\"caption\":\"Microsoft 365 Blog\"},\"image\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Learn how to unpivot static tables in Excel 2016 | Microsoft 365 Blog","description":"With the new Get & Transform capabilities in Excel 2016, you can now import, transform and combine data from different sources\u2014thanks to the integrated Power Query technology in Excel. Today we are going to focus on one of the most useful capabilities that Get & Transform offers\u2014the ability to use Unpivot to transform ANY table in your workbook to make it ready for powerful analysis using PivotTables and PivotCharts.","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:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/","og_locale":"en_US","og_type":"article","og_title":"Learn how to unpivot static tables in Excel 2016 | Microsoft 365 Blog","og_description":"With the new Get & Transform capabilities in Excel 2016, you can now import, transform and combine data from different sources\u2014thanks to the integrated Power Query technology in Excel. Today we are going to focus on one of the most useful capabilities that Get & Transform offers\u2014the ability to use Unpivot to transform ANY table in your workbook to make it ready for powerful analysis using PivotTables and PivotCharts.","og_url":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/","og_site_name":"Microsoft 365 Blog","article_published_time":"2015-12-15T17:00:16+00:00","article_modified_time":"2024-08-30T20:35:40+00:00","og_image":[{"url":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-1024x209.png"}],"author":"The Microsoft 365 Marketing Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"The Microsoft 365 Marketing Team","Est. reading time":"2 min read"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#article","isPartOf":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/"},"author":[{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/author\/the-microsoft-365-marketing-team\/","@type":"Person","@name":"The Microsoft 365 Marketing Team"}],"headline":"Learn how to unpivot static tables in Excel 2016","datePublished":"2015-12-15T17:00:16+00:00","dateModified":"2024-08-30T20:35:40+00:00","mainEntityOfPage":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/"},"wordCount":586,"commentCount":0,"publisher":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#organization"},"image":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#primaryimage"},"thumbnailUrl":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-1024x209.png","inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/","url":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/","name":"Learn how to unpivot static tables in Excel 2016 | Microsoft 365 Blog","isPartOf":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#primaryimage"},"image":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#primaryimage"},"thumbnailUrl":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-1024x209.png","datePublished":"2015-12-15T17:00:16+00:00","dateModified":"2024-08-30T20:35:40+00:00","description":"With the new Get & Transform capabilities in Excel 2016, you can now import, transform and combine data from different sources\u2014thanks to the integrated Power Query technology in Excel. Today we are going to focus on one of the most useful capabilities that Get & Transform offers\u2014the ability to use Unpivot to transform ANY table in your workbook to make it ready for powerful analysis using PivotTables and PivotCharts.","breadcrumb":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#primaryimage","url":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-1024x209.png","contentUrl":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2015\/12\/Learn-how-to-unpivot-static-tables-in-Excel-2016-1-1024x209.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/12\/15\/learn-how-to-unpivot-static-tables-in-excel-2016\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/"},{"@type":"ListItem","position":2,"name":"Learn how to unpivot static tables in Excel 2016"}]},{"@type":"WebSite","@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#website","url":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/","name":"Microsoft 365 Blog","description":"Microsoft 365 brings together Office 365, Windows 10, and Enterprise Mobility + Security. It delivers a complete, intelligent, and secure solution to empower people.","publisher":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#organization","name":"Microsoft 365 Blog","url":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2019\/08\/ms-logo-amp.png","contentUrl":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2019\/08\/ms-logo-amp.png","width":279,"height":60,"caption":"Microsoft 365 Blog"},"image":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#\/schema\/logo\/image\/"}}]}},"distributor_meta":false,"distributor_terms":false,"distributor_media":false,"distributor_original_site_name":"Microsoft 365 Blog","distributor_original_site_url":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog","push-errors":false,"_links":{"self":[{"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/posts\/192603"}],"collection":[{"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/users\/3368823"}],"replies":[{"embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/comments?post=192603"}],"version-history":[{"count":2,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/posts\/192603\/revisions"}],"predecessor-version":[{"id":274435,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/posts\/192603\/revisions\/274435"}],"wp:attachment":[{"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/media?parent=192603"}],"wp:term":[{"taxonomy":"content-type","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/content-type?post=192603"},{"taxonomy":"product","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/product?post=192603"},{"taxonomy":"audience","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/audience?post=192603"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/tags?post=192603"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/coauthors?post=192603"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}