If your data is arranged in vertical groups, such as survey responses or employee records listed in stacked rows, you can transpose each set of rows into columns for easier analysis. In this tutorial, I’ll show you how to move each set of rows to columns in Google Sheets using two methods:

• A modern dynamic array formula with WRAPCOLS
• A legacy approach using VLOOKUP

Transpose Each Set of Rows into Columns Using WRAPCOLS

The WRAPCOLS function is ideal for transforming vertical row groups into columns. It wraps values from a single column into multiple columns based on a specified group size.

Use Case Example:

Imagine you have employee data in column B with repeating groups of 4 rows for each employee (e.g., Name, Basic Pay, Present Days, OT Hours). You want to convert that vertical data into a horizontal table where each group forms a column.

Formula:

=WRAPCOLS(B2:B13, 4, "")

Formula Breakdown:

• B2:B13 – the range of stacked values.
• 4 – the number of rows in each group.
• "" – optional padding if the data doesn’t divide evenly.

Syntax:

WRAPCOLS(range, wrap_count, [pad_with])

Handling Dynamic or Expanding Data

If your dataset grows over time, use an open-ended range:

=WRAPCOLS(B2:B, 4, "")

However, this can result in a #REF! error if data exists in cells where the formula is expected to spill results.

To fix this, use a dynamic range that automatically adjusts to the last non-blank cell:

=WRAPCOLS(INDEX(B2:XLOOKUP(TRUE, B2:B<>"", B2:B, ,0, -1)), 4, "")

Optional: Remove All Empty Cells Before Wrapping

If you want to eliminate all empty cells before using WRAPCOLS, use TOCOL:

=WRAPCOLS(TOCOL(B2:B, 3), 4, "")

Note: This removes all blanks, not just trailing ones. That means the row groupings may become misaligned, especially if blanks are meaningful.

VLOOKUP Trick to Transpose Each Set of Rows into Columns (Legacy Method)

Before WRAPCOLS was introduced, we used a clever VLOOKUP trick to transpose each set of rows into columns. Here’s how it works:

=ARRAYFORMULA(
  VLOOKUP(
    TRANSPOSE(SEQUENCE(ROUNDUP(ROWS(B2:B13)/4), 4)),
    {SEQUENCE(ROWS(B2:B13)), B2:B13},
    2,
    FALSE
  )
)

How This Works:

SEQUENCE(ROUNDUP(ROWS(B2:B13)/4), 4) – creates a vertical matrix of sequential numbers filled column-wise, e.g.:

TRANSPOSE(...) – flips the matrix to organize the sequence numbers in row-wise groups:

{SEQUENCE(...), B2:B13} – creates a lookup table where:

• Column 1 = sequence numbers (1 to 12)
• Column 2 = corresponding data in B2:B13

VLOOKUP(..., 2, FALSE) – uses the transposed sequence numbers to look up and return matching values from B2:B13, effectively transposing each set of 4 rows into a column-aligned format.

Just like with WRAPCOLS, you can adapt this formula to use a dynamic range:

=ARRAYFORMULA(
  VLOOKUP(
    TRANSPOSE(SEQUENCE(ROUNDUP(ROWS(B2:XLOOKUP(TRUE, B2:B<>"", B2:B, ,0, -1))/4), 4)),
    {SEQUENCE(ROWS(B2:XLOOKUP(TRUE, B2:B<>"", B2:B, ,0, -1))), B2:XLOOKUP(TRUE, B2:B<>"", B2:B, ,0, -1)},
    2,
    FALSE
  )
)

Summary

You’ve now learned how to move each set of rows to columns in Google Sheets using both a modern and legacy method. Whether you’re working with survey responses, employee records, or any stacked dataset, these techniques can help you transpose each set of rows into columns cleanly and dynamically.

Related Resources

• Split a Column into Multiple Columns in Google Sheets (2 Ways)
• How to Unstack Data into Groups in Google Sheets