How to Split One Excel Column into Equal Groups or Parts in WPS Office
Question details
The user needs to divide a sorted Excel column into a chosen number of equal column groups, such as thirds or quarters, in a spreadsheet.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a sorted Excel column and wants to split its values into a specified number of columns with equal rows per group.
- Observed behavior
- The user needs a dynamic array formula that automatically redistributes the sorted source values into multiple columns according to the selected group count.
Make sure your version of WPS Office supports dynamic array functions such as LET, SORT, ROUNDUP, and WRAPCOLS, and place the desired number of columns in a separate input cell such as B1.
Split a Sorted Excel Column into Equal Groups Using a Dynamic Array Formula
Use this method when you want one formula to sort and wrap a column into any number of equal-height columns, such as thirds, quarters, or more.
This dynamic-array approach uses SORT to order the source list, ROUNDUP to calculate how many rows each column should have, and WRAPCOLS to wrap the sorted list into the requested number of columns. It updates automatically when the source range or column-count cell changes.
Open your workbook in WPS Office and select an empty cell where you want the first row of the split columns to appear.
Type =LET(data,A1:A123,numberOfColumns,B1,WRAPCOLS(SORT(data,1,-1),ROUNDUP(ROWS(data)/numberOfColumns,0),"")), replacing A1:A123 with your actual data range and B1 with the cell that contains the desired number of columns.
Press Enter to spill the results across columns. Change the SORT third argument from -1 to 1, or remove SORT(data,1,-1), to use low-to-high or original order instead.
Split a Column into Equal Groups with WPS Spreadsheets Dynamic Arrays
Use WPS Spreadsheets built-in LET, SORT, ROUNDUP, and WRAPCOLS functions to split a sorted Excel column into equal groups or parts, from thirds to any number of columns you choose.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the Excel workbook that contains the sorted column you want to split.
- 2. Create a column-count input cell: Type the desired number of groups, such as 3 for thirds or 4 for quarters, into an empty cell like B1.
- 3. Enter the dynamic-array formula: In an empty cell, enter =LET(data,A1:A123,numberOfColumns,B1,WRAPCOLS(SORT(data,1,-1),ROUNDUP(ROWS(data)/numberOfColumns,0),"")) and replace A1:A123 with your actual data range.
- 4. Press Enter and review the split groups: Press Enter to spill the sorted values into equal-height columns, and adjust the SORT order if you need low-to-high or original order.
Frequently Asked Questions
Can I split a column into three equal groups in WPS Office?
Yes. Use WRAPCOLS with ROUNDUP(ROWS(data)/3,0) after selecting the source data, or enter 3 in the column-count cell that the LET formula references.
Why does the formula return blank cells at the end?
WRAPCOLS pads missing positions with the last argument. The formula uses "" so any extra positions in the final column appear as empty text instead of errors.
How do I change the sort order when splitting the column?
Change the SORT function's third argument: use 1 for ascending, -1 for descending, or remove SORT to keep the original order. Then press Enter.
Does this method work when the number of rows is not evenly divisible by the number of columns?
Yes. ROUNDUP makes each column hold the required number of rows, and WRAPCOLS uses blank values to fill the remaining cells in the last column.
Is WRAPCOLS available in all WPS Office versions?
It requires a version of WPS Spreadsheets that supports dynamic array functions. Update WPS Office to the latest version if the formula returns #NAME?.
