Convert Columns to Rows in Excel (NO Transpose, NO Formulas - SIMPLY UNPIVOT in Power Query)

Join 400,000+ professionals in our courses here 👉 link.xelplus.c...
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....
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.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
🎥 RESOURCES I recommend: www.xelplus.co...
🚩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

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/unpivot-columns-to-rows-file

[@jameszimmerman4695]

You posted this video 2 years ago, but it's exactly what I need now to unpivot a ridiculously large data set. Thanks!

[@juanfeliperubio137]

You just saved three hours of my life. Please continue with this high-quality content!

[@vhanbarcelon4517]

awesome💕

[@seaneager4147]

Seriously!!

[@MrSupernova111]

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!!

[@omut4981]

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.

[@andreriley739]

I quite literally bought your VBA course to learn how to code this exact thing yet you explained it in 5 mins. You rock!

[@LeilaGharani]

I hope the course is still useful for you Andre.

[@shadrackawunyo7786]

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

[@KrishnaKumar-zn9kg]

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!

[@jesuseduardobittersuarez7517]

ive been doing it in excel with one table, this is like magic I LOVE IT!

[@kliving1210]

OMG! I have spent 3 days trying to get information in this exact format. You are a life saver with these videos.

[@1lynton128]

Leila, You are an Excel genius and your teaching is as good as your knowledge. A very rare combination. You deserve your success. Thankyou.

[@swiyadesarkar6958]

I had been looking for this solution for hours today. All thanks to your help I have finished my task

[@dearshiva]

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.

[@LeilaGharani]

Glad to help!

[@ssgLunchbox]

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!

[@LeilaGharani]

THAT'S GREAT! I'm so happy to heat that. Well Done! You did the searching, finding and implementing.

[@avswebmail7273]

Thanks!

[@brbball]

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!

[@LeilaGharani]

Glad it was helpful!

[@RaviKumar-gz9my]

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

[@LeilaGharani]

Great to hear!

[@dennisharris144]

Where have you been all of my life? LOL I can't tell you how happy I am I found this

[@LeilaGharani]

Haha, I'm glad you found the channel too :)

[@WESTPRINGLE]

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!

[@LIV-FREE-VET]

This is exactly what I needed. Needed this like 3 months ago haha. Thank you so much Leila! Love your content.

[@davie9816]

I love you Leila. You make excel look so simple. Just amazing

[@Wambuto]

this woman has 1.7 million subscribers for putting up excel-to-do videos on KZbin!Legend

[@tyrymanuel]

Wow. This saves multiple hours of work. Learning with Leila is awesome.

[@biankaolivier2330]

I've been struggling for days & you actually helped me - thanks so much!

[@rayvela9298]

¡Gracias!

[@LeilaGharani]

Thank you, Ray!

[@elactecnology]

The wonderful queen of Excel. Thank you for your tips.

[@LeilaGharani]

You're very welcome!

[@zakirhossain1051]

Leila! you rock! you took the teaching to it's higher level!

[@LeilaGharani]

Thanks for the kind feedback!

[@paolosoloperto4948]

congratulations Leila for the video .... you simplify my life. 👍

[@LeilaGharani]

Happy to help, Paolo!

[@mamunabdullahywna]

This video has solved my long pending issue as I wasn’t aware of power query. Thanks Leila

[@LeilaGharani]

Glad to help!

[@Virginianification]

Ms. Leila you're awesome. Your style of teaching is superb. Thank you

[@LeilaGharani]

Glad you like it :)

[@safiyyahsujee8227]

I’m in shock right now. I’ve been struggling forever and thought there was no solution. Extremely helpful 🎉thank you !

[@LeilaGharani]

Happy to help!

[@NaraTunes]

Thank you sooo much! super helpful!

[@prakashv4683]

Excellent Explanation, Actually i was looking for this for a while... Thank you!

[@LeilaGharani]

Our pleasure :)

[@mohammedsharaf5609]

Leila, i just can't thank u enough. This really really helped me a lot ❤❤❤

[@LeilaGharani]

You're welcome 😊

[@amudhanar1303]

I was looking for this solution for so long.... Thank u so much🙏

[@martinmana6157]

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.

[@LeilaGharani]

Wonderful! Thanks for your support.

[@MacrinSmith]

This helped me understand the data very easily. Thank you so much

[@carlospena1104]

This video saved me hours and hours of work. Thank you!

[@janerook7851]

Brilliant - just what I was looking for. Thank you. Jane UK

[@polloloci21]

MIND BLOWN!!! thank you! I spent like an hour trying to figure a plan of attack.

[@techsuplk2340]

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.

[@bobbygsmith8339]

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... :)

[@LeilaGharani]

Many thanks for the kind feedback. Now I have no choice but to make more PQ videos :)

[@scarexmusic5832]

Great! You save both my life and my time. I love you. Liked, commented and shared and clicked belled icon. (which i never do to any youtuber) but you made me do it. Thank you very much

[@shrs.3448]

Your teaching helps me save tons of time on my project. Thank you so much.

[@albertooteo985]

You just made a whole project easy! always can count on you. What monster can give a thumbs down?

[@LeilaGharani]

Glad it was helpful Alberto.

[@albertooteo985]

@@LeilaGharani , could I just ask you, what is the best way to deal with this when changing (adding or deleting) columns in the source table is going to be frequent? I get the Expression.error message about the column not being there. Can I bypass this? Thanks!

[@ronvds4713]

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.

[@LeilaGharani]

Thank you!

[@christovandermerwe2978]

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.

[@LeilaGharani]

Great to hear!

[@charlesallen8023]

This is an incredible time-saver. I frequently get sets of data that are structured like this and have been manually copying and pasting from each month into a single column. It's not hard but I'll never do it again.

[@LeilaGharani]

Glad it's helpful Charles.

[@ibnutridam6394]

wow, you just saved me from days of overtime. this information is very useful ❤

[@harjotbraich9348]

Just what I was looking for, you saved me hours of work

[@pvalenc1]

I love your videos. They save me so much time!

[@LeilaGharani]

Glad you like them!

[@arjanvonk786]

Amazing, I was not even sure what to search for with this issue and yet you exactly answered my question.

[@gabriellaprievara7379]

I use formulas to get this result, but now I can do my task much more faster. Thank you Leila!

[@LeilaGharani]

My pleasure Gabriella. Faster is good :)

[@denverfox6950]

You just saved me hours and hours of work!!!

[@LeilaGharani]

Happy to help!

[@elisasoto1]

Love your channel. So so so useful!!! This saved my afternoon :)

[@nabiledelbi5502]

Wohooo! I tortured my brain since weeks to figure out a solution for this myself, without success. This video makes me so happy 🙂

[@LeilaGharani]

Happy to hear it was helpful!

[@Natpessoa100]

You are the best! Thank you for your videos it made my Forecast reviews way easier to update.

[@CYTVim]

EXCELLENT video, exactly what i needed! Many thanks.

[@MrOgondi]

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

[@paulseebach5978]

I recently learned how to do this another way, but It looks like this way could be better. I'm going to have to learn this and see. Thank you Leila.

[@LeilaGharani]

I'd be interested in your final verdict Paul.

[@naveagar]

An excellent technique I learned, saved my time and got error-free results. Thanks.

[@LeilaGharani]

Glad it helped!

[@icic8389]

Thank you! This was exactly what I was looking for. Very useful.

[@Irfan120]

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

[@LeilaGharani]

You are most welcome

[@DenisonPradeep3392]

Thanks Leila, you saved me a lot of time.

[@sajju1985]

Aaammmaaazzziiiinnnggg... 😎 That was the thing i was looking for two months. Hats off

[@LeilaGharani]

Great! I'm glad the video is helpful for you.

[@michaelmorley7145]

You just rescued my spreadsheet! Thank you!

[@LeilaGharani]

Happy to help!

[@AndrewFroude]

Awesome Leila. As I use a Mac these features are not available in Excel, however luckily I run Parallels as well and have been able to install and run Excel in Windows to access and utilise this functionality. Thank you!

[@LeilaGharani]

Maybe it'll be available on Office for Mac at some point. At least the first steps have been taken.

[@petercompton538]

I was trying to do exactly that thing just today. Wow! Talk about serendipity!!

[@LeilaGharani]

Excellent. Glad it came out right on time then.

[@ehab5986]

In simple words, you are genius.

[@mrubas8826]

Truly truly grateful for this! Saved me so much time and grief!

[@bernertkb]

Exactly what I needed, very helpful. Thanks a lot!

[@ananthakumar1937]

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..

[@johnnykhoury7844]

Short video and straight forward, very good job

[@pradhanbalter3796]

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.

[@CaknaEmas]

its really make my life easier.. thank u for your videos. Really appreciate it 💝

[@chelitoph]

I LOVE YOU!!!! Wow, you saved hours of work!!!!!

[@RM-hq6zb]

What a star you are!

[@metingozcu1]

thank you so much! this is what i was looking for last 1 year :D

[@farahshahera3865]

Thanks a lot! Your tutorial are easy to understand and straight to the point.

[@LeilaGharani]

My pleasure. Glad you like it.

[@RogerStocker]

You did it again! Amazing how you save time for me with 5min Videos. 1000 Thanks!

[@LeilaGharani]

That's great Roger! I'm really happy the tutorials are helpful.

[@sujitjadhav2396]

You are my favorite excel teacher. Thanks so much for this excellent video.

[@nickkaskarelis8995]

More videos with power query please. Keep up the great job that you are doing your tutorials are excellent.

[@LeilaGharani]

There will be more Nick. Stay tuned :)

[@jenninenorthover8293]

and just like that... Subscribed! 😄 amazing ... you are an angel and the saviour of my sanity 😇

[@sarfarajkureshi6569]

I am searching this technique for transpose data, you share with us very simply and useful also, thank you mam

[@LeilaGharani]

I'm glad you find the tutorial helpful.

[@vijaygusain119]

At first it was out of my league but I rewatched it today and it all makes sense now!

[@khalidrehman5045]

Hi LG, that was quick and easy. Amazing solution. Thumbs up 👍

[@LeilaGharani]

Thanks for the thumbs up Khalid.

[@chimo2dax]

You are a Life and Time Saver thank you for the video! Exactly what I was looking for!!

[@LeilaGharani]

Glad I could help!

[@thomasw.johansen5257]

Many many many thanks. This was exactly what I needed!

[@TeamJB865]

I’ve got 7 different spreadsheets for my 7 markets where the column header is by day. The data goes back two years and I’ve got to convert it from matrix to column. I’m trying this today!

[@MsYblad]

OMG! That was SO usefull! You are undoubtedly my no. 1 excel hero ;)

[@LeilaGharani]

Wow, thanks Katrine!

[@GeorgesLacombe]

Leila always saving my day!! Thanks!

[@faridkamarul6444]

I love you Leila, u saved a lot my time

[@lisasheehy8500]

That was exactly what I needed. Thank you for explaining it in such a clear and concise way.

[@LeilaGharani]

You're very welcome, Lisa!

[@JadeDragon407]

That's pretty wild! Anytime I need to do something similar, I bail from Excel and run all that through Access and queries there. I've had various times I would have LIKED to've had data in Excel for more freedom of visual look, but saw no good way to do it. So cool!

[@abdallah.kandiel]

Yup, This is what i was asking you for This how we enjoy Happy Thursday your highness

[@LeilaGharani]

We definitely do :)

[@kshitijjain4852]

YOU HAVE SAVED MY LIFE!!! THANKS

[@LeilaGharani]

Happy to help :)

[@sevenhenson3926]

U r a life saver in many ways

[@HariKrishna-gi5un]

im from india. love your teaching style

[@winniewinnie6358]

I stil think of how to use function as my raw data involve various brch until view this video, it help and save lot of times. Thank you 😊

[@wqpro2012]

Very helpful video. Wanted to know how to do this for years!

[@deus2281]

Nice one! This helped me doing my task faster.👍