Transcripts for the above video clip:

TRANSPOSE

 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.