![paste and transpose excel shortcut paste and transpose excel shortcut](https://www.exceldemy.com/wp-content/uploads/2021/11/Excel-Paste-Transpose-Shortcut-4.png)
Normally that's going to be:Ĭ:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART\ (Note that Personal.xls is a hidden workbook and not an Excel Add-In.)Įxcel will put it in your startup directory. This way every time you start Excel, Personal.xls will load and its macros will be available to you. If you select Personal Macro Workbook and you have not previously recorded a macro there, Excel will create it for you, naming it Personal.xls and placing it in your Excel startup directory. If you use the Macro Recorder to record a macro, the dialog box asks where you want to store the macro: (a) the active wb, (b) a new wb or (c) in your Personal Macro wb. This is a workbook that Excel automatically creates. the macro is a "generic" macro it is better to place the macro in your Personal Macro Workbook. Now we have a transposed version of the original range and all the formula’s have maintained their original references.If you wish to always have the macro available and not just when one particular workbook is open - i.e. Press the Replace All button. Press the OK button in the pop up that follows, again it should say we made 6 replacements as there were 6 formula’s in our range.In the Find what box type in #= and in the Replace with box type in =.Select the cell where you want the transposed data. Paste the data using the transpose command. Open the Find and Replace window with the Ctrl + H keyboard shortcut or from the Home tab in the ribbon. You can also select the data then use the Ctrl+ Ckeyboard shortcut to copy the data instead of using the ribbon commands.Now we just need to change the text in the transposed version back to formulas. Step 3: Change The Transpose Range Back To Formula Select the All radio button under the Paste options.Select an empty cell where you want the transposed version and paste special. You can either use the Ctrl + Alt + V keyboard shortcut or the Paste button in the Home tab of the ribbon.You can either use the Ctrl + C keyboard shortcut or the Copy button in the Home tab of the ribbon.
![paste and transpose excel shortcut paste and transpose excel shortcut](https://www.affordablecebu.com/pictures/articles/computer_tricks/type-multiplication-sign.jpg)
![paste and transpose excel shortcut paste and transpose excel shortcut](https://images.idgesg.net/images/article/2017/11/excel_shortcuts-4-100741078-orig.gif)
Highlight and copy the range which you want to transpose.Now we can transpose our selection with copy and paste special. It should say we made 6 replacements as there were 6 formula’s in our range. I like to use # as it’s a character that’s unlikely to appear in any of the formula’s but you can use any text in front of the equal sign as long as it’s not in any of your formulas. This will turn any formula into a text string.
![paste and transpose excel shortcut paste and transpose excel shortcut](https://cdn.ablebits.com/_img-blog/paste-special/copy-values-paste-special.png)