Видео

So which video caused the controversy? I am interested to watch it.

=pivot.by( region , quarter, Volume , sum , 3 , 1 , , 0) done !

I have copied values from notepad and pasted to excel, but commas mixed with dots and I had to divide all values in colums (with a range of 405) to 1000. And you instructions saved my life. Thank you very much.

I'm never abandoning pivot tables and nothing you showed me here is compelling in the least. Personally, I think what you are missing is you are still starting from your manually de-normalized data rather than leveraging what pivot tables can do via Power Pivot. You mention you have lookup tables to get region and pull this into a central table using formulas, and this is denormalization. As Excel users we are used to working with wide single tables of data, but pivot tables can work off the data model which means off source tables. Just pop your main and lookup tables into the data model, drag a couple of fields together to create relatioships, then load to a pivot table. And I'd go on to argue that formatting is a strength of pivot tables, not a limitation. You can create custom groups, custom sorts, enable drilling-down via expanding and collapsing fields, set up sub totals, add blank rows, and more. You don't like the row headers, I get it, so turn them off. You can have subtitles on the top or the bottom of groups. Since they are tables, you can apply table styles and your concern about personal branding means you should just make a custom table style once and then you can slap it on everything in one click. You can change display units and even display the same values as multiple units such as % of GT or averages alongside the totals. You use conditional formatting, which is great, but that still works on pivot tables, so what is the problem? It sounds like you just don't know how to format pivot tables properly or a dealing with users that get upset when they interact with them as intended.

You are absolutely correct: Pivot tables suck for presentation and for the sinkhole of time needed to make it otherwise.

Very practical and informative. Thanks! Great ideas.

Very Very good

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.

Great video and points, thanks for sharing.

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.

Quarter End = EOMONTH(Start Date,2). Why make it so hard?

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

Hi Matt, what an important insight and thought process! The examples you've shown using Excel are super cool and fast. I am doing my capstone project now and I've learned much from you,thank you very much~!

what is the complete formula used at 7:20 please?

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?

The only vid that helps me. Massive Thanks Matt!

Good.

What's the tutorial you're referring to that triggered the question, "why didn't you just create a pivot table?"

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.

Excel will always be my first love and I feel that it is so disrespected by aspiring analysts. Thank you for popping up and showing out 🔥🔥🔥

Agree. I never use pivot tables too. Pivot table is a nightmare to Excel automation. I always build my summary tables with array functions.

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.

This is so helpful! Thank you!

Great solution, but is it possible to get totals for rows and coloumns? I tried the same technique with drop, but got an error.

I guess coordinates might have been used with exel maps

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?

i looked for your speedrun video and cold not find it

I love how you stress the business side of the analysis. There are too many young analysts who know the tech side but are unable to properly think about the actual business aspects. Love this about your content. Keep it up 😊

Great breakdown of how to tackle a case study under pressure! Loved how you emphasized not just the technical skills, but the importance of crafting a narrative around the data. It’s a reminder that in real-world scenarios, understanding the business context is just as important as crunching the numbers!

New tricks I learned today: how to take the day, the month, (start-end)*60*24, add conditional column.. I would've done it similarly.. thanks, Matt!

ti giuro se excel fosse una persona me lo immaginerei con la tua faccia

Another good video, Matt. I knew you would get in time trouble after the first five minutes, but you still managed to find some insights by the end. I took that Google course and agree it is a decent introduction to data analytics. Now, I want to download the data again and perform an updated analysis.

nice speedrun! Good idea too; I had similar thoughts about the course back when everyone was taking it (myself included). This is gonna help out some newbies; nice work 👍👍

Awesome man , Technical skills and industry knowledge are important. As technical folks , we should think like stakeholders or business owners and then use data insights to drive the business forward. I really enjoyed your videos when you posted this one. I was taking another Power BI course, but I stopped that and switched directly to your content because I could see the value you consistently provide. Please consider starting a simple series on financial analysis and modeling that would be suitable for beginners. Thank you very much.

You can use cube functions

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?

most of the commands don't work on laptops

spot on

I prefer that to Pivot Tables myself. Look into GROUPBY and PIVOTBY formulas if you haven't already.

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.

This was helpful, but I'm still stuck trying to figure something out... Currently I have a sheet. And when I type in cell B1, it will highlight rows with that value, but only if that value is in column B. I want it to search the entire sheet and highlight any row with the value in any column (even if that value exists in a cell with other things). So for example, I want to be able to type in "000 011 959" and highlight the entire row if it has that value within. Even if a cell contains something like "My File: 000 011 959", instead of just the value... Any ideas? Sorry if this is confusing, I've been trying to figure this out for a few hours now.

That's cool! Thanks for sharing!

Great explanation Thank You

Even though I'm not currently working as an analyst or data analyst, I have a strong interest in it. Here are my key takeaways and notes from your video: Don’t ignore seasonality. Don’t overlook correcting anomalies (avoid baking them in). Always build with long-term sustainability in mind. Bonus tips: Don’t overestimate the impact of initiatives. Anticipate questions and build scenarios accordingly. Be prepared to showcase: Best-case scenarios Worst-case scenarios Most likely scenarios Keep up the good momentum , really enjoying with your videos.

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))

i dont think the control page down works on laptops

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.

Nice video and explanation of the process! Plus, Matt used "Merge & Center"!!!!!! In full disclosure, I used to merge and center a lot. I've since seen the light and only do so when it makes sense.

Man, you're absolutely amazing and awesome. I started following you recently, and I’ve been blown away by your content. If you could start posting or creating more projects, it would be super helpful, especially since your examples are so close to real-world scenarios. I really appreciate the effort you put into helping us learn and understand these new ideas. Thank you so much, Keep up the great work.