Power Query Problems

How to Split a Long Excel Column into Multiple Columns at Subheadings

Natalie Taylor Sep 30, 2026 870 views

Question details

The user needs to reorganize a single long column containing both subheadings and a variable number of data rows into separate columns for each subheading.

How to Split a Long Excel Column into Multiple Columns at Subheadings

Product: Microsoft Excel
Device & OS: not provided
Scenario: Restructuring and categorizing stacked vertical data into a horizontal tabular format.
Observed behavior: The data is stacked in a single column with subheadings separating the groups, but the user wants the values placed under each respective heading in separate columns without assuming a fixed number of rows per group.

Before you start

Before splitting your data, ensure there is a reliable way to identify your subheadings, such as a specific text prefix (e.g., 'Heading-'), a known list of titles, or consistent blank rows immediately above them.

Solution 1Recommended

Use Power Query to Split the Column by Subheadings

Power Query is the most robust and dynamic way to handle variable data lengths between headings without writing complex formulas.

This method involves identifying the headings, grouping the data, and pivoting the results. It is highly recommended if your data originates from a CSV, text file, or database.

Load data into Power Query

Select your single column of data, go to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Add a conditional heading column

Go to 'Add Column' > 'Custom Column'. Write a formula to identify your headings. For example, if headings start with 'Head', use: if Text.StartsWith([Column1], "Head") then [Column1] else null.

Fill down the headings

Right-click the header of your newly created custom column, select 'Fill', and then click 'Down'. This tags every data row with its corresponding subheading.

Filter out the original heading rows

Click the filter dropdown on your original column and uncheck the values that represent the headings so that only the raw data values remain.

Pivot the data

Select the new heading column. Go 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 and load the data back to Excel.

Use Power Query to Split the Column by Subheadings

Dynamic Updates: Once set up, you can simply refresh the query whenever new data is added to the source column, and the columns will reorganize automatically.

Solution 2

Use Excel Formulas (MATCH and INDEX) to Split the Column

If you prefer not to use Power Query, you can extract the data dynamically using an advanced combination of MATCH, INDEX, INDIRECT, and CELL functions.

Efficient Data Processing

Organize Complex Data Easily with WPS Spreadsheet

WPS Office Spreadsheet provides full support for advanced array formulas and functions like INDEX and MATCH, allowing you to restructure messy, stacked data into neat columns quickly and completely for free.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your stacked vertical data.
2. Set up new columns: Type your subheadings horizontally across blank columns to serve as your new headers.
3. Apply extraction formulas: Input your INDEX and MATCH formula combination in the first cell under your new headers to pull the data dynamically.
4. Fill the remaining cells: Use the fill handle to drag the formula down and across, instantly organizing all variable rows into their correct columns.

Fully compatible with Microsoft Excel file formats (.xlsx and .csv)Supports advanced lookup formulas (INDEX, MATCH, INDIRECT)Lightweight architecture that runs smoothly on older operating systemsFree to download and use with a highly familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is Power Query better than Text to Columns for this task?

Text to Columns is designed to split data horizontally based on delimiters (like commas or spaces) within a single cell. Because your data spans multiple rows vertically and has a variable number of items between headings, Power Query's grouping and pivoting features are required to restructure the rows into columns.

How do I pivot the data in Power Query without aggregating it?

When using the Pivot Column feature in the Power Query Editor, click to expand the 'Advanced options' dropdown in the Pivot dialog box. Select 'Don't Aggregate' as the Values Function. This ensures your text or numerical values are listed out individually rather than being counted or summed.

Can I use a VBA macro to split this long column?

Yes. A VBA macro can be written to loop through the long column, check if a cell matches a heading criteria, and then shift the subsequent values into a new designated column. This is a great alternative if you frequently process datasets with identical structures and prefer a one-click automated solution.

Excel & Data Problems