The Mistake Almost EVERY Excel User Makes (Free File)

The reason why you find it hard to use PivotTables and more Excel features.
👩‍🏫 Learn more with my Excel courses: bit.ly/tabformat24courses
⬇️ Download the example file here and follow along: bit.ly/tabformat24file
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 NEWSLETTER - join 450K+ subscribers here: www.myonlinetraininghub.com/e...
🎯 FOLLOW me on LinkedIn: / myndatreacy
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
#Excel #TabularData #ExcelTutorial

[@MyOnlineTrainingHub]

❓How often do you use one of these wrong table layouts? Learn more with my Excel courses: bit.ly/tabformat24courses

[@chriswall4795]

Hi Mynda We encounter "raw" files in all of the "helpful" formats all the time, plus people "making it look nice for senior leadership" rather considering how the data is going to be used. Often these are exports from our Cognos datawarehouse and made for human eyes, not analysis. As we generally disseminate what we do via Power BI, we avoid Pivot tables in our "feeder files", but we do often have to undo the "good intentions" of others! My explanation to others: "arrange it like a database" and then explain what I mean and why. I learnt not to use pivot tables when they were first brought in for a few reasons: formatting was inflexible, naïve recipients broke them and blamed me, and at that time they bloated file size (was the late 1990s/early 2000s). Thank you Chris

[@notesfromleisa-land]

I've had to unwind some goofy formats from an old accounting system that exported data into excel BUT the debit credit columns would get goofy if the the number was greater than 9999. Oh geez. This is the head banger that made me learn power query. I had a "crappy data" tab and a "transformed data" tab. It was a trail of tears to get there, but once done, save me tons of time and Kleenex. Solving problems such as this is one of the best ways to really learn the power of PQ.

[@jonr6680]

Delving into office politics, most ppl are clueless about data science, they use excel like a scrsp pad. No1 issue I have myself is missing data points. Something that powerpivot isn't too good at handling but I bet you have a fix?! E.g. days on which a return was intentionally zero, but ppl simply didn't record anything at all. Or the opposite, where something did happen but that record is missing. Plus on a conceptual level, a lot of missing data points means your data requires statistical analysis to approximate reality...

[@atauriu]

download link not working.

[@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.

[@MyOnlineTrainingHub]

So pleased you find them helpful 🙏😊

[@MosesJingo]

I share the same frustration as you!

[@ChrisM541]

You won't be surprised to hear that your "Workplace expert in Excel (despite not being an expert)" status is duplicated in so many workplaces. My old workplace made use of Excel spreadsheets with manually entered formulas for very important use. Shockingly, it wasn't considered as a controlled document. The formula cells weren't even locked!

[@mangobrother]

@@ChrisM541 Indeed. At a startup, I created a temporary spreadsheet. I was promised the process would be migrated into the main system we used. It was not and I had to babysit this file for 20 months if anything went wrong. Then I went on a 4 weeks vacation. As soon as I came back, within weeks, the process was migrated. Spreadsheets are great TEMPORARY solutions but they are not systems. Surprisingly, very educated and qualified bosses don't seem to comprehend it. It always amazes me how corporations actually make money given the managerial "leadership". Office Space is not too far off, IMO.

[@MarvindeSalit]

I thought you would be saying, "The biggest challenge I face is that I am Expert in Excel but people I work with are not, so I am the one adjusting to their level of knowledge" 😢

[@supinderbabra7068]

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.

[@MyOnlineTrainingHub]

Thanks so much for your kind words 🥰

[@pietergeerkens6324]

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.

[@MyOnlineTrainingHub]

😁Thanks for sharing, Pieter!

[@bpbeary8011]

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"

[@MyOnlineTrainingHub]

Hopefully, you can share this video to help them see the light 😉

[@aSpyIntheHaus]

Everyday right! Ohh man

[@fernsehdesign]

haha… time to restructure the company again, and again and again..

[@brandonzzz9924]

Cool. 10 minutes of free content on KZbin 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.

[@MyOnlineTrainingHub]

Wonderful to hear!

[@yeohi]

Trump University?

[@Ch715A]

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.

[@MyOnlineTrainingHub]

Wonderful to hear!

[@LM-xg1ty]

You are vert lucky. In my company the like to use 3583 différents files

[@notesfromleisa-land]

I had a goofy data dump. I had to create a "crappy data" tab and a "transformed data tab". In fact, it was the mess that punted me into HAVING to learn PQ.

[@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 :)

[@MyOnlineTrainingHub]

This lesson is rarely taught and I hope this video changes that 😊

[@anurmertah3116]

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 ❤

[@MyOnlineTrainingHub]

Wow, thank you! 🥰

[@roberth.9558]

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

[@MyOnlineTrainingHub]

Thank you kindly! 🥰

[@ExcelTutorials1]

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

[@MyOnlineTrainingHub]

Thanks so much. Well said 😊

[@re-nz3sk]

Your video should be required viewing for many Excel users.

[@MyOnlineTrainingHub]

Feel free to share it 😉

[@richardhay645]

Great video. I see these mistakes and others all the time. So much resistance to Ptoper Data Sets and even more Resistace to using the Excel Table features.

[@MyOnlineTrainingHub]

It's frustrating, but don't give up. Feel free to share this video to support your points.

[@scottdobson1276]

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

[@MyOnlineTrainingHub]

Great point!

[@jonathandawson3091]

Lovely video. Reminds me of my Excel days. While the skills are useful to be even now in Spreadsheets (I use Libre Office now), they were pivotal to also use databases and SQL based reporting.

[@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.

[@MyOnlineTrainingHub]

Great idea! Thankfully, the leading zeros problem has been fixed in the latest version of Excel. I talk about how to fix the date issue here: www.myonlinetraininghub.com/fixing-excel-dates-formatted-text

[@dondgc2298]

@@MyOnlineTrainingHubis it possible to disable the automatic scientific notation when pasting large numbers?

[@silversolver7809]

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.

[@MyOnlineTrainingHub]

Perfect approach. I wish everyone did that.

[@gioyfa]

Great video! Always when I want to start creating a report that I am going to use and enrich in the future, I have in mind to create the RAW table, with all data will need and how will structure that, so to proceed later on reporting formats. So, I believe that if you have in mind first the format and the kind of data you will use in the future, you will be able later to create the report that match your needs.

[@MyOnlineTrainingHub]

Great idea to plan before diving into building 👍

[@notesfromleisa-land]

Unpivot is a powerful antidote to the goofy column issues (as well as other sins of data tables). Great vid as always.

[@MyOnlineTrainingHub]

Yes, Power Query's unpivot is a life saver 😅

[@victorneumann8467]

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!

[@MyOnlineTrainingHub]

Yikes! These changes will make the world of difference to your reporting process.

[@patslee1394]

thank you for the advice. i only use excel occasionally and this is useful info for down the road 👍

[@MyOnlineTrainingHub]

Great to hear!

[@Dezz005]

WE LOVE YOU!!!! YOU ARE AWESOME!!!!! You changed our Lives!!!

[@MyOnlineTrainingHub]

🥰🥰🥰thanks so much!

[@TheRandyGr]

I retired from doing this kind of work 2 years ago and miss it which is why I still watch these types of videos :) Great presentation!!

[@MyOnlineTrainingHub]

Thanks so much! Maybe it's time for a little consulting to keep your toes in the water 😁

[@TheRandyGr]

@@MyOnlineTrainingHub If that's an offer, let me know, lol. I'd be cheap because I love doing it, lol.

[@MyOnlineTrainingHub]

😁

[@moizalihusainw1309]

This is summarizing all the challenges I have faced since I started working in excel. Once again amazing video and best part is the Tabular Data rules which should be enforced upon. 😅

[@MyOnlineTrainingHub]

If we enforced these rules, then I guess we'd almost have Access 😜

[@Desimahol507]

Thank you for sharing a valuable video.

[@MyOnlineTrainingHub]

My pleasure 😊

[@dagwould]

A few years ago I worked for a small national professional membership society. Every week I got sent an update on membership: new joins, resignations, etc. It came in an over-structured Excel file that I was supposed to 'analyze'. I couldn't of course, without flattening it into a datatable. I tried to get head office to prepare the data this way and offered to run a short video conference on pivot tables. But no one was interested. They preferred the inefficiency of 'pretty' presentations because "that's what everyone is used to". Needless to say, it was a 'design' industry profession.

[@MyOnlineTrainingHub]

😂they do tend to like the pretty things...hence why they are also typically Mac obsessed.

[@HachiAdachi]

Thank you, Mynda! Incidentally, I just sat through a "quality control problem solving" webinar (not sure why) where they demonstrated data collection in a summarized report format (*manual* pivot table) 😞. This was hosted by a reputable professional association and attended by almost 200... I hope these attendees (and the speaker) will eventually find this video... (Data hygiene should be taught more prominently...)

[@MyOnlineTrainingHub]

Yikes! It's an uphill battle getting this message out there that's for sure.

[@chrism9037]

Great video Mynda and so spot on!

[@MyOnlineTrainingHub]

Cheers, Chris!

[@songderick3089]

Amazing job! One more student from Africa, Cameroon gained! Weldone!!

[@MyOnlineTrainingHub]

Awesome! Welcome 🤗

[@endthefed9937]

Thanks for the great tips!

[@MyOnlineTrainingHub]

My pleasure! Thanks for watching.

[@glc8312]

Thanks for this useful advice ! Wished I knew it (and applied it) from the get go !😅

[@MyOnlineTrainingHub]

Better late than never 😉

[@markkendal5490]

Very helpful video, thank you.

[@MyOnlineTrainingHub]

Great to hear 🙏

[@robertedwards1240]

Thanks for this. Here I'd been thinking myself semi-expert. Always new things to learn!

[@MyOnlineTrainingHub]

Glad it was helpful!

[@tug_van_tuggles]

How I wish that people at work would watch this video. I'm forever helping teams with wacky data layouts.

[@MyOnlineTrainingHub]

Feel free to share it 😉 I’m happy to be the bad guy.

[@nevermind2407]

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

[@travel-warrior]

Miracles are possible 🎉 thanks for a wonderful insight

[@MyOnlineTrainingHub]

Yes they are! 😁🙏

[@joshuamanampiu6489]

Much appreciated. Thank you.

[@MyOnlineTrainingHub]

My pleasure! Thanks for watching 😊

[@user-zn4vo6ii2i]

great tip :) thank you for the video

[@MyOnlineTrainingHub]

Glad it was helpful!

[@stevewolfe6096]

The fundamental problem with Excel is that it is not a database. Each cell is nominally independent of every other cell in terms of data content type, display format and validation rules. It is great for working with limited data sets (for a knowledgeable user of course) and for prototyping more complex applications. The backwards compatibility requirement over its long existence and its almost (and in many cases total) fatal attraction as an easy to use database tool has cost many companies dearly. Excel(and Lotus 123 of course) had a massive head start on PC based database apps. Excel still has a better ease of use in producing nice looking reports but is weak in keeping base data table and analysis integrity.

[@MyOnlineTrainingHub]

Well said, Steve. I hope that this video teaches those who don't have a database as an option how to avoid the pitfalls of storing their data incorrectly.

[@jadolphson]

MS Access has what you’re asking for. It doesn’t have the same approachability, however.

[@tonykelpie]

This tutorial is very useful in discouraging people from trying to use Excel. Thank you. I am old enough to have designed and used spreadsheets manually; the key fact is that if you don’t understand the data and have a clear idea of it’s purpose then any computer program poses risks

[@scottekoontz]

@@jadolphson As database programmers we have found that the only thing harder to unscramble than an Excel solution is an Access solution. I can see why people use Excel, and know why they get frustrated and attempt Access, and fully understand why they return to Excel. We drop every Excel table we receive into a database (not Access) to find the math errors, which exist for nearly all solutions that are more than a single table that began as an export from a database. A far more approachable database is FileMaker, but most companies would rather deal with the errors and even hiring more staff to maintain Excel tables than pay for a better solution (and everyone has Excel). One such company hired a full-time worker who spent her entire day copying and pasting between spreadsheets as she worked between three monitors. This happens far too often.

[@dondgc2298]

I would disagree with you when you say it is not a database while agreeing that it is best for limited datasets. It’s a flat file database and not relational, but it’s definitely useful for both storing and analyzing data - within limits.

[@silvestrecamposano6317]

Thank you, Mam...

[@vernacularbarnarchitecture]

Yeah, but the worse the layout the funner the challenge! These tips are great, I learn something new every time.

[@MyOnlineTrainingHub]

😁Glad you like them!

[@heinzthedutch]

Thanks for the vid! Its an often made mistake indeed, however unfortunately often "caused" by IT departments thinking that this would help me out as a user, rather than them embracing the concept of self-service BI and simply providing us with data in the desired pivottable-proof/tabular format. My eternal battle at all the companies I've been working for...

[@MyOnlineTrainingHub]

Unfortunately, IT departments are very protective of 'their' data!

[@GeertDelmulle]

1 minute into the video and I know already that I'm going to "clearly present" (read: rub it under their noses) this video at work to anyone who cares (not) to see it. This is essential stuff! Thanks Mynda. 🙂

[@GeertDelmulle]

A short while ago I got the explicit request that "users can enter their data in a calendar layout" - meaning: years spread over the columns. So, a "proper data set" (level 1 normalized table) may be unacceptable to them. Your suggestion at the end is exactly what I did last year, and in a bit of a sneaky way at that: do the Power Query ETL and load the result of that directly into a Pivot Table. The reaction you then get is: "There, you see: it IS possible for you to turn it in to a good Pivot Table! Why all the fuss?!" My reaction to that: "Yes, I can. Can you do this, too?" They: --- (=> no) Me: my point exactly.

[@MyOnlineTrainingHub]

🤣 great story, Geert!

[@mikrom]

I mostly struggle with numbers formatted as text in the erp exports 😊 Fortunately never had to work with crappy tables as in your examples so far 😊

[@MyOnlineTrainingHub]

Numbers as text is super annoying. Thankfully, Power Query can automate fixing them.

[@whimpypatrol5503]

Nice, but that's always been my starting point. So, im not sure if i could milk my data, given the level of probing I do, any easier without additional information functions, vlookup tables, and so forth. Either way, this is all drill down logic, not inferential model building. Why can't managers grasp robust empirical math models?

[@BathBombTheRussians]

Another awesome video

[@MyOnlineTrainingHub]

Glad you enjoyed it 🙏

[@Gregorovitch144]

If you need to use a spreadsheet to process any significant amount of data and if you were only allowed to watch one single instruction video ever then this one would be it. The critical lesson here BTW is to notice that the USA and UK columns are redundant and that USA and UK are in fact a single data element, i.e. country, that only requires one coded column. A similar thing is done when designing database schema, it's a process called normalisation (which can be thought of as systematically removing data duplication and redundancy). What she's doing is putting the data in a kind of 3NF (third normal form) and because of that all Excel's tools can do exactly what they say on their tins out of the box with no issues.

[@MyOnlineTrainingHub]

Thanks for your support 🙏 appreciate you sharing another perspective.

[@Gregorovitch144]

@@MyOnlineTrainingHub And I appreciate your video because although I worked with relational databases for years I never really used spreadsheets. Now I do need to use one for a non-trivial task (which is why I've come across your video) so learning that Excel is built on the same principles as databases, something I didn't know, is a Godsend to me.

[@orleanralia3297]

thank you Teacher

[@MyOnlineTrainingHub]

My pleasure.

[@silvo4880]

Great video, thanks. I’m having these issues a lot with inherited documents and software I have no control over. Nobody else sees the issue and I just get left to untangle it. 😊

[@MyOnlineTrainingHub]

Hopefully you're making use of Power Query to automate the untangling: kzbin.info/www/bejne/gmWlpoiwmMh_ptE

[@silvo4880]

I am, it doesn’t come as naturally to me as Excel yet but I can see it’s benefits and I’m gradually learning it. Been watching a few other creators contents too but yours has some really practical examples that I can better relate to. 👍🏻

[@khalednady5831]

Could you please explain in video the main structure of excel formulas. mean how to understand the structure of the formula when writing it... Thanks

[@MyOnlineTrainingHub]

I actually cover that in the free example video for my Excel Expert course. You can see the video here: www.myonlinetraininghub.com/excel-expert-upgrade

[@Stratelier]

I semi-recently ran into the problem of grouping/separating raw (but similar) data across multiple sheets myself. This was in the context of tracking my progress in the annual writing challenge of National Novel Writing Month -- at the start of each new year, I would prviously just duplicate the most recent sheet and wipe the raw data (dates and wordcounts) for the new year, but this kind of bloated the workbook (especially since each sheet contained various formulaic summaries and small charts, which were duplicated in turn). So I recently created one sheet to log all previous years (which involved more or less just copy-pasting the raw data from each sheet and annotating it per year), while maintaining a separate sheet where I can just type in a year number and it will (via formulas) pull the relevant data from said log. Not 100% optimal, perhaps, but it resulted in a much neater file overall. (I still maintain a separate page for the current year in progress)

[@MyOnlineTrainingHub]

Great to hear you figured this out on your own! I recommend you check out Slicers to automate filtering the data to display the data you want, so you can do away with the final separate page for the current year and formulas to extract the data: kzbin.info/www/bejne/aHmakoJ-gL9phrs

[@Stratelier]

@@MyOnlineTrainingHub Yeah, there are definitely a few automation features I've yet to explore at all (I've become quite handy with selective aggregates and lookups in the meantime). At home, I don't use Excel proper (rather, LibreOffice) and at work they migrated almost everything to Microsoft 365 (and Excel for Web is missing at least five features I routinely use). For this case specifically, having a separate sheet for the current year is simply a pragmatic decision because it means not having to log it into the larger sheet while the challenge is still in progress.

[@michaelkaliski7651]

Great advice but the biggest mistake that people make is to use Excel as a database instead of Access. Spreadsheets for analysis, databases for data entry and storing data.

[@MyOnlineTrainingHub]

Agree. And for those who don't have database skills, best they know how to at least store data in Excel properly.

[@00wheelie00]

I get too much data in the wrong format for pivot tables. I've switched to just reading it into a Pandas data table. Most of these issues are a simple script away and I can dump the result in a tabular table easily. The more I've used python the mkre I realized that at least for charts it's far superior to excel. I really only use excel anymore to use pivot tables to make the tables I need for my Word reports.

[@MyOnlineTrainingHub]

There's not much Excel can't do in terms of charts, but you have to know how. If you're interested, look up Roberto Mensa on LinkedIn. His Excel charts are mind blowing. That way you can use Power Query to clean the data - no coding required. Of course, if you're already comfortable with Python that's cool too. Python charts are also nice.

[@00wheelie00]

@@MyOnlineTrainingHub I'll make sure to check it out. I use excel occasionally 8f I have to make a quick chart of something. Always willing to learn new and better ways. I come from a programming background and knew python already and it was the logical choice. An example: I get a standard monthly excel 'report' that I need to extract data from and analyse historically and year to date. The layout is 'wrong', but always in the same format. I just drop the file in a directory name it 2024-1.xlsx etc... and run the script each month. The script writes out tables to excel files that are linked in Word and automatically updated. I looked at VBA to build the same and it's possible but just more work. I did write a Word macro that scans the directory and updates the graphs from the new images automatically.

[@MyOnlineTrainingHub]

Impressive!

[@johnnyragadoo2414]

Nice tips. Rules of data normalization can be worth keeping in mind for multiple tables. It's a shame pivot tables aren't updated as the source data is modified.

[@MyOnlineTrainingHub]

Yeah, dynamic pivot tables would be great. The new PIVOT function solves that problem: www.myonlinetraininghub.com/excel-groupby-and-pivotby-functions

[@seengyadu]

Thank you for this - basically if you analyse the data and normalise the rules as much as practicable, your life later will be much easier!!

[@MyOnlineTrainingHub]

Yes, exactly 👍

[@derekeano]

The solution really was to put the data into a SQL-friendly table.

[@MyOnlineTrainingHub]

Yep. 😅

[@soubhikd]

much needed

[@MyOnlineTrainingHub]

Glad you agree 😊

[@stopthink9000]

What I've found is the executive team has legacy "data" stored in these non-standard report types and thinks it's fantastic to be used as a resource to build other reports from. Then you're on a wild goose chase to find out which cell is a copy of another cell, what calculations have been applied to that original cell, where it was getting it's source data from, etc. etc. Then you have to build tabular data based off a report with the year as part of a merged cell in the title. I like your way much better!

[@MyOnlineTrainingHub]

Sounds like a nightmare, but these data tables are everywhere 🤦‍♀️ at least you can use Power Query to automate fixing it 😅

[@peterc2248]

What amazes me is how many large organisation here in the UK (banks, utilities, government etc) clearly do not store their data efficiently. My water supplier has at least two versions of my contact details and uses both in different systems. My local Council is even worse with at least five, yes five, different customer IDs for me used by different departments. This is basic data management stuff. No wonder I don't trust them with my data 🙂

[@MyOnlineTrainingHub]

Unbelievable! 🤦‍♀️

[@Anti_Woke]

TL;DR A spreadsheet application is not a database engine. No IT professional would store large data in a spreadsheet, let alone attempt to use it for anything useful. [Spreadsheets are the IT equivalent of 'back of an envelope'. Yes, I started working with spreadsheets (Visicalc) before PCs and Macs existed].

[@MyOnlineTrainingHub]

I somewhat agree, however not every business has an IT professional capable of maintaining a database employed, so what we have in reality is millions of businesses around the world storing data in Excel. I'm just helping them do that in a database layout to avoid errors and make their life easier.

[@IamTheReaper911]

Just stop enough of these videos. Stop already..... Just stop with always doing great informational videos 😉 Another killer video, you never disappoint 😎

[@MyOnlineTrainingHub]

You had me wondering there for a moment 😅

[@gova3vardhan]

Hey, can we upload any PDF/Word file in a dedicated Excel cell and share the sheet with a team, so they can access the file I uploaded?

[@maciejkopczynski55]

You can embed a word or pdf document inside of excel or just create a hyperlink to a cloud location of the desired word/pdf ensuring your coworkers have access to the resources.

[@NikosGeorgosopoulos]

If you use power query, having multiple sheets or even better, files for the data does not come too hard. I get your point and I agree up to a point but having people still today collect and store data in a proprietary file format such as xlsx for an application designed to be a reporting tool and data analysis, is a bad idea. That is why we have databases and data entry forms with and without Microsoft's help.

[@MyOnlineTrainingHub]

Absolutely, use a database if you can. For those who can't, this is for them.

[@LooseArrowBoy]

While expanding the complexity of a tool, I came to realize that data that's more complex for a person to summarize is easier for Excel to analyze. This is true in the reverse as well. I tried building complex formulas to pull data from a human made schedule. Through trial and error I realized pulling that data into a form that appeared unorganized was much easier to manipulate. It may seem like having a unique row for every scheduled instance is redundant when you could just have one row per date or personnel. Instead it makes the problem harder to solve. It's interesting how humans can skip logical steps to reach the correct conclusion, but at scale, it's not sustainable. Computers (for now) need baby steps to reach the conclusion, but computers can do it right countless times when coded correctly with clean data sets.

[@MyOnlineTrainingHub]

Interesting insights. Thanks for sharing.

[@cocoplume4421]

All of these are very well but when you work with other users (your manager for example) who doesn't have the same level and doesn't want to learn... You don't have the choice to use these kind of tables. More when you get tables already organised from other parties, it could be a long work to reorganise the data in a tabular layout.

[@MyOnlineTrainingHub]

I agree there will always be exceptions which is why I recommended my Power Query Unpivot video at the end so you can automate the conversion of these undesirable layouts into a tabular layout; kzbin.info/www/bejne/Y3qwop6daJqUmqMsi=z9aQeebZq99a1q0t

[@carsonjamesiv2512]

GOOD!

[@MyOnlineTrainingHub]

Thanks for watching 🙏😊

[@mogarrett3045]

love it

[@MyOnlineTrainingHub]

Thank you!

[@nikimolnar99]

Excel is the reason I learned relational databases and coding 😅

[@MyOnlineTrainingHub]

😁

[@Glenn42L]

How do you stop pivot sorting by named month eg add sales person April , or a city called March and pivot will sort it as a month.

[@MyOnlineTrainingHub]

Hmmm, it should only sort by month if it's in a column that contains other month names.

[@Jojo-gf5qb]

I hate that I sound like a snob, but these are my excel pet peeves 1. Bad data layout 2. 3rd party software that only generates report, and a badly formated report 3. Inconsistent formatting (your random punctuations, space and etc) To make it worse, it is harder to collaborate with data owner for improvement as they are used to dealing with bad data. They rather use pre-loaded report and format the data manually (yes by copy-pasting each item) limiting potential and wasting resources in dealing with the data. Big company understand data, and they value structure. Small company does not appreciate data structure and waste tons of resources dealing with inconvenience. Thank god for the existence of Power Query! Imagine a report generated in the format below Date Company Row 1 Invoice # Department Amount Row 2 1/1/2024 (Text) Company A Row 3 12345 Department A Row 4 12345 WHY!?

[@MyOnlineTrainingHub]

I hear you...hence my video. It's mostly a rant in disguise 😁

[@Jojo-gf5qb]

@@MyOnlineTrainingHub I can only thank all the Excel community, like yourself, so willingly to share their knowledge. If only all excel content creators' videos can reach a wider audience. It is a great deal to safe time and trouble by investing upfront, but not many appreciate it.

[@1978smt]

Exactly!

[@1978smt]

​@@MyOnlineTrainingHubthis is my constant rant at work Everything you said, I've said to so many people. They don't listen 🤦🏻‍♂️😅

[@olliehopnoodle4628]

Maybe not exciting but this video has very vital information to avoid a lot of work later.

[@MyOnlineTrainingHub]

Indeed. Feel free to share it with those who need it.

[@markbrad123]

3rd Normal form

[@victorneumann8467]

Maybe I missed a point, should data be formatted as a table prior to applyng the pivot table?

[@sledgehammer-productions]

Not necessarily, but I would put my raw data always in a proper table, also more predictable when adding data and then refreshing stuff. When your data is just in a bunch of rows, the pivot table might not "see" that rows have been added.

[@MyOnlineTrainingHub]

@victorneumann8467 as @sledgehammer-productions said, it's ideal if you can format in a table as his will also save you time and reduce errors and omissions.

[@victorh.neumann5945]

@@MyOnlineTrainingHub and @sledgehammer-productions, thanks for answering promptly.

[@paba10]

I have noticed that when I use the Tabular function, the excel file becomes much slower. Could there be a reason for that?

[@MyOnlineTrainingHub]

I suspect it's the formulas as opposed to the tabular layout making your file slow. This video covers the common causes of slow files: kzbin.info/www/bejne/m2XRoGZ-pbxkhLs

[@bartoni79]

Or the classic is the company forcing you to use “7 key mgt reports” then mgt asking you to create new analysis and the system owner refusing access to the data. Thus you spend hours/ days adding up separate monthly reports with merged cells everywhere… then mgt wonders why there are errors. It then transpired the mgt reports have merged additional cells mid way through the year thus disrupting your automated data gathering.

[@MyOnlineTrainingHub]

😁this type of shenanigans is more common than people realise!

[@KKB-tt4lj]

When I select "Order Date" I cannot ungroup the years... any suggestions? Thanks!

[@MyOnlineTrainingHub]

It's possible they're not proper dates, i.e. they're text. Not sure what data you're using. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@KKB-tt4lj]

@@MyOnlineTrainingHub I downloaded the file from the link in your video...

[@roywilson9580]

Thanks for addressing one of my pet peeves!

[@MyOnlineTrainingHub]

My pleasure 😉

[@rudiklein]

When you work in a large international company, you will be aware of all the wrong formatting. Once you've nailed it, they love to change it slightly. Don't get me started about the various regional settings and date formats.

[@MyOnlineTrainingHub]

I hear you! 🤦‍♀️

[@domenicoieracitano2138]

The Scream by Edvard Munch is everywhere...

[@1goore]

I dream of a better world. A world in which watching this vid is mandatory BEFORE using Excel.

[@MyOnlineTrainingHub]

😁Please share the video...it might just come true 😜

[@whimpypatrol5503]

It would help if the screen shots were clear

[@MyOnlineTrainingHub]

I wonder if the video wasn't playing in HD 🤔you can always download the file here: www.myonlinetraininghub.com/excel-tabular-data-format

[@MrBond249]

I'm desperately trying to stop people from recording important data in Excel. PowerApps and Dataverse all the way.

[@MyOnlineTrainingHub]

😁good luck with that! I know what you mean, but like I said to someone else yesterday, many small companies don't have the capacity to employ a database specialist to manage their data. In which case, Excel is a great option...as long as you use it the right way.

[@Robert-qw3lr]

if you use Excel this much, you need to learn to code. You're halfway there. Start with python and create a Sqlite database. Also, you can read and write Excel files in python easily. Doing all this manual work in Excel is slowing you down.

[@MyOnlineTrainingHub]

If you’re recommending using Python to clean the data into a tabular layout then I’d recommend using Power Query instead because it’s way easier to use and once set up, you can update the data with the click of a single button. I’ve seen people use overly complicated Python code to do things Excel can automate using the GUI. Not saying Python isn’t worth learning, just saying I wouldn’t use it to clean and transform data.

[@Robert-qw3lr]

@MyOnlineTrainingHub I'm saying, learn additional skills and combine them. If you can get this far with Excel, you already have a mindset to write code. Write code to do all the heavy work, spit it out to Excel when you need a pretty report and polish it manually in Excel to get it looking the way you want. Excel is one set of tools. Don't fall in love with only one set of tools. Learn multiple sets of tools and use whichever is best for the occasion.

[@MyOnlineTrainingHub]

Absolutely agree you should learn multiple tools. But I still say do your data cleaning in Excel with Power Query as opposed to over engineering it with Python, because you can't integrate Python with Excel easily to make it a one click process to update your reports like you can with Power Query.

[@tihomirhristov77]

💚

[@superscatboy]

TL;DR do what databases do

[@MyOnlineTrainingHub]

Assuming you know what databases do 😉

[@superscatboy]

@@MyOnlineTrainingHub True, lol

[@martinploughboy988]

Which is why Excel is not a good tool to store data, use a database & call it from Excel to populate an excel sheet.

[@MyOnlineTrainingHub]

Absolutely, and if you don't have database skills, use Excel, but make sure you use it this way. 😉

[@martinploughboy988]

@@MyOnlineTrainingHub Excel is a great tool for spreadsheets, but if you need a database learn how to create one. It really is a bad tool for databases & what you are doing is introducing the basics of databases. I suggest extend the training to actually create a database.

[@jaadotech]

OMG , I actually clicked on this. who is the intended audience? The software development engineers who make/format and hardwire the extraction reports from systems? Or is she giving advice so we can waltz into another department and tell them to improve their Excel formatting skills? WTF?

[@MyOnlineTrainingHub]

The intended audience are Excel users who skip the tabular data layout step and start inputting their data in the wrong layout and then wonder why they can't use functions and PivotTables to further summarise and analyse their data. Hope that clarifies things.

[@sergeyalexandrovich8443]

Thank you very much for the explanation. While AI is taking over the world, Excel is still unable to process tables intuitively created by users 😆

[@MyOnlineTrainingHub]

Probably won't be too far away before it can make sense of messy data.

[@tanveerabbas3271]

you beauty!

[@skenming]

I feel like I'm being watch😨

[@MyOnlineTrainingHub]

😁

[@agp1745]

TLDR: use long form datatables

[@MyOnlineTrainingHub]

Yep, and if you don’t know what that means, I explain it in the video. 😉

[@shadeburst]

Why is everyone so obsessed with pivots when the output looks like a dog's breakfast? Quick and nasty. The "ideal layout" is how the data was imported from the accounting package and how it was tortured into the five bad formats, who knows. I spent a couple of hours developing templates which I keep updated as new functions become available. The output looks magic and is rapidly understandable. If only Excel had a SQL SELECT function -- without writing code that nought point nought nought per cent of everyday Excel users can maintain. I could be run over by a cement truck tomorrow, or simply not wake up ever again.

[@MyOnlineTrainingHub]

You can use Power Query to automate gathering data from SQL databases without needing to know how to write any code. Once click of the Refresh All button has your reports updated without any editing required. You can make PivotTables look 'nice'. Check out this video: kzbin.info/www/bejne/pp2Tm5qYqrSkns0

[@shadeburst]

@@MyOnlineTrainingHub Whatever.

[@user-gj3kz7cm3x]

“The Mistake Almost EVERY Excel User Makes”… using excel

[@ytugtbk]

You're British. What are you doing using an American product? Doesn't Siemens, SAP, or something from the EU that comes with immigrant guarantees offer a better, more humane, alternative to Excel?

[@MyOnlineTrainingHub]

The short answer is no, there's nothing EU created that comes close to doing what Excel does.

[@diesquickly00]

If you use pivot tables you already lost. Show your damn work. Don’t be lazy.

[@stickoutofthemud]

The Mistake Almost EVERY Excel User Makes is putting currency symbol garbage characters down entire columns. The second worst mistake is using unnecessary leading zeros in dates. NO one was ever taught to count 01, 02, 03....

[@MyOnlineTrainingHub]

Your rant is most welcome 😉

[@stickoutofthemud]

@@MyOnlineTrainingHub I do try. Did you hear 30 years of frustration coming through? 🤣🤣😇

[@MyOnlineTrainingHub]

😂I'm glad I can provide a safe space!