Pinterest Pixel

TRANSPOSE Formula in Excel

Bryan
 .
TRANSPOSE Formula in Excel | MyExcelOnline TRANSPOSE Formula in Excel | MyExcelOnline

What does it do?

Converts a vertical range of cells to horizontal, or vice versa

Formula breakdown:

=TRANSPOSE(array)

What it means:

=TRANSPOSE(the horizontal or vertical range of cells that you want to flip the orientation)


Let us say you have a horizontal table, and you want to flip it to become a vertical table, did you know that there is an Excel formula that can do that for you? That’s right, let us use the TRANSPOSE Formula!

Whenever you use this function, you should treat it as an array formula and it is very easy to do! You can use this to transform your horizontal table to a vertical table, or the other way around from a vertical table to a horizontal one.

I explain how you can do this below:

TRANSPOSE Formula in Excel | MyExcelOnline

Download excel workbookTRANSPOSE-FORMULA.xlsx

STEP 1: Make sure to select your target table first with the same number of cells.

You can see we have selected a horizontal table that has the same number of cells (5 by 2) when compared to the original vertical table (2 by 5):

TRANSPOSE Formula in Excel

While it is selected, we need to enter the TRANSPOSE function:

=TRANSPOSE(

TRANSPOSE Formula in Excel

 

STEP 2: The TRANSPOSE arguments:

Table of Contents

array

What is the range of cells that contains the data?

Select the cells containing the data that you want to flip the orientation:

=TRANSPOSE(A9:B13)

TRANSPOSE Formula in Excel

 

STEP 3: Once you finish typing the formula, ensure you press CTRL + SHIFT + ENTER for this to be treated as an array formula: 

TRANSPOSE Formula in Excel

 

Your table now has a horizontal orientation with just the TRANSPOSE Formula!

 

How to Use the Transpose Formula in Excel

 

 

 

TRANSPOSE Formula in Excel | MyExcelOnline

 

If you like this Excel tip, please share it
TRANSPOSE Formula in Excel | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Vlookup Approximate Match in Excel

Steps To Follow

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...