How To Cut And Paste Transpose

2017-01-22

Excel has some great features available in paste special, but where Excel is lacking is the ability to do any sort of cut and paste special. One feature I’ve wanted is the ability to cut and paste transpose this can be handy as using copy and paste transpose won’t always result in formulas keeping the correct references. In this post we’ll explore a work around method to get the same results as a cut and paste transpose would give (if it existed).

 

Step-001-How-To-Cut-And-Paste-Transpose-1024x625 How To Cut And Paste Transpose

 

Step 1: Replace Any Formula With Text

 

Step-002-How-To-Cut-And-Paste-Transpose-1024x582 How To Cut And Paste Transpose

 

Replace any formula with text in the range which you want to transpose.

  1. Highlight the range you want to transpose.
  2. Go to the Home tab in the ribbon.
  3. Under the Editing section press the Find & Select button.
  4. Select Replace from the menu. Alternatively, you can use the Ctrl + H keyboard shortcut to open the Find and Replace window.
  5. In the Find what box type in = and in the Replace with box type in #=. This will turn any formula into a text string. 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.
  6. Press the Replace All button.
  7. Press the OK button in the pop up. It should say we made 6 replacements as there were 6 formula’s in our range.
  8. Press the Close button.

 

Step 2: Copy And Paste Transpose

 

Step-003-How-To-Cut-And-Paste-Transpose How To Cut And Paste Transpose

 

Now we can transpose our selection with copy and paste special.

  1. Highlight and copy the range which you want to transpose . You can either use the Ctrl + C keyboard shortcut or the Copy button in the Home tab of the ribbon.
  2. 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.
  3. Select the All radio button under the Paste options.
  4. Check the Transpose box.
  5. Press the OK button.

 

Step 3: Change The Transpose Range Back To Formula

 

Step-004-How-To-Cut-And-Paste-Transpose-1024x495 How To Cut And Paste Transpose

 

Now we just need to change the text in the transposed version back to formulas.

  1. Select the transposed range.
  2. Open the Find and Replace window with the Ctrl + H keyboard shortcut or from the Home tab in the ribbon.
  3. In the Find what box type in #= and in the Replace with box type in =.
  4. 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.
  5. Press the Close button.

 

Now we have a transposed version of the original range and all the formula’s have maintained their original references.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

37 Awesome Excel Mouse Tips & Tricks You Should Know

37 Awesome Excel Mouse Tips & Tricks You Should Know

While the keyboard is generally quicker, you shouldn’t completely ignore the mouse. There are also some great time saving mouse shortcuts as well. In this post we’ll take a look at some of the best Excel mouse time saving tips and tricks.

read more
25 Amazing Power Query Tips and Tricks

25 Amazing Power Query Tips and Tricks

Power query is amazing tool that allows you to import and transform data with ease and helps to create repeatable and robust procedures with your data. Here are some tips and tricks to help you get the most out Power Query.

read more

Comments

5 Comments

  1. Bruce

    Dear Sir/Madam – Your method show here is built and dangerously unreliable assumptions. Your method assumes the formulas in the copy/paste group do NOT refer to each other. If a single cells in the copy range references another cell in the copy range, it will point to the WRONG cells after the transformation (unless the cell it points to is on the exact diagonal of the matrix)! You really need to correct that ASAP. I hope nobody has used your advice for anything important. For example, if an engineer uses this method while designing safety features in an elevator YOU will be riding in, it could be your last ride. :-b

    Reply
    • Bruce

      I wish I had spell checked that. I rewrote it and that always creates problems! ?

      Reply
    • John

      Good point! I never considered that case when I thought of the solution as my use case didn’t have internal linking.

      I’d hope people check their work independently.

      Reply
  2. Michael V.

    This works great if you don’t have any cells referencing the range being transposed. But as this isn’t a true cut action, any references to the original range won’t be redirected to the new transposed cells. This is what I have been trying to figure out and can’t find any work-arounds online anywhere. The range I need transposed serves as a data entry point that feeds into multiple other sheets, so updating the formulas referencing the range isn’t practicable.

    Reply
    • John

      Yes, it only takes care of references in the cells you are cutting.

      Maybe try power query to transpose the data from the data entry area to the format you require.

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest

Share This