Function Problems

Split 16 Values into Four Rows of Four in Excel

Huda Qurayshi Nov 12, 2026 868 views

Question details

Rearrange 16 values in a single row into four rows with four columns each by using a dynamic-array formula.

Split 16 Values into Four Rows of Four in Excel

Product: Microsoft Excel for Microsoft 365 or Excel for the web
Device & OS: not provided
Scenario: The source values are stored horizontally, such as in F2:U2, and need to be displayed as a four-by-four table.
Observed behavior: The desired result is to retain the source order while placing every four values on a new row.

Before you start

Confirm that your Excel version supports dynamic arrays and the WRAPROWS and TOCOL functions. Select a destination cell with enough empty cells below and to the right for the 4-by-4 spilled result.

Solution 1Recommended

Use WRAPROWS and TOCOL to reshape the source row

Use this method in Microsoft 365 or Excel for the web when the 16 source values are in one horizontal row and you want an automatically updated 4-by-4 result.

The TOCOL function reads the horizontal range as one sequential column of values. WRAPROWS then places those values into rows containing four values each.

Select the output starting cell

Click the blank cell where the first value of the four-row result should appear, such as A1.

Enter the reshape formula

Type =WRAPROWS(TOCOL(F2:U2),4) and press Enter. Replace F2:U2 with the actual range containing your 16 source values.

Check the spilled output range

Allow the formula to spill into four rows and four columns. If a spill error appears, clear or move any content in the destination cells.

Use WRAPROWS and TOCOL to reshape the source row

Dynamic result created: The four-by-four output updates automatically when values in the original source range change.

Work with spreadsheet formulas in WPS Office

Reshape a Single Row into Four Rows in WPS Spreadsheet

WPS Spreadsheet helps you reorganize a horizontal set of values into a structured table with dynamic-array formulas. Enter the formula in a blank destination cell, and the resulting values can spill into a four-row, four-column layout.

1. Open the worksheet in WPS Spreadsheet: Open the workbook in WPS Office and select the worksheet containing the horizontal source range.
2. Choose an empty destination cell: Click the cell where the reshaped table should begin, ensuring the next four rows and four columns are empty.
3. Enter the dynamic-array formula: In the formula bar, enter =WRAPROWS(TOCOL(F2:U2),4), replacing F2:U2 with your source range.
4. Confirm the formula result: Press Enter and verify that WPS Spreadsheet displays the values across four rows with four values in each row.

Use dynamic-array formulas to reorganize sequential spreadsheet valuesKeep the original value order while wrapping every four items into a new rowOpen and edit Microsoft Excel .xlsx and .xls workbooks with format compatibilityUse a familiar spreadsheet interface for formulas, ranges, and cell references

microsoft office alternative - wps office

Frequently Asked Questions

Can I use WRAPROWS directly on a horizontal Excel range?

Yes. For a single horizontal row, you can use =WRAPROWS(F2:U2,4). Using =WRAPROWS(TOCOL(F2:U2),4) explicitly converts the range into one sequence before wrapping it.

Why does Excel show a #SPILL! error after I enter the formula?

A #SPILL! error means one or more cells in the intended output area contain data or are merged. Clear the cells in the four-row by four-column destination range, then enter the formula again.

What happens if the source range contains more than 16 values?

The formula continues wrapping values after every four items. For example, 20 values produce five rows of four values, provided the spill range has enough empty cells.

How can I split the values into rows with a different number of columns?

Change the second argument of WRAPROWS. For example, use =WRAPROWS(TOCOL(F2:U2),5) to place five values in each output row.

Excel & Data Problems