Let's take a look at three ways you can transpose your data in Excel. (upbeat music) Method one is static, two and three are dynamic. Let me know in the comments below which method you use. For me method three has been my most used one so far. Here's our sample dataset. We have a list of apps
and their sales values. Method number one is the static method so what I'm gonna do is
to highlight the area that I want to transpose. I'm gonna press Ctrl+C, go to the area where I
want my result to sit in, right mouse click, Paste Special, put a check mark for Transpose, click on OK. That's my data. But a downside to this
is it's not dynamic, this is static. So if something changes here, it's not gonna pull through in here. Method number two is to
use the transpose formula and yes, there is a
formula called TRANSPOSE. It only needs one argument. So I'm just gonna highlight this, close bracket, Press Enter. It doesn't work! If I click inside the formula bar and I click on F9 to see what's behind it, I can see my apps here, I can see my numbers here, but it can't put all of
these results in one cell. And because TRANSPOSE is an array formula what I need to do is to highlight the area so the cells that I want the results in before I write the formula. Now the tricky part is to
highlight the area first because let's say I'm sure
if it goes until here, or here, or here. I can count but I'm not
patient enough to count this. I'm just gonna highlight this, go to the formula bar, type in the same formula, close bracket. Now here's the important part. Don't press Enter, press Ctrl, Shift, and then Enter. We see our results in separate cells, which is great, but it gets cut up to here because I didn't select
enough cells before. So what I'm gonna do
is to pull this further until here but notice the
result is all messed up. That's because I need to
go back to the formula bar and click Ctrl+Shift+Enter again. Now we can see it's corrected. Because it's a formula
everything I change here is gonna pull through. So that's the advantage
of using this function. Let's pause. The future dynamic arrays are gonna make transposing data much easier. You don't have to worry about highlighting the result area first before writing your formula. You also don't have to worry about pressing Ctrl+Shift+Enter because the formula is
gonna SPILL by default. I already posted a video on dynamic arrays. It shows you the potential
of what it can do. Check the link in the description. So let's move on to method number three. The aim is to have simple cell references. I want this to equal this and then I want this to equal this one. But the problem is that I
can't pull this, this way because when I do, it actually
moves this way, right, it's not moving down. And I could go through each one and type the formula in but that's gonna be super annoying, right? So instead what I'm gonna do is to type the formula
in the cell as text. Instead of typing in the equal sign I'm gonna put A3, and then here I'm gonna put A4 and so on. But I'm not gonna do this manually, I'm gonna use Excel's fill options to help me do that. One other thing I'm gonna do is to add a prefix to this cell reference. I'm gonna use my initials lg. And you're gonna see why in a second. So I'm gonna put lgA3 here and lgB3 here. That's supposed to be this cell. I'm gonna highlight
this and pull this over. Then notice what's happening here. It says lgB12-B14. The numbers after the initials are moving in the right direction. Ctrl+H. I'm gonna look for lg and I'm gonna replace it with the equal sign. I get my formulas in there. And they're simple formulas but it's much faster
than going into each cell and typing in the reference
to the correct cell, especially if you have a lot of columns and a lot of data. Now, also let me know
in the comments below what your favorite transpose method is. Give the video a thumbs-up if you liked it and subscribe to this channel if you want to learn more about Excel. (upbeat music)
