Transposing data means changing the orientation of an array by converting its rows into columns or vice versa. In this tutorial, we will be sharing with you five different methods for switching the orientation of an array and achieve the desired result.

Switch columns to rows with Paste Special

Transpose and link data to source

• With TRANSPOSE function
• With INDIRECT, ADDRESS, COLUMN and ROW functions
• With Paste Special and Find & Replace
• With Power Query

Download transpose data examples (.xlsx)

Video: Transpose data in Excel

Switch columns to rows with Paste Special

Step 1: Copy the range to be transposed

Select the range of cells where you want to switch rows and columns, and then press Ctrl + C to copy the range.

Step 2: Select the Paste Transpose option

Right-click on the first cell of the destination range, and then click the Transpose icon (under Paste Options) from the right-clicking menu.

Result

Voila! The range is transposed in no time!

Transpose and link data to source

To dynamically switch the orientation of a range (switch columns to rows and vice versa) and connect the switched result to the original dataset, you could use any of the approaches below:

• TRANSPOSE function (Easy to use but results are non-editable)
• INDIRECT, ADDRESS, COLUMN and ROW functions (Large formula but allows making changes in results)
• Paste Special and Find & Replace (Complex but easy to understand)
• Power Query (Easy with results formatted in a table)

Change rows to columns using TRANSPOSE function

To turn rows to columns and vice versa with the TRANSPOSE function, please do as follows.

Step 1: Select same number of blank cells as the original set of cells but in other direction

Tip: Skip this step if you are using Excel 365 or Excel 2021.

Let’s say your original table is in range A1:C4, which means the table has 4 rows and 3 columns. So, the transposed table will have 3 rows and 4 columns. Which means you should select 3 rows and 4 columns of blank cells.

Step 2: Enter the TRANSPOSE formula

Type in the formula below in the formula bar, and then press Ctrl + Shift + Enter to get the result.

Tip: Press Enter if you are Excel 365 or Excel 2021 users.

=TRANSPOSE(A1:C4)

Result

The rows are turned into columns, and the columns are converted to rows.

Rotate data using INDIRECT, ADDRESS, COLUMN and ROW functions

Although the above formula is quite easy to understand and use, the drawback of which is that you could not edit or delete any cells in the rotated table. So, I will introduce a formula using INDIRECT, ADDRESS, COLUMN and ROW functions. Let’s say your original table is in range A1:C4, to perform a column-to-row transformation and keep the rotated data connected to the source dataset, please follow the steps below.

Step 1: Input the formula

Enter the below formula in the top-left-most cell of the destination range (A6 in our case), and press Enter:

=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))

Step 2: Copy the formula to right and below cells

Select the formula cell, and drag its fill handle (the small green square in the lower right corner of the cell) down and then to the right to as many rows and columns as needed.

Result

The columns and rows are switched right away.

Convert columns to rows with Paste Special and Find & Replace

A few more steps with the Paste Special method along with the Find and Replace feature allow you to transpose data while linking the source cells to the transposed ones.

Step 1: Copy the range to be transposed

Select the range of cells you want to transpose, and then press Ctrl + C to copy the range.

Step 2: Apply the Paste Link option

1. Right-click on a blank cell, and then click Paste Special.

   

2. Click on Paste Link.

   

You will get the result as shown below:

Step 3: Find and Replace the equal signs (=) from the Paste Link result

1. Select the result range (A6:C9) and press Ctrl + H, and then replace = with @EO (or any character(s) that does not exist in the selected range) in the Find and Replace dialog.

   

2. Click on Replace All, and then close the dialog. The following shows what the data will look like.

   

Step 4: Transpose the replaced Paste Link result

Select the range (A6:C9) and press Ctrl + C to copy it. Right-click on a blank cell (here I selected A11) and select the Transpose icon from Paste Options to paste the transposed result.

Step 5: Get equal signs (=) back to link transposed result to the original data

1. Keep the transposed result data A11:D13 selected, and then press Ctrl + H, and replace @EO with = (the opposite of step 3).

   

2. Click on Replace All, and then close the dialog.

Result

The data is transposed and linked to the original cells.

Transpose and link data to source with Power Query

Power Query is a powerful data automation tool that allows you to easily transpose data in Excel. You can get the job done by following the steps below:

Step 1: Select the range to be transposed and open the Power Query Editor

Select the range of data to be transposed. And then, on the Data tab, in the Get & Transform Data group, click From Table/Range.

Step 2: Convert columns to rows with Power Query

1. Go to the Transform tab. In the Use First Row as Headers drop-down menu, select Use Headers as First Row.

   

2. Click on Transpose.

   

Step 3: Save the transposed data to a sheet

On the File tab, click Close & Load to close the Power Editor window and create a new worksheet to load the transposed data.

Result

The transposed data is converted to a table in a newly created worksheet.

Related articles

• How to quickly transpose ranges and skip blank cells in Excel?
• When we paste a range as transposed, the blank cells will be pasted as well. However, sometimes, we just want to transpose the ranges ignoring blank cells as below screenshot shown, Are there any ways to quickly transpose ranges and skip blanks in Excel?
• How to transpose every 5 or n rows from one column to multiple columns?
• Supposing, you have long data in column A, and now, you want to transpose every 5 rows from column A to multiple columns, such as transpose A1:A5 to C6:G6, A6:A10 to C7:G7, and so on as following screenshot shown. How could you deal with this task without copying and pasting repeatedly in Excel?
• How to transpose / convert columns and rows into single column?
• When you use Excel worksheet, sometimes, you will meet this problem: how could you convert or transpose a range of data into a single column? (See the following screenshots:) Now, I introduce three quick tricks for you to solve this problem.
• How to transpose / convert columns and rows into single row?
• How to join multiple rows and columns into a single long row? Maybe, it seems easy for you, because you can copy them one by one and join them into a row manually. However, this can be time-consuming and tedious if there are hundreds of rows and columns. Here I will talk about some quick tricks to solve it.