Convert crosstab data to tabular - Excel | Power BI | Power Query

[@kamalakarpatil4716]

Thanks!

[@efficiency365]

Oh Wow. Thanks Kamalakar.
Do suggest topics for future videos.

[@Lyriks_]

This man is a gift to humanity (at least as far as business is concerned)

[@efficiency365]

Thanks @Lyriks_ 🙂🙏🏼
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - ruclips.net/video/rtDlORLmjlE/видео.html
Excel Green Marks - Error Checking - Best Practices - ruclips.net/video/XeLtlzd9lRY/видео.html
Excel Best Practices - Part 1 of 3 - Data Management - ruclips.net/video/rr_1ha6g6lc/видео.html
Excel Best Practices - Part 2 of 3 - Formulas - ruclips.net/video/kz_zAvMINAk/видео.html
Excel Best Practices - Part 3 of 3 - Analytics - ruclips.net/video/JNR5yx_Pg4A/видео.html
10 Excel Settings You Must CHANGE! - ruclips.net/video/vXrrXdKyJFk/видео.html
Automatic data clean up with Excel Flash Fill - ruclips.net/video/N3p_x_lXT_c/видео.html
Instant Excel Audit, Comparison and Analysis - Inquire - ruclips.net/video/cDdvUZxOUis/видео.html
Six powerful Excel Navigation Shortcuts - ruclips.net/video/bR-yMbGPq50/видео.html
Handle millions of rows in Excel - Slow to fast - ruclips.net/video/93h7rRsLF7Y/видео.html
Convert crosstab to tabular - Unpivot - Excel Power Query - ruclips.net/video/mTtOtZH-nNM/видео.html
Cheers.
Doc

[@sergiocely-10]

Thanks, your explanation is veery good.

[@efficiency365]

Thanks @sergiocely-10
Check out my other best practices videos as well.
Efficient Tasks Management - ruclips.net/video/vLFpWVfUfQ8/видео.html
Teams Meeting - Agenda, Action Points, Notes - ruclips.net/video/k0t8_mNkMDw/видео.html
Microsoft Teams - 15 Best Practices - ruclips.net/video/W8Ufx_znKxI/видео.html
OneDrive Best Practices Part 1 - ruclips.net/video/D7ZrfphW4vo/видео.html Part 2 - ruclips.net/video/nFx6YQxc-b4/видео.html
Smart and Effective Email - 5 powerful ways - ruclips.net/video/6NEFSMqHgQE/видео.html
Outlook Calendar Best Practices : Part 1 - ruclips.net/video/GzsQnecjAQo/видео.html and Part 2 - ruclips.net/video/padKH8ys5Cs/видео.html
OneNote - Best Practices - ruclips.net/video/m-4AY1cMi8s/видео.html
Microsoft 365 Best Practices - ruclips.net/video/kVC_YcL5ObU/видео.html
Cheers. Doc.

[@rein6873]

Thank you so much, this saves much time

[@efficiency365]

Thanks @rein6873
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - ruclips.net/video/rtDlORLmjlE/видео.html
Excel Green Marks - Error Checking - Best Practices - ruclips.net/video/XeLtlzd9lRY/видео.html
Excel Best Practices - Part 1 of 3 - Data Management - ruclips.net/video/rr_1ha6g6lc/видео.html
Excel Best Practices - Part 2 of 3 - Formulas - ruclips.net/video/kz_zAvMINAk/видео.html
Excel Best Practices - Part 3 of 3 - Analytics - ruclips.net/video/JNR5yx_Pg4A/видео.html
10 Excel Settings You Must CHANGE! - ruclips.net/video/vXrrXdKyJFk/видео.html
Automatic data clean up with Excel Flash Fill - ruclips.net/video/N3p_x_lXT_c/видео.html
Instant Excel Audit, Comparison and Analysis - Inquire - ruclips.net/video/cDdvUZxOUis/видео.html
Six powerful Excel Navigation Shortcuts - ruclips.net/video/bR-yMbGPq50/видео.html
Handle millions of rows in Excel - Slow to fast - ruclips.net/video/93h7rRsLF7Y/видео.html
Convert crosstab to tabular - Unpivot - Excel Power Query - ruclips.net/video/mTtOtZH-nNM/видео.html
Cheers.
Doc

[@LotfyKozman]

Very practical. Thnx

[@efficiency365]

Thanks Lotfy Kozman for the feedback.
Check out my recent videos
Modern tooltips in Power BI - ruclips.net/video/4ApEKBtdfXU/видео.html
Data Model in Excel and Power BI - ruclips.net/video/93h7rRsLF7Y/видео.html
Explore more Excel, Power BI and Analytics videos on this channel
ruclips.net/p/PL-UPeBWRvha2O2TF6vfVw4lfS4honS407
Check out my My Udemy courses
Excel to Power BI s.efficiency365.com/upbi
Pivot Tables s.efficiency365.com/upt
Learn 130 essential efficiency best practices - read my latest book - s.efficiency365.com/bk
Let me know if you want me to cover any specific topics as well.
Cheers. Dr Nitin (doc) Paranjape.

[@ThePaskatHousussa]

Very useful, thank you!

[@efficiency365]

Thanks Antoni Kyejo for the feedback.
You may also like my Power BI Tooltips video ruclips.net/video/4ApEKBtdfXU/видео.html
Explore more Excel, Power BI and Analytics videos on this channel ruclips.net/p/PL-UPeBWRvha2O2TF6vfVw4lfS4honS407
Check out my My Udemy courses
Excel to Power BI s.efficiency365.com/upbi
Pivot Tables s.efficiency365.com/upt
Learn 130 essential efficiency best practices - read my latest book - s.efficiency365.com/bk
Let me know if you want me to cover any specific topics as well.
Cheers. Dr Nitin (doc) Paranjape.

[@GaurangBhatt9]

Awesome!

[@efficiency365]

Thanks. ETL does not get much visibility. Everyone is running behind visualizations, dashboards and infographics. That is why I am covering these baseline topics.
There are entire departments doing such kind of manual clean up jobs - that too using inefficient processes, oldest versions of Excel and slowest possible hardware.

[@AmbreManish]

Thanks a lot Nitin sir, your simple and to the point explanation is indeed a boon for excel users!

[@efficiency365]

Thanks Manish Ambre for the feedback.
Explore more Excel, Power BI and Analytics videos on this channel ruclips.net/p/PL-UPeBWRvha2O2TF6vfVw4lfS4honS407
Check out my online courses
Excel to Power BI s.efficiency365.com/upbi
Pivot Tables s.efficiency365.com/mtyt
Learn 130 essential efficiency best practices - read my latest book - s.efficiency365.com/bk
Let me know if you want me to cover any specific topics as well.
Cheers. Dr Nitin (doc) Paranjape.

[@shwetkumar1719]

Thank you Sir

[@efficiency365]

Thanks. More stuff coming soon.

[@dhirajkundu]

Very much useful

[@efficiency365]

Thanks Dhiraj Kundu for the feedback.
Also watch the Data Model video: ruclips.net/video/93h7rRsLF7Y/видео.html
Explore more Excel, Power BI and Analytics videos on this channel ruclips.net/p/PL-UPeBWRvha2O2TF6vfVw4lfS4honS407
Check out my My Udemy courses
Excel to Power BI s.efficiency365.com/upbi
Pivot Tables s.efficiency365.com/upt
Learn 130 essential efficiency best practices - read my latest book - s.efficiency365.com/bk
Let me know if you want me to cover any specific topics as well.
Cheers. Dr Nitin (doc) Paranjape.

[@vijayarjunwadkar]

Very nice video! Thanks for sharing the super ideas of converting cross-tab data. Happy New Year!

[@efficiency365]

Yes. People are still struggling with manual / macro ways of doing this.

[@TinyMagiq]

This is absolutely brilliant especially the last two. You rock.

[@efficiency365]

Thanks Tiny Magiq for the feedback.
You may also like my video on Power BI Tooltips ruclips.net/video/4ApEKBtdfXU/видео.html
and Excel Data Model ruclips.net/video/93h7rRsLF7Y/видео.html
Explore more Excel, Power BI and Analytics videos on this channel ruclips.net/p/PL-UPeBWRvha2O2TF6vfVw4lfS4honS407
Check out my My Udemy courses
Excel to Power BI s.efficiency365.com/upbi
Pivot Tables s.efficiency365.com/upt
Learn 130 essential efficiency best practices - read my latest book - s.efficiency365.com/bk
Let me know if you want me to cover any specific topics as well.
Cheers. Dr Nitin (doc) Paranjape.

[@mouadbelfakir2978]

Thank you very very much. Very good video and explanation. Very well structured. You just gained yourself a subscriber

[@efficiency365]

Thanks @mouadbelfakir2978.
Check out my other best practices videos as well.
Efficient Tasks Management - ruclips.net/video/vLFpWVfUfQ8/видео.html
Microsoft Teams - 15 Best Practices - ruclips.net/video/W8Ufx_znKxI/видео.html
Smart and Effective Email - 5 powerful ways - ruclips.net/video/6NEFSMqHgQE/видео.html
Outlook Calendar Best Practices : Part 1 - ruclips.net/video/GzsQnecjAQo/видео.html and Part 2 - ruclips.net/video/padKH8ys5Cs/видео.html
OneNote - Best Practices - ruclips.net/video/m-4AY1cMi8s/видео.html
Microsoft 365 Best Practices - ruclips.net/video/kVC_YcL5ObU/видео.html
Word Tables Best Practices - The ultimate guide - ruclips.net/video/ueKCHxNUOkA/видео.html
Cheers. Doc.

[@rianlynass3707]

Thanks for this Dr Nitin. I'm trying to convert multiple crosstabs that have multiple headers. I don't suppose you have a tutorial for this? The headers in the cross tabs have different dates so I cant delete duplicate rows

[@efficiency365]

In this case, as each crosstab is different, you will need to do this in two steps. Step 1 - make all cross tabs similar in format. This will have to be done one-by-one.
Step 2 - consolidate cross tabs.

[@rianlynass3707]

@@efficiency365 Perfect, thank you!

[@arfanrasheed9941]

Excellent video, you have covered almost everything but in my case, i have attendance of students as lecture, practical and tutorial also in columns have students name and roll nos. If I go 1 step further there are multiple departments who deals with the students, so I have to make a summary to students of a class with different types of attendance (as mentioned above) and with multiple subjects, and i have to repeat it after two or 3 months so will you help me to solve this? *Note, I have already subscribe your channel 😊

[@efficiency365]

Thanks @arfanrasheed9941
It is the same concept as shown in the video. Just more iterations required to handle multiple crosstabs.
Try it out step by step - convert each individual piece of data into tabular.
Finally you will have columns like Student ID / Name, Date, Department, Subject and Category.
Now you can analyze it easily.
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - ruclips.net/video/rtDlORLmjlE/видео.html
Excel Green Marks - Error Checking - Best Practices - ruclips.net/video/XeLtlzd9lRY/видео.html
Excel Best Practices - Part 1 of 3 - Data Management - ruclips.net/video/rr_1ha6g6lc/видео.html
Excel Best Practices - Part 2 of 3 - Formulas - ruclips.net/video/kz_zAvMINAk/видео.html
Excel Best Practices - Part 3 of 3 - Analytics - ruclips.net/video/JNR5yx_Pg4A/видео.html
10 Excel Settings You Must CHANGE! - ruclips.net/video/vXrrXdKyJFk/видео.html
Automatic data clean up with Excel Flash Fill - ruclips.net/video/N3p_x_lXT_c/видео.html
Instant Excel Audit, Comparison and Analysis - Inquire - ruclips.net/video/cDdvUZxOUis/видео.html
Six powerful Excel Navigation Shortcuts - ruclips.net/video/bR-yMbGPq50/видео.html
Handle millions of rows in Excel - Slow to fast - ruclips.net/video/93h7rRsLF7Y/видео.html
Convert crosstab to tabular - Unpivot - Excel Power Query - ruclips.net/video/mTtOtZH-nNM/видео.html
Cheers.
Doc

[@vikrantpawar]

Can someone suggest how to get `Pivot table Wizard` on Mac with Office Excel for mac 16

[@efficiency365]

Go to VBA in Mac.
In the immediate window type this... (I have not tested this, but it should work).
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
Array("'Multiple Ranges'!R1C1:R6C7"), Version:=6).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", DefaultVersion:=6
If this works, just add it as a SUB and map it to a Quick Access Toolbar button and a keyboard shortcut.

[@stuartavvampato]

ily

[@efficiency365]

Thanks Stuart Avvampato
You may also like these related videos:
Is efficiency dangerous? ruclips.net/video/Tp3j-UEcxLA/видео.html
The Efficient Mindset. NOT Basic or Advanced - ruclips.net/video/_kpTgeBKUck/видео.html
How to manage ignorance? Learn to learn - ruclips.net/video/RsSJDVNC-Eo/видео.html
Don't buy Microsoft Office 365 - unless Podcast - ruclips.net/video/zbwQUKrJ_pg/видео.html
Do let me know your feedback.
Cheers
Doc