TRANSPOSE

Description

Use this function to convert a vertical range of cells to a horizontal range, or vice versa.

This is useful for changing the orientation of data in your worksheet, converting rows to columns or columns to rows.

Syntax

TRANSPOSE(array)

Inputs

This function accepts the following argument:

Name	Required	Description	Valid Input
array	Yes	The range or array of cells to be transposed.	A cell, a cell range, or a formula which results in either of these. These can contain numbers, text, or both.

Examples

Sample data

	A	B	C	D
1	Product	Price	Quantity	Sales
2	Apple	1.50	100	$150.00
3	Orange	2.00	95	$190.00
4	Kumquat	2.45	45	$110.00
5

Sample formulas

Use case Formula Explanation and Result

Shifts all the cells in the array A1:C3.

=TRANSPOSE(A1:C3)

For this data set, if the formula is in cell E1, this formula would return the following:

E	F	G
Product	Apple	Orange
Price	1.50	2.00
Quantity	100	75

Convert a long list of product categories into a horizontal header row for a report, ensuring no duplicates and proper sorting.

=TRANSPOSE(SORT(UNIQUE(A2:A5)))

This formula first uses UNIQUE to take the vertical list of items (possibly containing duplicates), and remove the duplicates, SORT sorts the results alphabetically or numerically, and TRANSPOSE displays them horizontally. For this data set this formula would return the following: (The "0" is because row 5 is empty.)

Apple

Kumquat

Orange

Display product names horizontally if the product's total sales (quantity × price) exceed $120.

=TRANSPOSE(FILTER(A2:A4, SUMIF(C2:C4,C2:C4,D2:D4)120))

This formula first uses SUMIF to calculate the sales totals, then FILTER to identify the ones whose value is greater than $100, TRANSPOSE then displays the results horizontally. For this data set this formula would return the following:

Apple

Orange

Creating a summary view that takes specific fields from a dataset and presents them in a different orientation for reporting purposes.

=TRANSPOSE(INDEX(A2:C10, SEQUENCE(3), {1,3}))

This formula first uses INDEX to retrieve specific values from the range A2:C10. SEQUENCE then generates an array that selects rows 1 and 3 from the range. TRANSPOSE then swaps the rows and columns of the resulting array, For this data set this formula would return the following:

Apple	Orange	Kumquat
100	75	45

Notes

As with all array functions, TRANSPOSE dynamically populates results across adjacent cells, starting from the cell containing the TRANSPOSE function.
IMPORTANT: The destination range cannot overlap with the source range.
TRANSPOSE:
- Starts from the cell where the formula is located, and automatically spills results into adjacent cells, creating new rows or columns if needed.
- The following need validation:
  - Links are transferred to the new cells.
  - Formatting—including Value Format style formatting—is transferred to the new cells.
  - Comments are not transferred.

Tips

If you have header row(s), start your array values one row down (for example in the above data set, start with A2 rather than A1).
You can include headers when transposing data by adding VSTACK or HSTACK.
If the referenced range contains empty cells, TRANSPOSE may return 0 instead of a blank (see example above). Use IF(A1="", "", A1) inside the formula to handle blanks.
Use INDEX with TRANSPOSE to extract and transpose specific columns (see example above).

Related functions

Here are the other array formula functions supported in Workiva spreadsheets.

Support

Community

Workiva Support Center