Thanks @EamonnCottrell, it's funny too that I literally got access to PIVOTBY while making this and considered weaving it in. Guess it'll have to be a new video.
This is awesome! I honestly don't know if I have the brain power to be able to learn all that you teach, but I definitely learn a lot from your videos. Thank you so much for creating your videos. I hope you continue to do so.
Love this approach. I have not tested this myself so curious could you have added to the SUMIFS() for the sum by region and QTR an option to not sum if the H Column had the word Total?
If I'm understanding the question, that's kind of what I'm doing in the bonus portion where I'm adding in a DROP function to exclude the bottom row. I'm sure there's a way to bake in the word "Total" somehow, but depends on the intent here and what you're hoping to achieve.
@@mattbrattin i have a lot to learn, but i'm finding myself more inline with your philosophy on pivot tables. i find pivot tables to be too limiting. i'm having to tell a story with CRM delivery data at work. looking forward to watching more of your videos. =)
Great stuff. Thanks for the tutorial. Pivot tables I use are often layered in the x and y dimensions...like markets and then sub categories based on revenue size on one axis and the quarters and possibly multiple values per quarter being shown. Do you have an example of how to do this using your approach?
The more complex, multi-level/dimensional reporting just requires another level of mapping to retrieve the data. Sometimes, though, a Pivot might actually the better approach, just depends. Well structured data should allow this level of flexibility though.
This is great. Do you happen to know how much overlap there is between these dynamic array functions and the DAX programming language that can be used in Modern Excel and Power BI? It's been a challenge to learn all the DAX functions and the M functions (that are used in Power Query, the front end part of Modern Excel for accomplishing ETL (Extract, transform and load), so it'd be great to find out Microsoft has been consistent with this other set of functions.
I haven't dabbled much in DAX to be able to speak confidently about that, but I know M (PQ) is often a challenge when you have certain seemingly basic tasks you can do in Excel but not in PQ, or at least not without using wild work-arounds. I would hope as they continue to evolve Excel that these things will all complement each other, it's just hard to say.
Yup the file is the same one I used in my Excel for Analytics project series so you can scoop that up here: ruclips.net/video/45_yTM1HfTc/видео.htmlsi=o_9zA5HOpBI1plA9
This is awesome Matt I was wondering if you could help me with a formula that if you had 28 players playing every week for 7 weeks but without each player meet any of the players that he played the previously I have the sheet if you would like to see and works for 5 weeks I appreciate any feed back and Thank You in advance.
Hey thanks for the note - sounds interesting - I've actually built something like this before. Maybe I can make a video on it. The functions get a little silly but the output seems to work well. Let me know if that's something you'd like to see.
Unfortunately right now I think you still need to use curly brackets {} around arrays in Sheets, but I suspect they'll find a way to address this eventually.
It should, yes, but what you'd need to do is add an HSTACK to both the headers to get the total, and the sum to apply them. I might be able to throw together a short to show how this would work.
Yes! I love this question actually, maybe I'll make a short on it, but essentially you're going to want to set a control/report date and use that to drive a YTD determination off of and then use that in your presentation layer. Hope that makes sense, but if not hopefully I can bust out a video and show you what I mean.
Or you could just use the new PIVOTBY function. It can do the same one formula in a single cell. It just released to the current channel of 365 and should soon be available to the monthly update channel.
It's funny that I literally got access after scheduling this video. I played around with it and I'm struggling a little with the ability to sort in specific ways that I want - seems a bit limited, but I'm sure after playing more I'll figure it out. Either way, it's a huge update! I decided to still release this video because 1) I like the flexibility it affords; 2) It shows multiple functions that each have their own value beyond this example.
![Seungmin "그렇게, 천천히, 우리(As we are)" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/kAzmhLHePqU/mqdefault.jpg)
Awesome! The best part was making it fully dynamic with the formatting and DROP. That will certainly come in handy.
Glad to hear it!
Great stuff, Matt. Some real nuggets in here with conditional formatting and then the good ol' DROP scenario at the end. 🔥🔥
Thanks @EamonnCottrell, it's funny too that I literally got access to PIVOTBY while making this and considered weaving it in. Guess it'll have to be a new video.
@@mattbrattin haha. yep i thought that's what it was going to be when I first saw it. I've got PIVOTBY and GROUPBY on my to-make-a-video list too :)
This was some magic, Matt! I'm a pivot table fan, but I dig learning what else is possible. Thanks for showcasing best practices in this how-to!
These new functions are so powerful, I'm still finding new applications every day! It's a really fun time to be an Excel nerd.
Great video!
This is awesome! I honestly don't know if I have the brain power to be able to learn all that you teach, but I definitely learn a lot from your videos. Thank you so much for creating your videos. I hope you continue to do so.
So glad to hear it - we all start where we start, the important thing is just to keep going and learning!
Very Very good
Love this approach. I have not tested this myself so curious could you have added to the SUMIFS() for the sum by region and QTR an option to not sum if the H Column had the word Total?
If I'm understanding the question, that's kind of what I'm doing in the bonus portion where I'm adding in a DROP function to exclude the bottom row. I'm sure there's a way to bake in the word "Total" somehow, but depends on the intent here and what you're hoping to achieve.
@@mattbrattin I had wondered if you could say "Total" in the SUMIFS and not need the DROP function.
i watched the last video on pivot tables. i hit like there. i watched this one here. i hit the like and hit the sub. i like your stuff. thanks!
Hey glad to hear it! Hope I can continue to earn your viewership!
@@mattbrattin i have a lot to learn, but i'm finding myself more inline with your philosophy on pivot tables. i find pivot tables to be too limiting.
i'm having to tell a story with CRM delivery data at work.
looking forward to watching more of your videos. =)
Great stuff. Thanks for the tutorial. Pivot tables I use are often layered in the x and y dimensions...like markets and then sub categories based on revenue size on one axis and the quarters and possibly multiple values per quarter being shown. Do you have an example of how to do this using your approach?
The more complex, multi-level/dimensional reporting just requires another level of mapping to retrieve the data. Sometimes, though, a Pivot might actually the better approach, just depends. Well structured data should allow this level of flexibility though.
This is great. Do you happen to know how much overlap there is between these dynamic array functions and the DAX programming language that can be used in Modern Excel and Power BI? It's been a challenge to learn all the DAX functions and the M functions (that are used in Power Query, the front end part of Modern Excel for accomplishing ETL (Extract, transform and load), so it'd be great to find out Microsoft has been consistent with this other set of functions.
I haven't dabbled much in DAX to be able to speak confidently about that, but I know M (PQ) is often a challenge when you have certain seemingly basic tasks you can do in Excel but not in PQ, or at least not without using wild work-arounds. I would hope as they continue to evolve Excel that these things will all complement each other, it's just hard to say.
Perfect 👍 please also share data file for practice
Yup the file is the same one I used in my Excel for Analytics project series so you can scoop that up here: ruclips.net/video/45_yTM1HfTc/видео.htmlsi=o_9zA5HOpBI1plA9
This is awesome Matt I was wondering if you could help me with a formula that if you had 28 players playing every week for 7 weeks but without each player meet any of the players that he played the previously I have the sheet if you would like to see and works for 5 weeks I appreciate any feed back and Thank You in advance.
Hey thanks for the note - sounds interesting - I've actually built something like this before. Maybe I can make a video on it. The functions get a little silly but the output seems to work well. Let me know if that's something you'd like to see.
this is great work Matt, however, i am working in google sheets, is there a way that i may be able to use the # in google sheets?
Unfortunately right now I think you still need to use curly brackets {} around arrays in Sheets, but I suspect they'll find a way to address this eventually.
Great solution, but is it possible to get totals for rows and coloumns? I tried the same technique with drop, but got an error.
It should, yes, but what you'd need to do is add an HSTACK to both the headers to get the total, and the sum to apply them. I might be able to throw together a short to show how this would work.
@@mattbrattin Please do.
Hi Matt , love the content.had a q, is there a way to create a dynamic Ytd view rather than by quarter? Eg Q2 ytd, q3 ytd etc, thank you, new sub
Yes! I love this question actually, maybe I'll make a short on it, but essentially you're going to want to set a control/report date and use that to drive a YTD determination off of and then use that in your presentation layer. Hope that makes sense, but if not hopefully I can bust out a video and show you what I mean.
A simple way to do this is to include a "
Questions is putting it nicely. Buncha nerds got all up in their feels, as the kids say. Matt is Excel Jesus and his word is the gospel.
Haha, people do like to see things in a very binary light. There's so much gray in this space and I guess that upsets folks.
Don’t understand how nerds will get mad at doing something in a new way…. Haters gonna hate I guess.
Or you could just use the new PIVOTBY function. It can do the same one formula in a single cell. It just released to the current channel of 365 and should soon be available to the monthly update channel.
It's funny that I literally got access after scheduling this video. I played around with it and I'm struggling a little with the ability to sort in specific ways that I want - seems a bit limited, but I'm sure after playing more I'll figure it out. Either way, it's a huge update! I decided to still release this video because 1) I like the flexibility it affords; 2) It shows multiple functions that each have their own value beyond this example.