Data Import & Export

How to Rearrange Repeating Cells into Consecutive Columns in Excel

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to split and rearrange data from a single vertical column, where values repeat in a specific pattern (such as groups of two or three), into multiple adjacent consecutive columns.

Product: Excel
Device & OS: not provided
Scenario: Restructuring a single continuous column of sequentially patterned data into a structured multi-column table format for easier reading and data analysis.
Observed behavior: The data is vertically stacked in one column, requiring array formulas or transformation tools to transpose alternating or grouped values into separate adjacent columns.

Before you start

Verify that your source data column does not contain any unintended blank rows that might disrupt the sequential pattern, and identify the exact group size (e.g., repeating groups of 2 or 3) before applying the formulas.

Solution 1Recommended

Use FILTER and MOD Formulas to Split Data into 3 Columns

Combine the FILTER, MOD, and ROW functions to extract specific items from a repeating sequence of three into their respective columns.

This method uses mathematical logic to determine the position of each item. By checking the remainder of the row number divided by 3, you can isolate the first, second, and third items of the sequence.

Extract the first item

Select an empty cell (e.g., C1) and enter the formula =FILTER(A1:A15,MOD(ROW(A1:A15),3)=1) to pull the first item of every three-item group.

Extract the middle item

Select the adjacent cell (e.g., D1) and enter the missing formula =FILTER(A1:A15,MOD(ROW(A1:A15),3)=2) to extract the second item of every group.

Extract the third item

Select the next adjacent cell (e.g., E1) and enter =FILTER(A1:A15,MOD(ROW(A1:A15),3)=0) to pull the final item in the three-item sequence.

Adjusting for Headers: If your data starts on row 2, the ROW() function will return 2 instead of 1. You can adjust the formula by subtracting 1 from the row number, like MOD(ROW(A2:A16)-1,3).

Solution 2

Filter Alternating Values into Two Columns

Separate a column of data grouped in alternating pairs into two distinct columns.

Solution 3

Use Power Query to Transform List into Columns

Use Excel's Power Query feature to automatically split your source list into defined columns without writing complex spreadsheet formulas.

Rearrange Column Data Effortlessly with WPS Spreadsheet

WPS Office provides powerful built-in dynamic array functions like FILTER, SEQUENCE, and INDEX to help you easily manipulate and restructure column data without hassle.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your single-column data.
2. Select a destination cell: Click on the cell where you want your new multi-column table to begin.
3. Input the array formula: Type your =FILTER() or =INDEX() array formula to extract the desired sequence logic.
4. Apply and spill: Press Enter, and WPS Spreadsheet will automatically spill the rearranged results into the adjacent rows and columns.

Fully compatible with Microsoft Excel formulas and array functions.Free and lightweight alternative for processing complex datasets.Intuitive interface that makes data formatting and transformation simple.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the WRAPROWS function to split a column?

Yes, in newer versions of Excel and WPS Spreadsheet, you can simply use the formula =WRAPROWS(A1:A15, 3) to automatically wrap a single vertical column of data into rows consisting of 3 columns instantly.

Why does my FILTER formula return an error?

This usually happens if the range sizes in your FILTER and ROW functions do not perfectly match, or if no values meet the MOD condition. Ensure both functions refer to the exact same range (e.g., A1:A15).

How do I split repeating cells using the INDEX function?

You can use a combination of INDEX and SEQUENCE. For example, to split data into 3 columns, you can use =INDEX(A1:A15, SEQUENCE(ROWS(A1:A15)/3, 3)). This generates a grid of row numbers and pulls the corresponding values from your source column.

Excel & Data Problems