How to Split Data with Newline Characters into Separate Rows in Excel Using Power Query

When working with datasets in Excel, you might encounter situations where multiple values are stored in a single cell, separated by a newline character (added using Alt + Enter). This can make data analysis challenging.

In this blog, we’ll walk you through how to split such data into separate rows using Power Query, a powerful tool within Excel for data transformation.

Example Dataset:

Employee Name	Department	Skills
Sarah	Marketing	SEO Content Writing
John	IT	Java Python
Emily	HR	Recruitment Onboarding
Michael	Finance	Budgeting
Jessica	IT	C++ JavaScript
Daniel	Sales	Negotiation

Let’s consider the above example.

Step-by-Step Guide to Using Power Query

Step 1: Load Data into Power Query

1. Select your dataset by CTRL+A (including headers).
2. Go to the Data tab → Click From Table/Range.
3. In the dialog box, ensure My table has headers is checked → Click OK.
   
4. This will open a new window.

Step 2: Split Column by Delimiter

1. 1. Select a column where multiple values are separated by newlines.
   2. Under the Home tab, find Split Columns and select By Delimiter.
   3. In the Advanced Options, choose to split the values into Rows or Columns, In this case we are splitting the columns in row wise. Hence selected as Rows.
   4. Under Insert Special Characters, select Line Feed and click OK.

Once it is loaded into the new Sheet then below is the output data.

Employee Name	Department	Skills
Sarah	Marketing	SEO
Sarah	Marketing	Content Writing
John	IT	Java
John	IT	Python
Emily	HR	Recruitment
Emily	HR	Onboarding
Michael	Finance	Budgeting
Jessica	IT	C++
Jessica	IT	JavaScript
Daniel	Sales	Negotiation

 

By following these steps, you can efficiently split data with newline characters into separate rows, making your data analysis much easier.