3 Ways to Switch Data in Columns to Rows in Excel (Multiple Values)
ฝัง
- เผยแพร่เมื่อ 9 ก.ค. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
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).
⬇️ Download the workbook here: pages.xelplus.com/transform-t...
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
★★★ My Online Excel Courses ► www.xelplus.com/courses/
More Power Query videos: • Excel Power Query (Get...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
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
Grab the file I used in the video from here 👉 pages.xelplus.com/transform-table-file
Also, just curious if anyone recognized these project names?
Yes, they are all sports and I think from Olympics... Correct??
They appear to be James Bond references. I recognised Grand Slam from Goldfinger and Thunderball is, obviously, from Thunderball.
Abcdefg
Expect a Dax course from you
RC Communication
The truth is brilliant, your videos are incredible with the practicality you explain, I use each of the things you show in the day-to-day life of my profession!!!!!!!
Wow, thank you!
Random Guy: I'm good at Excel.
Leila: Okay, please hold my glass!
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
Spectacular demonstration of knowledge of the subject. Thank you Leila!
Leila you are the best! You encourage me to learn VBA, DAX and and Power Query!
As always Leila, you're amazing! I learned something new again today from you! Really awesome! ❤
Thanks for the additional formula functions introduced here, got to rewatch and study it
I learned a couple things from this video. Thank you.
I actually miss your Excel videos , as usual great teachings
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...
I really like your videos where you explain every possible question people ask! Your answer everything, so smart!! No wonder you won the MVP prize in Microsoft!!! Your are the best, i have seen your website too!!! 🙏🙏👍👍🔥🔥🔥
So nice of you! Thank you.
BTW, Your website is amazing
You are life saver. I have looking for this solution for the past three days. Thank you so much
Excellent video Leila-- I am learning DAX currently and your explanantion was really concise. Would love to see more DAX videos from you!!
Almost at the magic million mark now. Hope there’s a special video lined up!
Thanks Andrew! Yes - planning something different.... I'll post on the community tab later this week. Thank you so much for all your support, comments & likes!
Expecting a big one here
Same here.. Never been this excited before following a very useful TH-cam channel (for me of course..) and feel very excited to see it grow..
Wow, nice tutorial. No need for special paid classes. YT is best teacher. And for excel Leila is best.
Super solution. Was stuck at work with one file for a long time. And got it resolved with this solution .... just like magic
Thanks Leila. I have multiple use cases for each of these three methods. Thanks for sharing them in one concise video.
Thank you Matt! Glad you find it useful.
Man, I been looking for formulas for ages to do this. Never knew about power queries either.
Gonna have to rewatch this video multiple times. Shame I can't thumbs up for each method.
Thanks for watching!
Leila's Tshirt: "Excel can do it! Now what's the question?" 🤣🤣
You have definitely taught me that! Thank you Ms.Leila 🙏🏾
Always best Teacher
lots of love from India
You always Teach very easily miss.
Appreciate it
Excellent tutorial! Many thanks for sharing Leila
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!
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.
Power query is better when you are working on huge data and specially form different sources
Omg, thank you so much! You saved my life.
From all the TH-camrs I subscribed... You are the one from whom I learned a lot and continue this journey along with you.. Lot of positives things besides the subject matter makes your video very interesting and interactives... Lot of well wishes for you and thumbs up for your efforts... Keep rocking
Thank you!! I've been struggle for so long, and finally found this video! Your explanation is really easy to understand. Thank you again :)
Glad it was helpful!
Loved this, though the DAX version was difficult! Formula and PQ rock! Thank you Leila for another great tutorial! 😊👍
Thanks Vijay.
I used to think Excel was for nerds until I stumbled across Lella. Now I am captivated and signing up for a course with her. Who would have thought! There should be mug with the encryption 'Excel with Lella.' She is enchanting.
i really love the Dax approach
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!!
Congratulations on approaching one million subscribers. You are awesome and deserve the best. Thank you for all the expert information you provide.
Leila, you totally rock! Happy weekend otherwise
Leila Gharani the great! Massive love from Nigeria.
Thanks for explaining it "Simply".....
what can I say, you are very good at explaining, you teach me many beautiful tricks. congratulations Leila 👍
Great video with great instruction Miss Leila.
Magic, you are reaching new levels now .. solving a problem in different ways .. all new to me 🤪.. i feel dumb
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”
Leila another great video. For me formulas are hands down in this case
Beautiful and tempting
Thank a lot for the lesson 👍👍👍🙋🙏
that's amazing. thank you very much.
Thank you for the method3, using DAX. That's very important!
My pleasure, Iván.
Thank you for the tutorial. I'd go with the Formulas approach.
Congratulations! for 1 Million and Thanks for these very impressive and useful tutorials. Sharing knowledge is the best job ❤️
I got a job just by learning from your videos. Thank you, may happiness comes with you
That's great to hear!
I like your all videos helped me alot
Wow, it’s amazing Leila… query method looks like the easiest way. I had to rewire my brain 🧠 after I watched the DAX version! Too hard for me 🔥🧠🔥
Thanks Afshin! Agree with you about the DAX part. I wasn't going to add it, but someone asked about it and I couldn't resist 😊
@@LeilaGharani Legend.
I was wondering if you could make a video for someone who has to deal with a new datasets. Basically the approaches he/she has to take to build up the meaningful reports.
For instance, someone who recently hired as a demand planner in a company and wants to impress the managers with figures and facts the person can takeout from row data.
I also have another idea (to make video) you maybe interested.
Thanks again
Thanks for sharing
Recently, i got assign to make dynamic table and i was struggling since 3days and atlast your video helped me alot and how to make a dynamic table👏👏🏆I wanna say Big Thank you!!!!for showing us a dynamic table best example😊😊@Leila
Great video! I vote for Power Query.
Wouah, amazing !!!
Great explanation
Thank you very much
So helpful, thanks Leila!
Thank you Chris!
Wow... Great learning
Very interactive and intelligent way👌🕸
It's really helped me...tq
Super Amazing!
Eager to see a full course on DAX next. I have learnt various skills from a lot of courses and video lectures, but the confidence and level of comfort in their use I was able to build were only once I went through yours. Truly unmatchable!
Amazing!!
You are Amazing! 👌
Excellent tutorial. Power Pivot looks much effective. Though I am familiar with PQ, found this method little difficult
you are my savior! thanks for this so much helping video, hope more people discover you.
You're very welcome!
Great example and comparison of 3 different methods to achieve the result
Thank you! Cheers!
Thanks for this video and all the rest. Very interested to see the whole DAX and Data Modelling in Udemy 😍
thank you!
Dear Marisa
You can also visit our channel to learn ms Excel and macros for free.
th-cam.com/channels/8WvuiN2SfVA8p6Uhx8UmpQ.html
Nice Video thanks
Dang, I ❤️ you! Truly awesome! Thank you for this!
My pleasure!
Hey Leila, you can add this video to your PowerQuery & Advance Excel courses as update. Just my thought since I've subscribed both in Udemy. Thanks for this wonderful video.
Outstanding. Thank you
My pleasure!
Thank you so much mam to give us too knowledge 🙏🏻🙏🏻😊
Thank you
the first two solution is great, third one was overkill :)
Hi Leila,
Pivot the table by adding below step after the Changed Type in applied steps:
Table.Pivot( #”Changed Type” , List.Distinct(#”Changed Type”[Department]) , “Department” , “Project” , (Person) => Text.Combine( Person , “#(lf)” ) )
I LOVE Power Query 😘
Wow! I knew the first method - but I learned a lot about the other 2 methods, especially the DAX/Power Pivot method!
Is DAX the same as Excel formulas - if not, could you please continue doing videos on DAX? I am very fascinated by it!
Also, I didn't know how to use Power Query to create and extract a list of values! Very interesting!
I really like this series. A quick question. How can I add the UNIQUE function to Excel 2016?
Bravooo 👏👏👏👏
Amazing :)
I like the PQ version better. Thanks for an awesome video as always "You Rock".
Thanks for watching, Robert!
Simply wow
Obrigado 👏👏👏👏👏👏👏
Good job
Thanks!
Thank you!
Thanks for the video. It really helps. I have a question though. I have table of data which has unique data for different cases. like in your video, there are three variables / columns but in my case instead of third column I have 6 more column of data. Now I need to switch my rows into columns keeping the 6 columns as they repeat for each row with unique values. I am not sure, if I am able to explain my problem or not but hoping to hear from you.
Tq so much
I am looking for this type of tutorial.Cute
You can fix a table reference when dragging or copying, you just need to double up your column reference and surround with extra square brackets e.g. TProject[[Person]:[Person]]. Don’t forget to separate the two instances with a colon.
Love your videos. I have a question is there a way to make xlookup dynamic when looking up from a pivot table, that is constantly being updated as currently I’m having to change range each time. I don’t know if you have a video regarding this problem. 🤔🤗
Coming soon 1M🥳🥳🥳🥳 congratulations in advance
Hi, Thank you very much for sharing all these guides and tips... I wonder is there any way to move multiple cells together from one column to another please
I find the Power Query and Dax options actually the easiest to understand and to get my head around. The Formula option was next level.
Do you need to go all the way to suppress the totals or will it not be easier to just switch the grand totals off on the Pivot Design?
Hi Leila, this is all good stuff but like any solution I would always like to know which is the quickest method when dealing with volumes.
for grand total hide - simply go to design >grand total>off rows and columns
Thanks
Thank you!
Hi, nice channel. I learned a lot thanks. Btw, could you please explain power query for Mac. I haven’t managed to get it for Mac. I will really love to use it.
@leilagharani can I use still use the concatenatex as a Dax function if I just want to return just one text?
Hi Leila, thx for your professional videos. I have a question to solution 1: If there is would be added in your data table a new Project, it would show up in column E, but how can we ensure, that the formulas in range F3 to H6 dynamically go a additional line?