Transpose Formula Excel

This topic has been explained in great detail in the video shown below, to learn this topic just play the video and the notes are mentioned below the video.


Transpose Formula Excel






How To Use convert Rows into Columns

Learn how to convert data in a row into a column or the from columns to rows. Its very easy to use and is very handy feature.

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.

Original cells above, cells with TRANSPOSE function below

The key to getting TRANSPOSE to work: Make sure to press CTRL+SHIFT+ENTER after typing the formula. If you’ve never entered this kind of formula before, the following steps will guide you through the process.



Step 1: Select blank cells

First select some blank cells. But make sure to select the same number of cells as the original set of cells, but in the other direction. For example, there are 8 cells here that are arranged vertically:

Cells in A1:B4

So, we need to select eight horizontal cells, like this:

Cells A6:D7 selected

This is where the new, transposed cells will end up.

Step 2: Type =TRANSPOSE(

With those blank cells still selected, type: =TRANSPOSE(

Excel will look similar to this:

=TRANSPOSE(

Notice that the eight cells are still selected even though we have started typing a formula.



Step 3: Type the range of the original cells.

Now type the range of the cells you want to transpose. In this example, we want to transpose cells from A1 to B4. So the formula for this example would be: =TRANSPOSE(A1:B4) — but don’t press ENTER yet! Just stop typing, and go to the next step.

Excel will look similar to this:

=TRANSPOSE(A1:B4)

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:

Result of formula with cells A1:B4 transposed into cells A6:D7

Tips



Technical details

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.

Syntax

TRANSPOSE(array)

The TRANSPOSE function syntax has the following argument:

  • array    Required. An array or range of cells on a worksheet that you want to transpose. The transpose of an array is created by using the first row of the array as the first column of the new array, the second row of the array as the second column of the new array, and so on. If you’re not sure of how to enter an array formula, see Enter an array formula.

This Excel tutorial has been created to help you learn microsoft excel online. On http://www.myelesson.org you get excel help and online file download facitity. This video lesson covers the entire topic in simple and easy to understand step by step approach.
Hindi








Our Other Services




Read Free Ebooks

myeboook.com

Myebook has 1000's of free ebooks for you. On productivity, health, online income, learning, music and many more, it's free of course!



Buy Excel Course

myelesson.org/product

Become an Excel Guru, Buy the best Excel course. Learn on your mobile, tv & laptop without internet. For COD, Call 9752003788



Create Best Looking Resume

http://www.makecv.org/

At Makecv.org you get to create a amazingly professional looking resume in just 3 steps with our intuitive resume builder!


Download Excel Crash Course


This Excel Crash Course contains the list of topics that cover the most important and used features and formula of Excel . This Excel Crash Course lists 43 major topics in Excel so that you can start using Excel like a pro in the shortest possible time.



Learn Photoshop Web Designing


You can Buy the Photoshop Course to learn Photoshop and start a career in Web Designing and Photo Editing. .



Learn HTML CSS


You can Buy the HTML CSS Course to learn HTML CSS and start a career in Web Designing and Photo Editing. .


Download Full list of Excel Shortcuts


This Excel Shortcuts Complete list is to help you become save time and use Excel like a Master level user. Excel is best used when you know the Keyboard shortcuts because the use of keyboard shortcuts in Excel helps you in completing tasks faster.

I have created a Excel Shortcuts complete list for you to use Excel better, this list has 242 excel keyboard shortcuts which means that it covers almost everything that can be done using shortcuts in Excel.




Full List of Excel Formulas

This is a complete alphabetical list of all the Formula in Excel with a description. You can learn all these formulas on www.Myelesson.org




Convert Numbers to Text in Excel

Now easily convert numbers to Text in Excel with this inbuilt sheet from myelesson.org .  Many times we need the amount in figures to be converted into words. This is a typical requirement for writing checks or any other financial reports. Microsoft Excel does not have standard function available for this requirement. Just download this sheet and then you can convert any number to text in Excel.




List of Most Commonly Asked Excel Questions in Interviews

Here is the list of most asked excel related questions in interviews.  Prepare for this excel questions and you would be able to most of the excel interviews. This list of excel questions covers excel formula, excel analysis, excel reporting, excel charts and more.





Myelesson © 2010 - 2014. All rights reserved.

Do You Want to Become a Expert in Excel ?
Buy The Full Excel Course Now
Enter Your Details & We Will Contact You
Great Choice
Thank You
Ok
Do You Want to Become a Expert in Excel ?
Buy The Full Excel Course Now
Enter Your Details & We Will Contact You
Great Choice
Thank You
Ok