Repeat Rows in Excel Based on Cell Split Values?

• ⚡ Power Query handles over 1 million rows and stays stable.
• 👨‍💻 VBA scripts automate repeating rows and splitting cells easily.
• 📊 Spreadsheeto found 66% of Excel power users like Power Query more than formulas.
• 🚀 Excel 365’s TEXTSPLIT function lets you split text strings.
• 🧩 Normalizing data with these tools is key for scripts, APIs, and automation.

Repeat Rows in Excel Based on Cell Split Values

Have you ever had a spreadsheet with many values in one cell, like 'A,B,C', and needed to make a new row for each value? Many people face this. This often comes up when you clean data, set up inputs, make forms, or get things ready for the back-end. And doing this easily can save many hours of manual work, whether you work in Excel as an analyst, developer, or IT professional. In this guide, we will look at good ways to do this with Excel formulas, Power Query, and VBA. We will go through each method to help you find the best one for what you need.

Understanding the Problem: Repeat Rows Based on Split Values in a Cell

Think of your data like this:

Your goal is to change it into:

This type of change is a way to "unpivot" or "normalize" data. And you often need to do this when getting data ready for:

• Exporting or importing clean CSVs
• Setting up databases
• Giving data to setup tools or CI systems
• Making clean inputs for APIs or data pipelines
• Showing and filtering data better in dashboards

To make these changes, you need to:

1. Split values in each cell with a separator (like a comma ,)
2. Make a new row for each value that you split, keeping the original ID.

You can do this in Excel in different ways using Excel formulas, Power Query, or VBA scripts. Each way has its own good points.

Excel Formula Method: Using TEXTSPLIT & Helper Columns

The easiest way, and good for smaller jobs in Excel, is to use formulas. Excel 365 offers new functions like TEXTSPLIT and SEQUENCE. These are good for splitting and changing lists of values in cells.

Step-by-Step Example Using Excel 365+

Here's how to repeat rows and split values using only formulas.

Sample Data

Step 1: Split the Items Using TEXTSPLIT

In a helper column (like column C), enter:

=TEXTSPLIT(B2, ",")

This changes A,B,C into a list: {A;B;C}.

Now, we need to copy the ID or related value for each item we split.

Step 2: Sequence Split Count

To figure out how many times to copy the ID, count the items:

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

This formula counts commas and adds one to get the total number of items.

Step 3: Use INDEX and SEQUENCE to Build the Output

To keep your main table clean, make a new table. Put formulas there that give you the final layout.

• Column A (ID Repeat):

=IF(ROW(A1)<=LEN($B$2)-LEN(SUBSTITUTE($B$2,",",""))+1, $A$2, "")

Copy the formula down for as many rows as you think you will need.

• Column B (Split Items):

=IF(ROW(A1)<=LEN($B$2)-LEN(SUBSTITUTE($B$2,",",""))+1, INDEX(TEXTSPLIT($B$2, ","), ROW(A1)), "")

Do this again for every row in your data.

Pros and Cons

✅ Pros

• No other tools or setup needed
• Updates by itself when cells change
• Works well for quick data changes

❌ Cons

• Does not work well for thousands of rows
• Needs Excel 365 (older versions do not have TEXTSPLIT)
• You have to manually add rows and copy formulas

Pro Tip

Use TRIM() with split values:

=TRIM(INDEX(TEXTSPLIT($B$2, ","), ROW(A1)))

This helps remove extra spaces from the data when you split it.

Power Query Method (No Code, Highly Scalable)

Power Query is Excel’s tool for getting, changing, and loading data. It is good for people who do not code but need strong ways to work with data using a visual menu.

Step-by-Step Walkthrough

Let's say your data looks like this:

Step 1: Convert to Table

1. Select your data.
2. Click Insert > Table or use Ctrl + T
3. Name the table Data (this helps but is not needed).

Step 2: Launch Power Query

• Go to Data > From Table/Range

This will open the Power Query editor.

Step 3: Split the 'Items' Column

• Right-click on the Items column > Split Column > By Delimiter…
• Choose Comma as the delimiter
• Select Each occurrence of the delimiter

You will see new columns like Items.1, Items.2, and so on.

Step 4: Unpivot to Normalize

• Select all new Items.X columns
• Go to Transform > Unpivot Columns

This changes wide rows into stacked, repeated rows.

Step 5: Clean Output

• Change the name of Attribute if you want (like "Field Order")
• Change the name of Value to Item
• Click Close & Load

And you are done!

Benefits of Power Query

• Works with big datasets (over 100,000 rows)
• Uses a visual menu (no coding)
• You can use it again—just refresh when you add new data
• Cleans messy data easily (like empty cells or extra spaces)
• Has good tools for errors and checking data

Microsoft's documents show that using "Split Column by Delimiter" and "Unpivot Columns" is a key way to change spreadsheets.

Example Output Table

This method works very well for big company tasks.

VBA Automation: Repeating Rows and Splitting Cell Values

If you know how to write scripts, VBA macros are a great way to quickly handle many rows without clicking many buttons.

Full VBA Script Example

Sub RepeatRowsBasedOnSplit()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Dim i As Long, j As Long
    Dim arr() As String
    Dim newRow As Long: newRow = lastRow + 1
    
    For i = 2 To lastRow
        arr = Split(ws.Cells(i, 2).Value, ",")
        For j = 0 To UBound(arr)
            ws.Cells(newRow, 1).Value = ws.Cells(i, 1).Value
            ws.Cells(newRow, 2).Value = Trim(arr(j))
            newRow = newRow + 1
        Next j
    Next i
End Sub

How It Works

• Goes through rows from 2 to the end of your data
• Splits the cell by commas
• Makes a new row for each item it splits

Notes and Warnings

• Always save a copy of your workbook before you run any macro.
• Change column numbers (Cells(i, 2)) if your columns move.
• If you want to run this with a button, link the macro to a shape or button.

Perfect For:

• Changing files on a set schedule
• Doing many tasks at once without the computer slowing down
• Sending results to other steps (like saving to CSV)

Comparison of Methods: Formula vs Power Query vs VBA

📊 Spreadsheeto's Excel Usage Report says that 66% of experienced users prefer Power Query for changing data. This is because of its easy-to-use menu and how you can use it again and again.

Extra Considerations: Excel Split Row Best Practices

These methods help you split values in cells and repeat rows. But there are a few things to keep in mind:

• 🔹 Same Separators: Make sure every item uses the same separator.
• 🔹 Spaces: Use TRIM() in formulas or Power Query's "Trim" step to handle extra spaces.
• 🔹 Empty Cells: Add ways to handle errors so you skip empty cells or rows.
• 🔹 Single Values: Do not skip or copy rows that only have one value if you don't need to.
• 🔹 Data Type: Check number and text formats after you change the data.

Using This in Dev Workflows and Automation Pipelines

These changes are not just for making Excel look neat. Instead, they are very important in coding and system automation.

Common ways engineers use this:

• ✅ CI/CD Settings: Clean up many test settings from config rows.
• ✅ Backend Setup: Get default permissions that are spread across permission tables.
• ✅ Form Builders: Change one-to-many field links into templates you can use again.
• ✅ ETL Steps: Change CSVs from Excel into flat data sets before putting them into SQL.

And when you add a CSV export or API trigger, these changes can be used right away in quick development plans or live systems.

Bonus: Automate Using Python + Pandas

Python developers can not use Excel at all and use Pandas to repeat rows after splitting cells:

import pandas as pd

df = pd.read_csv("data.csv")
df['Items'] = df['Items'].str.split(',')
df = df.explode('Items')
df['Items'] = df['Items'].str.strip()
df.to_csv("normalized_data.csv", index=False)

• explode() makes a new row for each value.
• Good for scripts and using in a data process.
• Works well with APIs or back-end services.

This is a useful tool when working with big data sets or automated tasks across different systems.

Final Thoughts and Recommendations

Need to split values in a cell and repeat rows in Excel? Here are your choices:

• 🔷 Pick Excel formulas for quick fixes on small data sets.
• 💡 Pick Power Query for easy-to-use, strong, and refreshable solutions.
• ⚙️ Pick VBA scripts when you want to automate tasks you can repeat and control with code.
• 🐍 Use Python/Pandas if your work is part of a bigger data process or code.

Knowing these methods lets you handle messy Excel data and change it into clean, organized formats. Then it is ready for analysis, automation, or to be used with other systems.

Citations

• Microsoft. (n.d.). Split columns by delimiter [Documentation]. Microsoft Support. https://support.microsoft.com/
• HowToExcel. (2022). Power Query vs formulas: When you should use each?. Retrieved from https://www.howtoexcel.org/
• Spreadsheeto. (2022). Excel Usage Trends: 2022 Report. https://spreadsheeto.com/