How to Separate Data in Excel by Comma into Rows

Dealing with data crammed into a single Excel cell can feel like trying to solve a puzzle. When you have a list of items like "Product A, Product B, Product C" all in one place, it's impossible to sort, filter, or analyze them properly. This tutorial will show you exactly how to take a comma-separated list in one cell and neatly split it into multiple, clean rows.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

We'll walk through a few different methods, from the most modern and powerful approach using Power Query to dynamic array formulas for Microsoft 365 subscribers, and even a reliable VBA script for those using older versions of Excel.

Why Split Comma-Separated Data into Rows?

First, let's look at a common scenario. Imagine you've exported a list of blog posts from your content management system. You have the post title in one column and a list of tags in another, separated by commas, like this:

If you wanted to find out which tag is most common, or create a chart showing the frequency of each tag, you can't do it with the data in this format. The goal is to transform it into a "normalized" or "tidy" format, where each tag has its own row:

With your data structured this way, you can easily use tools like PivotTables and filters to gain meaningful insights.

Method 1: The Best Way (Power Query)

For anyone using a modern version of Excel (2016 and newer, including Microsoft 365), Power Query is the most powerful and scalable solution. It feels like magic once you get the hang of it. You build a repeatable process, so if your source data changes, you can just click "Refresh" to update everything.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step Guide to Using Power Query:

1. Format Data as a Table: First, select your data range and format it as an Excel Table. You can do this by clicking anywhere in your data and pressing Ctrl + T. Make sure the "My table has headers" box is checked if it does. This makes the data easier for Power Query to work with.
2. Send to Power Query: With a cell in your new table selected, go to the Data tab on the Ribbon. In the "Get & Transform Data" section, click From Table/Range.
3. Open the Power Query Editor: This action will open a new window called the Power Query Editor. Here is where you'll transform the data. You'll see a preview of your table.
4. Select the Column to Split: Click on the header of the column that contains your comma-separated lists (e.g., the "Tags" column).
5. Split by Delimiter: Go to the Home tab within the Power Query Editor. Click on Split Column and choose By Delimiter from the dropdown menu.
6. Configure Split Settings: A dialog box will appear.
7. Review and Clean (Optional): Your data will now be split into rows. Power Query automatically duplicates the data in the other columns. Before loading, you might want to clean up extra spaces. Select the newly split column, go to the Transform tab, click Format, and then select Trim. This removes any leading or trailing spaces.
8. Close & Load: Once you're happy, go back to the Home tab and click Close & Load. This will load the transformed data into a new worksheet in your Excel file, perfectly structured and ready for analysis.

The beauty of this method is its durability. If you add more rows to your original table and then right-click your new output table and select "Refresh," Power Query will automatically repeat all your steps for the new data.

Method 2: For Microsoft 365 Users (Dynamic Formulas)

If you're a Microsoft 365 subscriber, you have access to new dynamic array functions that can accomplish this with a single formula. The star of the show here is TEXTSPLIT.

Splitting a Single Cell

Let's say cell A2 contains the text "SEO, Content Marketing, Blogging". To split this into separate rows, you can use the following formula:

=TRIM(TEXTSPLIT(A2, , ","))

How this formula works:

• TEXTSPLIT(A2, , ","): The TEXTSPLIT function has arguments for a column delimiter and a row delimiter. By leaving the column delimiter blank (the empty space between the first two commas) and providing "," as the row delimiter, we tell Excel to split the text vertically.
• TRIM(...): We wrap the whole thing in the TRIM function. This cleans up any unintentional spaces around the commas (e.g., "SEO, Content Marketing").

Handling an Entire Column (Advanced)

Splitting an entire column of comma-separated values while keeping associated data requires a more complex formula, as you need to build the final table dynamically. This can get complicated quickly and is where Power Query truly shines. For most multi-column scenarios, we recommend sticking with Power Query to avoid hard-to-read formulas. The dynamic formula approach is best for quick lookups or splitting lists from a single source cell.

Method 3: The Classic Solution (VBA Script)

If you're using an older version of Excel that doesn’t have Power Query or dynamic arrays, you can rely on a Visual Basic for Applications (VBA) macro. This requires some setup but is a very reliable way to get the job done.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step-by-Step Guide to Using the VBA Macro:

1. Open the VBA Editor: Press Alt + F11 to open the VBA Editor.
2. Insert a Module: In the top menu, go to Insert > Module. A new white empty pane will appear.
3. Paste the Code: Copy and paste the following code into the module window:

Sub SplitDataIntoRows() 'Declare variables for our objects and data Dim sourceSheet As Worksheet Dim targetSheet As Worksheet Dim sourceRange As Range Dim cell As Range Dim splitValues As Variant Dim val As Variant Dim targetRow As Long

'Set our source and target sheets
Set sourceSheet = ActiveSheet

'Create a new sheet for the output
Set targetSheet = Worksheets.Add(After:=sourceSheet)
targetSheet.Name = "Split Data Output"

'Let the user select the data range
On Error Resume Next
Set sourceRange = Application.InputBox("Select the range with your data.", "Select Range", Type:=8)
On Error GoTo 0

'Exit if user cancels
If sourceRange Is Nothing Then
    Application.DisplayAlerts = False
    targetSheet.Delete
    Application.DisplayAlerts = True
    Exit Sub
End If

Dim columnToSplit As Long
columnToSplit = Application.InputBox("Enter the column number to split (e.g., 2 for column B).", "Column to Split", Type:=1)

'Exit if invalid column number
If columnToSplit = 0 Or columnToSplit > sourceRange.Columns.Count Then
    MsgBox "Invalid column number. Please try again.", vbExclamation
    Application.DisplayAlerts = False
    targetSheet.Delete
    Application.DisplayAlerts = True
    Exit Sub
End If

'Copy headers to the new sheet
sourceRange.Rows(1).Copy Destination:=targetSheet.Cells(1, 1)
targetRow = 2 'Start pasting data on the second row

'Loop through each data row in the selected range (skipping header)
For Each cell In sourceRange.Columns(columnToSplit).Cells
    If cell.Row > sourceRange.Row Then
        'Split the cell's value by comma
        splitValues = Split(cell.Value, ",")
        'Loop through each of the split values
        For Each val In splitValues
            'Copy the entire original row to the target sheet
            cell.EntireRow.Copy Destination:=targetSheet.Rows(targetRow)
            'Overwrite the cell with the single, trimmed value
            targetSheet.Cells(targetRow, columnToSplit).Value = Trim(val)
            'Move to the next row in our target sheet
            targetRow = targetRow + 1
        Next val
    End If
Next cell
'Inform the user the process is done
MsgBox "Data splitting complete!", vbInformation

End Sub

1. Run the Macro: Close the VBA Editor, press Alt + F8, select "SplitDataIntoRows," and click Run.
2. Follow the Prompts: Select your data range and enter the column number for the comma-separated data when prompted.

The macro will create a new worksheet, copy the headers, and generate a clean row for each list item, giving you a tidy dataset.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Final Thoughts

Turning messy, comma-separated data into clean, usable rows is a foundational skill in Excel for anyone doing data analysis. Whether you choose the powerful and refreshable approach of Power Query, rely on modern dynamic array formulas, or use a VBA script for older workbooks, there is a reliable method to get the job done. This transformation unlocks your ability to properly analyze your data using PivotTables, filters, and charts to find valuable insights.

Of course, this kind of manual cleanup in spreadsheets is often just one step in a much longer reporting process. We built Graphed because we believe your time is better spent on strategy, not wrestling with CSVs and formulas. By connecting directly to your marketing and sales platforms, Graphed automates data preparation and lets you build real-time dashboards just by describing what you want to see in plain English. That means you can answer your biggest business questions in seconds, not hours.