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

[@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

[@Wambuto]

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

[@kliving1210]

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

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

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

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

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

[@MacrinSmith]

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

[@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

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

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

[@plzmailveera]

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

[@christopherchristiawan7787]

Agree

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

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

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

[@tyrymanuel]

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

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

[@polloloci21]

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

[@janerook7851]

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

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

[@biankaolivier2330]

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

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

[@LIV-FREE-VET]

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

[@Natpessoa100]

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

[@shrs.3448]

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

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

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

[@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 :)

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

[@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 :)

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

[@paolosoloperto4948]

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

[@LeilaGharani]

Happy to help, Paolo!

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

[@davie9816]

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

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

[@jenninenorthover8293]

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

[@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 😊

[@uBigDosser]

If you're in the energy industry, working with intervals, this video is especially useful for you!

[@sujitjadhav2396]

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

[@naveagar]

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

[@LeilaGharani]

Glad it helped!

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

[@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

[@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

[@elactecnology]

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

[@LeilaGharani]

You're very welcome!

[@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 :)

[@HariKrishna-gi5un]

im from india. love your teaching style

[@DenisonPradeep3392]

Thanks Leila, you saved me a lot of time.

[@arjanvonk786]

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

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

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

[@vijaygusain119]

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

[@ramon9204]

WOW amazing video. I am looking for this solution for weaks... Muchas gracias, saludos desde México

[@graemegourlay2850]

Fantastic. This solves a big problem for me with data output from a survey tool.

[@LeilaGharani]

Glad it's helpful.

[@CaknaEmas]

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

[@carlospena1104]

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

[@rachelleigh128]

This is fantastic. I have been watching videos all day trying to figure this out & with yours it was very simple! Question: what file type should I save this file as? Does that matter?

[@zakirhossain1051]

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

[@LeilaGharani]

Thanks for the kind feedback!

[@denverfox6950]

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

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

[@SonuPandey-me4fs]

Thanks a lot to post this video . I am deliberately looking for this trick in power pivot.

[@mrubas8826]

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

[@abdallah.kandiel]

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

[@LeilaGharani]

We definitely do :)

[@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 :)

[@michaelmorley7145]

You just rescued my spreadsheet! Thank you!

[@LeilaGharani]

Happy to help!

[@douglasbrown5329]

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!

[@LeilaGharani]

Thank you very much for the kind feedback Douglas!

[@emre318]

Omg. Microsoft Forms exactly gives survey results in first table's form. For transition of that table's form to like this, I've been writing lines of vba code with assigning 4 different variables. This method will be life - saver.

[@LeilaGharani]

Glad it's helpful for you.

[@helenmeleshagosmeles3835]

emre318 E.mail: [e-mail]

[@emre318]

@@helenmeleshagosmeles3835 and what do I suppose to do with this mail?

[@pvalenc1]

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

[@LeilaGharani]

Glad you like them!

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

[@richardwallis3737]

You changed my life...

[@mohammedsharaf5609]

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

[@LeilaGharani]

You're welcome 😊

[@bernertkb]

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

[@thomasw.johansen5257]

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

[@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 :)

[@msc_nkosi]

Thank you, simple and easy to follow.

[@farahshahera3865]

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

[@LeilaGharani]

My pleasure. Glad you like it.

[@harjotbraich9348]

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

[@daveys]

Just managed to do something in PQ that is incredibly valuable thanks to this. Ta!

[@LeilaGharani]

Great to hear, Dave!

[@kshitijjain4852]

YOU HAVE SAVED MY LIFE!!! THANKS

[@LeilaGharani]

Happy to help :)

[@faridkamarul6444]

I love you Leila, u saved a lot my time

[@elisasoto1]

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

[@wqpro2012]

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

[@johnnykhoury7844]

Short video and straight forward, very good job

[@FilmsLoz]

Holy smokes!! MASSIVE time saver for me!!! Just one note, this doesn't work on the Mac version of Excel as there is no way to Get Data from Table/Range.

[@KnuYorker]

Very well prepared example and an excellent presentation. I can use this today at work!

[@LeilaGharani]

That's great. I'm glad you can apply it directly at work.

[@deus2281]

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

[@icic8389]

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

[@evgeniam8882]

The one with replacing values is an awesome tip! Thank you so much Leila!

[@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

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

[@chelitoph]

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

[@andrewsconzo5863]

This really was phenomenal -- so well explained. Thank you.

[@LeilaGharani]

My pleasure Andrew. Glad you like it.

[@agussetyowicaksono3927]

very useful to me....thank you Leila Gharani....

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