Power Query Problems

How to Split Data from One Excel Cell into Multiple Rows using Power Query

Ayan Masood Oct 10, 2026 869 views

Question details

The user needs to split multiple items (such as SKUs) that are stored within a single Excel cell into separate rows, while repeating the associated data from other columns for each new row.

How to Split Data from One Excel Cell into Multiple Rows using Power Query

Product: Excel
Device & OS: not provided
Scenario: When dealing with unnormalized data where multiple values are bundled into one cell separated by a delimiter, the user needs to separate them into individual records for proper analysis.
Observed behavior: Multiple SKUs are currently combined in one cell. The goal is to transform this structure so that each SKU has its own row, with corresponding identifier data intact.

Before you start

Ensure your dataset has clear column headers and identify the specific delimiter (e.g., comma, space, or semicolon) used to separate the values within your target cells.

Solution 1Recommended

Split Cell Data into Multiple Rows Using Power Query Unpivot

Use the standard Power Query method to split delimited values into columns first, and then unpivot them into separate rows to keep related data.

Power Query provides a robust set of data transformation tools. By combining the 'Split Column' and 'Unpivot' features, you can easily reorganize grouped cell data into clean, individual rows.

Load Data into Power Query

Select your data range in the spreadsheet and go to the Data tab, then choose 'From Table/Range' to open the Power Query Editor.

Split Column by Delimiter

Select the column containing the multiple SKUs. Navigate to the Home tab, click on 'Split Column', and choose 'By Delimiter'.

Specify the Delimiter

Specify the exact delimiter used in your data (such as a comma). Power Query will separate the grouped values into multiple new columns.

Adjust Data Types

Review the newly created columns and remove or adjust the automatic data types assigned by Power Query if they are incorrect.

Unpivot the Columns

Select your primary identifier columns (the ones you want to keep intact). Right-click the header and choose 'Unpivot Other Columns' to transpose the separated SKUs into multiple rows.

Clean Up and Load

Remove any unwanted columns (such as the generic 'Attribute' column generated by unpivoting). Once your data looks correct, click 'Close & Load' to output the transformed data back into Excel.

Alternative Method: In newer versions of Power Query, you can bypass the Unpivot step by expanding the 'Advanced options' within the Split Column dialog and selecting 'Split into Rows' directly.

Free Microsoft Office alternative

Experience Seamless Data Management with WPS Office

While Power Query is a specific feature of Microsoft Excel, WPS Office provides a lightweight, highly compatible alternative for everyday data processing. Enjoy familiar spreadsheet tools and powerful text-to-columns features without the heavy subscription fees.

1. Download WPS Office: Visit the official WPS website to download and install the free Office suite.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and seamlessly open your existing .xlsx or .csv files without losing any formatting.
3. Process Your Data: Use built-in features like Text to Columns, Advanced Filter, and Pivot Tables to organize your datasets efficiently.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Comprehensive built-in data processing tools for cleaning and formatting raw data.Lightweight installation with fast performance for large datasets.Free to use with a familiar interface that requires zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Will splitting data with Power Query alter or delete my original Excel file?

No. Power Query establishes a connection to your original data and processes the transformations in a separate editor environment. Your original source data remains completely unchanged.

Can I split cells that use a line break as a delimiter instead of a comma?

Yes. In the Split Column dialog, you can select 'Custom' for the delimiter and insert the special character for a line break. Power Query also has a dedicated 'Special Characters' checkbox to easily insert a Line Feed.

How do I update the separated rows if I add new data to the original spreadsheet?

Because Power Query creates an active connection, you simply need to right-click anywhere in your resulting green output table in Excel and select 'Refresh'. The new data will automatically pass through the split and unpivot steps.

Why did my unpivot step create an 'Attribute' column?

When you unpivot columns, Power Query creates an 'Attribute' column containing the old column headers and a 'Value' column containing the actual cell data. You can simply right-click the 'Attribute' column and select 'Remove' if it is not needed.

Excel & Data Problems