TRANSPOSE Function in Excel

If you need to turn rows into columns, or columns into rows, the TRANSPOSE function in Excel does it with a formula. A Paste Special copy is fixed, while the TRANSPOSE result stays linked to the source and updates when the data changes.

In this article, I’ll show you how to use the TRANSPOSE formula with six examples, starting with a list and working up to a summary.

Excel TRANSPOSE Function Syntax

Here is the syntax of the TRANSPOSE function:

=TRANSPOSE(array)

• array is the required range or array whose rows and columns you want to switch. It can be a plain cell range or the result of another formula.

The dimensions swap too. A source with 5 rows and 4 columns returns a result with 4 rows and 5 columns.

In Excel 365, 2024, and 2021, enter the formula in the top-left result cell and press Enter. Excel fills the rest of the output area automatically.

Important: In Excel 2019 and earlier, select the full output range first, with rows and columns swapped. Type the formula, then press Ctrl+Shift+Enter.

Excel adds the curly braces around the formula itself, so don’t type them. If you press only Enter in those versions, you won’t get the full flipped result.

TRANSPOSE is available in Excel 365, 2024, 2021, 2019, 2016, and earlier versions, as well as Excel for Mac and Excel on the web.

When to Use the TRANSPOSE Function

Use this function when you need to:

• Turn a column into a row, or a row into a column.
• Flip a whole table so rows become columns, including the headers.
• Keep the flipped copy linked to the source so it updates. Use Paste Special when you want a fixed copy.
• Rotate the output of FILTER, XLOOKUP, or UNIQUE to fit your layout.
• Build a header row from a list.

Let me show you a few practical examples of how this works.

Example 1: Convert a Column to a Row

Let’s start with the simplest case: flipping a single column into a single row.

Below I have ten grocery items in column A, and I want to lay them across row 13, starting in column B.

Here is the formula:

=TRANSPOSE(A2:A11)

The ten items spill into B13:K13, from Milk to Oats.

The TRANSPOSE function reads the ten cells stacked in the column and returns them side by side. The order stays the same, so top to bottom becomes left to right.

It works the other way too. Give it a row and it returns a column, as you’ll see in Example 4.

If anything blocks the result cells, or the result can’t fit on the sheet, Excel returns a #SPILL! error. Clear those cells and make sure there’s room for the whole result.

Example 2: Transpose a Whole Table

Now let’s rotate an entire table, headers and all.

Below I have a coffee shop price list, and I want drinks across the top and sizes down the side to compare prices by size, starting in column F.

Here is the formula:

=TRANSPOSE(A1:D5)

The source is 5 rows by 4 columns, so the result spills into F1:J4 as a block that is 4 rows by 5 columns.

The drinks now run across the top row, and Small, Medium, and Large run down the first column.

Since the source range includes the headers, the labels move with the prices. That’s also why the Drink label from A1 now sits in F1, above the sizes.

The Medium Mocha price, $4.50, lands where the Mocha column meets the Medium row.

Pro Tip: The TRANSPOSE function brings values and formula results, not formatting. Apply currency formats, fills, and borders to the result yourself.

In the example file, I’ve already applied the currency format to G2:J4, which is why the prices show dollar signs.

Example 3: Keep Blanks From Becoming Zeros

Here’s a quirk that trips people up, along with the fix.

Below I have a week of workout minutes laid out across two rows, and I want to flip it into a two-column list below the data, starting in A5.

Wed and Sat are rest days, so their minutes cells are empty.

Here is the formula:

=TRANSPOSE(A1:H2)

The result fills A5:B12, including the Day and Minutes headers. But notice that Wed and Sat come back as 0 instead of staying blank.

Those zeros look like 0-minute workouts.

They would also pull down your weekly average, because the AVERAGE function counts zeros but skips empty cells.

To keep the blanks blank, wrap the source in the IF function before flipping it. Enter this version in D5.

Here is the formula:

=TRANSPOSE(IF(A1:H2="","",A1:H2))

Now D5:E12 shows the same week, with the minutes beside Wed and Sat left blank.

Here is how this formula works:

• The IF function checks every cell in A1:H2 with A1:H2="".
• When a cell is empty, IF returns "", an empty text string that looks blank.
• Otherwise, IF passes the original value through unchanged. A workout time such as 45 stays a real number.
• TRANSPOSE flips the array that IF returns, turning the two source rows into two columns.

You may also see =TRANSPOSE(A1:H2&"") used to keep blanks blank. That turns every number into text too, so 45 becomes “45”.

Use the version with the IF function when you want to keep the workout times numeric for calculations.

Example 4: Turn XLOOKUP Results Into a Column

You can also rotate the row returned by a lookup so it fits a report card.

Below I have test scores for ten students in five subjects, and I want the scores for the student named in H2 in column I.

H2 holds Saoirse, whose scores will appear beside the subject names.

Here is the formula:

=TRANSPOSE(XLOOKUP(H2,A2:A11,B2:F11))

Her five scores spill into I5:I9, lined up with the subject names.

Here is how this formula works:

• The XLOOKUP function finds Saoirse, the name in H2, in A2:A11.
• It returns her whole score row from B2:F11. That gives five values side by side.
• TRANSPOSE stands that row up into a column so the scores line up with the subject list in H5:H9.

Type a different name, such as Mei, in H2 and the report card updates automatically.

This example needs Excel 365 or Excel 2021 and later because it uses the XLOOKUP function.

Example 5: Lay Filtered Names Across a Row

Now let’s pull out only the names you need and turn that list sideways.

Below I have a guest list with party sizes and Yes or No RSVPs, and I want the Yes guests across row 13 for a name-tag strip, starting in column B.

Here is the formula:

=TRANSPOSE(FILTER(A2:A11,C2:C11="Yes"))

Six guests replied Yes, so their names fill B13:G13.

Here is how this formula works:

• The FILTER function checks C2:C11 for “Yes” and pulls the matching names from A2:A11 as a vertical list.
• TRANSPOSE turns that vertical list into a horizontal row, keeping the names in their source order.

Both functions stay live. Change Kwame’s RSVP to Yes and his name joins the row automatically.

The result grows by one cell, so keep the next cell clear too.

Like XLOOKUP, the FILTER function needs Excel 365 or Excel 2021 and later.

Example 6: Build Summary Headers and Totals

Let’s finish by using a list of categories to build a summary across the sheet.

Below I have an expense log with twelve entries, and I want each category listed once across row 1, starting in column F.

Here is the formula:

=TRANSPOSE(UNIQUE(B2:B13))

F1:J1 fills with Groceries, Transport, Dining, Utilities, and Entertainment, in the order they first appear in the log.

Here is how this formula works:

• The UNIQUE function returns the five distinct categories in B2:B13 as a vertical list.
• TRANSPOSE turns that list into a horizontal row of category headers.

Now let’s total the expenses for each header with a formula in F2.

Here is the formula:

=SUMIFS(C2:C13,B2:B13,F1#)

F2:J2 shows the total for each category, right below its header.

Here is how this formula works:

• The SUMIFS function adds the amounts in C2:C13 where the categories in B2:B13 match a header.
• F1# is a spill reference. It means the whole spilled range that starts in F1, so it gives SUMIFS all five category headers.
• SUMIFS returns one total per header, spilling all five totals across the row from F2.

If a new category appears within B2:B13, the header row and totals both grow by a column on their own. Keep enough empty space to the right.

These formulas use fixed source ranges. If you add entries below row 13, expand the source ranges in both formulas to include them.

This summary needs Excel 365 or Excel 2021 and later for the UNIQUE function and spilling results.

Pro Tip: For A to Z headers, use =TRANSPOSE(SORT(UNIQUE(B2:B13))) in F1. The SUMIFS formula needs no change.

I’ve shown you how to flip a list or table and rotate another formula’s results to fit your layout.

I hope you found this article helpful.

List of All Excel Functions

Other Excel Articles You May Also Like:

• Convert Columns to Rows in Excel
• How to Flip Data in Excel
• Transpose Multiple Rows into One Column
• GROUPBY Function in Excel
• SORT Function in Excel
• Lookup the Second, the Third, or the Nth Value in Excel
• 20 Advanced Excel Functions and Formulas (for Excel Pros)
• TOCOL in Excel
• MMULT function