ThisCalculator

Everyday

Excel Split One Row into Multiple Rows: 4 Ways

Updated · Standard formula, shown below

Excel Split One Row into Multiple Rows: 4 Ways

To split a single row of data into multiple rows in Excel, you can use Power Query’s “Split Column” feature, the TRANSPOSE function, or a simple copy-and-paste with Transpose. This article explains each method step by step, so you can choose the one that fits your data layout and skill level.

Why You Might Need to Split a Row

Excel rows are often used to store multiple related values in one line—like a customer’s order with several items, a project with multiple team members, or a sensor reading with many measurements. When you need to analyze, filter, or chart that data, having each value in its own row makes it much easier. For example, a row with “John, 2024, Q1, Q2, Q3, Q4” could be split into four rows, one for each quarter, so you can pivot or filter by quarter.

Method 1: Power Query (Best for One-Time Splits)

Power Query is Excel’s built-in data transformation tool. It’s the most reliable way to split a row into multiple rows, especially when the values are separated by a delimiter like a comma or semicolon.

  1. Select your data range and go to Data > From Table/Range (if your data isn’t already a table, Excel will convert it).
  2. In the Power Query editor, select the column containing the values you want to split.
  3. Go to Home > Split Column > By Delimiter.
  4. Choose the delimiter (comma, semicolon, custom, etc.) and select “Each occurrence of the delimiter” under “Split into”.
  5. By default, Power Query splits into new columns. To split into new rows, click Advanced options and choose “Rows”.
  6. Click OK, then Close & Load to put the result back into a worksheet.

This method works for both comma-separated values and data that has a fixed number of characters. If you need to split by position (e.g., first 3 characters, next 4), use “By Number of Characters” instead.

Method 2: TRANSPOSE Function (For a Single Row of Values)

If you have a single row with values in consecutive columns and you want to turn them into a single column, the TRANSPOSE function is the quickest formula-based approach.

  1. Select the cell where you want the first result to appear.
  2. Type =TRANSPOSE(A1:D1) (adjust the range to match your row).
  3. Press Ctrl+Shift+Enter (or just Enter in newer Excel versions that support dynamic arrays).

This will spill the values into a vertical list. If you need to split a row that contains multiple groups (like “Item1, Item2” in one cell), you’ll need to combine TRANSPOSE with TEXTSPLIT (Excel 365) or a combination of MID, ROW, and INDIRECT for older versions.

Method 3: Copy and Paste Transpose (Simplest, No Formulas)

For a quick, one-off conversion of a single row to a single column, you don’t need any formulas at all:

  1. Select the row of data.
  2. Press Ctrl+C to copy.
  3. Right-click the destination cell, choose Paste Special, then check Transpose.
  4. Click OK.

This turns a horizontal row into a vertical column. It doesn’t split delimited text within a cell—it only changes the orientation of the data.

Method 4: Text to Columns + Transpose (For Delimited Text in One Cell)

If your entire row is actually a single cell containing delimited text (like “apple,banana,cherry”), you first need to split that cell into separate columns, then transpose those columns into rows.

  1. Select the cell(s) with the delimited text.
  2. Go to Data > Text to Columns.
  3. Choose “Delimited”, select your delimiter (comma, space, etc.), and finish.
  4. Now you have multiple columns. Copy them and use Paste Special > Transpose to turn them into rows.

This is a manual two-step process but works in every Excel version.

Common Problems and How to Avoid Them

  • Losing data when splitting into rows: Always make a backup or use Power Query, which keeps the original data intact.
  • Empty cells causing issues: Power Query has an option to “Skip empty cells” when splitting—use it to avoid blank rows.
  • Delimiter not recognized: If your data uses a different delimiter (like a pipe | or a tab), choose “Custom” in Text to Columns or Power Query and type the exact character.
  • Formulas breaking after transpose: TRANSPOSE creates a dynamic array; if you edit the original row, the transposed result updates automatically. But if you need a static copy, use Paste Special > Values after transposing.

How to Choose the Right Method

  • One-time split of a single row: Use copy-paste transpose or Text to Columns.
  • Repeated splits on new data: Use Power Query—you can refresh the query when new data arrives.
  • Live, formula-driven split: Use TRANSPOSE or TEXTSPLIT (Excel 365) if you want the result to update automatically.
  • Data with multiple delimiters or irregular patterns: Power Query is the most flexible; it can split by multiple delimiters or by position.

Frequently Asked Questions

Can I split a row into multiple rows without Power Query?

Yes, you can use the TRANSPOSE function or the copy-paste transpose method. For delimited text, use Text to Columns first, then transpose. These methods work in all Excel versions, though they’re more manual than Power Query.

What if my data has a variable number of items in each row?

Power Query handles this well: when you split by delimiter into rows, it automatically creates as many rows as there are items, even if the count varies. The other methods require you to know the maximum number of items in advance.

Does splitting a row affect the original data?

Only if you use Text to Columns directly on the original cells—it overwrites them. Power Query and TRANSPOSE create new output without altering the source. Always copy your data to a new sheet if you’re unsure.

Can I split a row into multiple rows based on a condition (e.g., only split if a column has a certain value)?

Yes, in Power Query you can filter rows before splitting, or split all rows and then filter the result. For example, you could split only rows where the status column equals “Active” by filtering first, then splitting.

Is there a way to split a row into multiple rows in Google Sheets too?

Yes, Google Sheets has a SPLIT function that splits text into columns, and you can use FLATTEN to convert those columns into rows. For example, =FLATTEN(SPLIT(A1, ",")) will split the contents of A1 into separate rows.

Why does my TRANSPOSE formula show a #SPILL! error?

This happens when the cells where the transposed data would go are not empty. Clear the cells below and to the right of your formula, or move the formula to a location with enough empty space.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.