How To Transpose In Excel

The transpose function can be used to rearrange a group of cells in Excel from a column to a row.

How To Transpose In Excel

Last Updated on

Microsoft Excel has a large array of tools for organizing and managing data. And there may be times when you have a set of data arranged neatly in a column, but need to switch it to be organized in 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.

This can be a useful skill for quickly rearranging the way your sheet is organized, without copying and pasting the data cell by cell into the new arrangement you want. In this guide, we will tell you how to use the transpose function to rotate a collection of cells.

1

Rotating Cells In Excel With The Transpose Function

Switching a batch of cells from a column to a row can have a number of useful applications for organizing your spreadsheet.

Rotating Cells In Excel With The Transpose Function

Step

1

Highlight Horizontal Row Of Empty Cells

The first thing you will need is a column of numbers with names attached to them.

Highlight a horizontal row of empty cells, making sure to highlight enough to contain all the information in your column.

So, for example, if we had four names and four numbers allocated to each, then you would need to highlight 8 cells in total.

Step

2

Type =TRANSPOSE

While these cells are highlighted, type ‘=TRANSPOSE(‘ to begin your formula. You will then be able to type the range of cells that contains the column you want to transpose.

So if you had your list of names begin in B2 and end at C5, then you would enter the formula ‘=TRANSPOSE(B2,C5)’.

This formula specifies both the columns of cells containing your list and indicates that these are the cells you want to transpose.

Step

3

Transpose

When you are done, press ‘CTRL+SHIFT+ENTER’ to confirm your formula and transpose your cells.

2

How To Transpose Cells Using Copy And Paste

If you want to alter the orientation of a batch of cells, without changing the formatting of the text contained within, then you can do so using copy and paste.

How To Transpose Cells Using Copy And Paste

Step

1

Select The Batch Of Cells

Select the batch of cells that you want to transpose and copy, or cut them. You can do this by right-clicking, or using the key commands ‘CTRL+C’ or ‘CTRL+X’ respectively.

Step

2

Transpose Option

Now highlight the batch of cells that you want to transpose your data into. Right click once you have highlighted your selection and look for the ‘Transpose’ option in ‘Paste Options’.

You can then click this option and your data will be rearranged. Using this method will create duplicates that you may need to delete afterwards.

You don’t always need to manually type the range of cells that you want to transpose. Instead, after typing ‘=TRANSPOSE(‘ you can highlight the set of cells you want to re-orientate with your cursor. Just remember you have to press ‘CTRL+SHIFT+ENTER’ rather than just ‘ENTER’ on its own.

If you are transposing cells that contain formulas, then the formulas should be automatically adjusted to apply to their new cells.

Final thoughts

The transpose in Excel function 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, so it works both ways.

The transpose function is one of the simplest array formulas in Excel and is very useful when quickly making changes to the layout of your spreadsheet. Learning about other array formulas will give you lots of other useful tricks that you can use to master your data.

You might like this

Find out how to count cells in Excel with our guide - and several useful methods
Are you trying to make your Microsoft Excel sheet easier to read for others?
Are you trying to make your spreadsheet more readable for Excel’s formulas? Here's how
Avoid typing errors by using the autofill setting to add dates to Excel

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on pinterest
Managing Editor
at
PC guide
Kevin has been a technophile since he first owned a Commodore 64. Then an Amiga….progressing to Gameboy, self-built systems, consoles, and a 15-year career in and around computing and technology. Kevin is an all-around tech and gadget enthusiast. He was previously found at such places as Micro Mart, Custom PC, Bit-tech, and PC PRO, then Which? Computing, Den of Geek, and Daily Telegraph. Also WIRED, Hardware Heaven, and KitGuru.

Independent, transparent, rigorous and authentic, our reviews are the most thorough and honest in PC gaming. Learn about our review process.

Leave a Comment