In this example, we've got a number of countries down the left-hand side and we've got fruit along the top. What we'd like to do is swap those positions. We've already swapped the headings around. What I'll do is I'll go up to the numbers, I'll select them and I'll copy them, and then I'll go to my destination cell and I'll do paste special Control + Alt + V and then I want to press E for transpose. Transpose then swaps them around. So we can sit that the 4.1 and the 12.7 in our top box have swapped around. Then our 4.1, 12.7 in the opposite directions. For the transpose function you firstly select your output cells and you then type in the transpose function. It then asks you, "where's the array that you want to transpose?" We go and we select all of that. But instead of pressing enter I now need to press Control + Shift + Enter. That now has produced an array output again with my numbers switched around. In addition, we can use the offset function with the row and column functions. I start off by typing in offset and it asks me, "what's your reference cell?" Well, my first reference cell is going to be B5. That's where I'd like to start, I'm gonna lock that. It then says "how many rows do you want to move to the right?" Well, I'm gonna use the column function to insert the number of rows. My column function, I'm gonna type in A1 that will work out that that is the first column. I'll then ask "how many columns do you want to go to the right?" I will use the row function and from A1 that's in the first row.

If I copy that down and to the right as I've switched the column and the row around it's switched them around again so that 4.1 and the 12.7 have been switched. Next, so we could use the offset and match, very popular, this one. Type in my offset function, again it asked for a reference. I'm gonna go for B5 again. I'll lock that but now when it asks what row to go for I'm gonna get it to use the match function and it asks me "what value would you like to look up?" What I'd like to look up, the country US. And I'm going to lock the row so this copies to the right. Then asks, "where do you want to look for that?" Well I want to look for that in the column of options. The column of options down the left hand side here.

Okay, so I'll lock those and I put a zero 'cause I want an exact match. Next up it asks "how many columns do you want to go to the right?" I'll use the match function again here, but for this match function, I'm gonna get it to match to B45. Again, I'm going to lock onto the B so it stays in column B.

Where do I want to look? I've scrolled all the way up to the top, to that top table but I'm now going to search within the row of fruit and again I'll lock them. I'll type zero again because I want an exact match. Closed brackets, put closed brackets. And if I copy that down into the right, that has switched my countries and my fruits around. 12.7, 4.1, same as we expected. One extra to show you here, that is the index match function. So I type an index it asks, "where's your array?" My array is in C6 to I11, I'm going to lock onto that. It then ask me, "which row number do you want to go for?" Well, the row number I'd like to go for is wherever US is, I want to match US. And again, I'll match that, I'll lock that to row 54 and I'll match it to the column of items down the left hand side, lock that and zero. It then asks "how many columns do I want to move to the right?" I'll use that second match function. This time though, I want to search for bananas. So lock onto column B.

I tell it I want it to look between C5 and I5. I'll lock onto them. I want an exact match, so I'll use a zero. Copy it down and to the right. And again, we've seen those things all transposed.