The Mistake Almost EVERY Excel User Makes (Free File)

Поділитися
Вставка
  • Опубліковано 30 жов 2024

КОМЕНТАРІ • 247

  • @MyOnlineTrainingHub
    @MyOnlineTrainingHub  7 місяців тому +5

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

    • @chriswall4795
      @chriswall4795 7 місяців тому +1

      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
      @notesfromleisa-land 7 місяців тому

      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
      @jonr6680 7 місяців тому

      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
      @atauriu 7 місяців тому

      download link not working.

  • @mangobrother
    @mangobrother 7 місяців тому +89

    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
      @MyOnlineTrainingHub  7 місяців тому +2

      So pleased you find them helpful 🙏😊

    • @MosesJingo
      @MosesJingo 6 місяців тому +3

      I share the same frustration as you!

    • @ChrisM541
      @ChrisM541 6 місяців тому

      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
      @mangobrother 6 місяців тому

      @@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
      @MarvindeSalit 6 місяців тому +3

      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
    @supinderbabra7068 7 місяців тому +36

    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.

  • @roberth.9558
    @roberth.9558 7 місяців тому +5

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

  • @anurmertah3116
    @anurmertah3116 7 місяців тому +3

    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 ❤

  • @pietergeerkens6324
    @pietergeerkens6324 6 місяців тому +12

    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.

  • @Ch715A
    @Ch715A 7 місяців тому +15

    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
      @MyOnlineTrainingHub  7 місяців тому

      Wonderful to hear!

    • @LM-xg1ty
      @LM-xg1ty 7 місяців тому

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

    • @notesfromleisa-land
      @notesfromleisa-land 7 місяців тому

      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
    @name_it 7 місяців тому +13

    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
      @MyOnlineTrainingHub  7 місяців тому +8

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

  • @brandonzzz9924
    @brandonzzz9924 6 місяців тому +5

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

  • @silversolver7809
    @silversolver7809 7 місяців тому +2

    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.

  • @angieadwin8515
    @angieadwin8515 3 місяці тому

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

  • @HugRunner
    @HugRunner 7 місяців тому +3

    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
      @MyOnlineTrainingHub  6 місяців тому +1

      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
      @dondgc2298 5 місяців тому

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

  • @ExcelTutorials1
    @ExcelTutorials1 6 місяців тому +1

    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

  • @TheRandyGr
    @TheRandyGr 6 місяців тому

    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
      @MyOnlineTrainingHub  6 місяців тому +1

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

    • @TheRandyGr
      @TheRandyGr 6 місяців тому

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

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  6 місяців тому

      😁

  • @victorneumann8467
    @victorneumann8467 7 місяців тому

    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
      @MyOnlineTrainingHub  7 місяців тому

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

  • @notesfromleisa-land
    @notesfromleisa-land 7 місяців тому +1

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

  • @ClaudioCP
    @ClaudioCP 4 місяці тому

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

  • @gerardthompson196
    @gerardthompson196 4 місяці тому

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

  • @HachiAdachi
    @HachiAdachi 7 місяців тому

    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
      @MyOnlineTrainingHub  7 місяців тому

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

  • @richardhay645
    @richardhay645 7 місяців тому +1

    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
      @MyOnlineTrainingHub  7 місяців тому

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

  • @moizalihusainw1309
    @moizalihusainw1309 7 місяців тому

    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
      @MyOnlineTrainingHub  7 місяців тому +1

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

  • @Dezz005
    @Dezz005 6 місяців тому

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

  • @patslee1394
    @patslee1394 7 місяців тому +1

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

  • @GeertDelmulle
    @GeertDelmulle 7 місяців тому +1

    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
      @GeertDelmulle 7 місяців тому

      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
      @MyOnlineTrainingHub  7 місяців тому +1

      🤣 great story, Geert!

  • @bpbeary8011
    @bpbeary8011 7 місяців тому +19

    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
      @MyOnlineTrainingHub  7 місяців тому

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

    • @aSpyIntheHaus
      @aSpyIntheHaus 6 місяців тому

      Everyday right!
      Ohh man

    • @fernsehdesign
      @fernsehdesign 5 місяців тому

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

  • @re-nz3sk
    @re-nz3sk 6 місяців тому +4

    Your video should be required viewing for many Excel users.

  • @jonathandawson3091
    @jonathandawson3091 6 місяців тому

    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.

  • @MarekBernolak
    @MarekBernolak 28 днів тому

    This should be taught to every person working with Excel, at the very beginning of their career. 🥇🥇🎓🎓

  • @bernardchong4321
    @bernardchong4321 28 днів тому

    Very informative and useful for my daily work. Thank you 🙏🏻

  • @scottdobson1276
    @scottdobson1276 6 місяців тому +2

    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

  • @Desimahol507
    @Desimahol507 6 місяців тому +1

    Thank you for sharing a valuable video.

  • @dagwould
    @dagwould 6 місяців тому +1

    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
      @MyOnlineTrainingHub  6 місяців тому +1

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

  • @stevewolfe6096
    @stevewolfe6096 6 місяців тому +3

    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
      @MyOnlineTrainingHub  6 місяців тому

      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
      @jadolphson 6 місяців тому +1

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

    • @tonykelpie
      @tonykelpie 6 місяців тому

      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
      @scottekoontz 6 місяців тому

      @@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
      @dondgc2298 5 місяців тому

      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.

  • @gioyfa
    @gioyfa 7 місяців тому

    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.

  • @robertedwards1240
    @robertedwards1240 6 місяців тому

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

  • @songderick3089
    @songderick3089 6 місяців тому

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

  • @humansizedaperture
    @humansizedaperture 7 місяців тому

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

  • @derekeano
    @derekeano 6 місяців тому +4

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

  • @heinzthedutch
    @heinzthedutch 7 місяців тому

    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
      @MyOnlineTrainingHub  7 місяців тому

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

  • @Gregorovitch144
    @Gregorovitch144 6 місяців тому +1

    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
      @MyOnlineTrainingHub  6 місяців тому

      Thanks for your support 🙏 appreciate you sharing another perspective.

    • @Gregorovitch144
      @Gregorovitch144 6 місяців тому +1

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

  • @silvo4880
    @silvo4880 6 місяців тому

    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
      @MyOnlineTrainingHub  6 місяців тому +1

      Hopefully you're making use of Power Query to automate the untangling: ua-cam.com/video/L4BuUzccLpo/v-deo.html

    • @silvo4880
      @silvo4880 6 місяців тому

      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. 👍🏻

  • @endthefed9937
    @endthefed9937 7 місяців тому +1

    Thanks for the great tips!

  • @Anti_Woke
    @Anti_Woke 6 місяців тому +1

    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
      @MyOnlineTrainingHub  6 місяців тому +1

      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.

  • @johnnyragadoo2414
    @johnnyragadoo2414 6 місяців тому

    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
      @MyOnlineTrainingHub  6 місяців тому +1

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

  • @Stratelier
    @Stratelier 6 місяців тому

    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
      @MyOnlineTrainingHub  6 місяців тому

      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: ua-cam.com/video/2H7aOHKZ6PY/v-deo.html

    • @Stratelier
      @Stratelier 6 місяців тому +1

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

  • @travel-warrior
    @travel-warrior 6 місяців тому

    Miracles are possible 🎉 thanks for a wonderful insight

  • @mikrom
    @mikrom 7 місяців тому

    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
      @MyOnlineTrainingHub  7 місяців тому +1

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

  • @chrism9037
    @chrism9037 7 місяців тому

    Great video Mynda and so spot on!

  • @tug_van_tuggles
    @tug_van_tuggles 6 місяців тому +1

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

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  6 місяців тому +1

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

  • @glucky30
    @glucky30 7 місяців тому

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

  • @silvestrecamposano6317
    @silvestrecamposano6317 6 місяців тому

    Thank you, Mam...

  • @markkendal5490
    @markkendal5490 7 місяців тому

    Very helpful video, thank you.

  • @seengyadu
    @seengyadu 6 місяців тому

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

  • @00wheelie00
    @00wheelie00 6 місяців тому +1

    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
      @MyOnlineTrainingHub  6 місяців тому +1

      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
      @00wheelie00 6 місяців тому

      @@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
      @MyOnlineTrainingHub  6 місяців тому

      Impressive!

  • @LooseArrowBoy
    @LooseArrowBoy 7 місяців тому

    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.

  • @joshuamanampiu6489
    @joshuamanampiu6489 7 місяців тому

    Much appreciated. Thank you.

  • @gova3vardhan
    @gova3vardhan 7 місяців тому +1

    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
      @maciejkopczynski55 7 місяців тому

      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.

  • @michaelkaliski7651
    @michaelkaliski7651 6 місяців тому +1

    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
      @MyOnlineTrainingHub  6 місяців тому +2

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

  • @nevermind2407
    @nevermind2407 6 місяців тому +2

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

  • @orleanralia3297
    @orleanralia3297 6 місяців тому

    thank you Teacher

  • @TaylorChang-m4d
    @TaylorChang-m4d 6 місяців тому

    great tip :) thank you for the video

  • @stopthink9000
    @stopthink9000 6 місяців тому

    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
      @MyOnlineTrainingHub  6 місяців тому

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

  • @NikosGeorgosopoulos
    @NikosGeorgosopoulos 7 місяців тому

    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
      @MyOnlineTrainingHub  7 місяців тому

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

  • @whimpypatrol5503
    @whimpypatrol5503 5 місяців тому +1

    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?

  • @cocoplume4421
    @cocoplume4421 6 місяців тому

    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
      @MyOnlineTrainingHub  6 місяців тому

      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; ua-cam.com/video/-IMqkg35adA/v-deo.htmlsi=z9aQeebZq99a1q0t

  • @peterc2248
    @peterc2248 6 місяців тому

    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 🙂

  • @BathBombTheRussians
    @BathBombTheRussians 7 місяців тому

    Another awesome video

  • @khalednady5831
    @khalednady5831 6 місяців тому

    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
      @MyOnlineTrainingHub  6 місяців тому

      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

  • @IamTheReaper911
    @IamTheReaper911 7 місяців тому +1

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

  • @nikimolnar99
    @nikimolnar99 6 місяців тому +1

    Excel is the reason I learned relational databases and coding 😅

  • @Jojo-gf5qb
    @Jojo-gf5qb 7 місяців тому +2

    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
      @MyOnlineTrainingHub  7 місяців тому +4

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

    • @Jojo-gf5qb
      @Jojo-gf5qb 7 місяців тому

      @@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
      @1978smt 5 місяців тому

      Exactly!

    • @1978smt
      @1978smt 5 місяців тому

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

  • @olliehopnoodle4628
    @olliehopnoodle4628 7 місяців тому

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

  • @soubhikd
    @soubhikd 7 місяців тому

    much needed

  • @Robert-qw3lr
    @Robert-qw3lr 6 місяців тому +1

    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
      @MyOnlineTrainingHub  6 місяців тому +1

      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
      @Robert-qw3lr 6 місяців тому +1

      @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
      @MyOnlineTrainingHub  6 місяців тому +2

      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.

  • @markbrad123
    @markbrad123 7 місяців тому

    3rd Normal form

  • @rudiklein
    @rudiklein 7 місяців тому

    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.

  • @KKB-tt4lj
    @KKB-tt4lj 5 місяців тому

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

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  5 місяців тому

      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
      @KKB-tt4lj 5 місяців тому

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

  • @Glenn42L
    @Glenn42L 6 місяців тому

    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
      @MyOnlineTrainingHub  6 місяців тому

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

  • @carsonjamesiv2512
    @carsonjamesiv2512 6 місяців тому

    GOOD!

  • @paba10
    @paba10 7 місяців тому

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

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  7 місяців тому +1

      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: ua-cam.com/video/e4no3HpW1NY/v-deo.html

  • @victorneumann8467
    @victorneumann8467 7 місяців тому

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

    • @sledgehammer-productions
      @sledgehammer-productions 7 місяців тому

      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
      @MyOnlineTrainingHub  7 місяців тому

      @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
      @victorh.neumann5945 7 місяців тому

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

  • @levibono4780
    @levibono4780 Місяць тому

    it is good to learn in excel foulmart

  • @bartoni79
    @bartoni79 5 місяців тому

    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
      @MyOnlineTrainingHub  5 місяців тому

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

  • @mogarrett3045
    @mogarrett3045 7 місяців тому

    love it

  • @roywilson9580
    @roywilson9580 7 місяців тому

    Thanks for addressing one of my pet peeves!

  • @adrianconnolly8568
    @adrianconnolly8568 4 місяці тому

    would it not be better to use a database unless you want quick calculations

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 місяці тому

      Sure, if you have the infrastructure and budget for managing databases. Most small businesses don't have this luxury.

    • @adrianconnolly8568
      @adrianconnolly8568 4 місяці тому

      @@MyOnlineTrainingHub sorry but if you have office 365 for business you have ms access sub

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 місяці тому

      And you still need someone with Access skills, which are thin on the ground these days.

  • @1goore
    @1goore 6 місяців тому

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

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  6 місяців тому

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

  • @whimpypatrol5503
    @whimpypatrol5503 5 місяців тому

    It would help if the screen shots were clear

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  5 місяців тому

      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
    @MrBond249 6 місяців тому

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

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  6 місяців тому

      😁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.

  • @domenicoieracitano2138
    @domenicoieracitano2138 6 місяців тому

    The Scream by Edvard Munch is everywhere...

  • @tihomirhristov77
    @tihomirhristov77 6 місяців тому +1

    💚

  • @martinploughboy988
    @martinploughboy988 6 місяців тому

    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
      @MyOnlineTrainingHub  6 місяців тому

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

    • @martinploughboy988
      @martinploughboy988 6 місяців тому +1

      @@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
    @jaadotech 7 місяців тому

    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
      @MyOnlineTrainingHub  7 місяців тому

      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
    @sergeyalexandrovich8443 6 місяців тому

    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
      @MyOnlineTrainingHub  6 місяців тому +1

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

  • @superscatboy
    @superscatboy 6 місяців тому +1

    TL;DR do what databases do

  • @agp1745
    @agp1745 5 місяців тому

    TLDR: use long form datatables

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  5 місяців тому

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

  • @shadeburst
    @shadeburst 7 місяців тому

    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
      @MyOnlineTrainingHub  7 місяців тому

      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: ua-cam.com/video/pl0jgbuOqhk/v-deo.html

    • @shadeburst
      @shadeburst 7 місяців тому

      @@MyOnlineTrainingHub Whatever.

  • @skenming
    @skenming 7 місяців тому

    I feel like I'm being watch😨

  • @Charles-m7j
    @Charles-m7j 5 місяців тому

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

  • @ytugtbk
    @ytugtbk 6 місяців тому

    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
      @MyOnlineTrainingHub  6 місяців тому +1

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