OK, I give up, you absolutely put me to shame. I thought I was quite good at Excel but you are beyond belief, You make me realize just how little I know and how much more there is available. Thank you for your videos. (I rarely post on any media but you deserve so many accolades)
You are amazing, I learn so much from each video you produce. I was working when Excel first came out. It was a game-changer for my job then, now look what you can do with it. At this point I'm retired but still use Excel for home finances. I can't say I have a need to use everything you teach, however the lessons are fun to watch; if.. no, when(!)... I do get stuck I search your videos first. Thank you.
Believe it or not! Yesterday I just finished working on a dashboard to give the same results. I had a hard time creating multiple sheets and linking them to the reporting sheet, spending hours! Shame! If I had watched this tutorial video yesterday, I could have attained better dynamic report in lesser time. A big salute to you Mynda! I am gonna try this right away. Thank you Mynda.
Thanks Myanda, great idea to save dashboard space. I have implemented it in a couple of dashboards and used the "Sort Order" and "Sort By" method to make it simple for a user to change the sorting of Pivot Tables (so much easier to click a couple of Option Controls rather than the Pivot Sort/Filter button then having to select "More Filter Options" then selecting the Column to sort by and select ascending or descending. The option button clicks trigger a fairly simple VBA code that sorts the Pivot Table accordingly.
Really WOW! What an amazing idea and as usual explained in the most easy and understandable way as you do Mynda! Thanks a lot for so many ideas captured in this one video that could be applied in other requirements too! 🙂
Beautifully done as usual! Someone said the formula was long. It is always possible to use a Lambda function to conceal the workings! = ScrolledWindowλ(Stock, Order, SortBy, Scroll) A more significant change might be to use the array shaping functions to select the output, so the Lambda function refers to = LET( sortedTable, SORT(Stock, SortBy, CHOOSE(Order, 1, -1)), TAKE(DROP(sortedTable, Scroll), 10) )
I remember a dashboard from Chandoo about 10 years ago that essentially ended with the same thing. It is just UNBELIEVABLE though to see what the advances in excel functionality have allowed us to do! That dashboard ten years used additional calculation sheets with a series of small/large/match/offset to do everything that now literally fits into one formula 🤯
Wow! Superb updates to the formulas in Office 365 that open up so many possibilities and solutions. Another great video as always Mynda. Clear and very informative. Much appreciated.
Thank you for this nice and clear tutorial! Because I thought it wasn’t necessary to make a tutorial for the sort table and the scrolling, I was curious how you would explain it. I thought scrolling was automatically done by excel, but in general by windows, because it is standard when data is larger than the screen that a scrollbar appears. But then I realized that this tabledata is less than the screen size and that make it so useful. And I think this sorting is easy to use, because if you use the filter on a defined table, all columns get an arrow that you might not want.
I always enjoy your tutorials. You are part of the reason that my Excel skills are improving all the time. I am wondering if you can use Filter to get the same results?
Hi, at first I would like to thank you for such a great work and sharing the knowledge with us. I have a question. How could you comine the filter function with a scrollbar? In your example maybe I want to filter for "availability" (or any other selection) and this will be displayed.
Form controls don't work in Excel online, so if you're hoping to embed the file in SharePoint online, then it won't work, but users can open it in the Excel Desktop app from SharePoint.
Yes, you can reference a PivotTable cell range, but not the PivotTable name as the source. You might want to write a dynamic named range to reference the PivotTable if you expect it to grow.
Hi Mynda. Thank you for providing this valuable lesson. Can there be slicers to filter out the formula area? I am using this method for my budget which has spending categories.
Not sure what you mean. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Wow, the wonderful world of excel. I thought exactly what I need for my dashboard graphs and a table all connected to slicers ... However, my table is a power pivot table. Is there a way to load power pivot table rows into the dynamic sort & order table, or is this a bridge to far?
You could create a PivotTable in a tabular layout that feeds the scroll and sort table. You'd need to create a dynamic named range for the PivotTable that expands/contracts with the PivotTable on refresh.
This is brilliant! So 'tight' and efficient. I'm too embarrassed to tell the fangled way I did something similar - full of chewing gum, bits of string and sticky plasters! 😁😁
As usual high standard, great use of excel features, aka "tricks". Off topic question: have you seen any mention of when the next one-time payment version of Office will be released? I need to replace 2013, 365 pricing does not fit my budget: single user, single computer, no "family" to share cost with. Back in 2021 I thought there would be a new version for 2023 (more money!).
Thank you! The last version was 2021 and was released in the second half of 2020, so I'd say possibly late this year, but I haven't heard anything on it.
The key is to start with a clear idea of what you want to analyze. Ask yourself what insights you're looking to gain from your data. PivotTables are excellent for summarizing, analyzing, and presenting large amounts of data, enabling you to focus on specific areas. Before creating your PivotTable, ensure your data is well-organized, with each column representing a variable and each row a record. Then, when setting up your PivotTable, think about which fields will be your Rows, Columns, and Values - this will depend on what kind of summary or analysis you want. Remember, experimenting with different layouts and fields can often lead to valuable insights. Hope this helps :)
Shame about that. In my Excel Dashboard course I also teach a method for those on earlier versions of Excel: www.myonlinetraininghub.com/excel-dashboard-course
I like your videos a lot. Watched dozens and dozens of them. I particularly like it when you get unnerved by ‘little’ things like alignment. But this time I got unnerved by your UI design. There is a lot wrong with that I’m afraid and it will confuse users in the current form. Firstly, when using radio buttons, you always put the radio button first and then the description. The sort order group has it the wrong way around. Secondly, don’t use radio buttons above the list header. Put a little arrow next to the column name (just like file manager for instance does). Thirdly, place the scroll bar to the right of the list and align the top and bottom of the scroll bar to the height of the list. If you really want to make it CUA compliant … add up and down arrows to the scroll bar and make the large jump, not the whole bar length (10) but something smaller. Hope you don’t mind all the criticism, just think of it as me giving something back to the channel 😊
Thanks for the tips, @HenkAlles. I agree the order arrows would be better to the right of the radio buttons and the scroll bar to the right of the table. However, there's no 'arrow' form control, so putting an arrow next to the column name would have to be done with an additional image or symbol. This image/symbol will also imply the sorting is either ascending or descending depending on the arrow direction, and it's not possible to switch them to reflect changes in the sort order without writing some VBA code to handle it. The scroll bar form control should have arrows top and bottom but there is a bug in Windows 11 where the arrows do not display as they should. This has been reported to Microsoft, but I'm not holding my breath for a fix any time soon. Keep the feedback and suggestions coming. They're most welcome 😊
I use array formula's a lot to build tables but the frustrating thing is that you cannot use the Data>SORT on array tables. Do you have a solution for this?
If you use dynamic array functions you can use SORT. I'm interested to hear what type of tables you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Well this is interesting, but seems like a lot of faffing around! If one of my recipients of data asked for this, I would be highly motivated to suggest something different!
OK, I give up, you absolutely put me to shame. I thought I was quite good at Excel but you are beyond belief, You make me realize just how little I know and how much more there is available. Thank you for your videos. (I rarely post on any media but you deserve so many accolades)
Aw, thanks so much for your kind words! 🙏😊
I love the deliciously pixelated commands (radio buttons) which is flashing me back into the 90´s. Aaah, nostalgia…
😁they're coming back in fashion!
Each and every video of yours is superb. I am totally astonished to see the enhanced capabilities of Office 365.
Glad you like them!
You are amazing, I learn so much from each video you produce. I was working when Excel first came out. It was a game-changer for my job then, now look what you can do with it. At this point I'm retired but still use Excel for home finances. I can't say I have a need to use everything you teach, however the lessons are fun to watch; if.. no, when(!)... I do get stuck I search your videos first. Thank you.
Aw, that's wonderful to hear 🙏
I’ve watched a few videos on inserting scroll bars, but this is the first one I’ve been able to follow. Well done.
Wow! That's wonderful to hear 🙏
Brilliant, visual, impressive, functional for the company. Thank you very much Mynda.
Thanks so much 🙏
Believe it or not! Yesterday I just finished working on a dashboard to give the same results. I had a hard time creating multiple sheets and linking them to the reporting sheet, spending hours! Shame! If I had watched this tutorial video yesterday, I could have attained better dynamic report in lesser time. A big salute to you Mynda! I am gonna try this right away. Thank you Mynda.
So pleased you'll find it useful!
This is EXACTLY what I needed for a project at work! Thank you so much!!!
Glad I could help!
Thanks Mynda I'm always learning something new from you. I appreciate your help 🙏
Great to hear!
Surprising again. Thank you so much !! You have added too much add values to my knowledge and work . Thank you !!!!
Happy to hear that!
Wow! Absolutely brilliant. Thank you so much for this Mynda.
🙏 You are most welcome!
Mynda - Blown away - Again! Excellent video - am always amazed by yur clear delivery and simple explanations. Thanks again and keep going!
Wow, thank you!
Wow….just wow. So many new ideas to try. Thnx Mynda
Glad you like them!
I'm so grateful 🌷🌷🌷
Best regards .
Glad you liked it 🙏
Great Job, following you for many years and learned many things from you.
Glad to hear that!
Very, very cool! I love your reference to "Icing on the cake" 🙂
😁thank you!
This is like unlocking a secret superpower…. Amazing.
😁glad you liked it!
Always a joy to see notifications from u
Glad to hear it 🙏
Yes! Indeed! It was simple but very useful!
Great to hear 😊
Wao wao wao! Thank you soooooo much from France!
You are very welcome 😊
Thank you!!!! Very! Very! Ever!!!!
Glad you like it! 🙏
Thanks Myanda, great idea to save dashboard space. I have implemented it in a couple of dashboards and used the "Sort Order" and "Sort By" method to make it simple for a user to change the sorting of Pivot Tables (so much easier to click a couple of Option Controls rather than the Pivot Sort/Filter button then having to select "More Filter Options" then selecting the Column to sort by and select ascending or descending. The option button clicks trigger a fairly simple VBA code that sorts the Pivot Table accordingly.
Awesome to hear, Charles!
Fantastic!! Brilliant! Thank you!!!
🙏 so glad you liked it!
I love ABSOLUTELY EVERYTHING you share!
Wonderful, Mynda! 😊
Cheers, Ian 🙏
always love your tutorials
Mo
Thanks for your support, Mo!
@@MyOnlineTrainingHub welcome, you've always been (here) for me
thank you as always
Really WOW! What an amazing idea and as usual explained in the most easy and understandable way as you do Mynda! Thanks a lot for so many ideas captured in this one video that could be applied in other requirements too! 🙂
Glad you liked it, Vijay 🙏
Excellent tutorial!
Thanks so much!
THANK YOU SOOOOOOO MUCH ❗🏆❗ ocean of surprises 🙏
So pleased you liked it!
Loved this... I can't wait to implement it in some of my workbooks.
Great to hear!
Beautifully done as usual!
Someone said the formula was long. It is always possible to use a Lambda function to conceal the workings!
= ScrolledWindowλ(Stock, Order, SortBy, Scroll)
A more significant change might be to use the array shaping functions to select the output, so the Lambda function refers to
= LET(
sortedTable, SORT(Stock, SortBy, CHOOSE(Order, 1, -1)),
TAKE(DROP(sortedTable, Scroll), 10)
)
Nice, as always, Peter! Thanks for sharing 😊
Perfect tutorial thanks lot❤
You're welcome 😊
Excellent Tips! Impressed with all your Videos Mynda! Amazed by the infinitive possibilities and flexibility Excel keeps on adding every time! Thanks
Thanks so much! 🙏
Thats cool. I think using a nested Take & drop function is more intuative to write but both work well.
Yes, those functions are great and came out after this video, so great idea to use them as an alternative.
That’s really useful and I’m sure I’ll be using it soon…
Glad to hear it'll be useful!
Very cool Mynda, another superb video, thanks!
Cheers, Chris 🙏
I remember a dashboard from Chandoo about 10 years ago that essentially ended with the same thing. It is just UNBELIEVABLE though to see what the advances in excel functionality have allowed us to do! That dashboard ten years used additional calculation sheets with a series of small/large/match/offset to do everything that now literally fits into one formula 🤯
Dynamic arrays are the best!
Much appreciated ❤
My pleasure!
Very cool and clearly explained Thank you
Thank you very much!
Hi Mynda!
You made my day! How awesome!
I've got so many uses for this, I can't wait to get started...
Cheers,
Mark
Awesome to hear!
Wow! Superb updates to the formulas in Office 365 that open up so many possibilities and solutions.
Another great video as always Mynda. Clear and very informative. Much appreciated.
Thanks so much!
Excelente. Sin uso de VBA inclusive. ¡Gracias!
Gracias!
That’s awesome 👏 ❤
Thank you!
@@MyOnlineTrainingHub
🙏🙏🙏
Thank you for this nice and clear tutorial! Because I thought it wasn’t necessary to make a tutorial for the sort table and the scrolling, I was curious how you would explain it. I thought scrolling was automatically done by excel, but in general by windows, because it is standard when data is larger than the screen that a scrollbar appears. But then I realized that this tabledata is less than the screen size and that make it so useful. And I think this sorting is easy to use, because if you use the filter on a defined table, all columns get an arrow that you might not want.
Thanks so much! Glad you liked it.
Very good. Thank you 😊
Welcome 😊
Amazing as always Mynda. Very innovative and well explained as well. Thanks!
Thanks so much!
Amazing thank you!
Glad you liked it!
Nice use of form controls. As always very well written and presented!
Thanks so much, Rory!
amazing minda!
Thanks you 🙏
That's really terrific, Mynda!. I only wish there was a way to have buttons that can control the sorting of official Excel Tables.
Yes, that would be awesome! Of course you can click the drop down list buttons and select Sort, but it's not quite as easy.
Really great tutorial Thanks.
Glad you enjoyed it!
Masterpiece!
Thanks so much!
That’s awesome 👏 ❤
I always enjoy your tutorials. You are part of the reason that my Excel skills are improving all the time. I am wondering if you can use Filter to get the same results?
I presume you mean the FILTER function instead of INDEX. Probably, but I don't think it would be as elegant.
Blimey, you are so clever 😁
Aw, you're too kind 🙏
Somehow that use of sequence and {1,2,3,4,5} feels like inception 😎
😁
Excelente dica, parabéns!
Obrigado!
Hi, at first I would like to thank you for such a great work and sharing the knowledge with us. I have a question. How could you comine the filter function with a scrollbar? In your example maybe I want to filter for "availability" (or any other selection) and this will be displayed.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
This is a very insightful Video I really like it. One last thing: can we make the scroll bar limit dynamic ? Thank you for your hard work 👍
Great to hear! No, the scroll bar limits are hard keyed, sorry. If you use an Active X form control scroll bar you have more options.
Great !! a long formula but try out ...👌
It’s worth it 😉
Hola. Me ha parecido algo increíble. Muchas gracias.
Gracias!
Great video, excellent. Thank you.
I have one question: is this interactivity allowed when publishing on sharepoint?
Form controls don't work in Excel online, so if you're hoping to embed the file in SharePoint online, then it won't work, but users can open it in the Excel Desktop app from SharePoint.
Fantastic video. Is there a way of using a pivot table as the array instead of just a table?
Yes, you can reference a PivotTable cell range, but not the PivotTable name as the source. You might want to write a dynamic named range to reference the PivotTable if you expect it to grow.
Hi Mynda, thanks for useful info. What are best examples for usage of this scroll table with sort. And I must say you are looking absolutely gorgeous.
Glad it was helpful! I use them in Dashboards where I want to display a long list of records without taking up too much space.
Hi Mynda. Thank you for providing this valuable lesson. Can there be slicers to filter out the formula area? I am using this method for my budget which has spending categories.
Not sure what you mean. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
🙂...Interesante, gracias por compartir.....👍💯
😊 gracias!
fantastic
Thank you so much 😀
Marvelous 👍👍
Glad you liked it 🙏
Thanks
Welcome 😊
ALT + 30/31 is a handy way to speed up inserting the shapes
Nice...I'll try to remember that!
Wow, the wonderful world of excel. I thought exactly what I need for my dashboard graphs and a table all connected to slicers ... However, my table is a power pivot table. Is there a way to load power pivot table rows into the dynamic sort & order table, or is this a bridge to far?
You could create a PivotTable in a tabular layout that feeds the scroll and sort table. You'd need to create a dynamic named range for the PivotTable that expands/contracts with the PivotTable on refresh.
This is brilliant! So 'tight' and efficient.
I'm too embarrassed to tell the fangled way I did something similar - full of chewing gum, bits of string and sticky plasters! 😁😁
😁glad you'll find it useful 🙏
Awesome ❤
Thanks 😊
Awesome!
Glad you think so!
You are awesome 👍
Thanks so much for your support!
As usual high standard, great use of excel features, aka "tricks".
Off topic question: have you seen any mention of when the next one-time payment version of Office will be released? I need to replace 2013, 365 pricing does not fit my budget: single user, single computer, no "family" to share cost with. Back in 2021 I thought there would be a new version for 2023 (more money!).
Thank you! The last version was 2021 and was released in the second half of 2020, so I'd say possibly late this year, but I haven't heard anything on it.
excellent
Thank you!
Very good 👋
Glad you like it!
Thank you but do you have a video that shows a horizontal scroll option for conditions where the table is wider than the window on your dashboard.
I use a horizontal scroll bar in this tutorial: ruclips.net/video/5qtSioTE2wY/видео.html
wow! indeed
Glad you liked it 😊
Amazing....
Thanks a lot 😊
👍 Mynda thanks.
If I scrolled more bigger than 10 rows it will exceed table row number and return error. How may I solve this?
In this example the maximum is set to 10, so it won't exceed the number of rows. You can change the maximum value to suit your table size.
hmm can you help me to give hint or something when i want to use pivotable?
The key is to start with a clear idea of what you want to analyze. Ask yourself what insights you're looking to gain from your data. PivotTables are excellent for summarizing, analyzing, and presenting large amounts of data, enabling you to focus on specific areas. Before creating your PivotTable, ensure your data is well-organized, with each column representing a variable and each row a record. Then, when setting up your PivotTable, think about which fields will be your Rows, Columns, and Values - this will depend on what kind of summary or analysis you want. Remember, experimenting with different layouts and fields can often lead to valuable insights. Hope this helps :)
Beautiful. Unfortunately for me, excel that I use doesnt have SORT function. Thanks anyway.
Shame about that. In my Excel Dashboard course I also teach a method for those on earlier versions of Excel: www.myonlinetraininghub.com/excel-dashboard-course
You might be a genius.
😁you're too kind!
❤Team MOTH 👍😎✊
Cheers, Steven 👊
I like your videos a lot. Watched dozens and dozens of them. I particularly like it when you get unnerved by ‘little’ things like alignment. But this time I got unnerved by your UI design. There is a lot wrong with that I’m afraid and it will confuse users in the current form. Firstly, when using radio buttons, you always put the radio button first and then the description. The sort order group has it the wrong way around. Secondly, don’t use radio buttons above the list header. Put a little arrow next to the column name (just like file manager for instance does). Thirdly, place the scroll bar to the right of the list and align the top and bottom of the scroll bar to the height of the list.
If you really want to make it CUA compliant … add up and down arrows to the scroll bar and make the large jump, not the whole bar length (10) but something smaller.
Hope you don’t mind all the criticism, just think of it as me giving something back to the channel 😊
Thanks for the tips, @HenkAlles. I agree the order arrows would be better to the right of the radio buttons and the scroll bar to the right of the table. However, there's no 'arrow' form control, so putting an arrow next to the column name would have to be done with an additional image or symbol. This image/symbol will also imply the sorting is either ascending or descending depending on the arrow direction, and it's not possible to switch them to reflect changes in the sort order without writing some VBA code to handle it. The scroll bar form control should have arrows top and bottom but there is a bug in Windows 11 where the arrows do not display as they should. This has been reported to Microsoft, but I'm not holding my breath for a fix any time soon. Keep the feedback and suggestions coming. They're most welcome 😊
I use array formula's a lot to build tables but the frustrating thing is that you cannot use the Data>SORT on array tables. Do you have a solution for this?
If you use dynamic array functions you can use SORT. I'm interested to hear what type of tables you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
How could we add a filter to this formula to filter Name or Type?
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@MyOnlineTrainingHub I successfully implemented the filter function thanks to this video.
👏👏👏Sheer Magic!!!
Glad you like it 🙏
Well this is interesting, but seems like a lot of faffing around! If one of my recipients of data asked for this, I would be highly motivated to suggest something different!
Interesting. What would you use instead to achieve this?
👏👏
🙏
👍
🙏