Pros Use This Technique to Avoid PivotTables
ฝัง
- เผยแพร่เมื่อ 1 มิ.ย. 2024
- 🔽 Download the example file here: www.myonlinetraininghub.com/s...
If you find PivotTables a bit daunting, then Tables are a great alternative
because they can also be filtered with Slicers to create an interactive report. And with the right functions you can aggregate the data while ignoring the rows that are filtered by the Slicer.
🎓RELATED LESSONS:
Formatting Slicers: • Hidden Formatting Tric...
Slicers for PivotTables: • Excel Slicers, EVERYTH...
Forcing Slicers to Single Select: • Force Excel Slicers to...
LEARN MORE
===========
🏫 LEARN MORE in my Excel courses: www.myonlinetraininghub.com/
📰 EXCEL NEWSLETTER - join 450K+ subscribers here: www.myonlinetraininghub.com/e...
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
📢 Please leave me a COMMENT. I read them all!
🎯 FOLLOW me on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ TIMESTAMPS
==============
0:00 Slicers for Excel Tables
0:15 Formatting data in a Table
0:50 Inserting & working with Slicers
3:31 Linking Slicers to Formulas
6:13 Limitations
#pivottables #dataanalysis #exceltips - วิทยาศาสตร์และเทคโนโลยี
![ทททททททท #27 [หลุยส์ เค โตมายังไง ณ สระบุรี]](http://i.ytimg.com/vi/CaKoYw_Xmtg/mqdefault.jpg)
I never knew that i could use subtotals with tables like this. you are one of the best.
So pleased you learned something new that you can use, Doug.
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.
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
Thanks for watching. Will try my best 😊
Yet another gem from Mynda!! Thank you 🙏🏽
Glad you enjoyed it! 🙏😊
Matchless as always Mynda. Source no doubt juicy enough of great info of Excel's priceless feature of Slicers
Thanks for your kind words and support 🙏🥰
these are soooo good. Two decades of using Excel everyday and i never touched this. Terrific info!
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
So wonderful to hear! 🙏😊
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! :-)
Glad we think alike, Geert 😊 I couldn't live without Clear All on my QAT!
Read my yesterday's tip and forget the QAT 🙂
Another Great Video/Lesson! Thanks Mynda!
Thanks for watching! Glad you liked it 😊
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.
Awesome to hear 🙏
Great tutorial, i liked! Thanks Mynda!
Thanks so much, Luciano!
She is unbelievably helpful with these vids.
Aw, so glad to hear that 🥰
The work is perfect and the video editing is awesome ❤
Glad you think so! 🙏🥰
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.
Awesome to hear. Great point about being able to show both the summary and detail at the same time.
You just saved me a ton of time!!! Thank you, thank you, thank you!
My pleasure!
Very helpful, you always amaze me 💯
Glad you enjoyed it! 🙏
Thnks for your tipps...helping a lot even for rookies like me...
Glad to hear it 🙏😊
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.
Thanks so much, Chris! Can't wait for PIVOTBY and GROUPBY. I wasn't in the lucky 50% unfortunately 😭
Neither was I Mynda :( I have watched Mike Girvin's videos and am jealous lol
Just to let you guys know, I was in the lucky 50%. I've heard it goes in order of importance......
@ricos1497 😁probably! @chrism9037 I haven't watched any videos on them yet. It's too painful 😁
You are the best! thank you for your content.
Thanks so much! I appreciate that! 🥰
Great idea and lay out!
Glad you liked it! 🙏
Wow! Very informative and very impressive presentation. I was impressed enough that it got me to both "like" and subscribe. 😀
Wow, thank you and welcome! 🙏😊
Smashed it, Mynda (of course)! Tables are the way to go. :)
🥰it was your idea, Jon, so thank you!
Thank you for this video!
My pleasure 😊
I leart a new function AGGREGATE from this video. Thanks a lot for sharing your knowledge 👍
Glad to hear that 😊
cant believe your still at 550k + subscribers, you are a genius!
Aw, you're too kind 🙏🥰 please spread the word!
Quite useful... congratulations!
Glad you think so!
Excellent video. I think this approach is much better than Pivot Tables.
Great to hear you'll be able to make use of it! 🙏
Simple and useful
Glad you liked it 🙏
Excellent content❤
Thanks so much, Tracy!
Dear Mam Nice approach ,thanks
Thanks so much!
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.
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: th-cam.com/video/Nbhd0B5ldJE/w-d-xo.html or take my Power Query course: www.myonlinetraininghub.com/excel-power-query-course
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
Mynda you're the best
Thanks for your kind words and support 🙏
well done!
Thanks so much! 😊
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.
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])
@@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.
This is something new technique instead of Pivot tables 👍👍👍
🙏 Have fun with it 😊
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
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
It's the new Microsoft 365 theme.
@@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
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 ?
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.
@@MyOnlineTrainingHub okay I'ma keep an eye on this for sure ! Thank you so much
Here's the video on the correct data layout: th-cam.com/video/CNlw1-Vh4cE/w-d-xo.html
@@MyOnlineTrainingHub thank you very much , you are the best 💜💜💜
Thanks❤❤❤
Pleasure 😊
❤️❤️❤️
Super 👍
Thank you 👍
Hola desde chile ..Una consulta que grabador de pantalla usas , saludos Luis
Hello from Chile..A question which screen recorder do you use, greetings Luis
hello! I use Camtasia Studio.
Lo vere esta muy bueno , que tengas una linda seman dese santiago Chile y Gracias por tu gran aporte a esta comunidad.@@MyOnlineTrainingHub
Amazing
Thank you! 🙏😊
This was really nice. How to create a search box for a slicer.? Can it be done in Tables, or not?
Not if it's the search box hack that uses PivotTables. But remember, the filter drop down buttons expose a search box for tables.
@@MyOnlineTrainingHub Thank You!
@ 2:40 Tip on clearing all filters, not using the mouse: press CTRL+SHIFT+L two times.
Thanks for sharing!
Do you take on Google Sheets jobs on the side ?
No, I don't do consulting, sorry.
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.
So pleased to hear that!
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
Great idea. Not easily, but soon we'll have PIVOTBY and GROUPBY that can 😊
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
You can't create a slicer for the headings. Slicers are for the items in a column.
@@MyOnlineTrainingHub thank you for guidance
"Clear All" on the QAT? Not on my QAT! Alt,A,C always!
😁thanks for sharing, Richard!
👍🏻👍🏻👍🏻
🙏😊`
How is the document saved so it can be sent to someone else?
It's just an Excel file so you can share it over email or via SharePoint, OneDrive, DropBox etc.
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) ;-)))
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.
I fully agree with you. Thanks Mynda@@MyOnlineTrainingHub
@@MyOnlineTrainingHubWOW, you're calling someone an Excel whiz, Mynda?! Those are high words of praise coming from one of the best in the business!
You can move tool tips out of the way!!! 😮
😁Oh, yes!
Actually "pros" will now be using PIVOTBY and GROUPBY!! Time for a video on these?
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.
😁 Yes! If I could just get my hands on them. Unfortunately, I wasn't in the lucky 50% 😭
@@MyOnlineTrainingHub Me neither 😭
Been playing with GROUPBY and PIVOTBY. Look good
@@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
She talk’s clearly
😕😏😏
To help, you can use the cog icon in the bottom right to slow down the playback speed and use subtitles.
@@MyOnlineTrainingHub Thanks a lot
marry me!
Taken, sorry.
@@MyOnlineTrainingHub it's a pity
Can’t get my teams to use tables and spill formulas… ‘didn’t copy the formula down’ excuse gets old quickly
🤦♀️frustrating!
What you 'know' from the past is often the greatest impediment when it comes to further progress!