5 ways to transpose data in Excel (Step-by-step tutorial)
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.
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.
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.
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.
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:
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 (A6in our case), and press Enter:
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:
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.
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
Right-click on a blank cell, and then click Paste Special.
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
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.
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
Keep the transposed result data A11:D13 selected, and then press Ctrl + H, and replace @EO with = (the opposite of step 3).
Click on Replace All, and then close the dialog.
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.
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
Go to the Transform tab. In the Use First Row as Headers drop-down menu, select Use Headers as First Row.
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.
The transposed data is converted to a table in a newly created worksheet.
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.
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?
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?
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 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.