From the course: Excel: Managing and Analyzing Data

Splitting columns into rows - Microsoft Excel Tutorial

From the course: Excel: Managing and Analyzing Data

Start my 1-month free trial

Splitting columns into rows

“

- [Instructor] Here's data that needs to be peeled apart. Looking at the Cityscape project, there's Samantha, who's the team lead, and on the team there's Terri, Morten, Lance, and Anitra. This is great to read if we just want to see who's on each team, but what if we wanted to sort this data by name, or do some analysis to see who's on more than one team, or who's on three or more teams? We can't do that so easily with this data as it is. We could write some formulas but that could get messy really fast. So let's look at Power Query's feature Split Column into Rows. Now don't worry about rows 12, 13, and 14 yet. Those are for later when we done our work and we refresh and update. So let's get into it. Cursor the data set and rather than right-click and go straight into Power Query, I'm going to format this as a table. Let's grab this black color and yes, the table does have headers. And in the Table Design tab I'm going to name this, go here, TeamMembers. Okay, now that is the name of this table. It's also going to be the name of the query. Right-click, Get Data from Table/Range. First, I'm going to split off the leads so that I have each person's lead and project next to their name. Okay. Want to highlight that column. Split Column by Delimiter. I'm going to split it by semicolon space at the left-most delimiter. Click there. Okay. Open this up a bit so we can see. Now for the magic. Highlight this column. Split Column by Delimiter. I want to Split by Custom by semicolon space. Now you can split by the semicolon and then afterward deal with any spaces. But I like to do this this way, split by the semicolon space and keep moving. Now Advanced options, Split into Rows. Okay. Check that out. The names of the team members are all in their own cell on their own row, but you still know which project they're tied to and who the lead is. Al is on the Espresso project and Ayanna is the lead for that project. Let's scroll down and look at the data. We got Flamingo, OnRamp, project Vista. And there's Al again. And Cyrus also. Scroll back up. Now, something that we can do is highlight this column and then sort it ascending and then highlight the Team_2 column and then highlight that ascending. So now inside every project every team member is in there in alphabetical order. And also notice in the applied steps. All of our steps have been recorded. We can even go back and look. There's the source data. Then there's where we split to get the team leads separate from the other team members. Go down, Split Column by Delimiter and then we did a Sort. Now I'm going to rename. Call this Team Leads and then Team Members. Okay. And also notice when I said earlier that by naming the table TeamMembers without a space, Power Query automatically made that the name of the query. Next, close and load. There is our data, check it out. Okay. Now we do have to go back and then we're going to update. Now notice that our data is now in a table. That's what Power Query does for us. Slide that in. The new projects are there in the table. They have been absorbed. Notice we've got Burro, Optical2, and Silhouette. Go over and then Data, Refresh. All the steps we did in Power Query have been repeated. Scroll down. There's Silhouette, there's Optical2, everything is here. Now we can do whatever we want with this data. Sort it some more, filter it, group it, a lot of things that we can do now that the data is split out. And we did that by Splitting Column into Rows.

Contents