Transpose Function | How to Use Transpose Function in Excel | How to Transpose Using Paste Special
Hello and welcome back to excel tutorials powered by Acadgild. In this video, you will learn two topics, i.e. How to use the transpose function in excel and how to transpose using paste special. Before getting into this excel tutorial, we would suggest you to the following MS Excel video playlist to grow your excel knowledge.
Excel Videos for Data Analytics - • Excel Tutorial for Beg...
Conditional Formatting in Excel - • How to Highlight Cells...
VLOOKUP & HLOOKUP Functions in Excel - • VLOOKUP with Exact Mat...
Sort & Filter Functions in Excel - • How to Sort Data in Ex...
What is Transpose in Excel?
In simple language, converting the columns into rows.
How to Use Transpose Function in Excel?
Sometimes you need to switch or rotate cells. You can do this by copying, pasting, and using the Transpose option. But doing that creates duplicated data. If you don't want that, you can type a formula instead using the TRANSPOSE function. For example, in the following picture, the formula =TRANSPOSE(A1:B4) takes the cells A1 through B4 and arranges them horizontally.
Step 1: Select blank cells
So, we need to select eight horizontal cells, like this:
Step 2: Type =TRANSPOSE(
With those blank cells still selected, type: =TRANSPOSE(
Step 3: Type the range of the original cells.
Now type the range of the cells you want to transpose.
Step 4: Finally, press CTRL+SHIFT+ENTER
Now press CTRL+SHIFT+ENTER. Why? Because the TRANSPOSE function is only used in array formulas, and that's how you finish an array formula. An array formula, in short, is a formula that gets applied to more than one cell. Because you selected more than one cell in step 1 (you did, didn't you?), the formula will get applied to more than one cell. Here's the result after pressing CTRL+SHIFT+ENTER:
Tips
You don't have to type the range by hand. After typing =TRANSPOSE( you can use your mouse to select the range. Just click and drag from the beginning of the range to the end. But remember press CTRL+SHIFT+ENTER when you are done, not ENTER by itself.
Need text and cell formatting to be transposed as well? Try copying, pasting, and using the Transpose option. But keep in mind that this creates duplicates. So if your original cells change, the copies will not get updated.
There's more to learn about array formulas. Create an array formula or, you can read about detailed guidelines and examples of them here.
The TRANSPOSE function returns a vertical range of cells as a horizontal range or vice versa. The TRANSPOSE function must be entered as an array formula in a range that has the same number of rows and columns, respectively, as the source range has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array or range on a worksheet.
Hope this video helped you learn something new today. If you enjoyed this video please like, and share the video.
Subscribe to the channel for more videos. Drop-in your queries in the comment section for immediate response.
Негізгі бет Ғылым және технология Transpose Function | How to Use Transpose Function in Excel | How to Transpose Using Paste Special
Пікірлер: 1