Try This New Formula Instead of Pivot Tables

แชร์
ฝัง
  • เผยแพร่เมื่อ 22 ธ.ค. 2024

ความคิดเห็น • 224

  • @KenjiExplains
    @KenjiExplains  7 หลายเดือนก่อน +17

    🚀Get the FREE Data Analytics Guide from Hubspot: clickhubspot.com/g8g

    • @SheweCert
      @SheweCert 5 หลายเดือนก่อน

      it's impossible to download the file. When you press the download button, hubspot asks you to enter some info about your whatever but at the end of it when you complete filling & press the Submit button, nothing shows up & nothing happens. is that a clickbait to collect our phone numbers & email addresses?

    • @haroldlane4647
      @haroldlane4647 3 หลายเดือนก่อน

      I got it to download. I filled out EVERY blank. Since I don't have a company, I put (hopefully) an obvious fake biz name and URL and picked "1 employee", and it came through on my phone...

    • @shuvohasan123
      @shuvohasan123 2 หลายเดือนก่อน

      It's Not Even Available In 64bit Version of Standalone Excel Version. Available Only In Office 365. And Can be Manually Registered To 32bit Standalone versions Sadly... No Idea Why By It's Effing Microsoft...

  • @davidferrick
    @davidferrick 6 หลายเดือนก่อน +117

    PivotTables will NEVER go away. Many of us analysts still need ability to drill down on data subsets.

    • @nevermore17011990
      @nevermore17011990 6 หลายเดือนก่อน +3

      correct, and the ability of create measure when data store in DATA MODEL

    • @tumo27
      @tumo27 6 หลายเดือนก่อน +6

      It’s time saving , you don’t have to write down complex formulas

    • @9bm-hb2hpii
      @9bm-hb2hpii หลายเดือนก่อน

      yup 100% and his formula is only applicatble in latest office 365 and office2021 version.

  • @thomastaylor6595
    @thomastaylor6595 6 หลายเดือนก่อน +8

    I love the versatility of pivot tables if I’ve been asked to present data analysis, however, I’ve been using excel a lot more to create simple “dashboards” where I can return data from a few sources based on the entry of a reference, pivot tables and slicers were never friendly for this (and to be fair it’s not what they were designed for) but I can see this functionality being really useful to me , thanks for the no nonsense explanation 👍 thanks to the algorithm too 🙌

  • @CAKimberlyLewis
    @CAKimberlyLewis หลายเดือนก่อน

    These new functions JUST dropped for me and I am SO EXCITED! Thank you for this! 🤗 I've tried for years to get folks to understand refreshing tables and I'm happy Excel too figured that was impossible. Excellent solution and excellent explanation! Thank you!

  • @roberth.9558
    @roberth.9558 6 หลายเดือนก่อน +9

    Nobody does it better, Kenji. Thank you for the instruction.

  • @UpForDebate-999
    @UpForDebate-999 6 หลายเดือนก่อน +1

    Thanks!

  • @jeffersonsumbobo4167
    @jeffersonsumbobo4167 7 หลายเดือนก่อน +11

    I enjoy watching your videos. Your clarity and the fact that you add a sample document to follow along.

  • @ramblermaddy7691
    @ramblermaddy7691 7 หลายเดือนก่อน +15

    I like your content a lot. Please keep up the good work going. Thank you 😊

    • @KenjiExplains
      @KenjiExplains  7 หลายเดือนก่อน +5

      Thank you for the comment! It means a lot :)

  • @rickswineberg
    @rickswineberg 6 หลายเดือนก่อน +171

    Why so many pivottable haters, when they are so easy.

    • @dvdlog
      @dvdlog 6 หลายเดือนก่อน +7

      If you’re dealing with data model you should learn some dax in order to get your data consistent and ready for exploration.

    • @hermask815
      @hermask815 6 หลายเดือนก่อน +5

      Hitting refresh puts mental stress on some people,it seems.
      Knowing the plausibility of your data should be natural on some levels of company hierarchy.
      Is that too much asked for ?

    • @chineduordor4949
      @chineduordor4949 5 หลายเดือนก่อน

      I swear

    • @alex626ification
      @alex626ification 5 หลายเดือนก่อน +2

      You literally just need to right click refresh.

    • @gr8wh8orca53
      @gr8wh8orca53 5 หลายเดือนก่อน

      Upsell every Microsoft new Feature so the old becomes obsolete 😂

  • @iduncanw
    @iduncanw 6 หลายเดือนก่อน +1

    Looks good and very clearly explained. Two questions:
    1. Presumably if you use Slicers on your table, GROUPBY only shows visible data?
    2. Given the result is a dynamic array, how do you format the results so they always show the same format regardless of how big the array ends up being? I only saw you manually format the cells based on the current size.

    • @anpham7108
      @anpham7108 หลายเดือนก่อน +1

      1. No, the formula refer to ALL value in a column.
      2. No again, but you can use VBA.
      Just use Pivot Table

  • @themeettrees
    @themeettrees 2 หลายเดือนก่อน +2

    Groupby may be useful for someone who's not familiar with pivot tables. Pivot tables is King!

  • @rasheednayakkan6750
    @rasheednayakkan6750 7 หลายเดือนก่อน +15

    Why is this function not available on latest version of excel..

    • @waynez8136
      @waynez8136 7 หลายเดือนก่อน +1

      Newly released functions that are only available to insiders. I just double-checked Google Sheets, and they are not available there either.

    • @jtmh31
      @jtmh31 6 หลายเดือนก่อน +3

      Because very few content creators mention this is in beta/preview for Microsoft 365 Office Insiders ONLY. When XLOOKUP was announced, it took a year before it was available. LAMBDA took two years. GROUPBY and PIVOTBY were announced back in November 2023, so you may see them both before years end. I'm to the point I'm going to downvote every video that doesn't mention preview at the beginning.

    • @dondgc2298
      @dondgc2298 6 หลายเดือนก่อน

      @@jtmh31to his credit (somewhat) he does at least mention it about 90 seconds in. But I agree with your sentiment.

  • @JonesFamilyRanch
    @JonesFamilyRanch 7 หลายเดือนก่อน +2

    Really enjoy your videos…straight forward and easy to absorb. Looking forward to using this feature, when available. I envision this will help me track total number of job classifications across branches to keep executive managers informed of where job vacancies may begin to affect job performance and service delivery.

  • @nileshyeole6099
    @nileshyeole6099 5 หลายเดือนก่อน

    You are doing very noble job and helps a lot in education people. Your style of teaching is very very fluent easy to understand

  • @SaiHikawa
    @SaiHikawa หลายเดือนก่อน

    I have a few questions... which one takes less file size, the groupby or pivot? Can we use a custom calculation for groupby? and lastly, do the cells adapt the format of the cell it takes over (i.e. cell shading, font, font color, etc.) or it changes it to default?

  • @UlfatSahil
    @UlfatSahil 4 หลายเดือนก่อน

    Good to see such features, let's see if this could go beyond PV tables, which sometimes seems infinite but boundaries are plenty.

  • @alm9707
    @alm9707 6 หลายเดือนก่อน +1

    Great video and thanks for the clever formula. I tried to use it on my Excel but the Groupby function does not exist. Do you know how to install this function? Is there an "add on"? Thanks

  • @UjjwalJain-p8c
    @UjjwalJain-p8c 3 หลายเดือนก่อน +2

    Excel just need to send an update that allows Pivot table to auto update. If that happens then what would be the use of group by function?

  • @EgasBritesdaSilva
    @EgasBritesdaSilva 3 หลายเดือนก่อน

    what a great video, very practical to follow, this helps my work a lot, greetings from Timor-Leste

  • @vasanthkumarm55
    @vasanthkumarm55 6 หลายเดือนก่อน +1

    Can you tell me which version available in groupby function...

  • @PeterNwachineke
    @PeterNwachineke 6 หลายเดือนก่อน +4

    Question is how can it be controlled when I'm using a slicer

  • @mikelawton6053
    @mikelawton6053 6 หลายเดือนก่อน +2

    love this function and thanks for the demonstration. how long does it take to get access to this stuff in excel? i just set myself up as a beta user and the function still isn't available to me. I'm also hoping to be able to use the REGEX functions that are in beta. Does is take a couple days to kick in??

  • @MrDhunpagla
    @MrDhunpagla 7 หลายเดือนก่อน +3

    Seen this one before but now know the logic of it ...Thanks K ...keep posting dost 😊

  • @tngui79
    @tngui79 หลายเดือนก่อน

    If you wanted an auto recalculation/refresh, just change your options to refresh upon source update. I wonder how long you would take to toggle between the design of data, adding and deleting of data rows, columns and filters. Is there a real replacement to pivot table? Seems like it would only work in a limited summary of information, not really for long tables of pivot data?

  • @johnrohith1559
    @johnrohith1559 หลายเดือนก่อน

    Just like how you added "Powerade", can I add multiple filters ? What is the syntax for it ?

  • @PreMathSci
    @PreMathSci 3 หลายเดือนก่อน +1

    @KenjiExplains please explain why my Excel file doesn't have this "Groupby" function. My one is MS office 2021

  • @RahulPatil9702668705
    @RahulPatil9702668705 2 วันที่ผ่านมา

    Hi could we add multiple column labels in pivotby function
    So we would get the output like tabulation e.g cities in row side and zone and gender in columns side

  • @diondio482
    @diondio482 3 หลายเดือนก่อน

    I love all your videos, It helps me a lot. Thanks Kenji! 😀💯

  • @Yntam123
    @Yntam123 4 หลายเดือนก่อน +1

    Groupby is only available in beta version?

  • @zainab.saad9258
    @zainab.saad9258 11 วันที่ผ่านมา

    can we sort the percentage as well ? I want to find the sales value with the percentage and sort them all

  • @running4fun863
    @running4fun863 2 หลายเดือนก่อน

    Is this correct GROUPBY only works if you want to view one/two row fields (Headers) at a time. For example, I want to view data by region (example, London, Bristol, Yorkshire) then by Cost centre within region of London, Bristo, Yorkshire then by the clients within the Cost centres. If anyone knows your help will really be appreciated. Thanks

  • @venkatachalamiyer7098
    @venkatachalamiyer7098 6 หลายเดือนก่อน

    Hi, if one row data is required Unique and sumif also can be used

  • @anuragkhatri8104
    @anuragkhatri8104 6 หลายเดือนก่อน

    Hello kenji nice explanation. Will your playlist from beginers to expert , will help in understanding excel from scratch? And then we can proceed to more higher levels ?

  • @shivavenna315
    @shivavenna315 6 หลายเดือนก่อน

    How will it impact in terms of performance if we use formulae instead of pivot tables?

  • @Heybat1
    @Heybat1 6 หลายเดือนก่อน

    One scenario.
    Same item sailed with different prices in different days. In table mentioned below data:
    Date, Item Name, Quantity, Price, Value.
    To find total average price of item in Pivot Table you can go & put function in Fields items & sets like =Value / Quantity.
    In new functions like Groupby or Pivotby can’t get it yet. Or I can’t get it up to now.

  • @rupaabhi
    @rupaabhi 2 หลายเดือนก่อน

    Pivot, the word just sounds cool . It will never go away.

  • @manishkarnatak6425
    @manishkarnatak6425 5 หลายเดือนก่อน

    Can we add slicers to tables made from Groupby?

  • @aremoteforu
    @aremoteforu 6 หลายเดือนก่อน +7

    You can just click in the pivot table and press refresh. Do not need to go to the ribbon.

  • @olaolaitan7834
    @olaolaitan7834 7 หลายเดือนก่อน +3

    Thanks for the videos. I like how you explain to understand.

    • @KenjiExplains
      @KenjiExplains  7 หลายเดือนก่อน +1

      Thank you for your comment :)

  • @sheet-chat
    @sheet-chat 6 หลายเดือนก่อน +1

    Thank you for your valuable contribution, it's an excellent tutorial. Thank you! 😊

  • @hemantsingh2172
    @hemantsingh2172 5 หลายเดือนก่อน +4

    No doubt it is a very good function but pivot tables are better than this. In this video, you made many changes in the formula to get different results according to your requirements but in the pivot table, it is straightforward to make the changes very easily. ☺

  • @kameshsharma5164
    @kameshsharma5164 6 หลายเดือนก่อน

    Very educative video
    Sir can you please share which software do you use for video editing
    Please Share

  • @DarkFizo
    @DarkFizo 3 หลายเดือนก่อน +5

    Doesn't look superior to pivot tables to be honest. You have to write entire formula to get same results you can have with few clicks. I might've missed something but the only difference I noticed are live updates vs clicking "refresh". Maybe I'll change my mind if I play with it a bit but right now I'm sceptical.

  • @araratmaf
    @araratmaf 6 หลายเดือนก่อน

    How about if i need to filter? That funtion does not allow me to do that. It's harder to perfom the "Pivot" at all.

  • @manindranirmal
    @manindranirmal 7 หลายเดือนก่อน +3

    That's awesome. Thanks for letting us know. Really useful

  • @nongartinunt2014
    @nongartinunt2014 6 หลายเดือนก่อน

    Awesome as always 👍 However, Why can't I find it in my excel 365 version or are there any method that I have to add in, pls give me suggestions?

  • @monicacalvo1006
    @monicacalvo1006 6 หลายเดือนก่อน +1

    Love this! Going to redesign one of my dashboards today!

  • @user-sk7tm3ps1m
    @user-sk7tm3ps1m 4 หลายเดือนก่อน

    Hello Kenji, i can't find =Groupby Function on my Excel ! can u Assist me if you please

  • @salmanakhtar4669
    @salmanakhtar4669 5 หลายเดือนก่อน

    If i use groupby instead of pivot tables, then how to create data visual like graphs and charts for data analysis with this?

  • @YogeshSoni-h9n
    @YogeshSoni-h9n 7 หลายเดือนก่อน +1

    So if these formulas do everything that a pivot table does... What additional thing do we get other than self updating?

    • @andreimurariu
      @andreimurariu 6 หลายเดือนก่อน

      You get another set of data that would can manipulate

  • @DAXJutsu
    @DAXJutsu 4 หลายเดือนก่อน

    Pivot tables are static once laid out and will not be affected by other formulas in the workbook so with a very large table and other calculations involved, I wonder how GROUPBY can affect performance.

  • @manjit.sarania
    @manjit.sarania 6 หลายเดือนก่อน

    Very nice information and teaching sir.. from India North East

  • @kapilanawarathne5746
    @kapilanawarathne5746 6 หลายเดือนก่อน

    in which version onwards this formula is available?

  • @ArvinMartinez-of7tj
    @ArvinMartinez-of7tj 6 หลายเดือนก่อน

    How about for the repeated items? What if we dont want to see repeated items

  • @gauravloya193
    @gauravloya193 6 หลายเดือนก่อน

    Do these functions work only when the base data is structured in TABLE format?

    • @KenjiExplains
      @KenjiExplains  6 หลายเดือนก่อน

      No they also work without the table format :)

  • @diinitutor6379
    @diinitutor6379 6 หลายเดือนก่อน

    Hello. I'm using office 365, I didn't see groupby Function so what's the problem?

  • @mischaminnee
    @mischaminnee 5 หลายเดือนก่อน

    What is the use case for why one wouldnt just use pivot tables?

  • @nachoguzmanserrano331
    @nachoguzmanserrano331 5 หลายเดือนก่อน

    I couldn't find the GROUPBY function. I even updated my Excel version but don't know why don't find it :/

  • @Khatakrampal
    @Khatakrampal 6 หลายเดือนก่อน

    Hi Thanks a lot for this information, also please can shear the practice file

  • @maher_a._alzoubi
    @maher_a._alzoubi 6 หลายเดือนก่อน +1

    thank you so much for everything 🌹

  • @MUHAMMADAzimKhan-jh3hm
    @MUHAMMADAzimKhan-jh3hm 6 หลายเดือนก่อน

    From where we can get groupby function. In my excel sheet, it is not appearing.

  • @IslamicInspire-hx8ds
    @IslamicInspire-hx8ds 4 หลายเดือนก่อน

    I am using Microsoft professional plus 2021 and i didn't find both formulas in my excel sheet

  • @UtkarshKumar-mp2ls
    @UtkarshKumar-mp2ls 7 หลายเดือนก่อน +4

    Good going kenji

  • @dj_maridao
    @dj_maridao หลายเดือนก่อน

    Awsome! Thanks, man!

  • @Washulaundrymats
    @Washulaundrymats 6 หลายเดือนก่อน

    I have like 2lacks of data how can I solve it I want all the different values and name and I”d ..if I use the pivot table .the results are not good bcos all the duplicate value are deleted..what can I done the data

  • @rajkumarjain3697
    @rajkumarjain3697 6 หลายเดือนก่อน

    I m not getting Groupby functions in my Excel 365

  • @Lorac_Miko-rv9xe
    @Lorac_Miko-rv9xe หลายเดือนก่อน

    I still use pivot tables, some are linked to multiple sources too ... but this group by on the template you provided just gives me #NAME? as an answer. I'm a monthly subscribed user on the latest updated version so I dont get why its not working. Guess this monthly payments to MS is a waste of money if the latest and greatest stuff is not available ... lol

  • @justinspencer-young3935
    @justinspencer-young3935 6 หลายเดือนก่อน

    What if you had annual data and needed to add a date filter to get monthly?

  • @cg_yeomans9472
    @cg_yeomans9472 6 หลายเดือนก่อน

    How to do the group by date/daily?

  • @gintomino4136
    @gintomino4136 5 หลายเดือนก่อน

    This is the query equivalent in GSheets. But with limitations. PivotTables will stay because they can drill down.

  • @areebahmadvideos1948
    @areebahmadvideos1948 19 วันที่ผ่านมา

    Great one❤

  • @mannymany4423
    @mannymany4423 6 หลายเดือนก่อน +1

    I use VBA to update pivot tables automatically when source data has changed.

  • @charkueyteowww2221
    @charkueyteowww2221 หลายเดือนก่อน

    i think that it will also replace the =SUMIFS also

  • @tomr.5217
    @tomr.5217 6 หลายเดือนก่อน

    Wow this looks nice compare to pivot! Thanks to this new formula ❤

  • @aicx40
    @aicx40 6 หลายเดือนก่อน +2

    Thanks for sharing. Awesome!

  • @Vushan108
    @Vushan108 4 หลายเดือนก่อน

    which ms excel version?

  • @narendrakumarchauhan8928
    @narendrakumarchauhan8928 7 หลายเดือนก่อน

    Can i use this formula in 2019 excsl version

  • @atuldelhi
    @atuldelhi 6 หลายเดือนก่อน

    *groupby function is not showing in ms office 2024 and not in google sheet? how can i get this function?
    Pls. Respond.*

  • @khimseily
    @khimseily 6 หลายเดือนก่อน

    I can't find this groupby function on my excell

  • @pandharinathjoshi6565
    @pandharinathjoshi6565 6 หลายเดือนก่อน

    Sir here slicer are not working with groupby and pivot by

  • @FrederickMalley
    @FrederickMalley 6 หลายเดือนก่อน

    Is this a new update or add in? I just tried it and I dont have it.

  • @md.alamgirhossain905
    @md.alamgirhossain905 6 หลายเดือนก่อน

    We can use unique and sumif functions to do the same.

  • @ijaznoormohamedso4965
    @ijaznoormohamedso4965 5 หลายเดือนก่อน +19

    To be honest, Pivot table is best.

  • @bijaytanty8434
    @bijaytanty8434 6 หลายเดือนก่อน +1

    Thanks Sir 👍🏻👍🏻

  • @saharminhas1495
    @saharminhas1495 4 หลายเดือนก่อน

    Can you make videos related to google sheets also. Please

  • @suvarnareddy6866
    @suvarnareddy6866 7 หลายเดือนก่อน +1

    1st view I like your videos and more informative

    • @KenjiExplains
      @KenjiExplains  7 หลายเดือนก่อน +1

      Thanks so much!

  • @Live-InSportsNewsandtruths
    @Live-InSportsNewsandtruths 5 หลายเดือนก่อน

    Excellent 👌 briefing

  • @juzershabbir6199
    @juzershabbir6199 7 หลายเดือนก่อน

    Is the function available on Microsoft 365.. using Microsoft 365 but not able to get these functions

    • @SeyPras.
      @SeyPras. 7 หลายเดือนก่อน

      Require insider access.. If you’re using 365 personal you can head to File > Office > Help > Check for updates > advanced.. and change preview to insider.

  • @johnthedataanalyst
    @johnthedataanalyst 6 หลายเดือนก่อน

    Help, i don't have this kind of function in my Excel

  • @9bm-hb2hpii
    @9bm-hb2hpii หลายเดือนก่อน

    Pivot table for fast report and your formula is only applicable in office365 and office2021version, mostly is using ver.2010 to 2019ver.

  • @casonalimudgal7986
    @casonalimudgal7986 6 หลายเดือนก่อน

    Really this is Amazing, Keep going.

  • @patrickp8315
    @patrickp8315 6 หลายเดือนก่อน

    Thanks. Way simpler than pivot table.

  • @ezkul95828
    @ezkul95828 6 หลายเดือนก่อน

    Cool. Thanks for detail video.
    I can retire Pivot table in my simple excel.

  • @Techno-kidsTechnoHelpu
    @Techno-kidsTechnoHelpu 6 หลายเดือนก่อน +1

    GROUPBY IS NOT AVAILABLE IN ALL VERSIONS. I HAVE 365 APPS (2406) AND IT DOES NOT AVAILABLE.

  • @FinanceLioncrest
    @FinanceLioncrest หลายเดือนก่อน

    This function is not available in Google Sheets.

  • @8126haseeb
    @8126haseeb 6 หลายเดือนก่อน

    why groupby is not showing in my excel?

  • @bongperez6266
    @bongperez6266 6 หลายเดือนก่อน

    Wow, this is gold! Thanks!

  • @nishanth4323
    @nishanth4323 7 หลายเดือนก่อน +1

    Cool new formula 🙏🏻

  • @jessicasauberer3540
    @jessicasauberer3540 6 หลายเดือนก่อน

    how can i don't have that formula in my excel (=GROUPBY)

  • @Arpitr1697
    @Arpitr1697 6 หลายเดือนก่อน

    "groupby" is still not available in my excel