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
- Select data set to be transposed
- Type control-C to copy, “marching ants” will appear around the data selected
- Select the top left hand cell of the desired position for the transposed data
- Right click (or click on paste icon on top left of Home screen) then select Paste special click all-transpose-OK
- 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
- Select an area of exactly the same number of cells as the original or source data where the transposed data should be positioned.
- 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.
- Select the original or source data set.
- 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/