Change Horizontal Data to Vertical - Excel TRANSPOSE Function

Uploaded by contextures on 08.01.2011

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
I'll select the opposite, so I want
4 rows and 2 columns
With those cells selected I type =
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
and it's looking at all these years of salaries
and finding
the best four consecutive years.