How to Transpose Array in Excel (3 Simple Ways)

Sometimes we need to transpose rows into columns and columns into rows in Excel. Knowing how to perform this operation provides us a ton of flexibility while working with a dataset. There are several ways to convert columns of data into rows and vice versa. Some of these techniques are static while some of these are dynamic. In this blog post, we are going to unleash all the methods regarding transposing arrays in Excel.

Download the Practice Workbook

We have included an Excel workbook for you to practice along with it. In this workbook, you will find a database of a few girls with their corresponding ages. There are a total of 6 sheets in the following workbook. The first sheet is called Intro, which holds the raw dataset. The rests are for the 3 described methods respectively.

3 Ways to Transpose Array in Excel

We have come up with 3 simple ways to transpose an array in Excel.  Under each of the methods, you will find two sub-methods regarding transposing columns into rows and rows into columns for each method. Without further discussion, let’s dive right into them:

1. Copy and Paste

This is a simple technique to transpose an array in Excel. But this one is static. This means that, if you change the value within a particular cell, it doesn’t get updated automatically in the transmuted array. You can follow this technique if such scenarios don’t cross your path. So, let’s see it works:

A. Convert Columns into Rows

Step-1: Select the area of the cells that you want to transpose.

Step-2: Right Click on the selection area and click on Copy.

how to transpose array in excel (copy and paste method)

Step-3: Select a cell to paste it.

Step-4: Right-Click on it and then go Paste Special.

Step-5: Select Transpose from the list.

using paste special command to paste values in excel

B. Convert Rows into Columns

Step-1: Select the area of the cells that you want to transpose.

Step-2: Right Click on the selection area and click on Copy.

copy cells in excel

Step-3: Select a cell to paste it.

Step-4: Right-Click on it and then go Paste Special.

Step-5: Select Transpose from the list.

paste special command in excel

Read More: Excel Paste Transpose Shortcut: 4 Easy Ways to Use


Similar Readings


2. Transpose Function

Excel has a built-in function called TRANSPOSE to convert rows into columns and vice versa. This is a dynamic method as this one uses functions to operate. Without further discussion, let’s have a look at how to use this function:

A. Convert Columns into Rows

Step-1: Count the number of cells that you want to transpose.

Step-2: Select the exact number of cells to paste as the source dataset.

Step-3: Type

=TRANSPOSE(B5:C12)

in the first cell of the selected area.

Step-4: Press CTRL + SHIFT + ENTER.

transpose array in excel using transpose function

B. Convert Rows into Columns

Step-1: Count the number of cells that you want to transpose.

Step-2: Select the exact number of cells to paste as the source dataset.

Step-3: Type

=TRANSPOSE(B4:I5)

in the first cell of the selected area.

Step-4: Press CTRL + SHIFT + ENTER.

transpose array in excel using transpose function 2

Read more: Transpose Multiple Rows in Group to Columns in Excel

3. Using Cell Reference

This method is also a dynamic one and it is tricky. We can use a cell reference to copy data and then we can manually convert columns into rows and vice versa. But that seems so intimidating. So, we will use a trick instead of the conventional cell reference method. Here’s the whole process:

A. Convert Columns into Rows

Step-1: Type edB5 in cell B14 and edC5 in cell B15.

  • Here, “ed” is a random prefix that refers to Exceldemy. You can use anything you might like.
  • B5 and C5 are the beginning of the cell area that we want to convert.
  • B14 and B15 are the beginning of cells where we will put the transposed values.

Step-2: Now, select edB5 and edC5 and then drag the Fill Handle icon up to edB12 and edC12.

transpose array in excel using cell reference

Step-3: Press CTRL + H to get the Find and Replace window.

Step-4: Type “ed” in the Find What bar and “=” in the Replace With bar.

Step-5: Then click on the Replace All option.

transpose array in excel using cell reference 2

Read More: Excel Transpose Formulas Without Changing References (4 Easy Ways)

Things to Remember

  • Method 1 is static.
  • Method 2 & 3 are dynamic.
  • The destination area should be properly matched in terms of cell numbers with the source area of data in the case of methods 2 and 3.

Conclusion

Three simple methods to transpose an array in Excel have been demonstrated in this article. Practice all of them along with the given practice workbook and find which method best suits your cases. But you are recommended to use the second method which uses the TRANSPOSE function, as this procedure will provide you tons of flexibility.


Further Readings

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo