Skip to main content

Excel TRANSPOSE Function

The Excel TRANSPOSE function rotates the orientation of a range or array. For example, it can rotate a table that arranged horizontally in rows to vertically in columns or vice versa.


=TRANSPOSE (array)


Array (required): An array or range of cells that you want to transpose.

Return value

The TRANSPOSE function will return an array in a new orientation based on a certain range of cells.

Function notes:

1. The TRANSPOSE function must be entered as an array formula with pressing Ctrl + Shift + Enter keys simultaneously.
2. The transpose result: after transposing, the first row of the original array becomes the first column of the new array, the second row of the original array becomes the second column of the new array, the third row becomes the third column, and so on.


Example 1: Flip the orientation of a given range

Supposing you want to flip the orientation of range B4:I5 as the below screenshot shown, you can try as follows to achieve it.

1. Select a range for the new array, here I select K3:L10, copy the below formula into it:

Note: You must select the same number of cells as the original set of cells, but in the other direction.

2. And then press the Ctrl + Shift + Enter keys to return the new array.

Example 2: Transpose range without zero in Excel

Normally, if the specified array contains blank cells, these blank cells will display as 0 in the new array after using the Transpose function. See screenshot:

If you want to transpose table without zeros, the method in this section can help.

1. Select a range for the new array, here I select K3:L10, copy the below formula into it:

The syntax of the formula: {=TRANSPOSE(IF(rng="","",rng))}

2. Press Ctrl + Shift + Enter keys at the same time to get the new array without zeros as below screenshot shown.

Note: If you want to put “blank” into the blank cells of the new array rather than keep it empty, please enclose this text in the second double quotation marks in the formula:
And then press the Ctrl + Shift + Enter keys.

More Examples

How to quickly transpose blocks of data from rows to columns in Excel?

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations