How to Reshape Data in Excel Using WRAPROWS and WRAPCOLS Functions

—

by

Himanshu
in

Here’s a detailed explanation of how to use the WRAPROWS and WRAPCOLS functions in Excel — these are part of Excel’s dynamic array functions available in Microsoft 365 and Excel 2021 onwards.

1. WRAPROWS Function in Excel

Purpose:

WRAPROWS reshapes a single row or column of data into a table-like structure with a specified number of values per row.

Syntax:

WRAPROWS(vector, wrap_count, [pad_with])

Parameters:

  • vector: The range or array to reshape (single row/column)
  • wrap_count: How many items per row
  • pad_with (optional): Value to fill in if the last row is incomplete

Example:

Given a list in A1:A10:

A1:A10 = {1,2,3,4,5,6,7,8,9,10}

Formula:

=WRAPROWS(A1:A10, 4)

Result:

1   2   3   4  
5   6   7   8  
9  10

With padding:

=WRAPROWS(A1:A9, 4, "NA")

Result:

1   2   3   4  
5   6   7   8  
9  NA  NA  NA

2. WRAPCOLS Function in Excel

Purpose:

WRAPCOLS reshapes data into a column-wise format, specifying how many values per column.

Syntax:

WRAPCOLS(vector, wrap_count, [pad_with])

Example:

List in A1:A10:

=WRAPCOLS(A1:A10, 4)

Result:

1   5   9  
2   6   10  
3   7  
4   8

With padding:

=WRAPCOLS(A1:A9, 4, "N/A")

Result:

1   5   9  
2   6   N/A  
3   7   N/A  
4   8   N/A

✅ Key Notes:

  • These functions are useful for layout transformation, preparing data for printing, visualization, or dashboards.
  • They work well with other dynamic functions like SEQUENCE, SORT, UNIQUE, etc.

Top rated products