{"id":5581,"date":"2021-05-06T17:41:58","date_gmt":"2021-05-06T17:41:58","guid":{"rendered":"https:\/\/officetuts.net\/excel\/?p=5581"},"modified":"2024-03-28T10:44:50","modified_gmt":"2024-03-28T10:44:50","slug":"how-to-transpose-data","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/","title":{"rendered":"Transpose Data in Excel"},"content":{"rendered":"\n
Transpose-Data-in-Excel<\/a>Download File<\/a><\/div>\n\n\n\n

Sometimes you have to switch columns with rows inside your table. This process is called transposing, and you can do it in Excel using a few different ways.<\/p>\n\n\n\n

Let’s take a look at the following example:<\/p>\n\n\n\n

\"Table\n\nDescription<\/figure>\n\n\n\n

There are names of four people in the first column, the number of hours they worked in the second, and how much they earn per hour in the third one.<\/p>\n\n\n\n

What we want to do here, is to switch columns with rows<\/a>.<\/p>\n\n\n\n

How to quickly transpose data<\/h2>\n\n\n\n

Follow these steps to transpose data:<\/p>\n\n\n\n

    \n
  1. Select cells to copy.<\/li>\n\n\n\n
  2. Copy them (Ctrl + C)<\/strong>.<\/li>\n\n\n\n
  3. Choose a cell for the transposed table.<\/li>\n\n\n\n
  4. Right-click and choose Transpose<\/strong>.<\/li>\n<\/ol>\n\n\n\n
    \"\"<\/figure>\n\n\n\n

    Columns and rows are switched after this operation. The formatting is preserved.<\/p>\n\n\n\n

    \"Table\n\nDescription<\/figure>\n\n\n\n

    This is the quickest way to transpose data, but not the only one.<\/p>\n\n\n\n

    The TRANSPOSE function<\/h2>\n\n\n\n

    When you copy data and paste<\/a> it as transposed, the pasted data is not dynamic. This means that it doesn’t change automatically when you change the source.<\/p>\n\n\n\n

    This can be fixed with the TRANSPOSE<\/strong> function. Here’s how it works:<\/p>\n\n\n\n

    \"A<\/figure>\n\n\n\n

    The function takes an array as a parameter and switches columns with rows. It doesn’t keep formatting, so if you want to have it, you need to apply it manually or use Format Painter<\/strong>.<\/p>\n\n\n\n

    \"Graphical<\/figure>\n\n\n\n

    Remove zero from the transpose<\/h2>\n\n\n\n

    The problem with the previous example is that it shows 0 in a cell where the function is placed. You can quickly fix it using the following formula:<\/p>\n\n\n\n

    =TRANSPOSE(IF(A1:C5=\"\",\"\",A1:C5))<\/code><\/pre>\n\n\n\n
    \"Table\n\nDescription<\/figure>\n\n\n\n
    The formula inside the TRANSPOSE<\/strong> function copies the table from the source and if there is black space, it returns blank<\/a> instead of 0.<\/p>\n\n\n\n
    Transpose with formatting and formulas<\/h2>\n\n\n\n
    By default, when you use transpose by copy and paste, the formatting and formulas are preserved.<\/p>\n\n\n\n
    Let’s modify our example, to see how it works. Create an additional column with calculated earnings and transpose it by copying it the same way as before.<\/p>\n\n\n\n
    \"A<\/figure>\n\n\n\n
    As you can see the formulas are preserved and reference the transposed cells correctly.<\/p>\n\n\n\n
    Transpose with formatting and values<\/h2>\n\n\n\n
    If you want to automatically convert formulas to values, you have to use a different method.<\/p>\n\n\n\n
    First, copy the table as before, and then, instead o pasting it as transposed, click the Paste Special<\/a> button.<\/p>\n\n\n\n
    \"Graphical<\/figure>\n\n\n\n
    A new window will open. First, you have to check the Transpose<\/strong> option.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    Let’s say that you want to convert all formulas to values, but also keep the formatting.<\/p>\n\n\n\n
    Because these are radio buttons, you have to choose one of them. There is no such option as Values and Formats<\/strong>, so you have to first copy one and then the other.<\/p>\n\n\n\n
    First, choose Values<\/strong>, check Transpose<\/strong> and click OK<\/strong>.<\/p>\n\n\n\n
    \"Table\n\nDescription<\/figure>\n\n\n\n
    Next, repeat the same process, but this time instead of Values<\/strong>, choose Formats<\/strong>.<\/p>\n\n\n\n
    \"Table\n\nDescription<\/figure>\n\n\n\n
    Transpose with condition<\/h2>\n\n\n\n
    If you use conditional formatting<\/a> in your example, and you try to paste values, the formatting won’t appear. If you try to paste only formatting, all formatting will be present without conditional formatting.<\/p>\n\n\n\n
    What you need to do is to paste both (one after another, the order is not important) Values<\/strong> and Formats<\/strong>.<\/p>\n\n\n\n
    \"A<\/figure>\n\n\n\n
    Transpose with a keyboard shortcut<\/h2>\n\n\n\n
    When you were pasting values as transposed, you may have noticed a letter (T) next to the button. This button allows you to quickly paste value without the need of navigating with your arrow keys.<\/p>\n\n\n\n
    \"Graphical<\/figure>\n\n\n\n
    This quick shortcut is useful when you use Excel with your keyboard, instead of a mouse.<\/p>\n\n\n\n
    Use a macro keyboard shortcut<\/h2>\n\n\n\n
    When you work with Excel with a keyboard, you can use Ctrl + V<\/strong> to paste values, but there is no shortcut to paste transposed values. This doesn’t mean you can’t do it. You can, with the help of the VBA code.<\/p>\n\n\n\n
    This doesn’t mean, you have to be a VBA programmer, just record a simple macro and see what the code looks like.<\/p>\n\n\n\n
    We are going to create a macro to paste transposed data and apply it to Ctrl + Shift + V<\/strong>.<\/p>\n\n\n\n
    First, copy the data and click the cell where you want to place it as transposed.<\/p>\n\n\n\n
    Next, navigate to View >> Macros <\/strong>and click Record Macro<\/a>.<\/p>\n\n\n\n
    \"Graphical<\/figure>\n\n\n\n
    Inside the new window, name your macro and add a description, but what’s more important assign a keyboard shortcut.<\/p>\n\n\n\n
    We want to use Ctrl + Shift + V<\/strong>, so click inside the box next to Shortcut Key<\/strong> and press Shift + V<\/strong>. In macros Ctrl<\/strong> is added by default.<\/p>\n\n\n\n
    \"Graphical<\/figure>\n\n\n\n
    After you click the OK<\/strong> button, the macro starts recording. Inside the already selected cell paste transposed data and stop recording the macro.<\/p>\n\n\n\n
    Choose Ctrl + F11<\/strong> to open the VBA code editor.<\/p>\n\n\n\n
    This is what the code looks like:<\/p>\n\n\n\n
    Sub PasteTranspose()\n'\n' PasteTranspose Macro\n' Macro to paste transposed data.\n'\n' Keyboard Shortcut: Ctrl+Shift+V\n'\n    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _\n        False, Transpose:=True\nEnd Sub<\/code><\/pre>\n\n\n\n
    Besides the comment block, there are two lines of code. What is interesting is the following part:<\/p>\n\n\n\n
    Transpose:=True<\/code><\/pre>\n\n\n\n
    It indicates that the pasted data will be transposed.<\/p>\n\n\n\n
    Now, if you copy data, you can use the shortcut to paste it as transposed data.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Sometimes you have to switch columns with rows inside your table. This process is called transposing, and you can do it in Excel…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nTranspose Data in Excel<\/title>\n<meta name=\"description\" content=\"Learn how to transpose columns with rows in Excel, quickly and easily. Follow simple steps or use the TRANSPOSE function.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Transpose Data in Excel\" \/>\n<meta property=\"og:description\" content=\"Learn how to transpose columns with rows in Excel, quickly and easily. Follow simple steps or use the TRANSPOSE function.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/\" \/>\n<meta property=\"article:published_time\" content=\"2021-05-06T17:41:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-28T10:44:50+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/table-description-automatically-generated.png\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"Transpose Data in Excel\",\"datePublished\":\"2021-05-06T17:41:58+00:00\",\"dateModified\":\"2024-03-28T10:44:50+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/\"},\"wordCount\":818,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/table-description-automatically-generated.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"examples\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/\",\"url\":\"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/\",\"name\":\"Transpose Data in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/table-description-automatically-generated.png\",\"datePublished\":\"2021-05-06T17:41:58+00:00\",\"dateModified\":\"2024-03-28T10:44:50+00:00\",\"description\":\"Learn how to transpose columns with rows in Excel, quickly and easily. Follow simple steps or use the TRANSPOSE function.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/table-description-automatically-generated.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/table-description-automatically-generated.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Transpose Data in Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Transpose Data in Excel","description":"Learn how to transpose columns with rows in Excel, quickly and easily. Follow simple steps or use the TRANSPOSE function.","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:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/","og_locale":"en_US","og_type":"article","og_title":"Transpose Data in Excel","og_description":"Learn how to transpose columns with rows in Excel, quickly and easily. Follow simple steps or use the TRANSPOSE function.","og_url":"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/","article_published_time":"2021-05-06T17:41:58+00:00","article_modified_time":"2024-03-28T10:44:50+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/table-description-automatically-generated.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"Transpose Data in Excel","datePublished":"2021-05-06T17:41:58+00:00","dateModified":"2024-03-28T10:44:50+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/"},"wordCount":818,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/table-description-automatically-generated.png","keywords":["file","pinterest"],"articleSection":["examples"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/","url":"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/","name":"Transpose Data in Excel","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/table-description-automatically-generated.png","datePublished":"2021-05-06T17:41:58+00:00","dateModified":"2024-03-28T10:44:50+00:00","description":"Learn how to transpose columns with rows in Excel, quickly and easily. Follow simple steps or use the TRANSPOSE function.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/table-description-automatically-generated.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/table-description-automatically-generated.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/examples\/how-to-transpose-data\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Transpose Data in Excel"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/5581"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=5581"}],"version-history":[{"count":11,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/5581\/revisions"}],"predecessor-version":[{"id":17367,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/5581\/revisions\/17367"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=5581"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=5581"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=5581"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}