How to Convert Vertically Arranged Data into Rows in Excel
Question details
The user needs to restructure a single column containing vertically stacked records (e.g., Name, Email, Phone) into a standard table where each complete record occupies a single horizontal row.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Data exported or pasted from an external source has stacked multiple fields into a single column under repeated labels, making it impossible to filter or analyze as a standard database table.
- Observed behavior
- Fields belonging to a single record span multiple vertical cells rather than sitting side-by-side in columns.
Ensure your vertical data follows a consistent pattern (e.g., exactly three rows per record). If there are random blank rows separating the records, delete or filter them out before beginning the transformation.
Use Power Query to Reshape the Data
Power Query is the most reliable method because the transformation steps are saved. If you add more vertical data later, you can simply refresh the query.
This method involves adding an index and a modulo column to group the data logically, allowing Power Query to pivot the repeating fields into individual columns.
Select any cell in your vertical data. Go to the 'Data' tab on the ribbon and click 'From Table/Range'. Check 'My table has headers' if applicable, and click OK.
In the Power Query Editor, go to the 'Add Column' tab, click 'Index Column', and select 'From 0'.
With the Index column selected, click 'Standard' in the 'Add Column' tab and choose 'Modulo'. Enter the number of fields per record (e.g., type 3 if each record has a Name, Email, and Phone number) and click OK.
Select the Index column again, click 'Standard', and choose 'Divide (Integer)'. Enter the same number (e.g., 3). This creates a unique ID for each complete record.
Select the Modulo column. Go to the 'Transform' tab and click 'Pivot Column'. Under 'Values Column', choose your original data column. Expand 'Advanced options' and select 'Don't Aggregate', then click OK.
Remove any unnecessary grouping columns (like the Integer-Division column), rename your new headers, and click 'Close & Load' on the Home tab to insert the horizontal records into a new sheet.
Use the WRAPROWS Function (Microsoft 365)
For users on modern versions of Excel, dynamic array formulas offer an instant, single-step way to reshape predictable vertical data.
Quickly Reshape Data with WPS Spreadsheet
WPS Office fully supports advanced dynamic array functions like WRAPROWS, as well as powerful data manipulation tools, allowing you to instantly convert vertical data into rows at zero cost.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your vertically stacked data.
- 2. Apply the array formula: Select an empty cell and enter the formula =WRAPROWS(A2:A50, 3), replacing '3' with the number of fields each record contains.
- 3. Convert to plain text (Optional): Select the newly generated horizontal table, press Ctrl+C to copy, right-click, and select 'Paste as Values' to remove the formula dependence.
Frequently Asked Questions
Can I use Paste Special > Transpose instead of Power Query?
Paste Special > Transpose works well if you only have one single record to convert. However, if you have dozens of stacked records in a single column, transposing would turn the entire column into one endlessly long row. Power Query or WRAPROWS correctly groups the data into multiple rows.
What if the number of fields per record is not consistent?
If some records have three rows and others have four (e.g., missing phone numbers), formulas like WRAPROWS will misalign your data. In this scenario, Power Query is required. You can add a conditional column to flag the start of a new record (like a cell containing 'Name:'), fill down a custom index, and group by that index.
Is there a traditional formula alternative for older Excel versions?
Yes. If you don't have Microsoft 365, you can use a combination of the INDEX, ROW, and COLUMN functions to extract data at specific intervals. However, this method is prone to user error during setup, making Power Query the much safer choice for legacy versions.
