I never knew that i could use subtotals with tables like this. you are one of the best.

@MyOnlineTrainingHub

6 ай бұрын

So pleased you learned something new that you can use, Doug.

@jimbobkiwi

2 ай бұрын

Only one of the best? Because she's aussie, you can't give the top spot? Totally understandable

Great job Mynda! I clearly need to use slicers more. Also, the tip on SUBTOTAL and hidden rows (manually hidden vs filtered) was really useful. Thanks.

@MyOnlineTrainingHub

6 ай бұрын

Glad you liked it! Have fun with Slicers 😊

This is a very helpful video. I thoroughly enjoy these bite-sized ‘snacks’ that are not only easy to consume but also convenient to include in the daily work routine. More of them, please 👍👍👍 … Cheers

@MyOnlineTrainingHub

6 ай бұрын

Thanks for watching. Will try my best 😊

Yet another gem from Mynda!! Thank you 🙏🏽

@MyOnlineTrainingHub

6 ай бұрын

Glad you enjoyed it! 🙏😊

Matchless as always Mynda. Source no doubt juicy enough of great info of Excel's priceless feature of Slicers

@MyOnlineTrainingHub

6 ай бұрын

Thanks for your kind words and support 🙏🥰

She is unbelievably helpful with these vids.

@MyOnlineTrainingHub

6 ай бұрын

Aw, so glad to hear that 🥰

these are soooo good. Two decades of using Excel everyday and i never touched this. Terrific info!

@MyOnlineTrainingHub

3 ай бұрын

So glad you learned some new techniques!

Learn, Learn and Learn .... I always new things whenever I watch your videos .... thanks a lot ... God Bless You

@MyOnlineTrainingHub

3 ай бұрын

So wonderful to hear! 🙏😊

Another Great Video/Lesson! Thanks Mynda!

@MyOnlineTrainingHub

6 ай бұрын

Thanks for watching! Glad you liked it 😊

I completely agree with everything you said in this video. Including aggregations on top and having the “Clear All” function on the QAT: it’s on mine for years now, and I instruct everyone to do the same. Thanks Mynda for this great recap! :-)

@MyOnlineTrainingHub

6 ай бұрын

Glad we think alike, Geert 😊 I couldn't live without Clear All on my QAT!

@gerbherb8215

4 ай бұрын

Read my yesterday's tip and forget the QAT 🙂

Great tutorial, i liked! Thanks Mynda!

@MyOnlineTrainingHub

6 ай бұрын

Thanks so much, Luciano!

Thanks so much, great tips and clearly presented. This goes in my saved video list because I know I'll be coming back to watch this again.

@MyOnlineTrainingHub

2 ай бұрын

Awesome to hear 🙏

You just saved me a ton of time!!! Thank you, thank you, thank you!

@MyOnlineTrainingHub

3 ай бұрын

My pleasure!

Excellent as always.👍🌹 I have been using this technique for teh report runner I made for my colleagues in the past years. As compared to pivot tables, this has the advantage of presenting summary information in addition to the detailed/filtered supporting data underneath. This is easy for verification and across checks for my colleagues if they want. The tables I use this method on are the tables that are loaded from power query; therefore they are updated automatically every time I when the power quries refresh.

@MyOnlineTrainingHub

6 ай бұрын

Awesome to hear. Great point about being able to show both the summary and detail at the same time.

The work is perfect and the video editing is awesome ❤

@MyOnlineTrainingHub

5 ай бұрын

Glad you think so! 🙏🥰

Very helpful, you always amaze me 💯

@MyOnlineTrainingHub

6 ай бұрын

Glad you enjoyed it! 🙏

You are the best! thank you for your content.

@MyOnlineTrainingHub

6 ай бұрын

Thanks so much! I appreciate that! 🥰

Thanks Mynda! Great video. I'm glad Excel has so many ways to get things done. And with the new PIVOTBY function there are even more.

@MyOnlineTrainingHub

6 ай бұрын

Thanks so much, Chris! Can't wait for PIVOTBY and GROUPBY. I wasn't in the lucky 50% unfortunately 😭

@chrism9037

6 ай бұрын

Neither was I Mynda :( I have watched Mike Girvin's videos and am jealous lol

@ricos1497

6 ай бұрын

Just to let you guys know, I was in the lucky 50%. I've heard it goes in order of importance......

@MyOnlineTrainingHub

6 ай бұрын

@ricos1497 😁probably! @chrism9037 I haven't watched any videos on them yet. It's too painful 😁

Great idea and lay out!

@MyOnlineTrainingHub

6 ай бұрын

Glad you liked it! 🙏

Wow! Very informative and very impressive presentation. I was impressed enough that it got me to both "like" and subscribe. 😀

@MyOnlineTrainingHub

6 ай бұрын

Wow, thank you and welcome! 🙏😊

Smashed it, Mynda (of course)! Tables are the way to go. :)

@MyOnlineTrainingHub

6 ай бұрын

🥰it was your idea, Jon, so thank you!

Quite useful... congratulations!

@MyOnlineTrainingHub

6 ай бұрын

Glad you think so!

Thnks for your tipps...helping a lot even for rookies like me...

@MyOnlineTrainingHub

Ай бұрын

Glad to hear it 🙏😊

Thank you for this video!

@MyOnlineTrainingHub

6 ай бұрын

My pleasure 😊

cant believe your still at 550k + subscribers, you are a genius!

@MyOnlineTrainingHub

6 ай бұрын

Aw, you're too kind 🙏🥰 please spread the word!

I leart a new function AGGREGATE from this video. Thanks a lot for sharing your knowledge 👍

@MyOnlineTrainingHub

6 ай бұрын

Glad to hear that 😊

Excellent content❤

@MyOnlineTrainingHub

6 ай бұрын

Thanks so much, Tracy!

Excellent video. I think this approach is much better than Pivot Tables.

@MyOnlineTrainingHub

6 ай бұрын

Great to hear you'll be able to make use of it! 🙏

Simple and useful

@MyOnlineTrainingHub

3 ай бұрын

Glad you liked it 🙏

Mynda you're the best

@MyOnlineTrainingHub

5 ай бұрын

Thanks for your kind words and support 🙏

Best part about tables vs pivot tables. You can share data with colleagues in a locked table and they can still filter and sort it. Can’t do that with pivot tables, where you can’t sort by ascending or descending

well done!

@MyOnlineTrainingHub

6 ай бұрын

Thanks so much! 😊

Dear Mam Nice approach ,thanks

@MyOnlineTrainingHub

4 ай бұрын

Thanks so much!

This is something new technique instead of Pivot tables 👍👍👍

@MyOnlineTrainingHub

6 ай бұрын

🙏 Have fun with it 😊

Super 👍

@MyOnlineTrainingHub

15 күн бұрын

Thank you 👍

You are one of the best Excel teachers on YT! I have a very large CSV file for stock data (date, symbol, close, volume) and I have an Excel 365 spreadsheet that looks like a stock screener with many column titles: symbol, name, price, volume and several price change % periods with formulas for calculations. I don't want to see the CSV data in my spreadsheet (screener), but I want the spreadsheet to use the CSV data for formulations. What would be the best way to do this please? I also need to be able to sort the columns. PS I don't like =stockhistory or data scraping. Thank you again for helping others. I try to do this every day.

@MyOnlineTrainingHub

3 ай бұрын

You can import the CSV file with Power Query and then load the data to the Pivot Cache (load to > PivotTable) and from there you can analyse and extract data. If you'd like to learn more about Power Query, see this video: kzread.info/dash/bejne/gJacxpJ7Zc_Keqg.html or take my Power Query course: www.myonlinetraininghub.com/excel-power-query-course

Amazing

@MyOnlineTrainingHub

3 ай бұрын

Thank you! 🙏😊

Thanks❤❤❤

@MyOnlineTrainingHub

6 ай бұрын

Pleasure 😊

@chinthaka7245

6 ай бұрын

❤️❤️❤️

Hola desde chile ..Una consulta que grabador de pantalla usas , saludos Luis

@luisvillagra

5 ай бұрын

Hello from Chile..A question which screen recorder do you use, greetings Luis

@MyOnlineTrainingHub

5 ай бұрын

hello! I use Camtasia Studio.

@luisvillagra

5 ай бұрын

Lo vere esta muy bueno , que tengas una linda seman dese santiago Chile y Gracias por tu gran aporte a esta comunidad.@@MyOnlineTrainingHub

@ 2:40 Tip on clearing all filters, not using the mouse: press CTRL+SHIFT+L two times.

@MyOnlineTrainingHub

4 ай бұрын

Thanks for sharing!

Hi Mynda. Do you have any recommendations for using slicers with the FILTER function? I am not a great fan of playing 'peek-a-boo', hiding records to pretend they have gone, and the array functions I use ignore the fact that some rows are hidden. I have never even tried to sort the implications of a traditional filter on working with further tables laid out across the same sheet! I have tried a couple of workarounds to apply slicer selections as FILTER criteria but the best route is not obvious to me.

@MyOnlineTrainingHub

6 ай бұрын

Hi Peter, only with a helper column on the source table (I'll call it Visible). You can use SUBTOTAL (or AGGREGATE) in this column to detect if the row is visible e.g. =SUBTOTAL(109,[@Sales])0 Then reference that column in your FILTER formula: =FILTER(financials_tbl, financials_tbl[Visible])

@peterbartholomew7409

6 ай бұрын

@@MyOnlineTrainingHub Thanks for that suggestion. That is useful! I had applied the slicer to filter a small helper table comprising a list of distinct values and then used the 'visible' column of that table to generate the FILTER criterion using COUNTIFS. Your approach of filtering the original Table is more direct.

Thanks once again. I wanted to make my Report look like yours. Quick Question, I copied and pasted the pivot table without a data source. The problem is that it was not in table format. It was in a range so now when I try to make it a table, it gives filters on the first row. The first 3 rows have column labels and row labels. The third row is the one I want Filters on and not on the first row. How do I do that without removing any column labels or row labels? Also, I am trying to sort it ascending but it puts the Grand Total in between and messes up the Grand Total values. Any advice Please? Thanks

@MyOnlineTrainingHub

3 ай бұрын

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

Mynda, Which theme are you using? I don't have this color. Thanks

@MyOnlineTrainingHub

2 ай бұрын

It's the new Microsoft 365 theme.

@shoppersdream

2 ай бұрын

@@MyOnlineTrainingHub Thank you! Our Company is still using the 2013-2022 Theme and everything is done by Administrators and we don't have that New One. I do have this new one on Mac (That is what I use at home) but over 60 percent of the things don't work on Mac so I never even checked it. Thanks

This was really nice. How to create a search box for a slicer.? Can it be done in Tables, or not?

@MyOnlineTrainingHub

3 ай бұрын

Not if it's the search box hack that uses PivotTables. But remember, the filter drop down buttons expose a search box for tables.

@shoppersdream

3 ай бұрын

@@MyOnlineTrainingHub Thank You!

Hello thank you very much , what if we have an excel with many criterias , were talking about a 100 culumns , is filter still be usefull or should I figure it out another way ?

@MyOnlineTrainingHub

2 ай бұрын

I'd say if you have 100 columns that your data is in the wrong layout. I've got a video coming out next week on the correct data layout and why it's important, so keep your eye out for it.

@nightsky3623

2 ай бұрын

@@MyOnlineTrainingHub okay I'ma keep an eye on this for sure ! Thank you so much

@MyOnlineTrainingHub

2 ай бұрын

Here's the video on the correct data layout: kzread.info/dash/bejne/dYKg2ZNmhsuak6g.html

@nightsky3623

2 ай бұрын

@@MyOnlineTrainingHub thank you very much , you are the best 💜💜💜

Do you take on Google Sheets jobs on the side ?

@MyOnlineTrainingHub

Ай бұрын

No, I don't do consulting, sorry.

Super - but can you build a table with a column that summarize over the shown values i.e. total sales in area A,B C etc.? I believe this is 99% of pivot table usage

@MyOnlineTrainingHub

6 ай бұрын

Great idea. Not easily, but soon we'll have PIVOTBY and GROUPBY that can 😊

"Clear All" on the QAT? Not on my QAT! Alt,A,C always!

@MyOnlineTrainingHub

6 ай бұрын

😁thanks for sharing, Richard!

👍🏻👍🏻👍🏻

@MyOnlineTrainingHub

Ай бұрын

🙏😊`

how to make slicer for 1st row ( witch contains headings ). witch you use in the middle of your video ( Pro use this technique to avoid pivot table ) please send the link

@MyOnlineTrainingHub

4 ай бұрын

You can't create a slicer for the headings. Slicers are for the items in a column.

@Toqeer-Grt

4 ай бұрын

@@MyOnlineTrainingHub thank you for guidance

How is the document saved so it can be sent to someone else?

@MyOnlineTrainingHub

2 ай бұрын

It's just an Excel file so you can share it over email or via SharePoint, OneDrive, DropBox etc.

You can move tool tips out of the way!!! 😮

@MyOnlineTrainingHub

5 ай бұрын

😁Oh, yes!

Like your videos a lot (always) - this time i just disagree a bit with the title (i know i know YT alghorithm and so on). Pro would deffinitelly use Pivot over normal Table to get data out but still this i is a neat touch (slicers with normal tables) ;-)))

@MyOnlineTrainingHub

6 ай бұрын

Thanks for watching and your support. I promise it's not an algorithm hack. I got this idea from a pro. His name is Jon von der Heyden (I gave him a shout out in the video) and he is an Excel whizz who I've respected for many years. He messaged me to say he does this to avoid using PivotTables. I can see the sense in it because Pivot Tables add a lot of overhead to a file if you already have the data in there. Plus, PivotTables are limited in the calculations you can perform on the data.

@kebincui

6 ай бұрын

I fully agree with you. Thanks Mynda@@MyOnlineTrainingHub

@GeoffLilley

6 ай бұрын

@@MyOnlineTrainingHubWOW, you're calling someone an Excel whiz, Mynda?! Those are high words of praise coming from one of the best in the business!

Actually "pros" will now be using PIVOTBY and GROUPBY!! Time for a video on these?

@brianspiller9075

6 ай бұрын

Not when pros want certain date grouping functionality or easy flexibility. The "new" PIVOTBY and GROUPBY are actual new spins of an old included macro, well included on ancient Excel versions, known as crosstabular tables.

@MyOnlineTrainingHub

6 ай бұрын

😁 Yes! If I could just get my hands on them. Unfortunately, I wasn't in the lucky 50% 😭

@peterbartholomew7409

6 ай бұрын

@@MyOnlineTrainingHub Me neither 😭

@timlucas4155

6 ай бұрын

Been playing with GROUPBY and PIVOTBY. Look good

@piggybankaccountant

6 ай бұрын

⁠@@MyOnlineTrainingHubyoutube has been recommending my video after yours! What an honor. I explained the pivotby/groupby in 2 minutes. Send your friends my way Mynda 🙊

Excellent tricks. But please talk clearly and slowly that non native English speakers can understand. Thanks a lot

@2Theonemmm

6 ай бұрын

She talk’s clearly 😕😏😏

@MyOnlineTrainingHub

6 ай бұрын

To help, you can use the cog icon in the bottom right to slow down the playback speed and use subtitles.

@mohammadusmanshafi696

6 ай бұрын

@@MyOnlineTrainingHub Thanks a lot

Can’t get my teams to use tables and spill formulas… ‘didn’t copy the formula down’ excuse gets old quickly

@MyOnlineTrainingHub

6 ай бұрын

🤦‍♀️frustrating!

@peterbartholomew7409

6 ай бұрын

What you 'know' from the past is often the greatest impediment when it comes to further progress!

marry me!

@MyOnlineTrainingHub

6 ай бұрын

Taken, sorry.

@user-ul3tx5wu7c

6 ай бұрын

@@MyOnlineTrainingHub it's a pity

Just want to say thanks for introducing me to Slicers. They're much easier (and my head can rest easy) to see the flow of data in a table; instead of many filters.

@MyOnlineTrainingHub

3 ай бұрын

So pleased to hear that!