Convert Columns to Rows in Excel (NO Transpose, NO Formulas - SIMPLY UNPIVOT in Power Query)
Вставка
- Опубліковано 18 вер 2019
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
It sounds simple to just switch columns to rows in Microsoft Excel - basically transpose horizontal data to vertical. BUT you can't always use a simple transpose (neither the transpose feature nor the Transpose function) if you only need to transpose parts of your data set. Some columns, instead of all columns.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/unpivot-col...
For example if you have a report in Excel with a list of months in the columns, it would be easier to analyze the data and create formulas or pivot tables on the data, If your data is in a tabular format. For each value field in the original report, we will get a new row. Since this involves unpivoting or transposing parts of the data, it can be difficult to achieve with Excel formulas.
This is where you can use Excel's "Get & Transform" feature - (also called Power Query). Get & Transform is available since Excel 2016. All you need to do is create a table or a named range from your source data and then unpivot the columns in the Power Query editor.
If some of the value cells in your report happen to be empty - the entire row will be removed in the final table. To keep empty cells, you need to replace the null value in Power Query with 0.
🔍 What You'll Discover:
- How to transpose specific columns in Excel while preserving empty cells.
- Step-by-step guide to using Get & Transform (Power Query) for data manipulation.
- Tips for handling both table and range formats in Excel.
👩💼 Perfect for:
- Excel users looking for advanced data manipulation techniques.
- Anyone dealing with complex datasets that require selective transposition.
- Excel enthusiasts eager to explore the power of Get & Transform.
📌 Key Highlights:
- Transpose your data without losing sight of empty cells.
- Customize your data transposition for specific columns.
- Refresh your data dynamically with Power Query.
LINKS to related videos:
3 ways to transpose: • 3 Ways to Transpose Ex...
R1C1 trick to transpose: • How to Change HORIZONT...
Unstack data: • Quick Excel Trick to U...
➡️ 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/unpivot-columns-to-rows-file
You posted this video 2 years ago, but it's exactly what I need now to unpivot a ridiculously large data set. Thanks!
Simple and concise but with new tricks and skills to apply. I will urge you to seriously consider putting together a course in Power Query and Power Pivot etc. You have a gift of explaining concepts in a simple, logical and easy to grab manner. Keep up the good work
Lelia if your reading this thank you so much, I am an intern at a company and I was tasked to do this challenge which I thought was the hardest thing ever and I thought I could never do this. You taught me in 5 mins what I thought would take a whole month learning python and data science skills. Thank you so much, I also turned off the ad blocker just for u.
You just saved three hours of my life. Please continue with this high-quality content!
awesome💕
Seriously!!
I quite literally bought your VBA course to learn how to code this exact thing yet you explained it in 5 mins. You rock!
I hope the course is still useful for you Andre.
Leila, You are an Excel genius and your teaching is as good as your knowledge. A very rare combination. You deserve your success. Thankyou.
Wow. This is superb. I have Excel 2013 and I was in the middle of a data migration and came across a same kind of scenario. I was trying many ways without writing a program or using macros. But your video saved my day.
Its really useful. Thank you so much.
I'm struggling very high in the conversion of column to row and I'm doing it manually before I saw your video.
Now I very much impressed by seeing your video with detailed clear explanation, Thank you so much Leila..
Thank you so so much..
Wow, how could I be thankful to you more than this?! After hours and hours of googling, spot-on solution for the problem I was in .. Thank you so much!
Smart people like you sharing knowledge is the essence of humane growth
Agree
What I always wanted, and never did have, till you have so clearly outlined, that it takes my breath away! I am poetic today as Heaven knows how many hours I have spent on converting tables to database columns and did again the whole thing when the data changed! Thanks a ton, Leila! You are a gem! Move over, Mario!
ive been doing it in excel with one table, this is like magic I LOVE IT!
Hi Leila, I'm coming back to this video to let you know that from the publishing of this one video I started learning how to use Power Query. Recently, I was able to use these new skills to help my team append, merge, transform, and migrate about 500,000+ rows of data!! There is no way would would have been able to easily migrate all this data manually or by using functions because they would crash the program. My team was able to perform their individual duties in this project and my boss was extremely impressed as we didn't have IT support for this! Thank you!!
OMG! I have spent 3 days trying to get information in this exact format. You are a life saver with these videos.
Magic. Although I don't need all these features in Excel right now, its good to watch your videos and know what can be done, so in the future if I have a problem, I can remember a solution is there. You are so clever!
My goodness, Leila. I have watched video after video of yours. You are the best. Your explanations are precise, clear, down-to-earth and complete. On a lighter note, I am particularly impressed on how you pronounce your "t"s. Terrific.
Thanks for creating a Power Query tutorial and I'm really looking forward to those to come. You are very gifted at explaining complex excel tasks in a simplified way.
I have been waiting for months for you to start a power query series and each week I would check hoping one was uploaded.
You can imagine my delight when I saw this this morning... :)
Many thanks for the kind feedback. Now I have no choice but to make more PQ videos :)
The one with replacing values is an awesome tip! Thank you so much Leila!
You are my favorite excel teacher. Thanks so much for this excellent video.
Wow. This saves multiple hours of work. Learning with Leila is awesome.
Thanks, It was the first time that I know whatever you teach. But I have learned alot from you and eagerly waiting for your new training.
Thanksss
Leila - this video was so very helpful! I have learned so much watching your Excel videos these past few years. You are an outstanding instructor and your Excel skills are amazing! Thank you so very much for all you do!
Thank you very much for the kind feedback Douglas!
This is exactly what I needed. Needed this like 3 months ago haha. Thank you so much Leila! Love your content.
Your videos are precise and easy to follow. I have spent countless hours copy pasting data to transform the data from columns to rows and you just showed how to do it in less than a minute. Amazing!! Thank you for sharing your knowledge.
Glad to help!
this woman has 1.7 million subscribers for putting up excel-to-do videos on UA-cam!Legend
I've been struggling for days & you actually helped me - thanks so much!
I had been looking for this solution for hours today. All thanks to your help I have finished my task
Your teaching helps me save tons of time on my project. Thank you so much.
Yep, this I have been doing from many days!!
Thanks
I love you Leila. You make excel look so simple. Just amazing
Fantastic, clear and straight forward - just what I was looking for - Thanks :-)
Amazing, I was not even sure what to search for with this issue and yet you exactly answered my question.
This video saved me hours and hours of work. Thank you!
Truly truly grateful for this! Saved me so much time and grief!
Very well explained. Thank you for making this video!
MIND BLOWN!!! thank you! I spent like an hour trying to figure a plan of attack.
Thank you Leila!!! Was very informative and just what I needed 👍
At first it was out of my league but I rewatched it today and it all makes sense now!
Exactly what I needed, very helpful. Thanks a lot!
You are beyond amazing! What a great explanation. Thank you so so much.
Yup,
This is what i was asking you for
This how we enjoy
Happy Thursday your highness
We definitely do :)
In simple words, you are genius.
Love your video content. Can't say that I would use all the techniques that you show but as a video library to help trouble shoot when you are stuck for a solution you are a master. As always thanks.
Thank you!
thank you so much! this is what i was looking for last 1 year :D
That was exactly what I needed. Thank you for explaining it in such a clear and concise way.
You're very welcome, Lisa!
Great stuff Leila...many thanks..have a nice weekend ahead...cheers!
EXCELLENT video, exactly what i needed! Many thanks.
Leila! you rock! you took the teaching to it's higher level!
Thanks for the kind feedback!
You are the best! Thank you for your videos it made my Forecast reviews way easier to update.
Thank you! This was exactly what I was looking for. Very useful.
Ms. Leila you're awesome. Your style of teaching is superb. Thank you
Glad you like it :)
Astounding, I could not figure this one out. Many thanks Leila I started to recommend your guides to all of my colleagues who want to improve their excel skills.
Wonderful! Thanks for your support.
Brilliant as usual Leila! Thanks for sharing :)
excellent video, thanks a lot Leila, easy and done! nice technique, good teacher!
You truly are the excel Queen!!!!
Master! Wishing you health and happiness
Just what I was looking for, you saved me hours of work
This really was phenomenal -- so well explained. Thank you.
My pleasure Andrew. Glad you like it.
This helped me understand the data very easily. Thank you so much
Many many many thanks. This was exactly what I needed!
Thank you so much! This was very helpful. I subscribed!
Amazing, and, ohhhh, I would have loved this 20 (ok, 35) years ago. I used this tonight to help a PhD candidate better organize her qualitative data. (I also used a similar technique Leila describes in another video.) Thank you, Leila!
Glad it was helpful!
What a star you are!
Thanks Leila, you saved me a lot of time.
Leila thanks so much you just made my work so easy what was taking me accouple of minutes now take like two minutes you are the best
Love your channel. So so so useful!!! This saved my afternoon :)
Learned a lot from you. You are thr best many thanks. God bless
You did it again! Amazing how you save time for me with 5min Videos. 1000 Thanks!
That's great Roger! I'm really happy the tutorials are helpful.
This video has solved my long pending issue as I wasn’t aware of power query. Thanks Leila
Glad to help!
It's just awesome, this is what exactly I was looking for from quite a long time. I fell in love with the way you explain the tutorial... Long Living Leila, Almighty God Bless you all the time... Ameen
You are most welcome
Thank you, simple and easy to follow.
Really it helped. I was doing manually Copy and Paste Special. Columns to Rows. Daily I used do it. This tutorial helped a lot. With in no time, I am able to complete it comfortably. Thanks you Soooooooooooo much Leila Gharani
Great to hear!
Wow!!! As usual you give us really useful information in an easy way. Thank you. I bought your VBA tutorial and it's great.
I'm glad it's useful. Many thanks for your support.
The wonderful queen of Excel. Thank you for your tips.
You're very welcome!
Literally had this problem yesterday, came in to work today and your solution is in my subscription feed. I used some formula I found online but my sheet went from 2,000kb to over 20,000kb. With this it's only just over 3,000kb and so much easier!
THAT'S GREAT! I'm so happy to heat that. Well Done! You did the searching, finding and implementing.
👍
Always appreciate your work and skills.
You make it look so simple...,
Thanks.
Short video and straight forward, very good job
Leila always saving my day!! Thanks!
I love you Leila, u saved a lot my time
I LOVE YOU!!!! Wow, you saved hours of work!!!!!
Thanks Leila. This was an awesome learning and very useful
I was trying to do exactly that thing just today. Wow! Talk about serendipity!!
Excellent. Glad it came out right on time then.
Thanks a lot! Your tutorial are easy to understand and straight to the point.
My pleasure. Glad you like it.
Where have you been all of my life? LOL I can't tell you how happy I am I found this
Haha, I'm glad you found the channel too :)
Very helpful video. Wanted to know how to do this for years!
Thank you very much Gharani.
Another one that I can use immediately! Thank you for sharing!
That's great to hear!
Brilliant advise. Thanks so much
its really make my life easier.. thank u for your videos. Really appreciate it 💝
Thank you so much. I was looking for a solution other than copy and paste. This has saved me tons of time and frustration. Looking forward to learn more.
Great to hear!
I love your videos. They save me so much time!
Glad you like them!
VERY HELPFUL!!!! Thank you!
Thanks a lot!! You resolve several problems!!!
I use formulas to get this result, but now I can do my task much more faster. Thank you Leila!
My pleasure Gabriella. Faster is good :)
Nice one! This helped me doing my task faster.👍
Thank you sooo much! super helpful!
Aaammmaaazzziiiinnnggg... 😎 That was the thing i was looking for two months. Hats off
Great! I'm glad the video is helpful for you.
Very clear and helpful!
An excellent technique I learned, saved my time and got error-free results. Thanks.
Glad it helped!
Finally power query 😍
I like it alot