How to transpose cells in excel either manually via the Paste Special or automatically with a formula.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 03:23
- - Skill Level: 5
- - What's my skill level
Transpose cells in Excel from rows to columns or columns to row
The TRANSPOSE features give you the ability to quickly convert a row of information into columns or vica versa. And there are two methods, one being a manual method and one being an automatic method. You’ll learn both those methods in this segment.
Spreadsheets consist of rows and columns and while working with your spreadsheet you sometimes come across information that is in a row that you need to have in a column as well, or in a column that needs to be in a row.
A lot of people will simply retype the information or manually link the information to each individual one. However Excel has a couple of features built into it to make this an easier process, one a manual version and another an automatic version. The manual version is stored in the Paste Special command, so for example if you want this row to be down here you can say
copy
you click on the cell which is going to be the start of your column,
you go to Edit
Paste Special
and then you’ll see down here there’s and option called TRANSPOSE
and when you click on TRANSPOSE
and say ok,
what it does is it takes the copied item and if it’s a row it goes to that cell and makes it a column
and if it’s a column it goes to the cell and makes it a row.
The automatic version allows you to do the same thing however it is set up so that it is linked to the original. So for example
if you change this name,
the corresponding name will change,
In order to activate this let me just delete that first, what you need to do is
you highlight the cells that are going to contain the information
and you need to make sure that they match to information that is going to be pulled through,
then you click on the Function Wizard
you need to find the TRANSPOSE function which sits in the Lookup and Reference
and you can go to All ,
click inside there
and just push your T
and you’ll go straight down to the T’s and you just look for TRANSPOSE
click on it,
push Ok
what its asking for here is an Array, its saying where must I go to get the information
and in this case you’ll highlight these five cells,
most important here, this is an array formula and the only way to activate it is to push
– control
– shift
– and enter
It’s very important you do that, so its
– control
– shift
– and enter
and what you’ll see is there is now a formula in each of these cells
and what its saying is, go to this row here and pull through the relevant link,
so now for example if we changed one of these names to for example (Glasgow) ,
when you enter it
the corresponding name changes.