{"id":462,"date":"2019-04-05T16:57:44","date_gmt":"2019-04-05T13:57:44","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=462"},"modified":"2019-05-06T12:31:00","modified_gmt":"2019-05-06T09:31:00","slug":"how-to-transpose-cells-in-excel","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/how-to-transpose-cells-in-excel\/","title":{"rendered":"How to Transpose cells in Excel"},"content":{"rendered":"\n<p>In Excel the manipulation and management of data is not\nlimited to complex calculations.<\/p>\n\n\n\n<p>It is certain that at some point everybody needs to alter\nthe form and layout of their data, in order for them to be more readable, or\nanswer to some specific visual requirements.<\/p>\n\n\n\n<p>The ability to transpose our data is such a need.<\/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\/PVHhPoGBLw8?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:30px\" 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\/TRANSPOSE.xlsx\">TRANSPOSE<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/TRANSPOSE.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>In our example we can see two tables. These tables are filled\nwith the exactly the same data. Their only difference is that the rightmost\ntable was produced by the vertical arrangement of the leftmost one.<\/p>\n\n\n\n<p>There are two ways to achieve this.<\/p>\n\n\n\n<p>Let\u2019s go to the next worksheet which shows the monthly\ngrades of a student for the school year 2014 \u2013 2015.<\/p>\n\n\n\n<p>The data consists of a 12 columns to 7 rows table. Suppose\nwe need the months to be row headers and the courses to be column headers. <\/p>\n\n\n\n<p>So we need the table to be 7 columns wide and 12 rows tall.\nWe select a cell area that fits these criteria. In our example we will use the\narea from N1 to T12.<\/p>\n\n\n\n<p>We then type the equals sign and the <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-transpose-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"TRANSPOSE (opens in a new tab)\">TRANSPOSE<\/a><\/strong> function. We type the source array enclosed in parenthesis but do not press ENTER.<\/p>\n\n\n\n<p>Transpose can only be used in array formulas. Array formulas\nis a subject for another lesson, but their common characteristic is that to\nfinish an array formula you don\u2019t press Enter but CTRL+SHIFT+ENTER.<\/p>\n\n\n\n<p>By pressing this key combination, we see this result. The\narrangement of the table has changed.<\/p>\n\n\n\n<p>If we alter a grade at the leftmost table, for example the\nphysics grade for September to 7, we observe that the same change happens to\nthe rightmost table.<\/p>\n\n\n\n<p>These two tables are linked which is the main advantage of\nthe transpose function.<\/p>\n\n\n\n<p>But with a closer look at our new table we can see that it\ndid not inherit the formatting and the style of its parent table.<\/p>\n\n\n\n<p>This means we will have to stylize the new table from\nscratch which in some cases could prove to be a really time consuming job especially\nif we had formatting rules and conditions already applied to the parent table.<\/p>\n\n\n\n<p>we will choose a different approach.<\/p>\n\n\n\n<p>We select the table and copy it to the clipboard.<\/p>\n\n\n\n<p>Then we select the cell N1 and use the transpose paste\noption.<\/p>\n\n\n\n<p>We can see that all formatting has been preserved. <\/p>\n\n\n\n<p>But if we change the grade of Physics for September in the\nleftmost table, we will notice that the respective cell in the rightmost table\nremains unchanged. We have to change the grade manually.<\/p>\n\n\n\n<p>We will see though that the formatting rule was preserved\nand the cell background is no longer red.<\/p>\n\n\n\n<p>So as we can see this method makes a copy of the parent array arranged vertically and the two tables are not linked in any way.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>It is certain that at some point everybody needs to alter the form and layout of their data, in order for them to be more readable, or answer to some specific visual requirements.<br \/>\nThe ability to transpose our data is such a need.<\/p>\n","protected":false},"author":2,"featured_media":467,"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":[31],"class_list":["post-462","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-transpose"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/transpose.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-7s","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/462","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=462"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/462\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/467"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=462"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=462"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=462"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}