Excel Power Query: Split a List into Rows or Columns

How-to basics · Updated November 19, 2026

To split a delimited list in Excel with Power Query, select its column in the Power Query Editor, then choose Transform > Split Column > By Delimiter. Choose the delimiter, and under Advanced options select Split into Rows or Split into Columns, depending on the shape you need.

What “split a list” means in Power Query

Usually, an Excel “list” means several values typed into one cell and separated by a character, such as red, blue, green. Power Query can separate those values at each delimiter and put them in distinct columns or separate rows. This is useful when one cell contains multiple tags, product codes, names, or categories that you want to sort, filter, count, or join to other data.

There is a technical distinction worth knowing: Power Query also has a data type called a List. A delimited cell is text, not automatically a Power Query List. The Split Column command turns the text into separate values. If your query already contains a List object, its values can instead be converted to a table or expanded. The right operation depends on what you see in the Power Query preview: text in a cell, or a cell labeled List.

For example, a source table might contain:

  • Order 104 | Pencil, Notebook
  • Order 105 | Folder, Pen

Splitting the item field into rows produces one item per row while retaining its order number. Splitting into columns produces separate item fields on the same order row. Neither option is universally better: choose based on how you intend to analyze the result.

Prepare the source data

Start with a consistent delimiter and a clearly identified column. If the data is an ordinary Excel range, click inside it and use Data > From Table/Range. Confirm the range and whether it has headers, then select OK to open the Power Query Editor. If the data is already an Excel table or an existing query, open that query for editing instead.

Before splitting, check a few representative records. A comma-separated list such as pencil, notebook is straightforward, but a comma can also occur inside a value. If commas serve both as separators and as part of the values, a simple split may divide the wrong text. A consistent delimiter such as a semicolon, or a properly structured source file with quoted fields, can avoid that ambiguity.

  • Make sure the target column contains the list, not a mixture of list text and unrelated notes.
  • Identify whether separators are commas, semicolons, pipes, line breaks, or another character.
  • Decide whether blank items should remain or be removed.
  • Check whether every row has a similar number of values; uneven lists are common and can affect column splits.

Split a delimited list into rows

Splitting into rows is often the better choice when each item should become an independently filterable record. In the Power Query Editor, select the list column and choose Transform > Split Column > By Delimiter. In the dialog, choose or enter the separator, then expand Advanced options and select Rows under the split direction. Confirm the dialog to apply the transformation.

Suppose an order table has one row with order number 104 and items Pencil, Notebook. The result has two rows: order 104 with Pencil and order 104 with Notebook. Other columns, such as order date or customer, are repeated on both rows. This is expected: Power Query is making one record for each separated item, not dividing the order into unrelated data.

Rows are especially useful for questions such as “How many orders included each item?” or “Show every record tagged with ‘urgent.’” After splitting, the item field can be filtered, grouped, or merged with a separate item lookup table. Be aware that repeating order-level amounts on each item row can inflate totals if you later sum those amounts; the split changes the data’s row-level meaning.

Split a list into columns

Choose Columns in the same dialog when the values represent fixed positions or attributes that belong on one record. For example, splitting North|Retail|Open at the pipe character can create Region, Channel, and Status fields. Rename the resulting columns to meaningful labels so later steps and reports are easier to interpret.

Column splitting works best when values appear in a predictable order and the number of pieces is reasonably consistent. If one row contains two values and another contains four, Power Query may create enough columns for the longest split and leave missing positions blank or null in shorter records. Those empty positions are not necessarily errors; inspect the source and decide whether the values have a reliable positional meaning.

When a list can contain a variable number of items and each item should be analyzed equally, splitting into rows is generally more practical than creating a wide table with many numbered columns. Conversely, if each part has a distinct role, columns are easier to read and use in reports.

Choose the delimiter and handle spaces

In the split dialog, select a common delimiter from the list or specify a custom character or text sequence. The delimiter must match the source. Splitting one; two; three on a comma will not divide it; choose the semicolon instead. The dialog also provides choices about where to split, such as at each occurrence or at the left-most or right-most occurrence. Use each occurrence when the list contains repeated separators between all values.

A frequent nuisance is a space after a comma. Splitting pencil, notebook on the comma can leave a leading space before notebook. One approach is to use the full comma-and-space sequence as the delimiter if it is consistent. Another is to split on the comma and then select the result column and use Transform > Format > Trim. Trimming removes leading and trailing spaces; it does not remove spaces inside a value.

For inconsistent spacing, splitting on the separator and trimming each resulting value is usually more dependable than assuming every delimiter has exactly one space after it. If the source includes line breaks, choose the appropriate line-break option in the delimiter dialog where available, or use a consistent text delimiter in the source. Preview the result before loading it.

Clean empty values and check the result

Consecutive delimiters, a delimiter at the beginning or end of a cell, or a cell containing only a delimiter can produce empty results. For example, blue,,green has an empty item between the commas. Decide whether that empty position carries meaning. If not, filter the split column to remove blank or null values. Do not rely on Remove Blank Rows alone when other columns still contain data: a row with an empty item but a populated order number is not an entirely blank row.

After splitting, scan the preview for misplaced values, unexpected empty entries, and column names that need improvement. Compare the number and meaning of rows with the source. A row split increases the number of rows when a cell contains multiple items; a column split increases the number of fields. These are normal structural changes, but they can affect later calculations and relationships.

If the column contains numbers or dates, verify the data type after splitting. Power Query may infer a type, but text that looks numeric can remain text, and automatic type steps can sometimes need adjustment after other transformations. Select the relevant column and use the data type control in the editor to set the intended type. Keep identifiers such as postal codes as text if leading zeros matter.

Refresh the query and understand the output

When the transformation is ready, use Home > Close & Load to return the result to Excel. The split steps are saved as part of the query, rather than being a one-time manual edit. If the source table gains or changes rows, refresh the query using Data > Refresh All or the refresh command for that query. Power Query reruns the saved steps against the updated source.

Refresh does not mean every possible source change will be handled automatically. If a source column is renamed or removed, a later step that refers to it can fail. Adding new values to an existing delimited column is generally compatible with a split step, but review the output after refreshing, especially if source formatting or delimiters have changed. Keep the original source table intact if you need to compare the transformed output with the incoming data.

Common problems and fixes

  • Nothing splits: Check that the selected delimiter exactly matches the characters in the source, including spaces or punctuation. A comma and a semicolon are different separators.
  • Values have unwanted spaces: Split on the main delimiter, then apply Transform > Format > Trim to the resulting text field.
  • A value is split in the wrong place: The delimiter may also appear inside a value. Choose a safer separator or correct the source structure before splitting.
  • There are too many rows: Check for repeated delimiters, trailing separators, or values that contain the chosen character. Filter unintended blank items after splitting.
  • Columns do not line up: Column splitting assumes the parts have positional meaning. Use rows for variable-length lists, or standardize the source before creating fixed fields.
  • Totals look too large: A row split repeats other fields. Summing an order total on every item row counts the same order more than once; aggregate at the appropriate level or preserve a separate order-level table.

Power Query versus worksheet formulas

Power Query is a good fit when the list comes from a table or external source and you want a repeatable transformation that can be refreshed. The editor records the steps, making the process easier to reuse than manually copying and splitting cells every time new data arrives. It is also useful for combining splitting with other cleanup tasks, such as removing columns, changing types, or filtering rows.

A formula can be more convenient for a small, live worksheet where results should update directly as cells change. In Microsoft 365 or Excel 2024, TEXTSPLIT can split text by a delimiter into columns or rows, depending on its arguments. But formulas and Power Query serve different workflows: a formula spills results in the worksheet, while Power Query produces a query output that is refreshed. For repeatable data preparation, Power Query is often easier to audit; for immediate worksheet calculations, a formula may be simpler.

Excel for Microsoft 365 on Windows is the focus of these ribbon steps. Power Query is available in current Excel for Mac for supported sources and operations, but the available connectors and editing experience can differ by build. Excel for the web also has Power Query capabilities whose availability and functionality may vary; if a command is missing, use the Windows desktop editor for the full workflow described here.

Frequently Asked Questions

Can Power Query split one cell into multiple rows?
Yes. Select the column, choose Transform > Split Column > By Delimiter, then select Rows under Advanced options. Each separated item becomes a row, and the other fields are repeated for those rows.

Does splitting into rows delete the original list?
The transformation changes the query output, not the source worksheet data. Power Query retains the source and records the split as a query step. You can edit or remove that step in the query, although you should keep a copy of important source data as good practice.

Can I split a list using more than one delimiter?
The delimiter dialog lets you choose or enter a separator, but mixed separators may require additional split steps or source cleanup. For example, if some records use commas and others use semicolons, standardize them or apply a second transformation and inspect the preview to ensure values are not divided incorrectly.

Why did Power Query repeat my other columns?
That is how a split into rows preserves the record’s context. Each item inherits the original row’s other field values. If those fields contain totals or counts, account for the repeated values before summing or aggregating the result.

Can I split a Power Query List object the same way?
Not necessarily. The Split Column command is for text in a column. If the preview shows a value labeled List, that is already a Power Query list value; convert or expand it into a table or rows using the list-related controls, then continue shaping the data.

Related

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.