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

preview_player
Показать описание

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.

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:

🚩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
Рекомендации по теме
Комментарии
Автор

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

juanfeliperubio
Автор

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

jameszimmerman
Автор

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

MrSupernova
Автор

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

shadrackawunyo
Автор

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.

omut
Автор

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

andreriley
Автор

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

plzmailveera
Автор

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

Wambuto
Автор

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

kliving
Автор

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!

WESTPRINGLE
Автор

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!

KrishnaKumar-znkg
Автор

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.

dearshiva
Автор

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

lynton
Автор

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

swiyadesarkar
Автор

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 much Leila Gharani

RaviKumar-gzmy
Автор

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!

brbball
Автор

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

nabiledelbi
Автор

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!

ssgLunchbox
Автор

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.

christovandermerwe
Автор

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

naveagar