Function Problems

Split Excel Data into Column Groups Every 35 Rows

Steven ParkerSteven Parker Nov 20, 2026 868 views

Question details

Rearrange records from two columns into pairs of columns, with a fixed number of rows in each pair.

Split Excel Data into Column Groups Every 35 Rows
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the formula

In Sheet2, select cell A2 and enter =INDEX(Sheet1!$A:$B,ROW()+INT(COLUMN()/2-0.5)*35,MOD(COLUMN()+1,2)+1).

2
Fill down

Select A2 and drag its fill handle down through A36 to create 35 output rows.

3
Fill across

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.

Use an INDEX formula to create 35-row column groups in Excel
Check the final group: If the last group has fewer than 35 records, formulas beyond the source data may display zeros. Remove those extra formulas if they are not needed.
Work with spreadsheets in WPS Office

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. 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. 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. 3. Fill the group: Drag the fill handle from A2 down to A36 to create the first 35 output rows.
  4. 4. Create more groups: Select A2:A36 and drag the fill handle across enough columns to return all source records.
Arrange records in consecutive pairs of columnsKeep formula results linked to the source dataWork with Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and fill controls
microsoft office alternative - wps office

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.