The #1 Excel formula issue - Data structure | Excel Off The Grid

[@osoriomatucurane9511]

Awesome tutorial on an absolutely fundamental topic in data analytics. More than tabular data structure, the Data layout is key concept and it is indeed at the heart of the Dplyr in R, with the emphasis on tidy data (variables in columns, observations in rows and values in cells). So important to the point hey have a dedicated pakage, tidyR just to reshape datasets. Thank you once again for sheding light on the matter and contributing to drive Excel to new hights with a clear and consisitent path to approach data manipulation and analysis. You are truly a legend.

[@ExcelOffTheGrid]

Yes, it’s certainly not just an Excel issue. It’a fundamental to any data analysis. ✅

[@h.esther9400]

Great video, dealing with different data structures is often the hardest part of solving problems and isn’t discussed that often. I’d love to see more videos like this.

[@ExcelOffTheGrid]

I’m glad you found it so useful. Don’t worry, more videos coming soon.

[@IvanCortinas_ES]

Great explanation. Many times we find pivoted data structures. Power Query is always a great help. Thanks Mark.

[@ExcelOffTheGrid]

Thanks Ivan. Glad it was helpful!

[@mr.gk5]

I've been looking for this for so long. You helped me solved99% of my problems at work

[@ExcelOffTheGrid]

99% - that’s awesome 😁

[@mr.gk5]

@@ExcelOffTheGrid Could you show how to do the same thing for pivotted table with duplicate values, say I have my department column divide into 2 sections, upper section is Inpatient and and lower section is Outpatient but the departments are the same, how would I do it, thanks

[@salahaldeen7924]

your videos are highly educative, keep doing this type of sessions, please

[@ExcelOffTheGrid]

Thanks, will do! 👍

[@leerv.]

I feel like structuring the data correctly can solve or at least mitigate a lot of challenges. You end up devising the craziest formulas and tricks, because you're working to conform to the structure instead of making the structure conform to you. It really pays to learn some basic data modeling theory, for example, dimension tables (here your "Property, Type and Term Yrs table) vs. fact tables (here your Property, Year and Value table). IMO, mixing the two table types can be done, but it's rarely pretty or easy to work with, and they tend to only get worse as they grow! I know you know all of this, Mark, but this is for viewers who may not! :)

[@ExcelOffTheGrid]

100% - Couldn't have said it better. 👍

[@ExcelOffTheGrid]

Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders File Reference for this video: 0213 Data structure

[@GiorgioBerardi]

Great video, Mark. Thanks. Ideally, it could have also factored in the score related to the preparatory work needed to get to the three different starting points.

[@ExcelOffTheGrid]

The preparatory work is almost irrelevant - as we will should be using Power Query no matter the situation. Therefore, it will all update with a refresh.

[@alexrosen8762]

Wow! What a tutorial. Really important and very useful 👌

[@ExcelOffTheGrid]

Thanks 😁

[@peltiertech1879]

I had a different approach to finding the average term in the unpivoted column. At first I thought it would be easier, but after I finished I'm not so sure. But without a lookup function, my gut tells me it might be quicker. =AVERAGE(CHOOSECOLS(UNIQUE(FILTER(Unpivoted[[Property]:[Term (Yrs)]],Unpivoted[Type]=J10)),3)) I also think unpivoted is better than pivoted, so it should have a score of 5 or 6.

[@ExcelOffTheGrid]

It took me a second to see what you had done there. My only thought is that you're relying on the Term (Yrs) being the 3rd column. So I think the following with HSTACK could be more robust as we know the Term (Yrs) is always the 2nd column, even if somebody moves it. =AVERAGE(CHOOSECOLS(UNIQUE(FILTER(HSTACK(Unpvioted[Property],Unpivoted[Term (Yrs)]),Unpivoted[Type]=J10)),2))

[@peltiertech1879]

@@ExcelOffTheGrid Good thinking.

[@mergedinself]

Great video and very helpful for different scenarios at work. Just one humble request, if you can please remove zap sound (or any sound!) from slide transitions. I listen to videos using ear plugs and while focusing on a topic and if suddenly zapping sound comes, it hurts. Thank you for your amazing work! ❤

[@ExcelOffTheGrid]

Thanks for the feedback about the sound, that is really useful.. Is the sound just too loud? If I reduce the volume significantly, to make it subtle, will that help? Or is it the frequencies of that sound which are the issue?

[@kebincui]

Brilliant as always, thanks Mark

[@ExcelOffTheGrid]

Thanks Kebin 👍

[@v2pumo817]

aim to simplify !, thank for this demonstration ;-)

[@ExcelOffTheGrid]

Exactly - use Power Query to make your life easier.

[@GeertDelmulle]

Great video about a great concept, your explanation is right on the money as well. Thanks, Mark. :-)

[@ExcelOffTheGrid]

Thanks. I know you know this stuff already. So I also know you appreciate how important it is.

[@dougmphilly]

FILTER is more awesome than i realized. solved a huge problem for me.

[@ExcelOffTheGrid]

Yes, FILTER is awesome! Make sure you check out last week’s video for more FILTER techniques.

[@ExcelWithChris]

Loved it!!

[@ExcelOffTheGrid]

Thanks Chris 😁

[@shaharyarahmed6124]

Sir kindly make a video Sendkeys for onother program data entry please

[@chrism9037]

Great video thank you!

[@ExcelOffTheGrid]

My pleasure! Glad you liked it. 😁

[@cshahed]

Super!! Awsome!!

[@ExcelOffTheGrid]

Thank you! Cheers! 😁

[@TWENTY6ENTERPRISE]

6:20 your formula is for unique property names when terms are the same every year. How about if the same property has different terms every year ? Great video by the way. I love your teaching format, instead of giving the correct formula from the start, you build your formula and explain each step and what we need to do to reach the goal. It helps me realize mistakes and how to look at what the formulas are doing so I can correct it (Example putting the double dash to turn text years into number format)

[@ExcelOffTheGrid]

Thanks, I appreciate that feedback. If you have different terms each year, then the granularity is by lease and by year. So it will be the same as the Value calculation, but will use AVERAGE instead of SUM.