Split Excel Data into Column Groups Every 35 Rows
Question details
Rearrange records from two columns into pairs of columns, with a fixed number of rows in each pair.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Several thousand records in columns A and B need to be displayed in consecutive two-column groups of 35 rows.
- Observed behavior
- Each successive set of 35 records should appear in the next pair of columns.
Place the source data in columns A and B of Sheet1, starting in row 2, and use Sheet2 for the rearranged output. The formula below assumes row 1 is reserved for headers.
Use an INDEX formula to create 35-row column groups in Excel
Use this formula when you want the output to update as the source data changes.
The formula returns records 1–35 in columns A and B of Sheet2, records 36–70 in columns C and D, and so on.
In Sheet2, select cell A2 and enter =INDEX(Sheet1!$A:$B,ROW()+INT(COLUMN()/2-0.5)*35,MOD(COLUMN()+1,2)+1).
Select A2 and drag its fill handle down through A36 to create 35 output rows.
Select A2:A36 and drag the fill handle to the right across enough columns to include all records. Each pair of columns represents one group of 35 records.

Split Records into Column Groups with WPS Spreadsheets
Use the same INDEX formula in WPS Spreadsheets to arrange two source columns into 35-row groups across a worksheet.
- 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and check that the source records are in Sheet1 columns A and B, starting in row 2.
- 2. Enter the formula: Select Sheet2 cell A2 and enter =INDEX(Sheet1!$A:$B,ROW()+INT(COLUMN()/2-0.5)*35,MOD(COLUMN()+1,2)+1).
- 3. Fill the group: Drag the fill handle from A2 down to A36 to create the first 35 output rows.
- 4. Create more groups: Select A2:A36 and drag the fill handle across enough columns to return all source records.

Frequently Asked Questions
How do I use a different number of rows per group?
Replace 35 in the formula with the desired group size. Starting at A2, fill the formula down for that many rows, then fill it across.
How many output columns do I need?
Divide the number of records by 35 and round up to get the number of groups. Multiply that number by two, because each group uses two columns.
Why do zeros appear after the last record?
Formulas that refer to empty source cells can display zeros. In Sheet2, remove formulas beyond the last record if you do not need them.
Will the rearranged data update when I change Sheet1?
Yes. The formulas in Sheet2 refer to Sheet1, so their results update when the corresponding source cells change.




