Purpose
Return value
Syntax
=ROWS(array)
- array - A reference to a cell or range of cells.
Using the ROWS function
The ROWS function returns the count of rows in a given reference as a number. For example, =ROWS(A1:A5) returns 5, since the range A1:A5 contains 5 rows. ROWS takes just one argument, called array , which can be a range or array .
Examples
Use the ROWS function to get the row count for a given reference or range. For example, there are 10 rows in the range A1:F10 so the formula below returns 10:
=ROWS(A1:F10) // returns 10
The range A1:Z100 contains 100 rows, so the formula below returns 100:
=ROWS(A1:Z100) // returns 100
You can also use the ROWS function to get a row count for an array constant :
=ROWS({1;2;3;4;5}) // returns 5
Although there is no built-in function to count the number of cells in a range, you can use the ROWS function together with the COLUMNS function like this:
=COLUMNS(range)*ROWS(range) // total cells
=COLUMNS(A1:Z100)*ROWS(A1:Z100) // returns 2600
This article explains this formula in more detail.
Notes
- Array can be a range or a reference to a single contiguous group of cells.
- Array can be an array constant or an array created by another formula.
- To count columns , see the COLUMNS function .
- To get row numbers , see the ROW function .
- To lookup a row number, see the MATCH function .
Purpose
Return value
Syntax
=TRANSPOSE(array)
- array - The array or range of cells to transpose.
Using the TRANSPOSE function
The TRANSPOSE function converts a vertical range of cells to a horizontal range of cells or a horizontal range of cells to a vertical range of cells. In other words, TRANSPOSE “flips” the orientation of a given range or array:
- When given a vertical range, TRANSPOSE converts it to a horizontal range
- When given a horizontal range, TRANSPOSE converts it to a vertical range
When a range or array is transposed, the first row becomes the first column of the new array, the second row becomes the second column of the new array, the third row becomes the third column of the new array, and so on.
TRANSPOSE can be used with both ranges and arrays . Transposed ranges are dynamic. If data in the source range changes, TRANSPOSE will immediately update data in the target range.
Examples
When given a vertical array, TRANSPOSE returns a horizontal array :
=TRANSPOSE({"a";"b";"c"}) // returns {"a","b","c"}
To transpose the vertical range A1:A5 into a horizontal array:
=TRANSPOSE(A1:A5) // vertical to horizontal
To transpose the horizontal range A1:E1 to a vertical array:
=TRANSPOSE(A1:E1) // vertical to horizontal
In the example shown above, the formulas in I5 and F12 are:
=TRANSPOSE(B5:F6) // formula in I5
=TRANSPOSE(B12:C16) // formula in F12
Note: TRANSPOSE does not carry over formatting. In the example shown, the target ranges have been formatted in a separate step.
TRANSPOSE with other functions
TRANSPOSE can “catch” and transpose the output from another function. The formula below changes the result from XLOOKUP from a horizontal orientation to a vertical orientation:
=TRANSPOSE((XLOOKUP(value,lookup_range,return_range))
Older versions of Excel
In Excel 2021 or later, which supports dynamic array formulas , no special syntax is required, TRANSPOSE simply works and results spill into destination cells automatically. However, in older versions of Excel, TRANSPOSE must be entered as a multi-cell array formula with control + shift + enter:
- First select the target range, which should have the same number of rows as the source range has columns, and the same number of columns as the source range has rows.
- Enter the TRANSPOSE function, and select the source range as the array argument.
- Confirm the formula as an array formula with control + shift + enter.
Paste special
The TRANSPOSE function makes sense when you need a dynamic solution that will continue to update when source data changes. However, for a one-time conversion, you can use Paste Special with the Transpose option. This video covers the basics of Paste Special .
