Bruce from Texas calls in today with an Excel problem. Bruce needs to turn a worksheet on its side, moving the rows to columns and columns to row. Episode 361 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today we have another viewer calling, if you have a question, you can always call 866-581-0221, leave your question on the voicemail, and we'll try and get to you on a future netcast.
Today's call’s from Bruce: “Hello Bill, this is Bruce in San Angelo, Texas.
Got a question, on a spreadsheet I have a list of buildings that go all the way across the top, and then on the sides I have the utilities, electricity, gas, water.
I've decided I no longer want all the buildings across the top, I'd rather those come down the side of the spreadsheet, and just lift the utility type across the top.
Wondering if there's a quick way to just swap those columns to rows and rows to columns, just wondered if you might be able put that on a podcast.
Thanks so much, bye-bye.” That's a great question, and that actually comes up a lot in our power Excel seminars, people ask “How do we swap rows and columns?” So I'll just build a quick worksheet here, with buildings going across the top, and let me just throw in some random numbers.
I'm going to hit Ctrl+Enter here to fill all those in at once, Paste Special Values, OK.
So, now I have buildings going across the top of the worksheet and different utilities going down the side, we want to swap those, basically turn the range on its side.
So the first step is we select the entire data set, and use Edit, Copy to copy it to the clipboard.
And then we have to go to a new section of the worksheet, just go to a blank cell with plenty of space around it.
And we're going to use Edit, Paste, Special, and then in the Paste Special dialogue at the bottom we're going to choose Transpose!
Transpose is Excel’s fancy way of saying “turn it on its side.” Click OK, and our data that used to go across what now go down and vice versa.
You can also transpose, obviously, data that's going horizontally back to vertically.
So if I would do this again, Edit, Paste, Special, and choose Transpose, it will turn back to the original shape.
Great trick for turning data on its side!
It happens all the time, someone sent you a worksheet, and it doesn't quite match what you have, you turn the data on inside using Paste Special Transpose.
Hey, don't forget, if you want to call to be on a future netcast, just give us a call.
866-581-0221, goes to voicemail, tell us who you are, where you're from, and leave the question, we'll get to you.
Thanks for stopping by, we'll see you next time for another podcast from MrExcel!
Today we have another viewer calling, if you have a question, you can always call 866-581-0221, leave your question on the voicemail, and we'll try and get to you on a future netcast.
Today's call’s from Bruce: “Hello Bill, this is Bruce in San Angelo, Texas.
Got a question, on a spreadsheet I have a list of buildings that go all the way across the top, and then on the sides I have the utilities, electricity, gas, water.
I've decided I no longer want all the buildings across the top, I'd rather those come down the side of the spreadsheet, and just lift the utility type across the top.
Wondering if there's a quick way to just swap those columns to rows and rows to columns, just wondered if you might be able put that on a podcast.
Thanks so much, bye-bye.” That's a great question, and that actually comes up a lot in our power Excel seminars, people ask “How do we swap rows and columns?” So I'll just build a quick worksheet here, with buildings going across the top, and let me just throw in some random numbers.
I'm going to hit Ctrl+Enter here to fill all those in at once, Paste Special Values, OK.
So, now I have buildings going across the top of the worksheet and different utilities going down the side, we want to swap those, basically turn the range on its side.
So the first step is we select the entire data set, and use Edit, Copy to copy it to the clipboard.
And then we have to go to a new section of the worksheet, just go to a blank cell with plenty of space around it.
And we're going to use Edit, Paste, Special, and then in the Paste Special dialogue at the bottom we're going to choose Transpose!
Transpose is Excel’s fancy way of saying “turn it on its side.” Click OK, and our data that used to go across what now go down and vice versa.
You can also transpose, obviously, data that's going horizontally back to vertically.
So if I would do this again, Edit, Paste, Special, and choose Transpose, it will turn back to the original shape.
Great trick for turning data on its side!
It happens all the time, someone sent you a worksheet, and it doesn't quite match what you have, you turn the data on inside using Paste Special Transpose.
Hey, don't forget, if you want to call to be on a future netcast, just give us a call.
866-581-0221, goes to voicemail, tell us who you are, where you're from, and leave the question, we'll get to you.
Thanks for stopping by, we'll see you next time for another podcast from MrExcel!
