The Mistake Almost EVERY Excel User Makes (Free File)

The reason why you find it hard to use PivotTables and more Excel features.

One of the great things about Excel is you can put data anywhere in any format, and this flexibility means you can use it for almost anything from financial models right through to art. But this also means most people store their data in the wrong layout and this prevents you from using Excel's built-in tools that are designed to make your life easy. Instead, you end up writing long, complicated formulas that make your head hurt, just to wrangle your data to so what you want. It's also the number one reason people struggle to use PivotTables.

In this video, I'm going to unveil the 5 most common layout mistakes people make when storing data in Excel. I'll explain why they're problematic, and then show you the correct way to store data so you can skyrocket your productivity.

LEARN MORE
===========



#Excel #TabularData #ExcelTutorial

[MyOnlineTrainingHub]

❓How often do you use one of these wrong table layouts?

[mangobrother]

The biggest challenge I face is that I am the "expert" in Excel in every team I go to. The more I dive into Excel (or any subject really), the more I realize how little I know. So, I watch your videos to learn more and not to be stuck with bad habits that I have developed unknowingly that are limiting my productivity and the teams I am part of. Thank you for the tutorials.

[supinderbabra]

To quote you "one of the great things in excel" is having a good instructor like you. Thank you for the help and improving productivity.

[pietergeerkens]

The process described here is "Data Normalization", and is described in any book on relational databases. It can, however, be summarized by these rules:
0) Every cell is atomic, meaning that it contains no composite values.
1) Every data row is uniquely identified by a set of columns termed the "key"; thus giving every row a unique identifier. For accounting, it can help to regard each data row as resembling a "journal entry" (NOT a ledger entry) uniquely identified by its key.
2) No non-key cell has a value determined by some other non-key cell.
3) No non-key cell has a value determined by only part of the key.

This can be mnemonically summarized as:
The key; the whole key; and nothing but the key.

If you are struggling with data, it's mot likely due to not strictly following the normalization guidelines above.

[brandonzzz]

Cool. 10 minutes of free content on YouTube gave me more information than a three month course at university. Now I can actually apply what I learned in a later database course to the software that I still have access to.

[roberth.]

Outstanding, Mynda. Your preparation to provide a succinct presentaiton is greatly appreciated.

[ChA]

Luckily I work in a team that understands formatting data properly. We don’t often have to deal with these types of files and where we do PowerQurry helps tidy them up really well. One of the first things I try to help people learning excel understand is the importance of correctly formatting your data and naming objects.

[name_it]

It is so sad when one has to work first with the wrong data organisation and then learn how it should be done properly. For a beginner like me, the first question is how would I know that the presented data at first is properly structured? Thank you so much for this video :)

[ExcelTutorials]

Great video! I love the points that you make. you can know all the formulas in the world, but if your data is not layed out in a way that makes it easy to use any sort of reporting and summarizing can become very difficult very quickly

[HugRunner]

Thanks for the tips! Would be nice with a video about fixing formats that Excel automatically ruins, like if you paste something and it suddenly turns 2024-01-01 to a number 56345 or something. Also disabling scientific notation and removal of leading 0s in numbers like 0003554354 if that's e.g. a serial number. One of the worst is the difference between dates and dates as text though. If you paste 2024-01-01 as a value it might be 2024-01-01 saved as text, but if you edit it to 2024-01-02 Excel suddenly decides this is a date. Of course dates saved as dates and text don't mix in Excels autofilters, so you get March => 2024-03-01 if it's a date or 2024-03-01 if it's text, so they won't sort correctly.

[anurmertah]

The beautiful thing you do is not only improved in Excel, but in video editting as well, you deserve to be the prof in Excel ❤

[angieadwin]

I've used Excel for years, but this is the first time I've understood how to structure data for ultimate efficiency. Thank you!

[silversolver]

Excellent video Mynda!
I like to keep my data table alone in one sheet, and do all analysis and reporting and presentation spiffery in other sheets in the same workbook—just seems cleaner to me.

[bpbeary]

Have you been spying on my office? These are the battles I fight every day. The biggest obstacle is the inertia of "but we've always done it this way"

[ClaudioCP]

An excellent tutorial that will save me so much time. Thanks Mynda!

[gerardthompson]

Such good content. Watched this again and picked up a date editing feature I missed before! Thank you, yet again.

[scottdobson]

This is not limited to spreadsheets.
One of the biggest mistakes made in all systems is designing the storage of data based on how it's collected . If nothing else, you collect data once, and can use it thousands of times.
If there is processing needed, it makes more sense to do it once on collection, rather than every time it's accessed

[victorneumann]

Great video! I am working in a company where they started using Excel as a presentation and report formatting tools. Every report lives in a separate file, with auxiliary data in separate tabs inside that report file. Too often, data is repeated across files, unable to synchronize if a business parameter changes. Now, I have an idea how to arrange things from now on to make a more manageable system. I appreciate your effort and detail into your tutorials!

[nevermind]

Indeed. Bless the ERP systems that has a download raw data transaction function. God forbid standard reports! SAP! I am looking in your direction...

[re-nzsk]

Your video should be required viewing for many Excel users.