Excel transpose columns and rows

In this video tutorial on 2 methods to use Excel transpose columns and rows I run through the basics for beginners on how to transpose an Excel data set from columns to rows and rows to columns. The first method uses the paste and paste special tools and is called the static method because the new transposed set of data does not update when the original or source data is updated. The second method is called the dynamic method and uses an Excel function or formula called Transpose, with this method when the source or original data set is updated the transposed data set is updated at the same time. However with the dynamic method the transposed data is in the form of an array and therefore formatting is often lost when transposed and correcting or altering the transposed data is difficult.

Here are some step by step instructions for both methods of transposing Excel data:-

Static Method of transposing data in Excel

  1. Select data set to be transposed
  2. Type control-C to copy, “marching ants” will appear around the data selected
  3. Select the top left hand cell of the desired position for the transposed data
  4. Right click (or click on paste icon on top left of Home screen) then select Paste special click all-transpose-OK
  5. This will transform the data in the selected position complete with the original formatting and retain the original data set. Note the transformed data will not update if changes are made to the original data set, hence the name static method.

Dynamic method of transposing data in Excel

  1. Select an area of exactly the same number of cells as the original or source data where the transposed data should be positioned.
  2. Type “=transpose”. A function or formula (Transpose) will be suggested in blue. Double left click on this. Excel will ask for an array to be selected.
  3. Select the original or source data set.
  4. Type “cntrl+shft+enter” to confirm, this will transpose the data but without the original formatting. Note if the original data is updated this will also update the transposed data but changes to the new transposed data is difficult and is not reflected in the original source data.

Here is a link to the YouTube video on transposing Excel data:

Here are some time stamps for browsing the content of this video:

00:00 Beginning

00:50 Static method of transposing Excel’s columns to rows

01:30 Use paste-paste special for static method of transposing in Excel

02:08 Resize column width to fit data in MS Excel

03:55 Transpose Excel data set onto a different work sheet

04:36 Dynamic method of transposing Excel work sheet

05:03 Use Excel Transpose function or formula for the dynamic method of transposing

06:29 Updating Excel transposed data with the dynamic method

08:40 Making changes to an array in Excel

Comments are most welcome, if you decide to try this please send us some feedback☺️ Why not visit our channel and browse through our other videos? http://www.youtube.com/c/AnnErasmus

Here are some more Excel videos:

Short Disclaimer/disclosure: My videos/articles are for informational and educational purposes only, you should consult with a professional before attempting anything seen or done in my videos/articles. Please read full disclaimer here: https://basiclowdown.com/category/disclaimer-disclosure/