How to transpose in Excel – 4 easy methods

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…”

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

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

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:

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:

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.

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

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. 

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:

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:

Step #3: Switch the rows and columns 

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

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

Here’s what the transposed pivot table looks like:

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. 

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.’

This will create a connected table. 

Step 3: Find and replace

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

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:

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:

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

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

This is the resulting table:

• 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:

• How to add numbers in Excel
• How to sum cells with text and numbers in Excel
• How to remove gridlines in Excel