How to transpose in Excel – 4 easy methods

Here are four simple methods.

Microsoft Excel logo displayed with a stylized "x" on a green square background, accompanied by a smaller square in a deeper shade of green, set against a pale green background with the "How to

You can trust PC GuideOur team of experts use a combination of independent consumer research, in-depth testing where appropriate – which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

Last Updated on

If you want to know how to transpose in Excel, we’ve got you covered right here.

Microsoft Excel has a large array of tools for organizing and managing data. There may be times when you have a set of data arranged neatly in a column but need to switch it up to a row. In other words, you may want to transpose in Excel. Altering the orientation of a set of cells isn’t hard and can be achieved using this function.

In this guide, we’ll walk you through how to transpose in Excel using four different methods.

How to transpose your data in Excel

Scenario on hand: We have a dataset with student marks. 

What we want to accomplish: Explore how to transpose your data in Excel in four ways:

  1. Transposing using the paste special tool 
  2. Using the TRANSPOSE function
  3. Transposing using a PIVOT table 
  4. Transposing using Find and Replace

Transposing using the paste special tool 

Step 1: Copy the table

The first and simplest way to transpose a table is to copy it by either right-clicking and pressing “Copy” or pressing the shortcut CTRL+C. 

Step 2: Paste Special

When you right-click anywhere on the Excel sheet, you will see a couple of paste options. One of these options is the Paste Transpose, which is available under “Paste Options” or “Paste Special…”

A screenshot of Microsoft Excel with a dataset for transposing, featuring a table with student names and test scores, an individual cell highlighted in yellow, and the right-click context menu opened showing paste options as

Alternatively, you can also click the “Paste Special…” button. 

A screenshot of Microsoft Excel with an open workbook showing a dataset. The image includes annotations indicating how to transpose data in Excel, highlighting the steps involving copying a selected range of cells and then using the '

Upon clicking this button, a Paste Special menu will open up where you can select “Transpose”:

A screenshot of Excel tips for how to transpose data, showing a dataset titled "dataset for transposing in Excel", with a 'paste special' dialog box open and the 'transpose' option highlighted and

Using the TRANSPOSE function

This method uses the TRANSPOSE function in Excel to transpose a table. 

Step 1: Select the cells where you want the transposed table

If you do not have Excel 365 or 2016, the first step for this method is to select the cells where you want the transposed table. 

The selected number of cells must be the exact transpose of the table. 

This means that if you wish to transpose a 4×3 table, you should choose an area of 3×4 for the transpose function to work correctly. 

In our demo, we have a table with seven columns and ten rows. For the transpose function to work correctly, we will have to select an area consisting of seven rows and ten columns. 

Note: 

  • If you don’t have Excel 365 or 2016 and don’t select the right number of cells, it will leave out part of the data. 
  • If you select cells greater than needed, you will see an N/A value in the empty cells. 
  • This error can also appear if you select less or more than the required number of cells in Excel 365 or 2016 and press CTRL+SHIFT+ENTER instead of only ENTER:
A screenshot of a Microsoft Excel spreadsheet with various cells highlighted in red, indicating errors or missing values, specifically displaying '#n/a' in the cells. The spreadsheet appears to be tracking student names and their

Step 2: Type the TRANSPOSE formula

If you have Excel 365 or 2016, you can directly choose a single cell to type the transpose formula. 

If you have a different version of Excel, type the transpose formula in the array you’ve selected. 

Here is the syntax of the TRANSPOSE function:

=TRANSPOSE(table array)

For our demonstration, we used this formula:

=TRANSPOSE(B5:H14)

 Here’s the result:

A screenshot of a Microsoft Excel spreadsheet displaying a list of student names and their corresponding scores for various tests and projects, with the focus on a cell containing an Excel function that calculates the sum of scores for

For Excel 365 and 2016, simply type the transpose formula and press Enter. 

For other versions of Excel, type the transpose formula and press CTRL+SHIFT+ENTER’ to confirm your formula and transpose your cells.

Step 3: Use the paste special to disconnect tables

When you use the TRANSPOSE function to transpose a table, it pastes as an array that is connected to the original table. 

This means that it will only reflect the changes from the original table. 

You will not be able to change the table. If you try changing it, it will show a #SPILL error or display “You cannot change part of an array.” 

If you wish for the tables to remain connected, then this is the ideal way to transpose your table. 

But if you want to change the values in the transposed table without the values reflecting in the original table, you will have to fix it using the “Paste Special” again.

A screenshot of a Microsoft Excel workbook displaying a dataset with an error message "#spill" indicated by a red exclamation mark in a cell, where an attempt to transpose the data seems to have been

To disconnect the tables, copy the array and paste it “as value” in another area. 

A screenshot of a Microsoft Excel spreadsheet displaying a list of student names along with their respective scores for three tests, a midterm, and a final exam. The 'Page Layout' tab is currently selected in

Alternatively, you can also select the “Paste Special…” button on the menu to open up a window that lets you paste the table as simple values. 

Excel spreadsheet displaying a dataset for transposing, with student names and test scores, and an example of how to transpose the data on the right-hand side.

Transposing using a PIVOT table 

A third but slightly lengthy way to transpose your table is to use the PIVOT function in Excel. 

Here are the steps you need to follow for this method:

Step 1: Insert a PIVOT table

Select any cell on your table and head to Insert > Pivot Table > From Table/ Range. 

You can also use the keyboard shortcut ALT+N+V+T to open up the Pivot table box:

A screenshot of an Excel tutorial showcasing a microsoft excel spreadsheet with the "remove duplicates" dialog box open, indicating the user is about to remove duplicate values from a selected range of cells in a dataset.

You can insert this table into any existing worksheet or add it to a new worksheet. 

Step 2: Select all fields 

Select all the fields to add them all to the pivot table:

A screenshot of a Microsoft Excel application with an open spreadsheet displaying how to transpose a pivot table report of student test scores, including sums of test 1, test 2, and midterm scores.

Step #3: Switch the rows and columns 

Next, click on the field in the Columns category and click ‘Move to Row Labels.’

A screenshot of an Excel tutorial displaying a spreadsheet with student names and their test scores, along with a PivotTable showing aggregated data such as the sum of test scores.

Lastly, click on the field that was originally in the Rows category and click ‘Move to Column Labels.’

This is a screenshot of an Excel spreadsheet with a PivotTable being used. It shows various student names along with sums of their grades for different tests and projects. The "PivotTable fields" illustrate

Here’s what the transposed pivot table looks like:

A screenshot of a Microsoft Excel spreadsheet showing a table transposed, with students' names and their scores on various tests and assignments, with a total sum calculated at the bottom of each column.

Note that this transposed pivot table will not reflect the changes in the original table. 

Transposing using find and replace 

The TRANSPOSE function in Excel is a simple way to get a transposed table. However, it forms an array that cannot be changed. 

If we want the original and transposed tables to be connected without producing the #SPILL or ‘You cannot change part of an array’ error, this is the method to choose. 

Step 1: Copy the table

The first step is to select the entire table and copy it by using the shortcut CTRL+C or Right-click > Copy. 

The image shows a screenshot from an Excel tutorial illustrating a Microsoft Excel spreadsheet with a dataset ready to be transposed, and an instructional callout box pointing out the "copy" option in the right-click

Step 2: Paste as links

Now, right-click on the cell where you want to place the transposed table and press “Paste Special’. This will open a box where you should select ‘Paste Link.’

The image shows a computer screen with Microsoft Excel open, displaying a dataset for transposing. A user is in the process of using the paste special feature to transpose the selected data, as indicated by how

This will create a connected table. 

Step 3: Find and replace

Go to the Find and Replace button from the Home tab:

A screenshot of a Microsoft Excel worksheet with student names and their corresponding test scores, with the 'find & select' dropdown menu open and the 'transpose Excel' option highlighted.

On the window, type ‘=’ in the ‘Find what’ section. Replace it with a combination of symbols and click ‘Replace All.’ 

Here is what we added:

A screenshot of an Excel spreadsheet with multiple columns capturing students' names, test scores, and a final project score, with a 'find and replace' dialog box open searching for the text "mid.

Doing this might even replace the = in any formulas in your table. But we will replace it back, so you don’t need to worry. 

Here’s the resulting table:

The image shows a computer screen with a Microsoft Excel spreadsheet open. The spreadsheet contains cells with numerical data and formulas related to how to transpose data in Excel. A section of the spreadsheet is highlighted with a

Now copy the new table and ‘Paste Special’ it as a transpose:

A screenshot of a Microsoft Excel spreadsheet with the "paste special" dialog box open, indicating how to transpose data within the spreadsheet.

Again, head to ‘Find and Replace’ and this time, replace the symbols with the = sign again:

A screenshot of a Microsoft Excel spreadsheet with the 'transpose' feature highlighted in an Excel tutorial, demonstrating how to search for specific text within the spreadsheet.

This is the resulting table:

A screenshot of a Microsoft Excel tutorial on how to transpose a spreadsheet containing a list of names and associated test scores, with some cells highlighted in red to indicate scores below a certain threshold.
  • Now, when you change values in the original table, they will be reflected on this transposed table. However, you have the freedom to change the values on the table, too. 
  • But remember that if you change the values on this table, they will not be reflected in the original table. 
  • Also, changing any cell will disconnect that cell from the original table since the ={Cell reference} formula on the table will be changed. 

Wrapping up

The transpose function in Excel isn’t difficult to use and can be made even easier with a few simple tricks. You can use this function to turn a column of cells into a row or a row of cells into a column. However, depending on why you need to transpose your table, you can select other ways to transpose the table from the guide. 

Learn more about Excel through these helpful guides: