Скачать или смотреть Change Horizontal Data to Vertical - Excel TRANSPOSE Function

Use the TRANSPOSE function in Excel to change vertical data to horizontal, or vice versa.

If you don't need to keep links to the original data, use the Paste Special Transpose command instead.

You can use the TRANSPOSE function to do the following:

--Change data orientation from horizontal to vertical
--Use it with other functions to total a range of cells

Watch this video to see how to use the Excel TRANSPOSE function, and combine it with other functions.

Instructor: Debra Dalgleish, Contextures Inc.
More Excel Tips and Tutorials: http://www.contextures.com/tiptech.html
Subscribe to Contextures YouTube: https://www.youtube.com/user/contextu...
#ContexturesExcelTips

VIDEO TRANSCRIPT

With the TRANSPOSE function in Excel, you can take data that's arranged horizontally and display it vertically or vice versa, and you keep the links to the original data.

So if something changes here, it would also change in the display data with the TRANSPOSE function.

To look at an example of how we can use it, we have four years of data and the units sold in each year.

To transpose that, I would like to have the years down a column and the unit sales beside each year.

I've got 4 columns by 2 rows. To transpose that, I'll select the opposite. I want 4 rows and 2 columns.

With those cells selected, I type =TRANSPOSE, open bracket, and then the cells from the original data.

Close the bracket and the TRANSPOSE function is array entered. So on the keyboard, press the Ctrl and shift keys, and then tap the Enter key.

And 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 the size, 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.

I'm not sure how many years of data I'm going to have. Each formula's 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.

If I selected these three cells, and on the Home tab, I'll copy them. Then 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 the starting cell and then from the Paste drop down, click Transpose and there's the transposed data, and the original is still there.

They're not linked at all, but you have the data in a different layout.

You can use TRANSPOSE in other formulas. 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.