Skip to main content

5 ways to transpose data in Excel (Step-by-step tutorial)

Author: Amanda Li Last Modified: 2023-06-08

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.


Video: Transpose data in Excel


Switch columns to rows with Paste Special

Note: If your data is in an Excel table, the Paste Transpose option will not be available. You should convert the table to a range first by right-clicking on the table, then selecting Table > Convert to Range from the context menu.

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!

Note: The transposed data is static and independent from the original dataset. If you make any changes to the original data, those changes would not be reflected in the transposed data. To link the transposed cells to the original ones, please go to the next section.

(AD) Transpose table dimensions easily with Kutools

Converting a cross table (two-dimensional table) to a flat list (one-dimensional list) or vice versa in Excel always takes a lot of time and effort. However, with Kutools for Excel installed, its Transpose Table Dimensions tool will help you do the conversion quickly and easily.

Kutools for Excel - Includes more than 300 handy functions, making your jobs much easier. Get a 30-day full-featured free trial now!


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:


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)
Notes:
  • You should change A1:C4 to your actual source range that you want to transpose.
  • If there are any empty cells in the original range, the TRANSPOSE function would convert the empty cells into 0s (zeros). In order to get rid of the zero results and keep the blank cells while transposing, you need to take advantage of the IF function:
  • =TRANSPOSE(IF(A1:C4="","",A1:C4))

Result

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

Note: If you are using Excel 365 or Excel 2021, by pressing Enter key, the result automatically spills into as many rows and columns as needed. Make sure the spill range is empty before you apply the formula; otherwise the #SPILL errors are returned.

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)))

Notes:
  • You should change A1 to the top-left-most cell of your actual source range that you will transpose, and keep the dollar signs the way they are. The dollar sign ($) before the column letter and row number indicates an absolute reference, which keeps the column letter and row number unchanged when you move or copy the formula to other cells.
  • If there are any empty cells in the original range, the formula would convert the empty cells into 0s (zeros). In order to get rid of the zero results and keep the blank cells while transposing, you need to take advantage of the IF function:
  • =IF(INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))=0,"",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.

Note: The original formatting of the data is not saved in the transposed range, you can format cells manually if necessary.

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.

Note: The original formatting of the data is lost; you can restore it manually. Please feel free to delete the range A6:C9 after the process.

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.

Notes:
  • If the selected range of data is not in a table, a Create Table dialog box will pop up; click OK to create a table for it.
  • If you’re using Excel 2013 or 2010 and you cannot find From Table/Range on the Data tab, you will need to download and install it from the Microsoft Power Query for Excel page. Once installed, go to the Power Query tab, click From Table in the Excel Data group.

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.

Notes:
  • Additional column headers are generated in the first row as shown above. To promote the first row below the headers to column headers, select a cell within the data and click on Query > Edit. Then, select Transform > Use First Row As Headers. At last, select Home > Close & Load.
  • If any changes are made to the original dataset, you can update the transposed data above with those changes by clicking on the Refresh icon beside the table in the Queries & Connections pane, or by clicking the Refresh button on the Query tab.

(AD) Transform range with Kutools in few clicks

The Transform Range utility in Kutools for Excel can help you easily transform (convert) a vertical column into several columns or the other way around, or convert a row into multiple rows or vice versa.

Kutools for Excel - Includes more than 300 handy functions, making your jobs much easier. Get a 30-day full-featured free trial now!

Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations