Power Query Problems

How to Combine Three Excel Rows into One Row for Filtering

Elise Williams Sep 27, 2026 869 views

Question details

The user needs to consolidate records that currently span across groups of three rows into a single row to enable column-based filtering and data analysis.

How to Combine Three Excel Rows into One Row for Filtering

Product: Microsoft Excel
Device & OS: not provided
Scenario: Reformatting raw imported or exported data where a single logical record is split vertically across multiple rows, making standard table operations impossible.
Observed behavior: The data is arranged in repeating groups of three rows per record, which prevents the user from successfully applying Excel filters or sorting the dataset.

Before you start

Before proceeding, ensure your dataset strictly follows the three-row pattern without any missing data, blank rows, or extra headers interrupting the sequence, as these methods rely on a consistent row count.

Solution 1Recommended

Use Power Query to Group and Pivot Rows

Power Query is the most robust and reliable method for reshaping thousands of rows, and it allows you to easily refresh the format when new data is added.

This method uses an Index column combined with mathematical operations (Integer Division and Modulo) to group the data by threes and then pivot it into columns.

Load Data into Power Query

Select your single-column data in Excel, navigate to the Data tab, and click 'From Table/Range' to open the Power Query Editor.

Add an Index Column

Go to the Add Column tab, click 'Index Column', and select 'From 0'. This gives every row a sequential number starting from zero.

Create Grouping and Pivot Columns

Select the Index column, go to Add Column > Standard > Integer-Divide, and enter 3. This groups every three rows into a single record ID. Then select the original Index column again, go to Standard > Modulo, and enter 3. This identifies the column position (0, 1, or 2) for each row.

Pivot the Data

Select the Modulo column, navigate to the Transform tab, and click 'Pivot Column'. Choose your original data column as the Values Column. Expand Advanced Options and select 'Don't Aggregate', then click OK.

Load the Result to Excel

Remove any unnecessary columns (like the Integer-Division grouping column), rename your new columns, and click Home > 'Close & Load' to place the combined single-row records back into your worksheet.

Scalable Solution: Power Query handles massive datasets effortlessly. Once configured, you can simply click 'Refresh' if your source data updates.

Solution 2

Use the WRAPROWS Function (Microsoft 365 Only)

If you are using Microsoft 365, the dynamic array function WRAPROWS provides an instant way to convert a vertical list into a multi-column table.

Efficient Data Processing with WPS Office

Combine Multi-Row Data Effortlessly in WPS Spreadsheet

WPS Spreadsheet provides powerful data transformation tools, supporting advanced lookup and reference formulas to easily reshape your multi-row records into filter-friendly tables without complex setups.

1. Open Your Data in WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the vertically stacked data.
2. Set Up New Table Headers: In adjacent blank columns, type the headers for your new flat table (e.g., ID, Name, Date).
3. Apply the OFFSET Formula: In the first cell under your headers, use an OFFSET formula to target the rows. For example, type =OFFSET($A$1, (ROW(A1)-1)*3, 0) to get the first item, and adjust the column offset parameter for the subsequent columns.
4. Drag to Fill: Select the formula cells and drag the fill handle down to populate the rest of the records into single rows, then apply standard filters from the Data tab.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Handles advanced OFFSET and INDEX formulas to transpose data smoothlyLightweight architecture ensures quick processing of large, unorganized datasetsClean, user-friendly interface for effortless data cleaning and filtering

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I just use standard Excel filters on the original data?

Standard filters work strictly on a row-by-row basis. If a single logical record spans across three separate rows, filtering for a specific value in one row will hide the other two rows associated with that record, breaking your data view.

What happens if some of my records have two rows and others have three?

Automated grouping methods like Power Query's modulo math or the WRAPROWS function require a strict, uniform pattern. If your row counts vary, these methods will misalign your data. You would need to clean the data first or use a unique identifier with VLOOKUP/XLOOKUP to pull the data into columns instead.

Can I use VBA to combine these repeating rows?

Yes, a VBA macro can be written to loop through your dataset in steps of three and transpose the data into a single row. However, Power Query and dynamic array formulas are generally preferred today because they are easier to set up, update automatically, and do not require users to enable macros.

Excel & Data Problems