PivotTable Tricks That Will Change the Way You Excel (Free File)

Поделиться
HTML-код
  • Опубликовано: 24 дек 2024

Комментарии • 263

  • @MyOnlineTrainingHub
    @MyOnlineTrainingHub  9 месяцев назад +7

    ❓Which one of these tricks do you like the most?
    PivotTable course: bit.ly/pivottablecourse24

    • @inekesmit7030
      @inekesmit7030 9 месяцев назад +2

      The (Mr. Excel) trick with adding filters to other columns 🙂

    • @children3148
      @children3148 9 месяцев назад

      I liked then all but the GetPivot example was my favourite

    • @occelusorac
      @occelusorac 9 месяцев назад

      This Filter trick is just a killer. Thank you Mynda

    • @ziggle314
      @ziggle314 9 месяцев назад

      That Mr. Excel trick is gold. The GetPivot example is right up there too.

    • @occelusorac
      @occelusorac 9 месяцев назад

      I LOOOOVE the last trick: PVT Analysis > Options > Show Report Filter Pages
      Thanks again

  • @OleksiyKozubenko
    @OleksiyKozubenko 12 часов назад

    Reporting pages was the real insight from that video. Thank you so much!

  • @skyking2202
    @skyking2202 9 месяцев назад +17

    I have learned SO much about Excel and PQ from you. But I've actually learned more about how to teach technical content. Your approach is the master class in how to structure the flow of a technical feature, create relevant examples, and provide robust context. Simply the best I have ever seen.

  • @uninspired3583
    @uninspired3583 7 дней назад

    I spend a lot of time in spreadsheets, this channel has poured rocket fuel over everything i do, phenomenal work!!

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  7 дней назад

      Wow! Congratulations on your success. So glad I could help 🙏

  • @viktorasgolubevas
    @viktorasgolubevas 9 месяцев назад +4

    The 🍕 example is my winner!
    I somehow overlooked these amazing features of PT:
    Report Connections for Slicers,
    Filter Connections for PivotTables.
    ... and what a brilliant idea to tag/classify sheets with emojis 😍

  • @tashadj87
    @tashadj87 7 месяцев назад +5

    Without a doubt one of the best excel teachers on you tube, so clear

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  7 месяцев назад

      Wow, thank you so much!

    • @tashadj87
      @tashadj87 7 месяцев назад

      @@MyOnlineTrainingHub my pleasure, I have learnt so much and your sample files are excellent

  • @c.l4219
    @c.l4219 4 месяца назад

    4 minutes into the video and directly decided to follow your channel... Something that I don't do that often.
    Great explanation and so helpful. Calm and clear, no music, no excited screaming or sales pitch style of talking (God, so many you tubers have that...)
    I could listen to you all day!
    Will use everything right away on my huge list of data!

  • @chrism9037
    @chrism9037 9 месяцев назад +4

    Excellent Mynda. I knew about the timeline slicer but always forget to use it!

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Cheers, Chris 🙏😁 the only downside of the timeline slicer is it’s big!

  • @mogarrett3045
    @mogarrett3045 9 месяцев назад +4

    excellent as always Mynda...thank you
    Mo

  • @alexanderadamov7883
    @alexanderadamov7883 9 месяцев назад +23

    Great video, thank you. Mexico is still North America, though :)

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад +7

      Good job I’m teaching Excel and not geography 🤦‍♀️😆

    • @juanes1235
      @juanes1235 9 месяцев назад

      Everything south from Texas is Mexico.😂😂

    • @delemtube
      @delemtube 8 месяцев назад

      Poor mexico. We in South America say they're Middle-America (mexico to panama). Schroedinger's country's

    • @mikkib17
      @mikkib17 3 месяца назад

      I find it useful if one tries to add a correction, they should present that information in an easy to understand format. If one is trying to correct an expert on a public forum. They should do so using proper grammar and syntax. To be blunt you typed “Mexico is still North America, though” now grammatically speaking you are missing words to clarify what you are trying to say. Also, your use of punctuation is indicative of an individual that is using Google Translate. While this may be a good usage in Spanish the comma before though is not needed in English. Now to add clarity. To say this properly. This could be used. Mexico is part of the North American continent.
      However, if you heard this correctly the way the expert presented her information. She inferred that she was grouping the countries by region. Which includes Mexico being grouped with Canada and the United States in the same region. With direct comparison of opposition to the counter European countries of France and Germany.
      Lessons to be learned,
      1. Gather your information properly.
      2. If you are going to try and convince a kind expert of their minor vague ideology. Make sure that you do not come off as uneducated yourself.
      3. Due to your lack of translation proficiency, inferential understanding, ignorance and misunderstanding of the spoken and unspoken common sense understandings in your ability to translate English into Spanish then back to English. It would be better if you did not point out a splinter in your neighbors eye when you have a pole sticking out of your head. If you have nothing intelligent to add to an argument stay silent. Lastly, no man knows another man is a fool unless they open up their mouth.

    • @mikkib17
      @mikkib17 3 месяца назад

      @@MyOnlineTrainingHubyou keep being amazing. Note, you made your point clear. You were grouping the countries by region.❤

  • @ivanbork4175
    @ivanbork4175 9 месяцев назад +3

    You are one of the weekly highlights, and especially the formula to tell “Only select one item in the slicer” is a good idea to improvement.
    The other stuff I know about, or have been using, but it´s always good to have it refreshed or see new ways to use the features.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Thanks so much for your support 🥰 glad you liked the Slicer trick 😉

  • @al3xj
    @al3xj 8 месяцев назад

    hi Mynda, the navigation on side left when you were navigating is something I've never seen before too - ideal for complicated progress claims and invoice excel reports

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  8 месяцев назад +1

      The navigation pane is super handy. You can enable it from the View tab of the ribbon.

  • @BrezzyGoodvibes
    @BrezzyGoodvibes Месяц назад

    You have no idea how much you are saving my life right now! I have a huge data project due next week and needed help making my lead sheet dynamic. Thank you SO MUCH ❤

  • @Shhhpeppys
    @Shhhpeppys 3 месяца назад

    Wish I had this when I was working. Oh the joy I would have had when month-end came around. So powerful yet simple. Thanks for the education. Your vids are outstanding.

  • @steveduplessis5285
    @steveduplessis5285 9 месяцев назад

    This is a great video with loads of valuable information. Your presentation style is very easy to follow. Really liked learning about the option to apply conditional formatting to a pivot table. Thank you.

  • @munim777
    @munim777 8 месяцев назад +1

    Mynda 5:49 you can also select more options, and select the cell T7 to sort on Dec. I really liked trick 10. Thanks

  • @deebradford4026
    @deebradford4026 2 месяца назад

    adding a filter to the pivot table. Wow, how many times have I struggled with sorting. And to find out how simple it was to add the filter. Now hoping I'll remember it next time I need it!

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 месяца назад

      Great to hear you can make use of the filters feature 😊

  • @rajanghadi1982
    @rajanghadi1982 9 месяцев назад

    First time I thought nothing was new for me. But you nailed it. Click for not showing details was new for me.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Thanks for watching. Glad you discovered something new 😁

  • @patneb
    @patneb Месяц назад

    I have learned a ton from this video. And your teaching is top. Grazie

  • @miguelsanches6463
    @miguelsanches6463 8 месяцев назад

    Another great video Mynda!!! Put the filter button on the columns area really a great tricky!!! Thanks for contribute to my Excel growing ! ;)

  • @kumarsb
    @kumarsb 9 месяцев назад

    Thank you for this. It's a treasure trove of Pivot Table tricks in a short time frame.

  • @Fabi_terra
    @Fabi_terra 8 месяцев назад

    Amazing and easy to follow tutorial! Thanks so much, Mynda! 😍

  • @DineshKumar-xl2ug
    @DineshKumar-xl2ug 4 месяца назад

    Your Explanation is Top Noth and straight to the Point. Great Teaching Skills.Thanks a lot

  • @MrsJerjev
    @MrsJerjev 6 месяцев назад

    Thank you for your patience in teaching the me and the audience

  • @gerbherb8215
    @gerbherb8215 8 месяцев назад +1

    7:00 I would absolutely recommend doing this differently: it is not good practice to just select cells in a pivot table instead of using "apply rules to" to ensure that your conditional formatting still works when your underlying data changes.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  8 месяцев назад

      Good point. I didn't click the PivotTable icon that shows up in the bottom right after applying the formatting that allows you to specify which cells you want it applied to. If you expect your data to change, then it's a good idea to complete that step.

  • @LuisCarlosChavez717
    @LuisCarlosChavez717 8 месяцев назад

    Thank you, Mynda. The tutorial video was formidable.

  • @flaviogarlatticosta
    @flaviogarlatticosta 8 месяцев назад

    Thank you for your impeccable explanations, clear even for those who, like me, are not English speakers. Great.

  • @schoolfriendsteve663
    @schoolfriendsteve663 8 месяцев назад

    Want to thank you again for such excellent content. I keep all your emails after watching so I can go back when I need to find the one tidbit that I need. Love integrating the use of Slicers!

  • @chta2010
    @chta2010 9 месяцев назад

    Thanks for your tipps. You always learn somehing new in Excel, it's a never ending story ;-)
    The last feature, creating different sheets based on your selection is a thing that comes in handy for me just the right moment!

  • @osoriomatucurane9511
    @osoriomatucurane9511 9 месяцев назад

    Awesome tutorial on pivot table. Crystal Clear an Concise.

  • @humansizedaperture
    @humansizedaperture 9 месяцев назад

    Thank you for giving a good use case for when you need calculated fields: “instead of adding a column”!

  • @rpopecpa
    @rpopecpa 9 месяцев назад

    Hi Mynda, great video. I'm a long time fan of your channel. I love the timeline and the getpivotdata tips!!

  • @martinargimon730
    @martinargimon730 9 месяцев назад

    Great video Mynda. Lots of tips. The timeline’ slicer is the coolest !

  • @AkramKhan.HR4U
    @AkramKhan.HR4U 9 месяцев назад

    Awesome.. One of the best excel videos I have ever seen

  • @999_pan
    @999_pan 9 месяцев назад

    quick win on sorting in pivot tables (rows OR columns): - overtype the entry with the one you want to appear in that position - for instance, overtype USA with Germany, and the rows resort accordingly... same applies to columns, overtype with the valid label as necessary and they magically re-sort..
    Even better, that trick works in groups and subgroups

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Yes, that’s another cool way to sort 👍 Thanks for sharing 🙏😁

  • @SothearithKONGMrMuyKhmer
    @SothearithKONGMrMuyKhmer 9 месяцев назад +1

    Awesome! Thanks so much! 🎉

  • @vinnyjones1071
    @vinnyjones1071 8 месяцев назад

    Great teaching and learning so much from your videos. Thank you.

  • @hectorbello2104
    @hectorbello2104 7 месяцев назад

    In the calculated field, have you figured out a way to include functions into the formula? For instance if I want to calculate the age compared to today() I can't as these types of formulas are not allowed. I had to resource to calculate those in the source data to add it to the pivot table. Your thoughs on this are greatly appreciated. Thanks

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  7 месяцев назад +1

      If you need functions that aren't supported by regular PivotTables, you can use Power Pivot (the data model) and write DAX measures. I explain what Power Pivot is on my course page: www.myonlinetraininghub.com/power-pivot-course

  • @aprashant1048
    @aprashant1048 9 месяцев назад

    thanks for sharing, except the last one, have been using the remaining ones quite actively. Thanks again!!

  • @mahmoudamin6260
    @mahmoudamin6260 2 месяца назад +1

    شكرا

  • @deeperholiness996
    @deeperholiness996 3 месяца назад

    I always see the navigation in your videos. Please teach us how to range the Navigation in Excel

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 месяца назад

      It's coming soon! 😉in the meantime, you can turn it on via the View tab > Navigation in Excel 365.

  • @vikastiwari7134
    @vikastiwari7134 9 месяцев назад

    Love from India, Mr excel trick was wonder full & awesome video

  • @christines5430
    @christines5430 9 месяцев назад

    New subscriber. I just discovered your channel yesterday and have already started a couple of your tricks. Thank you!

  • @AMANZ2010
    @AMANZ2010 6 месяцев назад

    Thanks❗ This helped clear some doubts 😀
    I have a question for Pivot Table chart when using slicers. How to stop Excel from "forgetting" chart formatting🤔
    For example: I have data (numbers for Jan December) for years 2023 and 2024
    On graph, data for 2023 is on primary axis and 2024 is on secondary axis
    I have changed the Series overlap and Gap width so that 2023 and 2024 data columns overlap
    When slicer is used to select off 2024 and then select back on, the secondary axis is disabled and both years default back to primary axis

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  6 месяцев назад

      Remove all filters, then apply the formatting. However, if the filtering removes a series altogether, in some cases the Pivot Chart cannot retain the formatting. In which case, you can create a regular chart from the PivotTable: www.myonlinetraininghub.com/create-regular-excel-charts-from-pivottables

  • @whazee
    @whazee 9 месяцев назад

    "Show report filter pages" was a new one to me! 🙂
    I think I can think of some uses for that - although it would be more helpful with Tables in my case. (normally I'd just copy the TAB and amend the data in each)

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад +1

      You can always choose the “tabular layout” and repeat item labels to get the PivotTables in a table style/layout 😉

  • @Vlog88999
    @Vlog88999 8 месяцев назад

    Thank you for your efforts ❤❤

  • @sharmarudra
    @sharmarudra 8 месяцев назад

    Regarding sorting by the last column, you can simply right click and sort ascending or descending.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  8 месяцев назад

      and why have I never noticed that? 🤔😆 Thanks for sharing.

  • @sahralsahri7180
    @sahralsahri7180 5 месяцев назад

    fantastic all time. Thank you

  • @guitars0206
    @guitars0206 9 месяцев назад

    They were all great tips, thank you.

  • @hafiz786nasir
    @hafiz786nasir 9 месяцев назад

    Thanks for sharing the great video like always

  • @chahineatallah2636
    @chahineatallah2636 9 месяцев назад

    Great video , yes pivot tables are amazing ,
    I use conditional formatting , slicers and show values as (my favorite one)
    The trick by Mr excel is new to me I think it’s very useful

  • @NareshSen
    @NareshSen 9 месяцев назад +1

    Thank you for the valuable information.

  • @omarnader776
    @omarnader776 9 месяцев назад

    all are amazing specially number 5 of slicer connection

  • @AtoZTrivia
    @AtoZTrivia 5 месяцев назад

    fantastic as usual, thank you for all your insights

  • @joesmith4254
    @joesmith4254 8 месяцев назад

    Example 8: Would be great if it was possible to see the difference between years.
    Is it necessary to use PowerPivot and DAX to do that or is there a method to see change year-on-year?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  8 месяцев назад

      You can change the grouping to Years and then it will calculate the difference between years.

    • @joesmith4254
      @joesmith4254 8 месяцев назад

      @@MyOnlineTrainingHub Thanks, but it's not possible if I want to keep both Year and Month?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  8 месяцев назад

      In that case you'd have to use the data model/Power Pivot and write a DAX measure.

  • @TheMrMishutka
    @TheMrMishutka 9 месяцев назад

    Good content, thanks. You rather slipped by using multiple values from the pivot table slicers. I found it quite hard to get a range that could be used in formulae (however restricted). I eventually came up with =OFFSET(Pivot_Table_Range,,,COUNTA(Pivot_Table_Range),1) where the Pivot_Table_Range is the maximum size your slicer can be (maybe with a couple of extra rows for growth). Don't know if you or others have found a better way than this

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      OFFSET will return a range of cells, but this list is probably more useful as an array of values but then there isn’t a lot of functions that will work with an array of values, so the application is more limited and less useful, and more complex, hence why I didn’t cover it in this video. A topic for another time 😁

  • @yamilvogeld.2385
    @yamilvogeld.2385 8 месяцев назад

    wow great video!! trick n°3 is just OP thanks! A LOT!

  • @birgitjohannes6804
    @birgitjohannes6804 9 месяцев назад

    Hi Mynda, great video as always.
    One question to your last tip "Report filter pages", which I already knew.
    Everytime I have to create a country report and a dashboard with slicers and charts I get the additional request to send this to the regional managers or even every single seller but only with the data for their own territory. Do you have an idea how to create these regional reports in a simple and fast way?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      I’d use Power BI to automate this. You can use Row Level Security to control who can see what data, enabling you to build one report for many users. Here is an introduction to Power BI: Say GOODBYE to Excel Limitations with Power BI (FREE File)
      ruclips.net/video/gOs7EC-FebE/видео.html
      I cover Row Level Security in my comprehensive Power BI course: www.myonlinetraininghub.com/power-bi-course

  • @notesfromleisa-land
    @notesfromleisa-land 9 месяцев назад

    The filter trick is wowza!

  • @learningwithmaliha
    @learningwithmaliha 9 месяцев назад

    Thank you for this informative video Myanda, I like the Get pivot table utilization and Slicer one.

  • @Anoxe100
    @Anoxe100 9 месяцев назад

    Slicer, conditional formatting makes pivot table more powerful also pivot table have options to enter formulae so that we do not have to add an additional column for our calculation.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Not sure I follow how conditional formatting reducing the columns to put formulas in 🤔

    • @Anoxe100
      @Anoxe100 9 месяцев назад +1

      Sorry I was not referring to conditional formatting reducing columns. I was referring to the additional column where sales minus cogs can be calculated via pivot tables.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Ah, thanks for clarifying. Glad you found these tips helpful 😀

  • @PritamPaul717
    @PritamPaul717 6 месяцев назад

    Thanks a lot mam ......

  • @firmaevents9594
    @firmaevents9594 9 месяцев назад

    Wow wow wow. What a video and what a tips and trick. I adore you. To be honest only number 6 i did not understand it alot. But the remaining ones are clear. If i stay like 100 year. I could not know them

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Thank you so much! I recommend you download the file and inspect how number 6 is structured to better understand it. If you still have questions, you can reach out via email.

  • @janelenbaas4226
    @janelenbaas4226 8 месяцев назад

    Thanks again Mynda, I’m learning a lot from your videos. I am struggling with running totals in pivot tables. Our system provides me with a list of records, each of them containing a date stamp, a project id, a number of hours spent on a project, the spendings, hourly rate etc. Now I want to build a monthly report that shows the list of actual projects, including the hours spent in the month and from the beginning of the year (year to date). How could I do that? It seems that for a running total a date field is required. However, I just want the actual month name in the title. Is there a way to fix this?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  8 месяцев назад +1

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

  • @ExcelWithChris
    @ExcelWithChris 9 месяцев назад

    Some great tips. Greetings from South Africa.

  • @JJ_TheGreat
    @JJ_TheGreat 9 месяцев назад

    4:22 So what is the difference between Calculated Fields and Measures? Thanks!

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад +1

      Calculated fields are not available in the data model/Power Pivot, whereas measures are. And likewise, measures are not available for regular PivotTables. In other words, measures and calculated fields are the same thing but for different types of PivotTables, although measures are more powerful.

  • @dccd673
    @dccd673 8 месяцев назад

    Do you have a video on changing the Quarters to match a business’s fiscal year? I need the Q1 to begin on April 1. How do I do that?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  8 месяцев назад

      Yes, here you go: www.myonlinetraininghub.com/excel-convert-dates-to-fiscal-quarters-and-years

    • @dccd673
      @dccd673 8 месяцев назад

      @@MyOnlineTrainingHub I watched the video but I got an error and this was selected "Month[@Date". I typed the exact formula you showed. My table is formatted and the date columns are categorized as "date" already.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  8 месяцев назад

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

    • @dccd673
      @dccd673 8 месяцев назад

      @@MyOnlineTrainingHub I did it, I was actually missing a comma before the first 4 🙈 It’s all good now, thanks!

  • @TheJoshtheboss
    @TheJoshtheboss 9 месяцев назад

    Great content as usual. Thanks 👍

  • @evelynnwogu9784
    @evelynnwogu9784 9 месяцев назад

    Trick number 10 had my eyes going huh! How come I never knew that! Thanks Myanda

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Glad you hung in to the end and discovered something new 😁

  • @pinkmoon9085
    @pinkmoon9085 4 месяца назад

    Could you please do an updated pivot example for where there is employee data with multiple rows of different pay codes for each employee. I work in payroll, and I'd love to see an example video of the best way to summarise data, please, and thank you.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад

      I'd be using a PivotTable for sure with that data. If you have a specific question we can help you in our forum where you can post your question and sample Excel file and someone can help you further: www.myonlinetraininghub.com/excel-forum

  • @user-ed7zd5dl7u
    @user-ed7zd5dl7u 7 месяцев назад

    Hi, thank you for all your help! I have a problem that's driving me crazy. When I use measures in pivot tables I see the entire dataset once I double click to get the summaries. I end up doing groups in power query which result in my reports being huge… Is there a way to limit the summaries in pivot tables to what the measure is designed for vs the entire table???

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  7 месяцев назад +1

      I would create another PivotTable that shows the drilled down view you're after with the double click action and link it to any slicers you have connected to the original PivotTable. Then add a hyperlink (attached to a button shape if you want) that takes the user to see the drilled down view PivotTable. No need to drill down and only one dataset required in your file.

    • @user-ed7zd5dl7u
      @user-ed7zd5dl7u 7 месяцев назад

      @@MyOnlineTrainingHub thank you!!! i’ll give it a try 👌😊

  • @IamTheReaper911
    @IamTheReaper911 9 месяцев назад

    Your videos always leave me fulfilled 😎

  • @mesundar
    @mesundar 9 месяцев назад

    Thank you for all your excellent videos. I developed my portfolio sheet & dashboard after watching your videos. I want to know how we can create XIRR Stock wise. I have created XIRR for the entire portfolio.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      My pleasure! For XIRR at the stock level, you'd have to summarise the data by stock and then calculate XIRR.

    • @mesundar
      @mesundar 9 месяцев назад

      @@MyOnlineTrainingHub I got it using this formula. =XIRR(IF(Ledger1!$B$2:$B$15000=Dashboard!B6,Ledger1!$G$2:$G$15000,1),IF(Ledger1!$B$2:$B$15000=Dashboard!B6,Ledger1!$A$2:$A$15000,NOW()))

    • @mesundar
      @mesundar 9 месяцев назад

      Only issue currently I'm facing is negative return is coming as zero.

  • @tanveerabbas3271
    @tanveerabbas3271 7 месяцев назад

    you are super!!.. keep it going.. you have a very cute way of explaining...❤

  • @abubabakrbabhair6109
    @abubabakrbabhair6109 9 месяцев назад

    Hi , thanks for your Course is amazing , but when i make the table by day not month the timeline not accepted in Pivot table

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Hard to say why your timeline isn't available. You're welcome to post your question and Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

    • @abubabakrbabhair6109
      @abubabakrbabhair6109 9 месяцев назад

      @@MyOnlineTrainingHub if you don't mind how can post my question and Excel file can you give me from where to go in your website , Thanks in advance for you support

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      The link to the forum is in my reply above. Here it is again: www.myonlinetraininghub.com/excel-forum

  • @andy660216
    @andy660216 9 месяцев назад +2

    There seems more familar way for sorting pivot table rather than adding filters at adjacent column. Please try "select a cell in the middle of pivot table and then select excel ribbon menu "Data" --> and then "sort". This cause generation of selection window and we can choose option of sorting (ascending/decending" and also can select direction of sorting (by column or by line). For me, this way looks more familar and consistent with typical sorting methods rather than adding filters.

  • @RafiqulIslam-dv9cu
    @RafiqulIslam-dv9cu 9 месяцев назад

    would you please explain how can u add levigation from where you select example

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Levigation? Not sure what you’re referring to, sorry.

  • @user-nx6oi6vz3e
    @user-nx6oi6vz3e 9 месяцев назад

    I liked the Date Range Slicer/selector

  • @jandaamen4490
    @jandaamen4490 9 месяцев назад

    Your Mr Excel tip, I wonder, can it not also be done like this: Sort Descending (Z to A) by: Sum of Sales, and then clicking on More sort options, where the current setting is shown as Sort by Endtotal, but below that the option is to sort on the value in a certain column, where you can choose $U$7. (I am using Excel in Dutch language, so I hope I got the terms correctly in English). Cheers from Jan

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад +1

      Nice, Jan! Even better as this doesn't require the extra filter icons. It'll still need updating each time you add a new month, but not if you make changes during the current period. Thanks so much for sharing 🙏

  • @vdpeer
    @vdpeer 8 месяцев назад +1

    Mexico hasn't moved to South America that I have heard. But you do a nice job with Excel.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  8 месяцев назад

      😆good thing I'm teaching Excel and not geography!

    • @jboag4631
      @jboag4631 8 месяцев назад

      Thanks vdpeer - completely unnecessary waste of my eyes having to read your comment 👍

  • @jboag4631
    @jboag4631 8 месяцев назад

    The perfect person doesn’t exis…..

  • @CzechCzar
    @CzechCzar 9 месяцев назад

    what is the downward pointing hand?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      It’s the emoji I chose for the Slicer selection in formulas example sheet tab i.e. it's part of the sheet name. 😉

  • @philgeek572
    @philgeek572 9 месяцев назад +2

    You missed the key motivation for Calculated Fields. Having a smaller file is going to be irrelevant in most situations. What really matters is when the calculation can't be aggregated at the row level. For instance, if I have a column for profit and a column for sale price, I can calculate a profit margin by profit/sale price. Calculated fields are essential for this to work correctly because you can't add up the individual row profit margins and calculate an average. But, with a calculated field, you'll get the right calculation no matter how you slice the data in the Pivot Table.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад +1

      Yes, another great reason to use calculated fields. Thanks for raising this 🙏😊

  • @paradeshkasireddy6941
    @paradeshkasireddy6941 9 месяцев назад

    how to improve dashboards performances ??

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      This video explains the main causes of slow Excel files: ruclips.net/video/e4no3HpW1NY/видео.html

  • @muhammadkamranali3586
    @muhammadkamranali3586 7 месяцев назад

    Hi, my name is Kamran and I am based in Dubai. I am watching your videos and impressed with your excel skills. I was wondering if you can help me with one formula to automatically calculates cost of shares sold on First-in-First-out basis. I regularly trade in stock market so volume of transaction is quite large. Hence, it is not possible to manually check cost of each share. Hence, I am looking to automate my file, however, I am not able to arrive at cost of shares sold. I took help from AI tools, watched youtube videos etc but remained unsuccessful. Problem comes if I make a split purchase and a split sale i.e. first buy quantity of any share on different dates and then sell the quantity in small chunks on later dates. I would be grateful if you have or you can suggest any solution for this.
    KR

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  7 месяцев назад

      When you buy the shares they need to be tagged with a tranche number. Then when you sell the shares you need to also tag them with the tranche they came from. If you sell 10 shares and 5 are from tranche 1 and 5 are from tranche 2, then you need to split this transaction over two lines so they can be tagged accordingly. This way, you can lookup the original tranche purchase price to calculate the cost correctly. HTH.

    • @muhammadkamranali3586
      @muhammadkamranali3586 7 месяцев назад

      @@MyOnlineTrainingHub unfortunately it's not that easy. I had tried in many way. 2ndly, i want to fully automate my process so that trade transaction is automatically picked up by my model and do the rest for me. So thats why I am looking to connect with some expert who can understand my requirement and help me build a model with a fair reimbursement of his/her efforts.

  • @kevinr1166
    @kevinr1166 9 месяцев назад

    BUT, you cannot select non-contiguous months/quarters using the timeline. This would be helpful when comparing Jan of 2022 with Jan of 2023. I use a slicer instead w/ the months. Need a MS to add this. Please?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Correct. Better to use a regular slicer for that. I don’t think we’ll get any improvements to the timeline slicer as it’s had these limitations for a long time 🤔

  • @gerbherb8215
    @gerbherb8215 8 месяцев назад +1

    6:10 Not trying to offend Mr. Excel, but wouldn't it be easier to just rightclick a cell in the column you want to sort on and then choose sort?

  • @arslan_TM
    @arslan_TM 5 месяцев назад

    How to manually isnert a row in a pivot table, I have file from our planning department that has manual rows with manual inputs,but I tried hard to learn it but still unable to do that

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  5 месяцев назад

      You cannot edit a PivotTable manually at all. Data you see in a PivotTable only comes from the source data. You can add rows/columns outside the PivotTable, perhaps that's what you see in the planning file.

    • @arslan_TM
      @arslan_TM 5 месяцев назад

      @@MyOnlineTrainingHub
      Is there any way i can share you that file privately?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  5 месяцев назад

      You can reach out via our website: www.MyOnlineTrainingHub.com

  • @rusektor
    @rusektor 9 месяцев назад +1

    I was thinking whether there will be Trick #3... ))))

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Glad I didn’t let you down 😉

    • @rusektor
      @rusektor 9 месяцев назад

      @@MyOnlineTrainingHub Ha-ha, thanks! ))

    • @rusektor
      @rusektor 9 месяцев назад +1

      ​@@MyOnlineTrainingHubBy the way, thanks for subtitles! Although I almost always understand your speech, but sometimes take a glimpse at subtitles (I'm not English-speaker) ))))

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Glad they're helpful 😉

  • @PBeth-gl2sl
    @PBeth-gl2sl 7 месяцев назад

    my favorite is how to add a filter button to all the columns of a pivot table

  • @godxavier
    @godxavier Месяц назад +1

    Just realised that I should have been using calculated fields.

  • @GeertDelmulle
    @GeertDelmulle 9 месяцев назад

    Good stuff and good video, as usual! :-)
    OK, apart from the disable drill down, I didn’t learn anything new. So, I’m pretty much up to snuff with PTs.
    OTOH, I should indeed give those Calculated Fields another good look: they remind me of (implicit ?) measures in DAX and are indeed more efficient than calculated columns.
    On the GetPivotData functions or more generally the CUBE functions: the latter is what you need to do MDX stuff and take PTs to the next level still. Déjà vu - didn’t converse on that topic not so long ago?… ;-)
    BTW, here’s a trick you can use to allow multiple selections in a slicer to work with your formulas: apply both slicers and formulas on an Excel data table and… done.
    And yes, we know the Mr.Excel trick for filtering PTs - he has a nac for finding tricks like that. :-)

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад +1

      Thanks for watching, Geert! Yes, calculated fields are regular PivotTable's measures, although not quite as good as DAX.
      I did do a video on CUBE functions a while ago. You have a good memory!
      Slicers for tables are a great trick. It's just a shame you can't connect them to multiple tables or both tables and PivotTables 🤔

    • @GeertDelmulle
      @GeertDelmulle 9 месяцев назад

      @@MyOnlineTrainingHubIf I remember correctly you said you would do a refresher of said video (put it on your list, anyway). ;-)
      On those Slicers: arrr… that’s true: we can’t cross-slice tables, etc. But we can copy those table slicers and they will both be linked to that table. Then you create a PT and wonder “why not?”. This is a reason to replace those PTs by formulas… and we’re back to where we started this conversation. :-)

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      😁

  • @umairmomin8868
    @umairmomin8868 9 месяцев назад

    Navigation way is too cool

  • @s1ngularityxd64
    @s1ngularityxd64 9 месяцев назад

    how to setup the timeline so it will end with the last record? It always shows me the timeline until december even my Data ends in June

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Unfortunately, that’s a limitation of the Timeline slicer. Better to use regular slicers instead.

    • @s1ngularityxd64
      @s1ngularityxd64 9 месяцев назад +1

      good to know, thank you very much❤@@MyOnlineTrainingHub

  • @7absinth
    @7absinth 8 месяцев назад

    Why I dont have Show Report Filter page?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  8 месяцев назад

      Maybe because you've loaded your data to the Data Model i.e. Power Pivot.

    • @7absinth
      @7absinth 8 месяцев назад

      @@MyOnlineTrainingHub Nope. It's a simple Pivot table which is not loaded to Data Model.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  8 месяцев назад

      Have you put a field in the Filters area? It uses this field to create the filter pages.

    • @7absinth
      @7absinth 8 месяцев назад

      @@MyOnlineTrainingHub yep. I put all fields in the same way. This feature is greyed out.

  • @missamo80
    @missamo80 9 месяцев назад +1

    I'm only 1:19 in and my mind is blown. There's a timeline slicer!?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад +1

      Surprisingly, many Excel users are not aware of it 😃

  • @Ajeet-js2oy
    @Ajeet-js2oy 9 месяцев назад

    Unable to download example file.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  8 месяцев назад

      No one else is reporting this issue. It might be your browser, so you could try a different one. If you're still having trouble, please reach out via email so we can get the file to you: website at MyOnlineTrainingHub.com

  • @marjensendk
    @marjensendk 9 месяцев назад

    But calculated fields only work, when the pivot is not made from the data model :/

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Correct. If you’re using the data model then you have measures which are better IMO.

  • @learnitinstructor5792
    @learnitinstructor5792 7 месяцев назад

    Volume higher please.

  • @apamwamba
    @apamwamba 9 месяцев назад

    1 and 5

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  9 месяцев назад

      Thanks for watching. Glad you found some tips you can use 😁🙏