A10<\/strong>.<\/p>\n\n\n\nThe advantage of this function over the standard copy and transpose is that it’s dynamic. If you change one value inside the source data it will automatically change in the transpose function.<\/p>\n\n\n\n
It’s very useful, especially when you want to transpose it between different sheets.<\/p>\n\n\n\n
Shortcut for transposing<\/h2>\n\n\n\n You can quickly copy transposed data by creating a macro and applying a shortcut.<\/p>\n\n\n\n
Here’s how you can do it.<\/p>\n\n\n\n
First, copy the data you want to transpose, then select a place where you want to insert the transposed data.<\/p>\n\n\n\n
Now, it’s time to create a macro.<\/p>\n\n\n\n
navigate to View >> Macros >> Record Macro<\/strong>.<\/p>\n\n\n\n <\/figure>\n\n\n\nInside the new window enter the name for your macro. Click the white square under the Shortcut key<\/strong> and press Shift + T<\/strong>. This will assign Ctrl + Shift + T<\/strong> to your macro. Click OK<\/strong> to start recording.<\/p>\n\n\n\nRight-click a cell and choose the transpose icon.<\/p>\n\n\n\n
Navigate to View >> Macros >> Stop Recording<\/strong>.<\/p>\n\n\n\n <\/figure>\n\n\n\nIf you want to see how your macro looks like, go to View >> Macros >> View Macros<\/strong>.<\/p>\n\n\n\nAnd there it is, the myTranspose<\/strong> macro.<\/p>\n\n\n\n <\/figure>\n\n\n\nClick Edit<\/strong> to see how it looks like, or click Cancel<\/strong> to close the window.<\/p>\n\n\n\nThis is how the code looks like:<\/p>\n\n\n\n
Sub myTranspose()\n'\n' myTranspose Macro\n'\n' Keyboard Shortcut: Ctrl+Shift+T\n'\n Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _\n False, Transpose:=True\nEnd Sub\n<\/code><\/pre>\n\n\n\nThere are a macro name and comment with information about the shortcut assigned to this macro.<\/p>\n\n\n\n
At the end of the function, there is a line that copies everything, and transpose is set to true.<\/p>\n\n\n\n
Let’s check how our macro works.<\/p>\n\n\n\n
Copy cells you want to transpose, and click a cell where you want to place the new transposed cells.<\/p>\n\n\n\n
Now, you can use the keyboard shortcut we created earlier (Ctrl + Shift + T<\/strong>).<\/p>\n\n\n\n <\/figure>\n\n\n\nNow, you can transpose data with a quick keyboard shortcut.<\/p>\n\n\n\n
You can modify the code if you want to transpose data without formatting. If you don’t know how to do it, you can create another macro paste special and look at the VBA code that is generated by Excel.<\/p>\n","protected":false},"excerpt":{"rendered":"
Rearranging data from rows to columns or columns to rows is called transposing. It’s useful when you want to change the structure of…<\/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":"\n
Convert Rows to Columns in Excel<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n