Thanks @KeshavSharma-yb9ke Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - ua-cam.com/video/rtDlORLmjlE/v-deo.html Excel Green Marks - Error Checking - Best Practices - ua-cam.com/video/XeLtlzd9lRY/v-deo.html Excel Best Practices - Part 1 of 3 - Data Management - ua-cam.com/video/rr_1ha6g6lc/v-deo.html Excel Best Practices - Part 2 of 3 - Formulas - ua-cam.com/video/kz_zAvMINAk/v-deo.html Excel Best Practices - Part 3 of 3 - Analytics - ua-cam.com/video/JNR5yx_Pg4A/v-deo.html 10 Excel Settings You Must CHANGE! - ua-cam.com/video/vXrrXdKyJFk/v-deo.html Automatic data clean up with Excel Flash Fill - ua-cam.com/video/N3p_x_lXT_c/v-deo.html Instant Excel Audit, Comparison and Analysis - Inquire - ua-cam.com/video/cDdvUZxOUis/v-deo.html Six powerful Excel Navigation Shortcuts - ua-cam.com/video/bR-yMbGPq50/v-deo.html Handle millions of rows in Excel - Slow to fast - ua-cam.com/video/93h7rRsLF7Y/v-deo.html Convert crosstab to tabular - Unpivot - Excel Power Query - ua-cam.com/video/mTtOtZH-nNM/v-deo.html Cheers. Doc
Thanks @waleadeniji6959 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - ua-cam.com/video/rtDlORLmjlE/v-deo.html Excel Green Marks - Error Checking - Best Practices - ua-cam.com/video/XeLtlzd9lRY/v-deo.html Excel Best Practices - Part 1 of 3 - Data Management - ua-cam.com/video/rr_1ha6g6lc/v-deo.html Excel Best Practices - Part 2 of 3 - Formulas - ua-cam.com/video/kz_zAvMINAk/v-deo.html Excel Best Practices - Part 3 of 3 - Analytics - ua-cam.com/video/JNR5yx_Pg4A/v-deo.html 10 Excel Settings You Must CHANGE! - ua-cam.com/video/vXrrXdKyJFk/v-deo.html Automatic data clean up with Excel Flash Fill - ua-cam.com/video/N3p_x_lXT_c/v-deo.html Instant Excel Audit, Comparison and Analysis - Inquire - ua-cam.com/video/cDdvUZxOUis/v-deo.html Six powerful Excel Navigation Shortcuts - ua-cam.com/video/bR-yMbGPq50/v-deo.html Handle millions of rows in Excel - Slow to fast - ua-cam.com/video/93h7rRsLF7Y/v-deo.html Convert crosstab to tabular - Unpivot - Excel Power Query - ua-cam.com/video/mTtOtZH-nNM/v-deo.html Cheers. Doc
Thanks @papayuyu1189 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - ua-cam.com/video/rtDlORLmjlE/v-deo.html Excel Green Marks - Error Checking - Best Practices - ua-cam.com/video/XeLtlzd9lRY/v-deo.html Excel Best Practices - Part 1 of 3 - Data Management - ua-cam.com/video/rr_1ha6g6lc/v-deo.html Excel Best Practices - Part 2 of 3 - Formulas - ua-cam.com/video/kz_zAvMINAk/v-deo.html Excel Best Practices - Part 3 of 3 - Analytics - ua-cam.com/video/JNR5yx_Pg4A/v-deo.html 10 Excel Settings You Must CHANGE! - ua-cam.com/video/vXrrXdKyJFk/v-deo.html Automatic data clean up with Excel Flash Fill - ua-cam.com/video/N3p_x_lXT_c/v-deo.html Instant Excel Audit, Comparison and Analysis - Inquire - ua-cam.com/video/cDdvUZxOUis/v-deo.html Six powerful Excel Navigation Shortcuts - ua-cam.com/video/bR-yMbGPq50/v-deo.html Handle millions of rows in Excel - Slow to fast - ua-cam.com/video/93h7rRsLF7Y/v-deo.html Convert crosstab to tabular - Unpivot - Excel Power Query - ua-cam.com/video/mTtOtZH-nNM/v-deo.html Cheers. Doc
Hi, great video, there is very little good information on this online, so thanks for providing it. A few questions: First, when there are multiple multiple-choice questions, why do you make all of them in seperate tables and not just keep splitting them in a single table? Else creating this many tables causes everything to slow down tremendously, especially if you have let's say 30 multiple-choice questions. Secondly, related to this, how would you go about doing this for very many multiple choice questions?
1. Why make separate table? The reason is that every time you split to rows, the number of rows multiplies. If you repeat this process for multiple columns, the number of rows expands exponentially. Even if you can manage the analysis with increased rows, filtering on individual answer item becomes difficult in UI. Lastly, creating separate tables creates a more optimal data structure which performs faster. (This is normalized design. Everything to rows is denormalized table) 2. How to make separate tables automatically? Conceptually, this is possible using some Power Query coding. I will research this and if there is a solution, I will publish it soon. Check out this video as well : ua-cam.com/video/sG7HHOjNfkY/v-deo.html
Thanks Matt. Watch Excel Custom Sorting Part 1 and 2 - you will like it: ua-cam.com/video/MSj9rb633BM/v-deo.html and ua-cam.com/video/2oReiB8kgTw/v-deo.html Cheers. Doc.
Thanks Roll TheRs. Check out my other best practices videos as well. Efficient Tasks Management - ua-cam.com/video/vLFpWVfUfQ8/v-deo.html Microsoft Teams - 15 Best Practices - ua-cam.com/video/W8Ufx_znKxI/v-deo.html Smart and Effective Email - 5 powerful ways - ua-cam.com/video/6NEFSMqHgQE/v-deo.html Outlook Calendar Best Practices : Part 1 - ua-cam.com/video/GzsQnecjAQo/v-deo.html and Part 2 - ua-cam.com/video/padKH8ys5Cs/v-deo.html OneNote - Best Practices - ua-cam.com/video/m-4AY1cMi8s/v-deo.html Microsoft 365 Best Practices - ua-cam.com/video/kVC_YcL5ObU/v-deo.html Word Tables Best Practices - The ultimate guide - ua-cam.com/video/ueKCHxNUOkA/v-deo.html Cheers. Doc.
@@efficiency365 The video was so useful I could immediately apply the concept in my data. Checking the spell error by removing the duplicates was profound.. Thank you very much !
As you learn more, you will realize that each feature has multiple use case. Just because you found the obvious (and originally intended) use case should not constrain you from thinking of more relevant scenarios.
Thanks @user-wf2pg3si5c Check out my other best practices videos as well. Efficient Tasks Management - ua-cam.com/video/vLFpWVfUfQ8/v-deo.html Task Apps Comparison: ua-cam.com/video/ViWJIzMPQZg/v-deo.html Teams Meeting - Agenda, Action Points, Notes - ua-cam.com/video/k0t8_mNkMDw/v-deo.html Microsoft Teams - 15 Best Practices - ua-cam.com/video/W8Ufx_znKxI/v-deo.html OneDrive Best Practices Part 1 - ua-cam.com/video/D7ZrfphW4vo/v-deo.html Part 2 - ua-cam.com/video/nFx6YQxc-b4/v-deo.html Smart and Effective Email - 5 powerful ways - ua-cam.com/video/6NEFSMqHgQE/v-deo.html Outlook Calendar Best Practices : Part 1 - ua-cam.com/video/GzsQnecjAQo/v-deo.html and Part 2 - ua-cam.com/video/padKH8ys5Cs/v-deo.html OneNote - Best Practices - ua-cam.com/video/m-4AY1cMi8s/v-deo.html Microsoft 365 Best Practices - ua-cam.com/video/kVC_YcL5ObU/v-deo.html Cheers. Doc.
Sir, I have a question. How to make yes no item/criteria into the list data as like as data in your thumbnail sir? I want some of tutorial, if this case can do by using query in excel. Thank you 🙏🏻
Thanks @holamala8691 If yes / no, only one answer at a time. It will work just fine. No need to split and unpivot. Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - ua-cam.com/video/rtDlORLmjlE/v-deo.html Excel Green Marks - Error Checking - Best Practices - ua-cam.com/video/XeLtlzd9lRY/v-deo.html Excel Best Practices - Part 1 of 3 - Data Management - ua-cam.com/video/rr_1ha6g6lc/v-deo.html Excel Best Practices - Part 2 of 3 - Formulas - ua-cam.com/video/kz_zAvMINAk/v-deo.html Excel Best Practices - Part 3 of 3 - Analytics - ua-cam.com/video/JNR5yx_Pg4A/v-deo.html 10 Excel Settings You Must CHANGE! - ua-cam.com/video/vXrrXdKyJFk/v-deo.html Automatic data clean up with Excel Flash Fill - ua-cam.com/video/N3p_x_lXT_c/v-deo.html Instant Excel Audit, Comparison and Analysis - Inquire - ua-cam.com/video/cDdvUZxOUis/v-deo.html Six powerful Excel Navigation Shortcuts - ua-cam.com/video/bR-yMbGPq50/v-deo.html Handle millions of rows in Excel - Slow to fast - ua-cam.com/video/93h7rRsLF7Y/v-deo.html Convert crosstab to tabular - Unpivot - Excel Power Query - ua-cam.com/video/mTtOtZH-nNM/v-deo.html Cheers. Doc
Thanks @narakdk That is multi-select. That is the exact option shown here. Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - ua-cam.com/video/rtDlORLmjlE/v-deo.html Excel Green Marks - Error Checking - Best Practices - ua-cam.com/video/XeLtlzd9lRY/v-deo.html Excel Best Practices - Part 1 of 3 - Data Management - ua-cam.com/video/rr_1ha6g6lc/v-deo.html Excel Best Practices - Part 2 of 3 - Formulas - ua-cam.com/video/kz_zAvMINAk/v-deo.html Excel Best Practices - Part 3 of 3 - Analytics - ua-cam.com/video/JNR5yx_Pg4A/v-deo.html 10 Excel Settings You Must CHANGE! - ua-cam.com/video/vXrrXdKyJFk/v-deo.html Automatic data clean up with Excel Flash Fill - ua-cam.com/video/N3p_x_lXT_c/v-deo.html Instant Excel Audit, Comparison and Analysis - Inquire - ua-cam.com/video/cDdvUZxOUis/v-deo.html Six powerful Excel Navigation Shortcuts - ua-cam.com/video/bR-yMbGPq50/v-deo.html Handle millions of rows in Excel - Slow to fast - ua-cam.com/video/93h7rRsLF7Y/v-deo.html Convert crosstab to tabular - Unpivot - Excel Power Query - ua-cam.com/video/mTtOtZH-nNM/v-deo.html Cheers. Doc
@@efficiency365 If I understand that video correct, then it is not possible afterwards the unpivoting to cross-reference (interact) with other questions and their responses, because they are no longer in the same row of the dataset but on each own row in the same coloumn. I have solved it by making a new datatable for each multi-question and link it together, but I just think there must be an easier solution : )
Helped me a lot sir ! Valuable video. I don't usually comment on yt videos but this is a gem !!
Thanks @KeshavSharma-yb9ke
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - ua-cam.com/video/rtDlORLmjlE/v-deo.html
Excel Green Marks - Error Checking - Best Practices - ua-cam.com/video/XeLtlzd9lRY/v-deo.html
Excel Best Practices - Part 1 of 3 - Data Management - ua-cam.com/video/rr_1ha6g6lc/v-deo.html
Excel Best Practices - Part 2 of 3 - Formulas - ua-cam.com/video/kz_zAvMINAk/v-deo.html
Excel Best Practices - Part 3 of 3 - Analytics - ua-cam.com/video/JNR5yx_Pg4A/v-deo.html
10 Excel Settings You Must CHANGE! - ua-cam.com/video/vXrrXdKyJFk/v-deo.html
Automatic data clean up with Excel Flash Fill - ua-cam.com/video/N3p_x_lXT_c/v-deo.html
Instant Excel Audit, Comparison and Analysis - Inquire - ua-cam.com/video/cDdvUZxOUis/v-deo.html
Six powerful Excel Navigation Shortcuts - ua-cam.com/video/bR-yMbGPq50/v-deo.html
Handle millions of rows in Excel - Slow to fast - ua-cam.com/video/93h7rRsLF7Y/v-deo.html
Convert crosstab to tabular - Unpivot - Excel Power Query - ua-cam.com/video/mTtOtZH-nNM/v-deo.html
Cheers.
Doc
Awesome.... explanation is on point
Thanks @waleadeniji6959
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - ua-cam.com/video/rtDlORLmjlE/v-deo.html
Excel Green Marks - Error Checking - Best Practices - ua-cam.com/video/XeLtlzd9lRY/v-deo.html
Excel Best Practices - Part 1 of 3 - Data Management - ua-cam.com/video/rr_1ha6g6lc/v-deo.html
Excel Best Practices - Part 2 of 3 - Formulas - ua-cam.com/video/kz_zAvMINAk/v-deo.html
Excel Best Practices - Part 3 of 3 - Analytics - ua-cam.com/video/JNR5yx_Pg4A/v-deo.html
10 Excel Settings You Must CHANGE! - ua-cam.com/video/vXrrXdKyJFk/v-deo.html
Automatic data clean up with Excel Flash Fill - ua-cam.com/video/N3p_x_lXT_c/v-deo.html
Instant Excel Audit, Comparison and Analysis - Inquire - ua-cam.com/video/cDdvUZxOUis/v-deo.html
Six powerful Excel Navigation Shortcuts - ua-cam.com/video/bR-yMbGPq50/v-deo.html
Handle millions of rows in Excel - Slow to fast - ua-cam.com/video/93h7rRsLF7Y/v-deo.html
Convert crosstab to tabular - Unpivot - Excel Power Query - ua-cam.com/video/mTtOtZH-nNM/v-deo.html
Cheers.
Doc
Thank you so much!
Thanks @papayuyu1189
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - ua-cam.com/video/rtDlORLmjlE/v-deo.html
Excel Green Marks - Error Checking - Best Practices - ua-cam.com/video/XeLtlzd9lRY/v-deo.html
Excel Best Practices - Part 1 of 3 - Data Management - ua-cam.com/video/rr_1ha6g6lc/v-deo.html
Excel Best Practices - Part 2 of 3 - Formulas - ua-cam.com/video/kz_zAvMINAk/v-deo.html
Excel Best Practices - Part 3 of 3 - Analytics - ua-cam.com/video/JNR5yx_Pg4A/v-deo.html
10 Excel Settings You Must CHANGE! - ua-cam.com/video/vXrrXdKyJFk/v-deo.html
Automatic data clean up with Excel Flash Fill - ua-cam.com/video/N3p_x_lXT_c/v-deo.html
Instant Excel Audit, Comparison and Analysis - Inquire - ua-cam.com/video/cDdvUZxOUis/v-deo.html
Six powerful Excel Navigation Shortcuts - ua-cam.com/video/bR-yMbGPq50/v-deo.html
Handle millions of rows in Excel - Slow to fast - ua-cam.com/video/93h7rRsLF7Y/v-deo.html
Convert crosstab to tabular - Unpivot - Excel Power Query - ua-cam.com/video/mTtOtZH-nNM/v-deo.html
Cheers.
Doc
Hi, great video, there is very little good information on this online, so thanks for providing it.
A few questions:
First, when there are multiple multiple-choice questions, why do you make all of them in seperate tables and not just keep splitting them in a single table? Else creating this many tables causes everything to slow down tremendously, especially if you have let's say 30 multiple-choice questions.
Secondly, related to this, how would you go about doing this for very many multiple choice questions?
1. Why make separate table?
The reason is that every time you split to rows, the number of rows multiplies. If you repeat this process for multiple columns, the number of rows expands exponentially.
Even if you can manage the analysis with increased rows, filtering on individual answer item becomes difficult in UI.
Lastly, creating separate tables creates a more optimal data structure which performs faster.
(This is normalized design. Everything to rows is denormalized table)
2. How to make separate tables automatically?
Conceptually, this is possible using some Power Query coding.
I will research this and if there is a solution, I will publish it soon.
Check out this video as well : ua-cam.com/video/sG7HHOjNfkY/v-deo.html
@@efficiency365 Thanks for the reply! Very helpful
This is awesome Dr. P.
Thanks Matt.
Watch Excel Custom Sorting Part 1 and 2 - you will like it: ua-cam.com/video/MSj9rb633BM/v-deo.html and ua-cam.com/video/2oReiB8kgTw/v-deo.html
Cheers. Doc.
Excellent!
Thanks Roll TheRs.
Check out my other best practices videos as well.
Efficient Tasks Management - ua-cam.com/video/vLFpWVfUfQ8/v-deo.html
Microsoft Teams - 15 Best Practices - ua-cam.com/video/W8Ufx_znKxI/v-deo.html
Smart and Effective Email - 5 powerful ways - ua-cam.com/video/6NEFSMqHgQE/v-deo.html
Outlook Calendar Best Practices : Part 1 - ua-cam.com/video/GzsQnecjAQo/v-deo.html and Part 2 - ua-cam.com/video/padKH8ys5Cs/v-deo.html
OneNote - Best Practices - ua-cam.com/video/m-4AY1cMi8s/v-deo.html
Microsoft 365 Best Practices - ua-cam.com/video/kVC_YcL5ObU/v-deo.html
Word Tables Best Practices - The ultimate guide - ua-cam.com/video/ueKCHxNUOkA/v-deo.html
Cheers. Doc.
My first impression was spilt by comma and perform unpivot. But split by row changed everything. Thanks
Yes. My general rule is - the first thought which comes to mind is usually inefficient! (At least in case of Office apps)
@@efficiency365 The video was so useful I could immediately apply the concept in my data. Checking the spell error by removing the duplicates was profound.. Thank you very much !
As you learn more, you will realize that each feature has multiple use case.
Just because you found the obvious (and originally intended) use case should not constrain you from thinking of more relevant scenarios.
Simple and amazing
Split to rows is ok if there was ONE field. With multiple fields, you end up with too many rows unnecessarily.
What is the vision of this exal? And how to get all these properties?
Thanks @user-wf2pg3si5c
Check out my other best practices videos as well.
Efficient Tasks Management - ua-cam.com/video/vLFpWVfUfQ8/v-deo.html
Task Apps Comparison: ua-cam.com/video/ViWJIzMPQZg/v-deo.html
Teams Meeting - Agenda, Action Points, Notes - ua-cam.com/video/k0t8_mNkMDw/v-deo.html
Microsoft Teams - 15 Best Practices - ua-cam.com/video/W8Ufx_znKxI/v-deo.html
OneDrive Best Practices Part 1 - ua-cam.com/video/D7ZrfphW4vo/v-deo.html Part 2 - ua-cam.com/video/nFx6YQxc-b4/v-deo.html
Smart and Effective Email - 5 powerful ways - ua-cam.com/video/6NEFSMqHgQE/v-deo.html
Outlook Calendar Best Practices : Part 1 - ua-cam.com/video/GzsQnecjAQo/v-deo.html and Part 2 - ua-cam.com/video/padKH8ys5Cs/v-deo.html
OneNote - Best Practices - ua-cam.com/video/m-4AY1cMi8s/v-deo.html
Microsoft 365 Best Practices - ua-cam.com/video/kVC_YcL5ObU/v-deo.html
Cheers. Doc.
Sir, I have a question. How to make yes no item/criteria into the list data as like as data in your thumbnail sir? I want some of tutorial, if this case can do by using query in excel. Thank you 🙏🏻
Thanks @holamala8691
If yes / no, only one answer at a time. It will work just fine. No need to split and unpivot.
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - ua-cam.com/video/rtDlORLmjlE/v-deo.html
Excel Green Marks - Error Checking - Best Practices - ua-cam.com/video/XeLtlzd9lRY/v-deo.html
Excel Best Practices - Part 1 of 3 - Data Management - ua-cam.com/video/rr_1ha6g6lc/v-deo.html
Excel Best Practices - Part 2 of 3 - Formulas - ua-cam.com/video/kz_zAvMINAk/v-deo.html
Excel Best Practices - Part 3 of 3 - Analytics - ua-cam.com/video/JNR5yx_Pg4A/v-deo.html
10 Excel Settings You Must CHANGE! - ua-cam.com/video/vXrrXdKyJFk/v-deo.html
Automatic data clean up with Excel Flash Fill - ua-cam.com/video/N3p_x_lXT_c/v-deo.html
Instant Excel Audit, Comparison and Analysis - Inquire - ua-cam.com/video/cDdvUZxOUis/v-deo.html
Six powerful Excel Navigation Shortcuts - ua-cam.com/video/bR-yMbGPq50/v-deo.html
Handle millions of rows in Excel - Slow to fast - ua-cam.com/video/93h7rRsLF7Y/v-deo.html
Convert crosstab to tabular - Unpivot - Excel Power Query - ua-cam.com/video/mTtOtZH-nNM/v-deo.html
Cheers.
Doc
how about when the survey give like 4 coloumns for 1 question and sometimes they can answer more than one of those 4?
Thanks @narakdk
That is multi-select. That is the exact option shown here.
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - ua-cam.com/video/rtDlORLmjlE/v-deo.html
Excel Green Marks - Error Checking - Best Practices - ua-cam.com/video/XeLtlzd9lRY/v-deo.html
Excel Best Practices - Part 1 of 3 - Data Management - ua-cam.com/video/rr_1ha6g6lc/v-deo.html
Excel Best Practices - Part 2 of 3 - Formulas - ua-cam.com/video/kz_zAvMINAk/v-deo.html
Excel Best Practices - Part 3 of 3 - Analytics - ua-cam.com/video/JNR5yx_Pg4A/v-deo.html
10 Excel Settings You Must CHANGE! - ua-cam.com/video/vXrrXdKyJFk/v-deo.html
Automatic data clean up with Excel Flash Fill - ua-cam.com/video/N3p_x_lXT_c/v-deo.html
Instant Excel Audit, Comparison and Analysis - Inquire - ua-cam.com/video/cDdvUZxOUis/v-deo.html
Six powerful Excel Navigation Shortcuts - ua-cam.com/video/bR-yMbGPq50/v-deo.html
Handle millions of rows in Excel - Slow to fast - ua-cam.com/video/93h7rRsLF7Y/v-deo.html
Convert crosstab to tabular - Unpivot - Excel Power Query - ua-cam.com/video/mTtOtZH-nNM/v-deo.html
Cheers.
Doc
@@efficiency365 If I understand that video correct, then it is not possible afterwards the unpivoting to cross-reference (interact) with other questions and their responses, because they are no longer in the same row of the dataset but on each own row in the same coloumn. I have solved it by making a new datatable for each multi-question and link it together, but I just think there must be an easier solution : )