Pros Use This Technique to Avoid PivotTables

[@ziggle314]

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]

Glad you liked it! Have fun with Slicers 😊

[@dougmphilly]

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

[@MyOnlineTrainingHub]

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

[@jimbobkiwi]

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

[@happymystic9800]

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]

Thanks for watching. Will try my best 😊

[@kebincui]

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]

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

[@susanpateyledrew]

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]

Awesome to hear 🙏

[@theharsh2005]

She is unbelievably helpful with these vids.

[@MyOnlineTrainingHub]

Aw, so glad to hear that 🥰

[@DavidRandolphStudio]

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

[@MyOnlineTrainingHub]

So glad you learned some new techniques!

[@GeertDelmulle]

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]

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

[@gerbherb8215]

Read my yesterday's tip and forget the QAT 🙂

[@faraz-online]

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

[@MyOnlineTrainingHub]

Thanks for your kind words and support 🙏🥰

[@chrism9037]

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]

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

[@chrism9037]

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

[@ricos1497]

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

[@MyOnlineTrainingHub]

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

[@venkatiyer7459]

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

[@MyOnlineTrainingHub]

So wonderful to hear! 🙏😊

[@vishwanath1810]

Yet another gem from Mynda!! Thank you 🙏🏽

[@MyOnlineTrainingHub]

Glad you enjoyed it! 🙏😊

[@dave2059]

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

[@MyOnlineTrainingHub]

Wow, thank you and welcome! 🙏😊

[@thepurplepanda1194]

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

[@MyOnlineTrainingHub]

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

[@anurmertah3116]

The work is perfect and the video editing is awesome ❤

[@MyOnlineTrainingHub]

Glad you think so! 🙏🥰

[@luisvillagra]

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

[@luisvillagra]

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

[@MyOnlineTrainingHub]

hello! I use Camtasia Studio.

[@luisvillagra]

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

[@IFBBProGeorgeDorsey]

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

[@MyOnlineTrainingHub]

My pleasure!

[@Luciano_mp]

Great tutorial, i liked! Thanks Mynda!

[@MyOnlineTrainingHub]

Thanks so much, Luciano!

[@frankweber5540]

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

[@MyOnlineTrainingHub]

Glad to hear it 🙏😊

[@shoppersdream]

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

[@MyOnlineTrainingHub]

It's the new Microsoft 365 theme.

[@shoppersdream]

@@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

[@miguelsanches6463]

Another Great Video/Lesson! Thanks Mynda!

[@MyOnlineTrainingHub]

Thanks for watching! Glad you liked it 😊

[@michaelkuhn6328]

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

[@MyOnlineTrainingHub]

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

[@IamTheReaper911]

Very helpful, you always amaze me 💯

[@MyOnlineTrainingHub]

Glad you enjoyed it! 🙏

[@systemtourtvganesh]

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

[@MyOnlineTrainingHub]

Glad to hear that 😊

[@txreal2]

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]

So pleased to hear that!

[@jonvonderheyden382]

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

[@MyOnlineTrainingHub]

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

[@mrpdmasta]

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

[@nightsky3623]

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]

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]

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

[@MyOnlineTrainingHub]

Here's the video on the correct data layout: kzbin.info/www/bejne/eX_PqGRji81nmac

[@nightsky3623]

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

[@jambaraz7641]

You are the best! thank you for your content.

[@MyOnlineTrainingHub]

Thanks so much! I appreciate that! 🥰

[@ar3582]

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]

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: kzbin.info/www/bejne/hJPLlWN4atGXgKc or take my Power Query course: www.myonlinetraininghub.com/excel-power-query-course

[@reetakisna]

Dear Mam Nice approach ,thanks

[@MyOnlineTrainingHub]

Thanks so much!

[@calsc4403]

Quite useful... congratulations!

[@MyOnlineTrainingHub]

Glad you think so!

[@nazarkamal8831]

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

[@MyOnlineTrainingHub]

🙏 Have fun with it 😊

[@quentinbricard]

Thank you for this video!

[@MyOnlineTrainingHub]

My pleasure 😊

[@shoppersdream]

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

[@MyOnlineTrainingHub]

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]

@@MyOnlineTrainingHub Thank You!

[@Hundo_Mo]

Great idea and lay out!

[@MyOnlineTrainingHub]

Glad you liked it! 🙏

[@gerbherb8215]

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

[@MyOnlineTrainingHub]

Thanks for sharing!

[@mogarrett3045]

Mynda you're the best

[@MyOnlineTrainingHub]

Thanks for your kind words and support 🙏

[@josemanuelsarzedas1429]

Simple and useful

[@MyOnlineTrainingHub]

Glad you liked it 🙏

[@shoppersdream]

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]

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

[@tracy_dtc]

Excellent content❤

[@MyOnlineTrainingHub]

Thanks so much, Tracy!

[@tricialoveridge7945]

Do you take on Google Sheets jobs on the side ?

[@MyOnlineTrainingHub]

No, I don't do consulting, sorry.

[@schubertpeter79]

well done!

[@MyOnlineTrainingHub]

Thanks so much! 😊

[@peterbartholomew7409]

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]

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]

@@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.

[@stephenl5693]

I never thought on using slicers on a table only. Everyday is a school day 😊

[@MyOnlineTrainingHub]

Glad you can make use of this 😁

[@chinthaka7245]

Thanks❤❤❤

[@MyOnlineTrainingHub]

Pleasure 😊

[@chinthaka7245]

❤️❤️❤️

[@abdulbasital-sufyani6828]

Super 👍

[@MyOnlineTrainingHub]

Thank you 👍

[@RashidPremier]

Amazing

[@MyOnlineTrainingHub]

Thank you! 🙏😊

[@sørenf-l6s]

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]

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

[@richardhay645]

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

[@MyOnlineTrainingHub]

😁thanks for sharing, Richard!

[@MGA19a]

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]

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]

I fully agree with you. Thanks Mynda@@MyOnlineTrainingHub

[@GeoffLilley]

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

[@Toqeer-Grt]

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]

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

[@Toqeer-Grt]

@@MyOnlineTrainingHub thank you for guidance

[@jerryoltremari317]

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

[@MyOnlineTrainingHub]

😁Oh, yes!

[@dccd673]

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

[@MyOnlineTrainingHub]

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

[@Isreal-Hamas-Iran-war-update]

👍🏻👍🏻👍🏻

[@MyOnlineTrainingHub]

🙏😊`

[@richardhay645]

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

[@brianspiller9075]

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]

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

[@peterbartholomew7409]

@@MyOnlineTrainingHub Me neither 😭

[@timlucas4155]

Been playing with GROUPBY and PIVOTBY. Look good

[@piggybankaccountant]

⁠@@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 🙊

[@mohammadusmanshafi696]

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

[@2Theonemmm]

She talk’s clearly 😕😏😏

[@MyOnlineTrainingHub]

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

[@mohammadusmanshafi696]

@@MyOnlineTrainingHub Thanks a lot

[@Rkcuddles]

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

[@MyOnlineTrainingHub]

🤦‍♀️frustrating!

[@peterbartholomew7409]

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

[@ИванПолищук-б3и]

marry me!

[@MyOnlineTrainingHub]

Taken, sorry.

[@ИванПолищук-б3и]

@@MyOnlineTrainingHub it's a pity