7 Advanced PivotTable Techniques That Feel Like Cheating

  Рет қаралды 98,452

MyOnlineTrainingHub

MyOnlineTrainingHub

Күн бұрын

Пікірлер
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
❓Which one of these techniques do you like the most? Learn more about the Excel Dashboard course: bit.ly/advancedpt24course
@vidyabhushankumar5361
@vidyabhushankumar5361 3 ай бұрын
16 minutes well invested. Thank you so much for these EXCELlent tricks. Text in values was something which had kept me away from pivots, but now I am again a happy Pivot user :)
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Awesome to hear 🙏😁
@jimerlynsiao7578
@jimerlynsiao7578 57 секунд бұрын
So amazing! I'm happy to have found your channel.
@chrism9037
@chrism9037 3 ай бұрын
I always look forward to Tuesday mornings because I know a new video is coming out. Thanks Mynda this is great!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Thanks for your support, Chris 😁
@DCCConsults
@DCCConsults Ай бұрын
I wonder that the way you share your knowledge including the workbook. Thanks Mynda, this helped me a lot.
@MyOnlineTrainingHub
@MyOnlineTrainingHub Ай бұрын
You're welcome! I'm glad it helped.
@jon99long
@jon99long 3 ай бұрын
Using DAX to add labels is amazing. I’m working on a project that requires that. Now I can stop scratching my head on how to do it. Thanks from Sydney!!!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
So pleased to hear that! 😁
@rdatta
@rdatta 27 күн бұрын
An excellent set of tips and tools. Well done! Thank you.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 27 күн бұрын
Glad it was helpful!
@mcwahaab
@mcwahaab 3 ай бұрын
These tips are both helpful and insightful. Thank you, Mynda.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
That's wonderful to hear!
@jihadalbustanji3760
@jihadalbustanji3760 22 сағат бұрын
Amazing input ... wishing you a very Merry Christmas and Happy New Year 2025 ...
@MsSilver2000
@MsSilver2000 3 ай бұрын
Point no6 is amazing, as usual your videos are stunning, Clear teaching with sample data. Thanks for sharing the tricks and tools
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Thanks so much! Glad you liked number 6. 😁
@56Nine
@56Nine 3 ай бұрын
NO!!! You can't make me choose the "best" one. What an amazing set of tips...I've been missing out of so many features, just by not seeing an application. Thank you for putting this together!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
😁so glad you liked them 🙏
@brighttriangle
@brighttriangle 3 ай бұрын
Great video, as always. Tip number 6 was new to me. Your use of the CONCATENATEX DAX function is amazing. Thank you for sharing.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Glad you enjoyed it! 🙏
@ExcelWithChris
@ExcelWithChris 3 ай бұрын
Great tips again, especially using the DAX formulas. Thanks from South Africa.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Cheers, Chris!
@yertletheturtle5843
@yertletheturtle5843 3 ай бұрын
I always learn something new in your videos!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Glad to hear it!
@lawrencelim8324
@lawrencelim8324 27 күн бұрын
Still learning...thanks dear
@MyOnlineTrainingHub
@MyOnlineTrainingHub 27 күн бұрын
You’re welcome 😊
@HelenoPaiva
@HelenoPaiva Ай бұрын
Techinique 1 is a specific customization. It is only good if you are working alone with the data. If you are collaborating with others or need to handover sheets, it is preferable to create another column on your raw data.
@MyOnlineTrainingHub
@MyOnlineTrainingHub Ай бұрын
I guess if you're the only one who wants this grouping, then yes, but if it's a company standard, then it doesn't prevent collaboration as such.
@JamesBond-qg5gx
@JamesBond-qg5gx Ай бұрын
Great, very informative and helpful
@MyOnlineTrainingHub
@MyOnlineTrainingHub Ай бұрын
I'm glad you found it helpful!
@madlarch
@madlarch 2 ай бұрын
Well, I'm going to have to do a bit of playing around, but I think that technique #6 might well solve a problem that I've been stuck on for quite a while now. Some of the others look like they might help solve some other problems. So thank you VERY much for this !!!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Wonderful to hear!
@rakeshsaraf5139
@rakeshsaraf5139 2 ай бұрын
Thanks for these wonderful tricks
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Most welcome 😊
@leerv.
@leerv. 3 ай бұрын
Holy moly, Mynda, where has tip 1 (grouping pivot table rows) been all my life?! I always added a bucket column to the data or more recently, a column in the data model. This is so much better!!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Glad you can make use of that technique 😁
@leerv.
@leerv. 3 ай бұрын
@@MyOnlineTrainingHub the trick with having all data in the pivot and using the filter to split it out is also really clever. I never ever would have thought of that!
@regme4life191
@regme4life191 3 ай бұрын
Thank you Mam for this most useful and informative video.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
So nice of you 😊
@brianwied3702
@brianwied3702 3 ай бұрын
Very helpful, hoping they come out with power pivot for the Mac version of excel
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Thank you! Not much chance of Power Pivot for Mac anytime soon, sorry.
@hanshoogland7452
@hanshoogland7452 3 ай бұрын
You show me new stuff (I didn't know) every single week Mynda, 🎉 thank you so much!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
I'm so glad to hear that!
@JB-nv9fn
@JB-nv9fn 2 ай бұрын
Absolutely brilliant. Thanks for sharing. 😊
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Glad you enjoyed it 😊
@felixdelarosa9266
@felixdelarosa9266 3 ай бұрын
excellent video. You are the best. in the Dashboard you made once you used the honeycomb. What is the hexadecimal code for that color of honey? I await your response please
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Glad you liked it! I'm not sure what colour you're referring to. Best to download the file and then you can inspect the colours yourself.
@Vlog88999
@Vlog88999 3 ай бұрын
Thank you for your efforts 🎉🎉
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
It's my pleasure 😊
@ahmedameenkhalaf
@ahmedameenkhalaf 3 ай бұрын
Text in Values is an amazing add
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Glad you like it 🙏
@karolinab9749
@karolinab9749 2 ай бұрын
I did not know the trick to count unique values in a pivot table - thank you!!!😊
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Glad you discovered something new 😊
@NAWAZSHAIKH-t6m
@NAWAZSHAIKH-t6m Ай бұрын
Last two was awesome 🎉❤
@MyOnlineTrainingHub
@MyOnlineTrainingHub Ай бұрын
So pleased you liked them!
@vikaasb2016
@vikaasb2016 Ай бұрын
Earlier I thought other videos which were the best but this stands out
@MyOnlineTrainingHub
@MyOnlineTrainingHub Ай бұрын
Thanks so much!
@zachcross8868
@zachcross8868 2 ай бұрын
I love these tips, especially the text labels. I'm trying to use Technique No. 4 and have a problem. I have a pivot table and want to generate a worksheet for each filter. However the "Show Report Filter Pages" is greyed out. I'm using a pivot table from data model with relationships. Could that be why it's not available?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Yes, the show report filter pages feature is not available when you load the data to the data model.
@KimzCraftz
@KimzCraftz 3 ай бұрын
Text in values and number formatting - both great, but number formatting will be used a lot!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Awesome to hear!
@olakunleoriyomi
@olakunleoriyomi 2 ай бұрын
You are a blessing to my smartness in Excel usage in ffice. Once I'm lost, I'll just go to your page and reconnect myself 😊. Many thanks for your teachings 🙏
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
That's wonderful to hear 🙏
@tanveerabbas3271
@tanveerabbas3271 3 ай бұрын
great as always😅
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Thank you so much 😀
@salilgangal5913
@salilgangal5913 2 ай бұрын
Excellent !!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Thank you! Cheers!
@mipe5227
@mipe5227 3 ай бұрын
Hi Mynda, I've been looking for something like in tip no. 4 for a long time... Is there a way to lock the filter as the different recipients of the filtered tables should not be able to see the rest of the data? And by the way: I absolutely ❤your videos! Please keep going! Thank you so much!!!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Thank you! You can protect the sheets (Review tab > Protect sheet) to stop recipients from editing the PivotTables. Make sure you disable "Select Locked cells".
@georgewilliams6139
@georgewilliams6139 2 ай бұрын
Thanks lovely data lady
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Glad it was helpful!
@georgewilliams6139
@georgewilliams6139 2 ай бұрын
@@MyOnlineTrainingHub I should say Excel Data Lady
@BookwormFUT
@BookwormFUT 2 ай бұрын
Glad I found this channel. I basically live in Excel, so these tips are extremely useful. Also, off-topic comment, but that's a great haircut.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
😁thanks so much x 2!
@shoppersdream
@shoppersdream 3 ай бұрын
Very Nice, thanks! I was planning to give my report to my supervisor tomorrow but now I think I will add those Data Bars before sending my report.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
So pleased you can make use of the data bars 😊
@shoppersdream
@shoppersdream 3 ай бұрын
@@MyOnlineTrainingHub I tried it today but Data Bars didn't show on my Show Filter Pages. My numbers were not very big so Data Bars didn't look nice. Many numbers were like 1 to 10 and it didn't give any noticeable bars for these small numbers. It definitely looks nice if you have bigger numbers. Thanks
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Yes, if you have large disparities in values, then the small bars will get lost. Unfortunately, no formatting comes across when you create report filter pages, but once set up, you can refresh those PivotTables and the formatting sticks.
@rokaskrisciunas6015
@rokaskrisciunas6015 2 ай бұрын
Thanks for great tips! I didn't even know DAX is used in excel!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Yes, DAX was in Excel before Power BI even existed 😁 The data model/Power Pivot in Excel is the same as the one found in Power BI, so those skills are transferrable. Note: not all functions in Power BI are available in Excel.
@adelgonalves7839
@adelgonalves7839 3 ай бұрын
Mynda you're so EXCEllent at what you do, you always provide us with best content. I really wish I could take one of your courses unfortunately I can't pay for it because of the country I'm in. 😢. Thanks a lot for all these high quality content
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Thanks for your kind words!
@martinargimon730
@martinargimon730 3 ай бұрын
Excellent tips. You are a ⭐️. Rgds. Martin
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Thanks so much, Martin! Glad you liked them 😁
@moonilyas
@moonilyas 3 ай бұрын
Yeah, great 7 Advanced Pivot table techniques, Thanks!!!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Glad you liked them 🙏
@anandsharanpandya6836
@anandsharanpandya6836 19 күн бұрын
Thanks
@MyOnlineTrainingHub
@MyOnlineTrainingHub 19 күн бұрын
Welcome 😊
@ashishshiriskar123
@ashishshiriskar123 3 ай бұрын
Superb Video😊
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Thanks 🤗
@arkai59
@arkai59 3 ай бұрын
I am curious of how you would handle comments updates per row with updated data everyday in an automatic way (example: status per open order, until the order is closed and doesn't show anymore in source data)
@jgs343
@jgs343 3 ай бұрын
Probably using a macro that detects what is in the data column and then a movement. I think you can have macros run every x amount of time. I use ai for help building mine
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
There's not enough information here for a specific answer, but you could have a status column that says 'open/closed' and then filter the PivotTable to exclude 'closed' items. If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@TrainWrecks-b5p
@TrainWrecks-b5p Ай бұрын
why doesnt excel let you set default number formats?
@MyOnlineTrainingHub
@MyOnlineTrainingHub Ай бұрын
You can with Power Pivot PivotTables aka the data model, but not with the original/regular PivotTables.
@joshuaholland3705
@joshuaholland3705 3 ай бұрын
It's like magic! Straight fire!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
😁🙏
@JamesBond-qg5gx
@JamesBond-qg5gx Ай бұрын
Struggling to sort the values in my pivot table but no success. I have item codes, item descriptions and sales value. Appreciate your support. Thanks
@MyOnlineTrainingHub
@MyOnlineTrainingHub Ай бұрын
PivotTables sort from left to right, therefore you need to put the column you want to sort by first in the row labels. If you're still stuck, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@mogarrett3045
@mogarrett3045 2 ай бұрын
excellent
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Many thanks!
@KevinLongUSA
@KevinLongUSA 3 ай бұрын
That number formatting trick!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
😁
@mohammeddapwan6226
@mohammeddapwan6226 3 ай бұрын
thank so much for amazing vedio
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
So pleased you liked it 🙏
@tomhaase1386
@tomhaase1386 3 ай бұрын
Rhis was for me 16 minutes worth spend. Text in values was new to me , as well as adding to data model to get distinct values. Very well done 😅
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Thanks so much, Tom!
@bestoptimate
@bestoptimate 2 ай бұрын
Hi, My problem is different from the topic of this video, but I have to connect with you for the solution of a small problem that I am facing. When I define a named range and deselect and select the range-address, the marching-ants around the defined range are not displayed. Also, when I select an already defined named-range from the list, the cursor does not follow and go to that range. This problem is only in one file that contains VBA code. I don't know whether I have mistakenly disabled that feature. I am using Excel 365. Please do reply, it is a pain for me.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@druzicka2010
@druzicka2010 3 ай бұрын
Omg these technics ! 🤩 i think i gonna suffer because i don't enough know pivot table functions and practice... but i like challenge so... thanks.🌹
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Great to hear! 😁
@Vlog88999
@Vlog88999 3 ай бұрын
Can you provide this excel file please
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
The link for my file downloads is always in the video description. Here it is again for your convenience: bit.ly/advancedpt24file
@Vlog88999
@Vlog88999 2 ай бұрын
@@MyOnlineTrainingHub thank you for your help
@nazarkamal8831
@nazarkamal8831 3 ай бұрын
❤❤❤❤❤ great 👍👍👍
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
🙏😁
@DavidKing-wk1ws
@DavidKing-wk1ws 3 ай бұрын
In reality excel could be considered a formulaic decision tree :)
@cornereye842
@cornereye842 3 ай бұрын
I recently got me a desktop and am trying to learn Excel to freelance and to help me in near future jobs (if God blesses me with one). But for now I thought of freelancing and don't know much how to start. I don't know any Excel freelancers to seek guidance from. I don't know how to I evaluate my skills in Excel. May I ask for some suggestions? *English is not my first language, so I'm sorry if it's hard to read.*
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
You can get up to speed with the basics with our free Excel course: www.myonlinetraininghub.com/free-registration I hope that gets you started and from there you can build up your skills until they're more comprehensive. There are various online tests you can do to evaluate your skill level that you can find with a Google/Bing search.
@Tomg2525
@Tomg2525 3 ай бұрын
#7 Number Formats as it will save a lot of time versus constantly updating.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Glad you can make use of it, Tom!
@stanTrX
@stanTrX 3 ай бұрын
Conditional formatting in my pivot doesnt work at all
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Conditional formatting in a PivotTable only works on the value fields. You cannot apply it to row or column labels, unless you do so manually, i.e. not using the PivotTable regions.
@stanTrX
@stanTrX 3 ай бұрын
@@MyOnlineTrainingHub thank you now it worked. But i think its not possible to apply it for the whole row based on the value field cond. Formatting rule
@abidurrahmanabir7745
@abidurrahmanabir7745 2 ай бұрын
Oh my god 😱😱😱 i m looking for this video... Btw m still trying to analyze your video😢😢😢😢
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Hope you can make use of these techniques.
@rehanbaig71
@rehanbaig71 18 күн бұрын
👍👍👍
@MyOnlineTrainingHub
@MyOnlineTrainingHub 18 күн бұрын
🙏
@tibibara
@tibibara 19 күн бұрын
"That's one thing that drives me crazy is big numbers wo comma separators..." 🤣🤣🤣🤣🤣 similalry to me 😅🤣🤣🤣🤣
@MyOnlineTrainingHub
@MyOnlineTrainingHub 19 күн бұрын
I know, right!?
@tibibara
@tibibara 19 күн бұрын
@@MyOnlineTrainingHub yes, and thanks for this tutorial ;-)
@ChandBabu-xz5kd
@ChandBabu-xz5kd 3 ай бұрын
Is it worthy because google recommended me
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Hope you enjoyed it!
@JamesBond-qg5gx
@JamesBond-qg5gx 27 күн бұрын
Is it possible to have two data bars against two columns values in one column
@MyOnlineTrainingHub
@MyOnlineTrainingHub 26 күн бұрын
No, but you could align them beside each other so they appear to be in one column.
@cscarpulla1
@cscarpulla1 3 ай бұрын
0:03 That iMac with no power cord.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
😂
How to easily automate boring Excel tasks with Power Query!
17:10
MyOnlineTrainingHub
Рет қаралды 1,6 МЛН
PivotTable Tricks That Will Change the Way You Excel (Free File)
17:47
MyOnlineTrainingHub
Рет қаралды 155 М.
Cheerleader Transformation That Left Everyone Speechless! #shorts
00:27
Fabiosa Best Lifehacks
Рет қаралды 16 МЛН
REAL or FAKE? #beatbox #tiktok
01:03
BeatboxJCOP
Рет қаралды 18 МЛН
Quando A Diferença De Altura É Muito Grande 😲😂
00:12
Mari Maria
Рет қаралды 45 МЛН
The Excel Tool That Does What PivotTables Can’t (File Included)
13:59
MyOnlineTrainingHub
Рет қаралды 180 М.
Try The DGET Function Instead of INDEXMATCH & XLOOKUP
12:29
Kenji Explains
Рет қаралды 115 М.
5 Conditional Formatting Hacks That Will Blow Your Mind
11:16
Mike’s F9 Finance
Рет қаралды 9 М.
High-Income Excel Skills Worth Learning in 2025 (Free File)
29:19
MyOnlineTrainingHub
Рет қаралды 407 М.
The Excel Functions Almost Everyone Overlooks (Better Than SUMIFS)
10:29
MyOnlineTrainingHub
Рет қаралды 114 М.
Interactive Excel Dashboard Tutorial in 3 Steps (+ FREE Template)
18:57
MyOnlineTrainingHub
Рет қаралды 152 М.
Make Excel Formulas Dynamic with the This Trick
10:54
Kenji Explains
Рет қаралды 240 М.
10 Excel Formulas That Will Set You Apart (+Cheat Sheet)
18:04
MyOnlineTrainingHub
Рет қаралды 352 М.