Hi, this is Wayne again with a topic “The Excel TRANSPOSE Function”.
In this short excel video, i want to show you how to use the transpose function in excel here. I am in an excel spreadsheet that i’ve started using and i’m regretting a little bit how i laid out this information. I don’t know if you’ve ever had that experience where you start typing in data you put in your column headings you put in your rows and then you say to yourself wait a minute. Maybe i should have put in this case the names vertically in column a instead of horizontally in row, one. I know that’s happened to me a few times.
In other cases, the way i’ve entered the data is fine and it’s what i want, but i might want to have it also displayed in the opposite way, maybe on a separate spreadsheet, so we’re going to look at an excel function called transpose. That will help us. Do that and i’m going to begin by clicking on this plus sign to add sheet number two and i’ll go to the upper left corner of where i want the new data to appear and i’ll tap the equals sign and type in transpose. You can see that excel recognizes the function that i’m trying to use, and it gives me a description. It converts a vertical range of cells to a horizontal range, or vice versa. So now i’ll type left, parenthesis and excel is now expecting an array. In other words, a range of information, so i’m going to click back on sheet 1 and then i’ll simply click and drag to highlight the range of information that i would like to transpose. I think i got everything and now i’ll just type the right parenthesis here in the formula bar.
You can see that same formula that i started on sheet2 is still present with me here through the formula bar and i put in the right parenthesis. It usually works. Fine without the right parenthesis, but i’m trying to get in the habit of always putting it in there.
So now i’ll tap enter on the keyboard and i get a little message saying my formula spilled: the formula returned multiple values, so we spilled them into the neighboring. Blank cells got it. That sounds fine to me.
So, let’s see how this worked. Just like i expected it took the data on sheet 1 that has people’s names across the top and phone number donation amount and state across the left side of the spreadsheet and on sheet 2. It flipped all of that. That’S exactly what i wanted, but to help us see this better, i’m going to click and drag from a to d here at the top in the column, letters and then i’ll, just double click between any two of those columns.
How about here, between c and d, right on that line, just double click that will automatically resize the columns so that the information in the columns fits perfectly. Let’S take a look at this data, so for the most part it looks like it worked. Well, let’s test it out with catherine mcinturf. If i go back to sheet1, i can see that katherine mcinturf her phone number ends in 58. She donated 58, that’s a coincidence and she’s from new jersey.
Let’S test that out. Catherine mcinturf, 58, 58 new jersey. So it looks like the information was transposed accurately and safely.
Having said that, it did change the way some of the data is displayed, for example, it added a zero here and unfortunately i can’t just delete that now. The reason why is because, even though it says zero there’s something other than zero in that cell, if i select it, you can see it here in the formula bar. That’S where my transpose formula is located. So if i delete that cell and tap enter it erases all of the data off of sheet 2. – it’s still on sheet 1, but not sheet 2..
So i’m going to hold ctrl and tap z to undo that. So i need to leave that there looks like my phone numbers got messed up a little bit, so i can click on b go to the home tab and in the number group i can change the number format by going here to more number formats, and i Can go to special phone number now those are converted to phone numbers. I could do the same kind of thing with the dollar amounts, but for the most part this has been a very successful transposition that i’ve done now. It’S important to know that if i change the information in sheet 1, like the donation amount for morgan, let’s say he donates a little more morgan’s information will also change on the linked sheet when it transposes the links stay intact.
If that’s not what you want, you should probably transpose using copy and paste special, and if you want to see how to do that, watch my tutorial on transposing data in excel. The other thing you could do is now that you’ve transposed the data. You can click and drag to highlight all of the data and then hold ctrl and tap c to copy the data, and then you could go to another sheet, let’s say or somewhere in the same sheet: click in the upper left corner of where you would like That data to be displayed and then go to the home, tab, home, ribbon, clipboard group and i’m going to click here on the bottom part of the paste button and i’ll just go here to paste values. Now, let’s try it. If i delete that 0, the data is still there. Why? Because, when i did paste special and pasted the data as values, it converted the data from information that was created by the transpose function and formula that i wrote. It changed it from that and converted it to simply values. So now, at this point, if i wanted to, i could delete sheets 1 and 2 and sheet 3 is still intact. This is now independent data.
I hope that you found this tutorial on the transpose function. To be helpful, if you did please like follow and subscribe, and when you do, please click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, you can do that through my patreon account and you’ll see a link to that in the description below speaking of patreon.
I want to give a quick shout out to my five dollar patreon supporters. Thank you so much for all you do to support my channel. .