I see your point. A huge reason we like to summarize data at our company in a pivot table is so the user can click each aggregate value in the pivot and then a new tab will open with the list of records that sum into that aggregate value. Perfect to get a quick understanding of what exactly is being added together for each pivot table value.
The key is ultimately use your best judgment. If your workplace has people who appreciate and know their way around pivot tables, that's great and I'd argue that's not necessarily the norm - but good! I'd be curious, though, if instead of a pivot you got a little fancy with the presentation and added in the ability to filter and summarize views whether that would also be well received? Could be worth a try.
@@mattbrattin I appreciate your thoughts - I will brainstorm how I could do that. I completely agree pivots are ugly and not presentation worthy. I do really appreciate their efficiency and ease of use to summarize data in seconds and the ability to explore the data so easily. I'm happy I ran across your video to think about these things and how to better present data in a more end user friendly format.
Great point about building up your personal brand internally. I've done this and am now working on several bespoke Excel projects for different teams. Whether or not you use a pivot table, dynamic array, formatted table or the data model will vary by use case, but all are great tools to have at your disposal.
As a financial controller, i hear you. Delivarables are dependent on the mission and the recipient. The CEO sure love good looking stuff, and i get it. For super quick analysis and if it's internal in finance department, pivot is the way to go. I do add slicer and ask myself if refresh will be needed.
Thank you for sharing a different perspective around pivot tables, I appreciate seeing things from another viewpoint. I am going to think about this further.
Understood. I think if you have the time to develop a template with custom formulas it makes sense. But for quick and dirty data which is usually requested on the fly, pivot works. But great point.
I get into this argument all the time. There are use cases better suited to pivot tables and use cases better suited to dynamic array formulas. Often, it's a matter of preference and style. Or what your boss/client/customer wants.
100%. It seems more prevalent lately that the idea of putting in more effort "for the man" is taboo. We all get to use our best judgment for the situation at hand. It's just important to be reminded that sometimes you create your own luck in this world, and it's not really that hard to create all the time.
Great perspective on pivot table as I never thought about that in that way. Would you please do a complete walk through as I would love to see all your formulas?
😮@mattbrattin I would love to see how you total. I currently am using a pivot to support some cash entries, but I haven't been able to find a way to add a total at the bottom when the lines change all the time. This is the closest I have seen to get there.
@@Rose-xc7wz and @TH-vw4qn check out the video I just dropped today and let me know if that answers your questions: ruclips.net/video/eBckiTvKYIg/видео.htmlsi=5Jg2knlDF2nvvBBM
I like your perspective of using this method vs a pivot table. I have a case where I'm reporting data from a large data set, few million rows. Current, I run all the ETL through Power Query. Once I'm finished the only real option I have for presentation is to load to a pivot table. A regular table would not be able to handle the amount of data. Are there any methods similar to this to use with large data sets?
I have a similar use case, maybe. ETL through PQ. Load into Data Model. Then use CUBE functions to reference the data. Allows you to do a lot of the heavy lifting with DAX. Think of it like Power BI Desktop Lite.
I'd have a lot more questions about the data if you're pulling this much volume this close to a presentation layer - and without more information I don't want to be prescriptive so I'd just ask whether there's any way to crunch that data down a little more using PQ or even further up stream? I think what @ExcelTL said is a great suggestion, but the caveat is that this is assuming there's not more heavy lifting that can take place upstream. This is a phase in the process where you really should start questioning whether you're using the right tool for the job. Excel CAN do it...but should it?
Well your issue is you dont like the way pivot table looks like then just use it as refrence and create a good dashboard, this way you will get the benefits of using pivot table amazing features without spending time writting lenghty formula..
Yes and no, you still have to contend with data freshness and building presentation layers on top of pivots can present its own set of issues - like a hop skip and a jump when you could just jump straight from the dataset. I say this after having spent years building models and templates on top of pivots and realizing all the shortcomings over time. You can do this, I just don't recommend it.
I too have moved away from using Pivot Tables as much as possible. I also know the Pivotby function will do much of the same thing, but not all users have that function. Instead, i prefer to use a single cell formula. Example below is what I recreated from your table structure. =LET(volume,Data[Vol],quarter,Data[Quarter],region,Data[Region], qh,Data[[#Headers],[Quarter]],rh,Data[[#Headers],[Region]], rhs,UNIQUE(region),qhs,TRANSPOSE(SORT(UNIQUE(quarter),,1)), fr,HSTACK("Region",qhs,"Total Volume"), sr,HSTACK(rhs,SUMIFS(volume,region,rhs,quarter,qhs),SUMIFS(volume,region,rhs)), tr,HSTACK("Total Volume",SUMIFS(volume,quarter,qhs),SUM(volume)), VSTACK(fr,sr,tr))
Love it! Over the last several months I've been "one-formula-ing" as much as I could for the personal challenge, plus just to make sure I'm up to speed on all the latest releases (or at least as much as I can be). I've started to tone it down a bit though as the inevitable response from stakeholders is less enthusiastic, and auditability becomes more of a challenge the more complexity that's introduced. Doesn't mean we can't still have fun!
There's always ways of adjusting settings and formats and trying to make things look just right, but you're still dealing with a data freshness issue. I'll say it again, it's a personal judgment call based on the situation, so if going tabular works for you, let it work for you!
It's another option for sure, this is just an alternative for folks looking for a more custom means of presenting their work product that's "guaranteed fresh" and doesn't compromise on building your personal brand at work.
Hi my friend, I like your Idea, but I'm curious how you will solve it when you will have a more than a million of rows and data are coming from different dataset.
My default here is Power Query is your friend, but similar to the question posed by @Lance215 I'd be curious about data sources and whether there's an infrastructure gap that should eventually be addressed. The quick response is you need to introduce an ETL step and try to create more manageable snapshots of the data to work with.
Hey I go into depth on this in the first couple minutes of this video: ruclips.net/video/eBckiTvKYIg/видео.htmlsi=POnoMiHE9uHJDNtg I get to this formula right at the 2 minute mark.
Sometimes I get a little carried away with formula structure, especially if it's embedded with more than one nested function. Basically just a series of row adds and indents to keep everything organized. I'm going to do a follow up to talk through the functions because a few people have asked.
Ditto. I hate that they don't automatically update when data changes. Too many times I've had customers jump into a dashboard that we had pivot tables in and complain that the data was inaccurate. The pivot tables just hadn't been refreshed yet because we were still updating the data. But, because the data was exactly as it was yesterday, they assumed we weren't doing anything with it. So frustrating (especially having customers that want to watch you do your job even remotely).
I'll share the function here and I plan to do a walk-through of the functions in an upcoming video: =VSTACK( SUMIFS( Volume_Data[Vol], Volume_Data[Region], $B5#, Volume_Data[Quarter], C$4# ), SUMIFS( Volume_Data[Vol], Volume_Data[Quarter], C$4# ) )
This guy's classic, that's what we need more of Matts. Talks like a normal person not these other MFs with their high end coding mumble jumble and at the end of their vid it's like WTF was the dude saying. Matt tells you as is and why. Shot Matt
Hey thanks for that! One of the best compliments I ever got was past colleagues saying the person they see in the videos is the person they remember working with. No need to put on a show, just trying to keep it real! Cheers
Haha, grazie! Questo è il massimo dei complimenti per chi usa Excel 😄. Lo prenderò come un segno che sto facendo bene il mio lavoro! Forse dovrei cominciare a presentarmi come 'Excel in forma umana
I mean, the first thing I said in the video was after you watch you can go back to doing whatever you want 😁But seriously, I put it like this - we all have standards for ourselves and we have the free will to uphold them, set them high or low, or ignore them. This is an area where I sincerely believe if you put your best foot forward often enough it will serve you well in the long run. To each their own.
I see your point. A huge reason we like to summarize data at our company in a pivot table is so the user can click each aggregate value in the pivot and then a new tab will open with the list of records that sum into that aggregate value. Perfect to get a quick understanding of what exactly is being added together for each pivot table value.
The key is ultimately use your best judgment. If your workplace has people who appreciate and know their way around pivot tables, that's great and I'd argue that's not necessarily the norm - but good! I'd be curious, though, if instead of a pivot you got a little fancy with the presentation and added in the ability to filter and summarize views whether that would also be well received? Could be worth a try.
@@mattbrattin I appreciate your thoughts - I will brainstorm how I could do that. I completely agree pivots are ugly and not presentation worthy. I do really appreciate their efficiency and ease of use to summarize data in seconds and the ability to explore the data so easily. I'm happy I ran across your video to think about these things and how to better present data in a more end user friendly format.
Great point about building up your personal brand internally. I've done this and am now working on several bespoke Excel projects for different teams. Whether or not you use a pivot table, dynamic array, formatted table or the data model will vary by use case, but all are great tools to have at your disposal.
That's right - knowing the various tools and the pros and cons of each is key.
You are absolutely correct: Pivot tables suck for presentation and for the sinkhole of time needed to make it otherwise.
As a financial controller, i hear you. Delivarables are dependent on the mission and the recipient. The CEO sure love good looking stuff, and i get it. For super quick analysis and if it's internal in finance department, pivot is the way to go. I do add slicer and ask myself if refresh will be needed.
Exactly - It's a judgment call, not a blanket mandate. Do what makes the most sense for you in your position.
Thank you for sharing a different perspective around pivot tables, I appreciate seeing things from another viewpoint. I am going to think about this further.
Understood. I think if you have the time to develop a template with custom formulas it makes sense. But for quick and dirty data which is usually requested on the fly, pivot works. But great point.
Yeah it's a situation-by-situation call we each have to make, so knowing what options are out there is half the battle.
I get into this argument all the time. There are use cases better suited to pivot tables and use cases better suited to dynamic array formulas. Often, it's a matter of preference and style. Or what your boss/client/customer wants.
100%. It seems more prevalent lately that the idea of putting in more effort "for the man" is taboo. We all get to use our best judgment for the situation at hand. It's just important to be reminded that sometimes you create your own luck in this world, and it's not really that hard to create all the time.
Great perspective on pivot table as I never thought about that in that way. Would you please do a complete walk through as I would love to see all your formulas?
Yup, that's now on my short list for next video because so many have asked!
😮@mattbrattin I would love to see how you total. I currently am using a pivot to support some cash entries, but I haven't been able to find a way to add a total at the bottom when the lines change all the time. This is the closest I have seen to get there.
@@Rose-xc7wz and @TH-vw4qn check out the video I just dropped today and let me know if that answers your questions: ruclips.net/video/eBckiTvKYIg/видео.htmlsi=5Jg2knlDF2nvvBBM
I've I've argued this point for years. Very few pivot tables in my world.
@mattbrattin Group by function doesn't work in the version of Excel my work has. Sad. Back to the drawing board...
Great video and points, thanks for sharing.
Agree 100%, it’s all about the effort, this is a really well presented video, makes sense I’m definitely adapting this will advise the results lol
My belief is putting your best foot forward can only help you, so why not do it as often as you can!
So which video caused the controversy? I am interested to watch it.
I feel the same about deliverables and encourage my team to think this way too. Subscribed!
It feels like a simple idea, but for many it just doesn't compute. It really does make a difference!
Very practical and informative. Thanks! Great ideas.
I like your perspective of using this method vs a pivot table. I have a case where I'm reporting data from a large data set, few million rows. Current, I run all the ETL through Power Query. Once I'm finished the only real option I have for presentation is to load to a pivot table. A regular table would not be able to handle the amount of data. Are there any methods similar to this to use with large data sets?
I have a similar use case, maybe. ETL through PQ. Load into Data Model. Then use CUBE functions to reference the data. Allows you to do a lot of the heavy lifting with DAX. Think of it like Power BI Desktop Lite.
I'd have a lot more questions about the data if you're pulling this much volume this close to a presentation layer - and without more information I don't want to be prescriptive so I'd just ask whether there's any way to crunch that data down a little more using PQ or even further up stream? I think what @ExcelTL said is a great suggestion, but the caveat is that this is assuming there's not more heavy lifting that can take place upstream. This is a phase in the process where you really should start questioning whether you're using the right tool for the job. Excel CAN do it...but should it?
Well your issue is you dont like the way pivot table looks like then just use it as refrence and create a good dashboard, this way you will get the benefits of using pivot table amazing features without spending time writting lenghty formula..
Yes and no, you still have to contend with data freshness and building presentation layers on top of pivots can present its own set of issues - like a hop skip and a jump when you could just jump straight from the dataset. I say this after having spent years building models and templates on top of pivots and realizing all the shortcomings over time. You can do this, I just don't recommend it.
I too have moved away from using Pivot Tables as much as possible. I also know the Pivotby function will do much of the same thing, but not all users have that function. Instead, i prefer to use a single cell formula.
Example below is what I recreated from your table structure.
=LET(volume,Data[Vol],quarter,Data[Quarter],region,Data[Region],
qh,Data[[#Headers],[Quarter]],rh,Data[[#Headers],[Region]],
rhs,UNIQUE(region),qhs,TRANSPOSE(SORT(UNIQUE(quarter),,1)),
fr,HSTACK("Region",qhs,"Total Volume"),
sr,HSTACK(rhs,SUMIFS(volume,region,rhs,quarter,qhs),SUMIFS(volume,region,rhs)),
tr,HSTACK("Total Volume",SUMIFS(volume,quarter,qhs),SUM(volume)),
VSTACK(fr,sr,tr))
Love it! Over the last several months I've been "one-formula-ing" as much as I could for the personal challenge, plus just to make sure I'm up to speed on all the latest releases (or at least as much as I can be). I've started to tone it down a bit though as the inevitable response from stakeholders is less enthusiastic, and auditability becomes more of a challenge the more complexity that's introduced. Doesn't mean we can't still have fun!
what if you change the pivot table view to the tabular format.. that removes that top row you spoke of hiding but I do see your take for presentation
There's always ways of adjusting settings and formats and trying to make things look just right, but you're still dealing with a data freshness issue. I'll say it again, it's a personal judgment call based on the situation, so if going tabular works for you, let it work for you!
Even better, change the default pivot table layout to Tabular!
@@arpwable Yeah I'm definitely more of a tabular guy myself, if I had to choose 😁
You can make the pivot look the same very quickly, also Pivot tables take up less space in the overall spreadsheet to formula design method
It's another option for sure, this is just an alternative for folks looking for a more custom means of presenting their work product that's "guaranteed fresh" and doesn't compromise on building your personal brand at work.
Makes sense! Great point🎉
What's the tutorial you're referring to that triggered the question, "why didn't you just create a pivot table?"
It was on tiktok: www.tiktok.com/@mattbrattin/video/7288314571457121582?is_from_webapp=1&sender_device=pc&web_id=7398215479410738719
Hi my friend, I like your Idea, but I'm curious how you will solve it when you will have a more than a million of rows and data are coming from different dataset.
My default here is Power Query is your friend, but similar to the question posed by @Lance215 I'd be curious about data sources and whether there's an infrastructure gap that should eventually be addressed. The quick response is you need to introduce an ETL step and try to create more manageable snapshots of the data to work with.
@@mattbrattin thanks
Very nice perspective for not using PT 🤔 Can you please provide link for that video that everybody comment?
Yup, here: www.tiktok.com/@mattbrattin/video/7288314571457121582
what is the complete formula used at 7:20 please?
Hey I go into depth on this in the first couple minutes of this video: ruclips.net/video/eBckiTvKYIg/видео.htmlsi=POnoMiHE9uHJDNtg
I get to this formula right at the 2 minute mark.
how did you write the function looks like programming language. thanks.
Sometimes I get a little carried away with formula structure, especially if it's embedded with more than one nested function. Basically just a series of row adds and indents to keep everything organized. I'm going to do a follow up to talk through the functions because a few people have asked.
spot on
Agree. I never use pivot tables too. Pivot table is a nightmare to Excel automation. I always build my summary tables with array functions.
Ditto. I hate that they don't automatically update when data changes. Too many times I've had customers jump into a dashboard that we had pivot tables in and complain that the data was inaccurate. The pivot tables just hadn't been refreshed yet because we were still updating the data. But, because the data was exactly as it was yesterday, they assumed we weren't doing anything with it. So frustrating (especially having customers that want to watch you do your job even remotely).
@@chasbjoes I hear your pain, Mate. Don't use pivot tables as reference because they are not dynamic and can't refresh themselves.
can you share your formula for cell I5 where you stack/sumif?
I'll share the function here and I plan to do a walk-through of the functions in an upcoming video:
=VSTACK(
SUMIFS(
Volume_Data[Vol],
Volume_Data[Region], $B5#,
Volume_Data[Quarter], C$4#
),
SUMIFS(
Volume_Data[Vol],
Volume_Data[Quarter], C$4#
)
)
@@mattbrattin Thanks, very helpful. was able to rebuild it for my purpose
@@lr4610 Awesome, glad to hear! There's a good use case for a LET function as well that I may cover in the video when I pull that together.
This guy's classic, that's what we need more of Matts. Talks like a normal person not these other MFs with their high end coding mumble jumble and at the end of their vid it's like WTF was the dude saying. Matt tells you as is and why. Shot Matt
Hey thanks for that! One of the best compliments I ever got was past colleagues saying the person they see in the videos is the person they remember working with. No need to put on a show, just trying to keep it real! Cheers
Which recent video are you referring to?
This one: www.tiktok.com/@mattbrattin/video/7288314571457121582
You can use cube functions
Admittedly I've never gotten into those much. Perhaps I should revisit and see how they stack up/fit in with the newer breed of functions.
i looked for your speedrun video and cold not find it
Here: www.tiktok.com/@mattbrattin/video/7288314571457121582?is_from_webapp=1&sender_device=pc&web_id=7163775355135067694
@@mattbrattin thanks, i don't normally use TikTok, but links like that work.
You're great
Why thank you!
Delivering pivot tables as end products is like giving everyone snicklefrits. Sometimes snicklefrits is ok to give out but not all the time.
I don't know what snicklefrits are and I'm too afraid to google it, so I'll just take your word for it
@@mattbrattin it’s a line from the movie, pineapple express that I greatly failed that trying to reference here 😭🥲
@@mattbrattin, nice.❤. Sending the files
I cannot the original video
Here: www.tiktok.com/@mattbrattin/video/7288314571457121582
ti giuro se excel fosse una persona me lo immaginerei con la tua faccia
Haha, grazie! Questo è il massimo dei complimenti per chi usa Excel 😄. Lo prenderò come un segno che sto facendo bene il mio lavoro! Forse dovrei cominciare a presentarmi come 'Excel in forma umana
I'm sorry but none of the points you've made are valid enough to stop using Pivot Tables. If it ain't broke, don't fix it.
I mean, the first thing I said in the video was after you watch you can go back to doing whatever you want 😁But seriously, I put it like this - we all have standards for ourselves and we have the free will to uphold them, set them high or low, or ignore them. This is an area where I sincerely believe if you put your best foot forward often enough it will serve you well in the long run. To each their own.