{"id":172377,"date":"2021-02-06T14:23:32","date_gmt":"2021-02-06T11:23:32","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-transpose-excel-data-from-rows-to-columns-or-vice-versa\/"},"modified":"2021-02-06T14:23:32","modified_gmt":"2021-02-06T11:23:32","slug":"how-to-transpose-excel-data-from-rows-to-columns-or-vice-versa","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-transpose-excel-data-from-rows-to-columns-or-vice-versa\/","title":{"rendered":"#How to Transpose Excel Data from Rows to Columns (or Vice Versa)"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a35bd95ad1c1\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a35bd95ad1c1\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-transpose-excel-data-from-rows-to-columns-or-vice-versa\/#The_Static_Method\" >The Static Method<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-transpose-excel-data-from-rows-to-columns-or-vice-versa\/#Transpose_Data_with_the_Transpose_Formula\" >Transpose Data with the Transpose Formula<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-transpose-excel-data-from-rows-to-columns-or-vice-versa\/#Transposing_Data_with_Direct_References\" >Transposing Data with Direct References<\/a><\/li><\/ul><\/nav><\/div>\n<p><strong>&#8220;#How to Transpose Excel Data from Rows to Columns (or Vice Versa)&#8221;<\/strong><\/p>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-435491\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2019\/07\/stock-lede-microsoft-office_excel-1.png\" alt=\"Excel Logo on a gray background\" width=\"650\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If you started to enter data in a vertical arrangement (columns) and then decided it would be better in a horizontal one (rows), Excel has you covered. We\u2019ll look at three ways to transpose data in Excel.<\/p>\n<h3 id=\"static-method\"><span class=\"ez-toc-section\" id=\"The_Static_Method\"><\/span>The Static Method<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>In this method, you can quickly and easily transpose data from a column to a row (or vice versa), but it has one critical drawback: It\u2019s not dynamic. When you change a figure in the vertical column, for example, it won\u2019t automatically change it in the horizontal one. Still, it\u2019s good for a quick and simple fix on a smaller dataset.<\/p>\n<p>Highlight the area you want to transpose and then press Ctrl + C on the keyboard to copy the data.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-702816\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2020\/11\/highlight.png\" alt=\"highlight columns\" width=\"650\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Right-click the empty cell where you\u2019d like to display your results. Under \u201cPaste Options\u201d click \u201cPaste Special.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-702804\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2020\/11\/paste-special.png\" alt=\"paste special menu\" width=\"650\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Check the box next to \u201cTranspose\u201d and then press the \u201cOK\u201d button.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-702805\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2020\/11\/transpose-ok.png\" alt=\"check transpose box\" width=\"650\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h3 id=\"transpose-data-with-the-transpose-formula\"><span class=\"ez-toc-section\" id=\"Transpose_Data_with_the_Transpose_Formula\"><\/span>Transpose Data with the Transpose Formula<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>This method is a dynamic solution, meaning we can change the data in one column or row and it will automatically change it in the transposed column or row, too.<\/p>\n<p>Click and drag to highlight a group of empty cells. In an ideal world we\u2019d count first, as the formula is an array and requires you to highlight exactly the number of cells you need. We\u2019re not going to do that; we\u2019ll just fix the formula later.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-702806\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2020\/11\/click-drag.png\" alt=\"click and drag\" width=\"650\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Type \u201c=transpose\u201d into the formula bar (without quotes) and then highlight the data you want to transpose. Instead of pressing \u201cEnter\u201d to execute the formula, press Ctrl + Shift + Enter instead.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-702807\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2020\/11\/transpose.png\" alt=\"transpose formula\" width=\"650\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>As you can see, our data has been cut off because we didn\u2019t select enough empty cells for our array. That\u2019s okay. To fix it, click and drag the box at the bottom, right-hand side of the last cell and drag it out further to include the rest of your data.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-702808\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2020\/11\/drag-box.png\" alt=\"\" width=\"650\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Our data is there now, but the result is a little messed up because of our lack of precision. We\u2019re going to fix that now. To correct the data, just go back to the formula bar, and press Ctrl + Shift + Enter one more time.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-702809\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2020\/11\/formula-bar-ctrl-shift-enter.png\" alt=\"formula bar\" width=\"650\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h3 id=\"transposing-data-with-direct-references\"><span class=\"ez-toc-section\" id=\"Transposing_Data_with_Direct_References\"><\/span>Transposing Data with Direct References<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>In our third method of transposing Excel data we\u2019ll use direct references. This method enables us to find and replace a reference with the data we want to display instead.<\/p>\n<p>Click an empty cell and type in a reference and then the location of the first cell we want to transpose. I\u2019m going to use my initials. In this case, I\u2019ll use bcA2.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-702810\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2020\/11\/bcA2.png\" alt=\"data entry \" width=\"650\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In the next cell, below our first one, type in the same prefix and then the cell location to the right of the one we used in the previous step. For our purposes, that would be cell B2, which we\u2019ll type in as bcB2.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-702811\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2020\/11\/bcB2.png\" alt=\"data entry\" width=\"650\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Highlight both of these cells and drag the highlighted area out by clicking and dragging the green box at the bottom right of our selection.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-702812\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2020\/11\/drag-box2.png\" alt=\"drag to enlarge\" width=\"650\" height=\"299\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Press Ctrl+H on your keyboard to bring up the \u201cFind and Replace\u201d menu.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-702813\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2020\/11\/ctrl-h.png\" alt=\"find and replace\" width=\"650\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Type your chosen prefix, \u201cbc\u201d in our case (without quotes), into the \u201cFind what\u201d field, and then \u201c=\u201d (without quotes) into the \u201cReplace with\u201d field.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-702814\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2020\/11\/bc-.png\" alt=\"find and replace\" width=\"650\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Click the \u201cReplace All\u201d button to transpose your data.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-702815\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2020\/11\/replace-all.png\" alt=\"\" width=\"650\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You might be wondering why we didn\u2019t just add \u201c=A2\u201d to the first empty cell and then just drag it out to autofill the rest. The reason for this is due to the way Excel interprets this data. It will indeed autofill the cell next to it (B2), but it will quickly run out of data because C3 is an empty cell and Excel reads this formula from left to right (because that\u2019s the way we\u2019re dragging when transposing our data) instead of top to bottom.<\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/702803\/how-to-transpose-excel-data-from-rows-to-columns-or-vice-versa\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#How to Transpose Excel Data from Rows to Columns (or Vice Versa)&#8221; If you started to enter data in a vertical arrangement (columns) and then decided it would be better in a horizontal one (rows), Excel has you covered. We\u2019ll look at three ways to transpose data in Excel. The Static Method In this method,&#8230;<\/p>\n","protected":false},"author":1,"featured_media":172378,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2019\/07\/stock-lede-microsoft-office_excel-1.png?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-172377","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/172377","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=172377"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/172377\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/172378"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=172377"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=172377"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=172377"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}