In Excel for Microsoft 365 on Windows, use Power Query: select a cell in your source table, choose Data > From Table/Range, then in the Power Query Editor select the column and choose Transform > Split Column > By Delimiter. Pick the delimiter, open Advanced options, choose Split into Rows, and select OK. Power Query creates one output row for each separated item while repeating the other columns’ values.
What splitting a cell into rows does
Splitting into rows is useful when a cell holds several values that should be treated as separate records. For example, a project table might have one row for a project and a Participants cell containing Maya; Leo; Sam. Expanding that cell produces three rows for the project, one for each participant. The project name, date, and other columns are repeated on those rows, so each participant remains connected to the project.
This is different from splitting a cell into columns. Splitting into columns would put Maya, Leo, and Sam in separate fields on one row. Splitting into rows gives each value its own record, which is usually more useful for filtering, counting, matching, and building PivotTables. It also changes the number of rows: a source row with four delimited items contributes four output rows.
Power Query does not divide the original worksheet cell in place. It records a sequence of transformation steps and loads the result as a query output, usually a new table. The original source remains available, and you can change the source data and refresh the query to rebuild the output.
Prepare the source table
Power Query works best when the data has one header row and a consistent structure. Convert the range to an Excel table first if it is not already one: select a cell in the range, choose Insert > Table, confirm whether the table has headers, and select OK. Table headers should be descriptive and unique. Avoid merged cells and blank header names.
For a simple example, suppose the table has columns OrderID, Customer, and Products, and a Products value is Notebook; Pen; Folder. The goal is one row per order-product combination. Select a cell in the table and choose Data > From Table/Range. Excel opens the Power Query Editor. If Excel asks to confirm the range or create a table, check the selection and confirm.
In the editor, click the column to split. Selecting only that column is generally the clearest approach; the other columns remain in the query and are repeated as the values expand. Then choose Transform > Split Column > By Delimiter. In the dialog, select the delimiter that separates the values, such as semicolon, comma, or space. Under Advanced options, choose Split into Rows, then select OK.
Review the preview before loading. For the example, the OrderID and Customer values should appear on three rows, and Products should contain Notebook, Pen, and Folder on separate rows. If the values are correct, choose Home > Close & Load to load the result to a worksheet. To choose a destination or load type, use Home > Close & Load To when that option is available.
Choose the right delimiter and split setting
The delimiter is the character or text placed between items. Use the separator that actually appears in the source, not simply the one that looks most familiar. A comma is common in lists, but a semicolon may be safer when individual values can contain commas, such as city and state names. A pipe character or another uncommon separator can also work if the source consistently uses it.
Choose splitting at each occurrence when every separated value should become its own row. For instance, A|B|C should produce three rows if the pipe separates all three items. If the cell contains a value with multiple delimiters but only the first or last separator should be used, the dialog’s split-at choices may be more appropriate; check the preview because those choices can create a different structure from splitting every occurrence.
Watch for spaces around separators. A value such as Notebook; Pen; Folder may leave a leading space before Pen and Folder after splitting. That may not be obvious visually, but it can cause a lookup or comparison to fail. In the editor, select the resulting text column and use Transform > Format > Trim to remove leading and trailing spaces. Use Clean as well when the source may contain nonprinting characters. Check the preview after applying either step.
Empty items need attention too. A value such as A;;C has two delimiters in a row, so the middle item is empty. A trailing delimiter, as in A;B;, can also yield an empty item. Decide whether empty rows represent missing information that should be retained or noise that should be removed. You can filter the split column to exclude blank values, but do so only if removing them matches the meaning of the data.
Keep related fields and row order understandable
Power Query expands the selected column and duplicates the other fields for each resulting item. That is usually the desired behavior: the order number is repeated beside each product, making the output suitable for analysis. It can also increase totals if you later sum a value that was repeated. For example, an order total of 60 repeated across three product rows will sum to 180 if you total that column directly. Do not treat repeated parent-level amounts as item-level amounts unless you deliberately allocate them.
If the original sequence matters, add an index before splitting. In the Power Query Editor, choose Add Column > Index Column and select a starting option. The resulting index identifies each source row and repeats for its split items, making it easier to group or sort the output back into source order. If you also need a reliable position number for each item within its original cell, plan for that as a separate requirement; a source-row index alone does not assign a distinct item number.
It is also sensible to rename the split column to reflect its new meaning. Products might become Product, for example. Clear names make later filters and reports easier to understand. If the split output will be used by other queries or reports, keep the output columns and their data types consistent from one refresh to the next.
Handle common problems
- The whole cell stays together: Confirm that the delimiter in the dialog matches the source exactly. A regular space and a nonbreaking space can look alike but are different characters. Inspect the source or replace inconsistent separators before splitting.
- Values split into too many pieces: The chosen character may also occur inside legitimate values. Select a less ambiguous delimiter, standardize the source, or use a split-at option that matches the intended structure. A text split is not automatically aware of the meaning of each value.
- Quoted commas are still split: Splitting a text column on commas is a character-based operation; it should not be assumed to interpret quotation marks as CSV rules. If commas inside quoted values belong to the value, use a properly parsed source or clean and structure the data before splitting.
- Unexpected blanks appear: Check for repeated delimiters, a delimiter at the beginning or end, or cells that contain only spaces. Filter blanks only after deciding whether they represent meaningful missing entries.
- Manual edits disappear after refresh: Query output is regenerated from its source and transformation steps. Do not use the loaded result as a place for permanent hand-entered corrections. Correct the source or add a deliberate transformation instead.
Another common issue is treating the expanded rows as independent original records. They are derived rows. If you need a distinct count of orders after expanding products, count unique OrderID values rather than counting output rows. Likewise, verify whether each measure is per order or per product before summarizing it.
Refresh and maintain the query
After loading the result, changes to the source table do not necessarily appear in the output immediately. To update it, choose Data > Refresh All, or right-click the query output table and choose Refresh. Power Query reruns the recorded steps against the current source. A newly added source row will be included when the query points to the Excel table and the new row is part of that table.
To edit the transformation, open the query from Data > Queries & Connections, then edit it in the Power Query Editor. The Applied Steps list shows operations such as selecting a table, splitting a column, trimming values, and changing types. Selecting an earlier step lets you inspect the data at that stage. Changing or deleting a step can affect later steps, so review the preview before closing and loading again.
Power Query results can be loaded to a worksheet or, depending on the load options and workbook setup, to the Data Model. A worksheet output is convenient for inspection and further Excel analysis. If the result is large, consider whether loading every expanded row to a worksheet is practical; Excel worksheets have a row limit, and a split can multiply the source row count substantially.
Power Query compared with other approaches
Power Query is a strong choice when the split needs to be repeatable. Once the query is set up, refresh applies the same steps to updated source data. It also keeps the original data intact and makes transformations visible in a sequence of steps. This is preferable to manually inserting rows each time a list changes.
Excel formulas can be useful for a small, dynamic display, but they are not the same as a durable cleaned table. Microsoft 365 includes text and dynamic-array functions that can split text, but arranging a varying number of results into a normalized table with matching fields may take additional formulas. Power Query is often easier to maintain for recurring imports and cleanup tasks.
Use Split into Columns when the destination needs a fixed set of fields, such as FirstName and LastName. Use Split into Rows when every item should be a separate record. If the source instead has a wide layout with repeated columns such as Product1, Product2, and Product3, the more suitable Power Query operation may be to select those columns and choose Transform > Unpivot Columns. Unpivoting converts repeated field columns into attribute-value rows; it solves a related but different layout problem.
Excel for Mac and Excel for the web
The steps above describe Excel for Microsoft 365 on Windows. Power Query is also available in current Microsoft 365 versions of Excel for Mac, but the available connectors and parts of the interface can differ by version. Look for the Get Data or Power Query commands on the Data tab, then use the editor’s split-column controls if they are available. If a particular dialog or connector is missing, check that Excel is updated and consult the options in that Mac build rather than assuming every Windows feature is present.
Excel for the web has different data-import and query-editing capabilities from the Windows desktop app, and not every Power Query authoring action is available in every account or release. For the full procedure described here, use the desktop Excel app when the browser version does not expose the required editor controls.
Frequently Asked Questions
Can Power Query split one cell into multiple rows?
Yes. In the Power Query Editor, choose Transform > Split Column > By Delimiter, select the separator, and choose Split into Rows under Advanced options. The other columns are repeated for each new row.
Does splitting change the original data?
No. Power Query creates a transformed query result and leaves the source data in place. The loaded result can be refreshed from the source, so make corrections in the source or in the query steps rather than relying on edits to the output.
Can I split a cell using a semicolon instead of a comma?
Yes. Choose semicolon as the delimiter in the By Delimiter dialog. You can also use a custom delimiter when the source consistently uses another character or text string.
Why are the other columns repeated after the split?
Each expanded value needs the context of its original record. Power Query repeats the other fields so each output row remains associated with the same order, customer, project, or other source information.
How do I remove extra spaces after splitting?
Select the resulting text column in the editor and choose Transform > Format > Trim. This removes spaces at the beginning and end of values. Check for other inconsistencies, such as different delimiter characters, separately.
Can I undo the split after loading?
You can edit the query and remove or change the split step in the Applied Steps list, then close and load the query again. The original source is not split in place, which is one reason to keep the source table intact.
Related
- Excel Power Query: How to Hide or Remove Columns Easily
- Vertex42 Budget Spreadsheet: How to Choose and Use One
- Excel Power Query: How to Group Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Present PowerPoint in Teams With Notes: How It WorksYes. Microsoft Teams can present a PowerPoint while you privately view your speaker notes by using PowerPoint Live:…
- Count Cells With Text in Google Sheets: Formulas and TipsTo count cells containing text in Google Sheets, use =COUNTIF(A2:A100,"*"), replacing the range with your data. For a…
- How to Add Animation to PowerPoint Text Step by StepTo animate text in PowerPoint, select the text box or placeholder, open the Animations tab, and choose an…
- Excel Client Contact List Template: Fields and SetupAn Excel client contact list template is a structured worksheet for storing names, company details, contact preferences, and…
- Google Sheets Dropdown Equals: Formulas and Rules ExplainedTo check whether a Google Sheets dropdown equals a particular choice, compare the dropdown cell with that value…
- How to Put a PowerPoint Slideshow on LoopTo make a PowerPoint presentation repeat, open it in the desktop app and choose Slide Show > Set…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.