TRANSPOSE function

The TRANSPOSE function in Excel is used to change the orientation of a range or array, converting rows to columns and columns to rows. It allows you to flip the layout of your data without having to manually copy and paste each individual cell.

Syntax

=TRANSPOSE(array)

Parameters

• array: The range of cells or array you want to transpose. This is the only required parameter.

How It Works

• The TRANSPOSE function takes an array (a range of cells or values arranged in rows and columns) and flips it. Rows become columns, and columns become rows.
• You must select the destination range that matches the size of the transposed data. For example, if you’re transposing a 2×3 array (2 rows and 3 columns), you should select a 3×2 destination range.

Steps for Using TRANSPOSE

1. Select the destination range where you want the transposed data to appear. Make sure the range is the correct size to accommodate the flipped data.
2. Type the formula =TRANSPOSE(array) where “array” is the range you want to transpose (e.g., A1:C3).
3. Press Ctrl + Shift + Enter for array formulas (older Excel versions), or simply Enter in newer Excel versions that support dynamic arrays. Excel will automatically transpose the data into the selected range.

Examples

Example 1: Transpose a 2×3 Array

Let’s say you have the following data in cells A1:C2:

To transpose this data (convert rows into columns), follow these steps:

1. Select a 3×2 range for the destination (e.g., E1:F3).
2. Enter the formula:

=TRANSPOSE(A1:C2)

3. Press Ctrl + Shift + Enter in older Excel versions (or just Enter in newer versions).

The result will look like this:

Example 2: Transpose with Dynamic Arrays (Newer Excel Versions)

If you have Excel 365 or Excel 2021, which supports dynamic arrays, simply entering the TRANSPOSE function without pressing Ctrl + Shift + Enter will work.

For example, to transpose data in A1:B2 to D1:E2:

1. Select any single cell where you want the result (e.g., D1).
2. Enter the formula:

=TRANSPOSE(A1:B2)

3. Press Enter.

Key Points

• In older versions of Excel, you must select the target range and enter the formula as an array formula by pressing Ctrl + Shift + Enter.
• In newer versions that support dynamic arrays, you can just press Enter, and Excel will automatically fill the necessary cells.
• Be sure to select a destination range that is large enough to accommodate the transposed data. If the range is too small, some data may be truncated.

When to Use TRANSPOSE

• When you need to change the orientation of your data (from horizontal to vertical or vice versa).
• Useful in scenarios where data was initially structured incorrectly or for formatting and reporting purposes.

The TRANSPOSE function helps save time and reduces manual work when reorganizing data.