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

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.

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

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

Great video thank you!

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

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.

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

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

Brilliant as always, thanks Mark

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.

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

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

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

Loved it!!

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! ❤

Super!! Awsome!!

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.

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

"- -".... Excuse me, what is that lol