Uploaded by contextures on 08.01.2011

Transcript:

With the TRANSPOSE function in Excel you can take data that's arranged horizontally and

display it vertically or vice versaand you keep the links to the original data so if

something changes here

it would also change in the displayed

data with the TRANSPOSE fraction So, to look at an example of

how we can use it it we have 4 years of data

and the units sold in each year

to TRANSPOSE that, I would like to have the years down the column

and the

unit sales beside each year So I've got

4 columns by 2 rows

To TRANSPOSE that

I'll select the opposite, so I want

4 rows and 2 columns

With those cells selected I type =

TRANSPOSE(

and then

the cells from the original data

close the bracket =TRANSPOSE(B1:E2)

and the TRANSPOSE function is array entered so on the keyboard

press the Ctrl and Shift keys

and then tap the Enter key.

If you look in the formula bar, you can see curly brackets

have been added at the beginning

and end of that formula, to show that it's array entered.

So there's the data

that's gone from horizontal

to vertical

You can do that with other functions though. The TRANSPOSE function is a bit limiting

because you have to know ahead of time

exactly the size of the original data

and select that size in the

location where you're pasting it

If you use a function like INDEX instead, so here I've used INDEX

and I could just make that more flexible so I'm not sure how many

years of data I'm going to have Each formula is individually entered

in a cell, it's not array entered so it gives me a

bit more flexibility

So that's an alternative to using TRANSPOSE, is to to use another function like INDEX.

If you don't need to keep the links

you can also use Paste Special to change data from horizontal

to vertical.

So if I've selected these three cells,

and on the Home tab, I'll copy them

and I can select a cell where I'd like to start

pasting those.

I don't have to pick the range that's the exact size, I just select a starting cell

and then from the Paste dropdown

click TRANSPOSE and there is the transposed data,

and the original is still there. They're not linked at all

but you have the data

in a different layout.

And you can use TRANSPOSE

in other formulas. So this is a very

long complicated formula but you can see TRANSPOSE

is used in here. You can see curly brackets

at the end and the beginning so this is another array entered

formula

and it's looking at all these years of salaries

and finding

the best four consecutive years.

display it vertically or vice versaand you keep the links to the original data so if

something changes here

it would also change in the displayed

data with the TRANSPOSE fraction So, to look at an example of

how we can use it it we have 4 years of data

and the units sold in each year

to TRANSPOSE that, I would like to have the years down the column

and the

unit sales beside each year So I've got

4 columns by 2 rows

To TRANSPOSE that

I'll select the opposite, so I want

4 rows and 2 columns

With those cells selected I type =

TRANSPOSE(

and then

the cells from the original data

close the bracket =TRANSPOSE(B1:E2)

and the TRANSPOSE function is array entered so on the keyboard

press the Ctrl and Shift keys

and then tap the Enter key.

If you look in the formula bar, you can see curly brackets

have been added at the beginning

and end of that formula, to show that it's array entered.

So there's the data

that's gone from horizontal

to vertical

You can do that with other functions though. The TRANSPOSE function is a bit limiting

because you have to know ahead of time

exactly the size of the original data

and select that size in the

location where you're pasting it

If you use a function like INDEX instead, so here I've used INDEX

and I could just make that more flexible so I'm not sure how many

years of data I'm going to have Each formula is individually entered

in a cell, it's not array entered so it gives me a

bit more flexibility

So that's an alternative to using TRANSPOSE, is to to use another function like INDEX.

If you don't need to keep the links

you can also use Paste Special to change data from horizontal

to vertical.

So if I've selected these three cells,

and on the Home tab, I'll copy them

and I can select a cell where I'd like to start

pasting those.

I don't have to pick the range that's the exact size, I just select a starting cell

and then from the Paste dropdown

click TRANSPOSE and there is the transposed data,

and the original is still there. They're not linked at all

but you have the data

in a different layout.

And you can use TRANSPOSE

in other formulas. So this is a very

long complicated formula but you can see TRANSPOSE

is used in here. You can see curly brackets

at the end and the beginning so this is another array entered

formula

and it's looking at all these years of salaries

and finding

the best four consecutive years.