Excel Unpivot Data with Multiple Headers (Multiple Row levels into Columns with Power Query)

Stop wasting time creating complex formulas to transpose data in columns with multiple headers to get proper tabular data. With Excel's Power Query you can easily create a dynamic solution to transform your report-style data into a dataset you can use to run Pivot Tables on or do further analysis on with formulas.


Unpivot means to create rows from columns. For example if you have a sales report and the months Jan - Dec are each reported in its own column. By Unpivoting we "shrink" this report with 12 columns for the months into a tabular format where all months are consolidated in one column and the data is shown in the rows. This way your data is properly structured for further analysis.

📝 Video Overview:
- Tackle the challenge of converting complex reports into tabular datasets.
- Learn to handle datasets with multiple headers and totals embedded within.
- Discover the dynamic solution to ensure automatic updates with new data additions.
- Get detailed guidance on using Power Query to unpivot and clean your data.

💡 Key Points:
- Simplify complex data for easy analysis and pivot table creation.
- Learn dynamic techniques to keep your data analysis up-to-date.
- Transform your approach to data manipulation in Excel with expert tips.

00:00 Purpose of UnPivot in Excel
02:27 Prepare data for Excel Power Query
04:44 Use Power Query Editor to Unpivot Data
10:06 Test Dynamic Solution when adding new data
11:26 Remove empty cells from report

🚩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

[ajitdb]

I had totally different Financial Ledger data in multiple row headers. Was breaking my head to manually change to tabular format, then came across your video. Followed your steps and yo!!...structured it beautifully in a simple tabular format. Thank you so much for so clear and concise instructions.

[duffygc]

I've watched hundreds of Excel videos over the years, and this is probably the best I've ever seen; the perfect balance of context, explanation, insight and detail (and exactly what I needed help with today). Thank you, Leila.

[excelisfun]

Power Query: the greatest tool since the PivotTable! Thanks, teammate : )

[JuanLuisZamora]

This is such a High Quality Tutorial, including the workbook, it is very satisfying to see how much love and effort you and your team put in doing this, some day it'll pay off.

[jamesfrelichowski]

After two days of googling for tips you gave me exactly what i needed! And I love your style of presentation the best. I needed to sum up data for each cultivar of plant across all the days of the year. This makes it easy to make rows of each data point because of all the blanks for most plants on most days and because further analysis do better without blanks taking up space and memory. Eventually i want to move it to an access table and it is setup this way and keep the informative columns to the left of the actual data. Other databases like GRIN-Global are this way also but original data gathering is still best with excel condensed worksheets. THANK YOU SO MUCH! I will look to subscribe.

[geoffreydeleplanque]

Leila you are insane, I followed many excel Chanel but yours is the most clear and simple to follow, I improved a lot myself with you. Thanks

[gregorytaylor]

I am halfway through your course and it has paid for itself in one day in time savings!

[HighHopesHizzy]

Leila, Thank you for posting these awesome videos. Your impressive teaching style is so easy to follow. I love Excel, but I'm mostly self taught, which can make it difficult to understand the lingo, etc., in certain tutorials as I troubleshoot. Your videos are so clear that I find myself watching them just for fun. Thank you for rekindling my love for Excel. Be well!

[chh]

Thursdays mornings with Leila is 'must-attend' classroom time … and this morning was another excellent presentation (no surprise) in terms of delivery and content … and a very timely topic for me. Thank you Professor Leila …

PS
A 'Pro' makes things look easy … and Leila is an 'All-Star' Pro … the Star of the 'Show' … she is my hero (heroine???) as I toil away in the minor leagues trying to get a little better each day … all her videos provide excellent training techniques … thank you … thank you … thank you.

[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 she describes in another video.) Thank you, Leila!

[anilbabbar]

Amazing, thank you. I have been struggling with Multiple Headers, it has given me a breath of fresh air. Thank you again.

[nadeemmalik]

Time spent with Leila learning new ways and techniques in excel is time well spent. Thank you Leila.

[SkysUp-yogc]

Great content! Thank you Leila for such a clear-cut video! Saved me A LOOOOT of time and multiple headaches. Big addition to my data cleaning know-hows

[SimohmedRaiss]

So wonderful. Thank you so much Leila. I used to do lots of copy/paste, with a high risk of errors, to get this result. I do as well, appreciate a lot the trick of the Split button. Very useful. 👍👍

[dannykurniawan]

Thank you SO MUCH, Leila 🙏 This tutorial has saved me from manually unpivoting Multinational Sales Data of 14 products with 3 levels of headers (Countries, Ports, Months).

[Jollofloff]

Thank you! Spent the entire day untangling a large table. This video made the same thing possible in 15 minutes!

[notAM]

maaaan... I could not ever imagine myself 15 years ago spending my leisure time exploring the all mighty EXCEL .. thanx for your amazing videos

[westielvr]

THANK YOU! This video saved my sanity! I am fairly new to Power Query and this was extremely easy to follow and understand!

[Oppadu]

You are the best, Always! Thank you for uploading great video ❤️

[felipe]

Excellent explanation Leila thank you!!!!

I wouldn't had eliminated the "null" at the end, I would've replaced them with "0" and keep the data in order to show changes on sales of the articles upon the time!