3 Ways to Switch Data in Columns to Rows in Excel (Multiple Values)

In this video you'll learn how to switch data in columns to rows. Pivoting columns in Excel is a common problem when cleaning and organizing data. In this case however, we have a more complex scenario where we have multiple values when pivoting data. We'll have to combine these multiple values into one cell. I'll show you 3 methods to get this done. We'll use Dynamic array Formulas, Power Query (Pivot columns with multiple records) and finally Power Pivot and DAX (CONCATENEX together with HASONEFILTER).

We'll tackle a common data organization challenge in Excel—converting data in columns into rows. The scenario involves multiple departments working on various projects, with the goal of presenting this information in a dynamic and structured way.

Key Steps Covered:
Using Formulas: We start by utilizing Excel formulas, such as UNIQUE and FILTER, to extract unique values and handle cases where multiple people from the same department work on the same project.

Power Query: We explore the Power Query approach, demonstrating how to pivot columns and handle cases with multiple values in a cell.

Power Pivot and DAX: For more advanced users, we dive into Power Pivot and DAX to create a measure that dynamically concatenates values based on project and department, providing a highly flexible solution.

00:00 Transform Data in Tables from Columns to Rows
01:10 How to Use Formulas to Switch Data from Columns to Rows
05:51 How to Use Power Query to Pivot Columns with Multiple values
10:27 How to Use Power Pivot and DAX to create a measure with multiple values
16:33 Wrap Up

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[LeilaGharani]

Also, just curious if anyone recognized these project names?

[ijgosling]

Using the combination of Unique, Transpose and Filter functions has helped me transform a very large data set which would have taken me hours to format. I very much appreciate this video so thank you for posting this. Have been an avid watcher of your channel but I am very much a novice with Excel but love it. Thanks again Ian

[IvanCortinas_ES]

Spectacular demonstration of knowledge of the subject. Thank you Leila!

[martyc]

Excellent video Leila-- I am learning DAX currently and your explanantion was really concise. Would love to see more DAX videos from you!!

[hussainakhtar]

This is really amazing, especially the CONCATENATEX part.. I have applied these formula and other tricks to my data and it worked. Leila you're just on the next level's next level...

[kejmil]

Leila you are the best! You encourage me to learn VBA, DAX and and Power Query!

[fredzfranz]

As always Leila, you're amazing! I learned something new again today from you! Really awesome! ❤

[Franceskineos]

In the PQ approach you can also use a dummy criteria in the pivot aggregation (for example count) and then with the query editor hack the pivot line substituting the “count” with “text.combine”

[Mhziko]

Random Guy: I'm good at Excel.
Leila: Okay, please hold my glass!

[wayneedmondson]

Hi Leila. An awesome lesson! My first pass on the FILTER function solution was: =FILTER(TProject[Person], (TProject[Project]=$F2)*(TProject[Department]=G$1), ""). I like your use of "&" to concatenate the two conditions of the include argument.. very nice! PQ and DAX solutions are great too. Thanks for sharing these useful techniques and solutions :)) Thumbs up!!

[JonathanExcels]

I learned a couple things from this video. Thank you.

[SeanGHOB]

I like the formula method best, as it doesn't require the user to right-click & refresh to update the report. Those new formulas that spill values are very cool.

[s.y.daniel]

Thanks for the additional formula functions introduced here, got to rewatch and study it

[jaredervin]

I really could have used this 2 days ago. I've been trying to dev a cause and effect diagram for instrumentation I/O and this is way easier than using an access database.

Thank you!

[miracleugwu]

You are life saver. I have looking for this solution for the past three days. Thank you so much

[mattschoular]

Thanks Leila. I have multiple use cases for each of these three methods. Thanks for sharing them in one concise video.

[vijayarjunwadkar]

Loved this, though the DAX version was difficult! Formula and PQ rock! Thank you Leila for another great tutorial! 😊👍

[pascalejacquelinepetit]

Excellent tutorial! Many thanks for sharing Leila

[R-nk]

I actually miss your Excel videos, as usual great teachings

[kaushiksekar]

Wow, nice tutorial. No need for special paid classes. YT is best teacher. And for excel Leila is best.