Power Query Split Cell Into Rows: Steps and Examples

To split one cell into multiple rows in Power Query for Excel for Microsoft 365 on Windows, select the column, choose Transform > Split Column > By Delimiter, select the separator, expand Advanced options, and choose Split into Rows. Confirm with OK, then select Home > Close & Load to return the transformed data to Excel.

What splitting a cell into rows means

Power Query can divide text at a delimiter such as a comma, semicolon, pipe, or line break. The split can create separate columns, or it can create separate rows. Choose rows when each separated item should become its own record—for example, when a product has several tags, or a person is associated with several categories.

Suppose a source table contains one row with a customer name and a comma-separated list of interests: “Mina” and “hiking,reading,cooking.” Splitting the Interests column into rows produces three records, one for each interest. Power Query repeats Mina on those rows, so each interest stays connected to the right customer.

This is a transformation in the query result, not a change to the original source cell. If the source is an Excel table, the original list remains in that table while the query loads a separate result. That separation is useful: you can preserve the imported data and repeat the transformation when the source changes.

A concrete example and the expected result

Imagine an Excel table named Orders with columns OrderID, Customer, and Items. One source row might contain order 1042, Mina, and “Notebook;Pen;Folder.” After splitting Items by the semicolon into rows, the query result should look like this:

1042 | Mina | Notebook
1042 | Mina | Pen
1042 | Mina | Folder

The three output rows represent three item-level records. OrderID and Customer are repeated because they describe the original row, not just the first item. This matters if you later filter, group, count, or join the data. For example, a count of output rows now counts order items, not orders. If the goal is to count distinct orders, use OrderID as the unit of counting rather than treating each expanded row as a separate order.

Before transforming, decide what one row in the final table is supposed to represent. Split into rows is appropriate when each separated value is a separate observation or relationship. If the comma-separated text is simply a display label that you do not need to analyze individually, leaving it in one cell may be more suitable.

Step-by-step instructions in Excel for Windows

Make the source range an Excel table. Select a cell in the range and choose Insert > Table, or press Ctrl+T. Confirm whether the table has headers. A table gives Power Query a named, expanding source when new rows are added.
Select a cell in the table and choose Data > From Table/Range. Excel opens the Power Query Editor. If prompted, verify the table range and headers before continuing.
In the preview, select the column that contains the delimited values. Make sure you select the column header, not a neighboring field. The operation applies to the selected column.
Choose Transform > Split Column > By Delimiter. In the dialog, select the delimiter used in the data. Pick a listed separator, or enter a custom one if the values use a character such as a vertical bar.
Under the split-at setting, choose Each occurrence of the delimiter if every separated item should be extracted. Expand Advanced options and choose Split into Rows, rather than the default split into columns.
Review the preview, then select OK. Check that the output contains one item per row and that other columns have been repeated as expected.
Choose Home > Close & Load to load the result. To specify a destination or load behavior, use Home > Close & Load To… when available in the editor.

Power Query records the transformation as an applied step. You can select that step in the editor to review it, or remove it if the result is not right. The steps are part of the query definition, so you do not have to repeat the manual split every time the source table is updated.

Choosing the delimiter and split setting

The delimiter must match the data. A comma is common, but it is not interchangeable with a semicolon, slash, or line break. If the source uses a semicolon and you choose a comma, Power Query may leave each entire list unchanged. Look at several source values before choosing: data exported from different systems may use different separators.

The split-at choice controls how many parts are made. Each occurrence is generally the right choice for a list that may contain any number of items. Left-most or right-most splitting is useful when only one boundary matters, such as separating a prefix from the remainder. For example, splitting a code at only its first hyphen keeps later hyphens together in the second part; that is different from expanding every segment into its own row.

Comma or semicolon: use when that punctuation consistently separates list items.
Custom character: use a character such as | when it is the actual separator and does not occur inside values.
Line break: use when each item is on a separate line inside the cell; choose the line-feed or carriage-return option that matches the source data.

If a value itself can contain the chosen delimiter, a simple split may divide that value incorrectly. For instance, a list such as “Portland, Oregon;Seattle, Washington” should be split on the semicolon, not the comma, if commas belong to the city names. If the source has quoted fields or inconsistent separators, inspect the preview and consider cleaning or parsing the source format before splitting.

What happens to other columns

When one column is split into rows, Power Query duplicates the values in the other columns for each resulting item. This is usually the desired behavior because it preserves context. A row containing an invoice number, date, and three products becomes three rows that each retain the invoice number and date.

That repetition can affect later calculations. Summing an invoice total after expanding its product list may count the same invoice total once for every product. To avoid inflated totals, decide whether a measure belongs to the original record or to each expanded item. You might aggregate at the invoice level before expanding, keep a separate invoice table, or use a distinct invoice identifier in later analysis.

Splitting does not automatically create a unique identifier for each new row. If you need to distinguish repeated items or maintain item order, add an index column at the appropriate point in the query and verify that its placement gives the numbering you need. An index added before splitting identifies original rows; an index added after splitting numbers the expanded rows.

Common problems and how to fix them

The cell stays whole: check that the selected delimiter exactly matches the separator in the data. Leading or trailing spaces do not usually change the delimiter itself, but inconsistent punctuation can prevent a match.
The result has columns instead of rows: reopen the split step and expand Advanced options. Select Split into Rows; the default choice may split into columns.
Extra spaces appear around items: after splitting, select the resulting text column and choose Transform > Format > Trim. Trim removes spaces at the beginning and end of each value; it does not remove spaces between words.
Some output rows are blank: empty parts can occur when the source has consecutive delimiters, a delimiter at the start or end, or an empty value. Inspect the source and decide whether blank items are meaningful. If not, filter out null or blank results after splitting, taking care not to remove legitimate values.
Values break in the wrong place: the delimiter may also appear inside a value, or the source may use quoted text. Choose a safer separator if possible, or clean the source structure rather than applying a broad split.
The loaded result seems to overwrite or ignore edits: treat the query output as generated data. Make corrections in the source or in Power Query steps; a refresh can replace manual changes made directly in the loaded result.

After any fix, inspect several records, not only the first preview row. Pay particular attention to blank values, values containing punctuation, and records with more or fewer list items than usual. A transformation can look correct on a simple example while mishandling an unusual source value.

Refreshing the result and preserving the source

Once the query is loaded, changes to the source table are reflected when you refresh the query. In Excel, use Data > Refresh All, or right-click the loaded query table and choose Refresh. If you add rows to the original Excel table, the table source normally expands to include them; refresh to rerun the split and load the updated result.

Refresh reruns the saved steps in order. It does not ask you to split the new cells manually, but it also cannot infer a new delimiter if the source format changes. If a provider changes from semicolons to commas, edit the split step and test the result. Keep source data and query output clearly labeled, especially if both are on the same worksheet, to avoid editing the wrong table.

In Excel for Mac, Power Query is available in Microsoft 365, but the editor and available connectors or authoring features can vary by release. The Windows ribbon paths above may not match every Mac build exactly. Excel for the web has query support that also depends on the feature and data source; use the desktop editor when the web interface does not expose the required split options.

When to use another approach

Split into rows is designed for repeatable data preparation. It is a strong choice when a source list changes over time and you want a refreshable, auditable transformation. It is less suitable when you need to preserve the original row count, when the delimiter is ambiguous, or when the list values have a more complex structure than a simple separator can describe.

For a one-time small cleanup, you could edit cells manually, but that work must be repeated when data changes and is easy to apply inconsistently. Splitting into columns is a better fit when each position has a fixed meaning, such as first name and last name. A formula-based approach may be useful for a dynamic worksheet, but it requires building and maintaining a separate formula result; Power Query keeps the transformation in a refreshable query instead.

For structured data such as nested JSON, a delimiter split may be the wrong tool because punctuation can be part of the data and the structure may have multiple levels. Use the appropriate Power Query connector or parsing steps for that source, then expand the relevant records. The key decision is whether the text is genuinely a simple delimited list or merely a string that looks like one.

Frequently Asked Questions

Can Power Query split a cell into rows instead of columns?
Yes. In the By Delimiter dialog, expand Advanced options and choose Split into Rows. Selecting that setting is the key difference from the common split-into-columns operation.

Will splitting one cell into rows change my original Excel data?
No. Power Query creates a transformed query result and leaves the source table unchanged. Edit the source or query steps if you need to change what appears after refresh.

Can I split by a line break?
Yes, if the cell contains line breaks between items. In the delimiter choices, select the line-break option that matches the source. Check the preview because imported text can use different line-ending characters.

Why are the values in my other columns repeated?
Each new row represents one part of the original record, so Power Query repeats the remaining column values to keep that part associated with its source record. Account for that repetition when calculating totals.

Can I remove spaces after splitting?
Yes. Select the split text column and use Transform > Format > Trim. This removes leading and trailing spaces from each value while preserving spaces inside multiword values.

Does the split happen automatically when new data arrives?
The saved transformation is applied again when you refresh the query. New rows must be included in the query source, and the incoming values must use the expected delimiter for the split to work correctly.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.