Excel Tutorial on Transposing Data

This Excel tutorial on transposing data will show you 3 ways to transpose data. You know, converting horizontal excel data to vertical data or rows into columns. The reason for this Excel tutorial was that I had a client that had the responsibility of preparing data for a pivot table. However, the data they received was in the wrong format. Now it's true that there are other ways of accomplishing this task. But I thought if I went through 3 ways then the choice would be up to you.

#computertutoring #transposeinexcel #exceltutorial #horizontaltoverticaldata #datatableswhatifanalysis

Transposing data method 1 - Copy and Paste - 01:11

The copy and paste method is by far the most straightforward method of transposing data. If fact some of you may already be more that familiar with selecting text and using the paste special - transpose way of doing this. Obviously the downside to this is that it is not flexible. Should you want to change the source data you will have to go through this process again so if we were rating this I would give this a 4/10.

Transposing data method 2 - Using the TRANSPOSE function - 03:32

The Transpose function is an array function that allows you to transpose data from horizontal to vertical and vertical to horizontal. Whereas this solution of solving this Excel problem is a little more flexible that the copy & paste solution of method 1 but because it's an array function it can limit where you would use this. In fact at the time of recording the video I didn't know how to solve the problem of N/A appearing in cells that are outside the range of the formula. Now though I have solved the problem. It was actually pretty straight forward but you still have to remember to select the number of cells you want included in the formula. You also need to remember to hold down the Ctrl and Shift keys on the keyboard when you press enter. That's Right! the TRANSPOSE function is an array formula which means you have to select the number of cells that it applies to beforehand.

Transposing data method 3 - OFFSET() - 07:33

My suggestion is that you get familiar with the OFFSET function. It's a really handy one to know if you want to make your Excel formulas a load more flexible. Rather that have me go through the various arguments of the OFFSET function you're more than welcome to view the details here: https://www.computertutoring.co.uk/questions/excel/offset-excel-function.asp
#computertutoring

? Check out my website and social media to keep up to date - enjoy learning ?

? http://www.computertutoring.co.uk/ ?
? https://www.tiktok.com/@thecomptutor ?
???? https://www.facebook.com/ComputerTutoring.co.uk/ ????
? https://twitter.com/CT_Tutoring ?
?https://www.instagram.com/computer_tutoring/ ?
?https://www.pinterest.co.uk/CT_Training/ ?