excel because it is widely used in the companies i worked at. the statistics add-on was a great move for excel. but cleaning the data with excel is not fun.
I would say that Excel is like an Swiss army knife you can do a little bit of everything but are you going to cut down a tree for god's sake use a chainsaw
This is me trying to get my employers to switch to SQL. They'd have Excel files with millions of rows, the files themselves would be GB's in filesize, and everybody would be trying to edit the file at once on an online hard drive, causing it to keep getting corrupted over and over again, and there was barely any security.
“What up, my name is, and I cover these topics in this channel” got me to subscribe so fast. I hate the “please subscribe, it helps the channel!!!” Like… yeah, I know. But why should I subscribe? You just did it flawlessly. 10/10.
Hello Luke. I got three certificates from google & coursera of data analyst , and five more to go. It was bit fun and bit tough learning this new course. Your motivation led me to know how wonderful is the world with DATA.
Its not just about dataset size. The nice thing about code based data processing is you can track everything you or other person did to the data. Its quite hard to track if somehow other person change value of a certain cell by mistake in excel.
Complex interrelated data also benefit from a database approach, regardless of the amount of data you have, so it is not just the size of data. You can have a series of tables that relate to each other via primary and foreign keys and then with SQL queries you can very quickly combine elements of different tables. Databases are also good for maintaining data integrity vs spreadsheets. Until you spend sometime learning basic SQL and play around with databases it is difficult to fully appreciate what you might be missing for your needs.
@@henrylawson430 Is there a way to easily access, add and sync data to databases cross platform (windows, ios, android) with support for offline access? Like, google sheets is so convenient for all these that I don’t think I can replace it..
@@BakrAli10 at the moment I use Libreoffice Base to connect to a Mariadb (MySQL clone) server on an Ubuntu laptop. So when I take the laptop, with me the data goes with me. My Windows PC also runs Libreoffice and can connect to the laptop, I just need to have the laptop running. This is useful because my Windows PC is more powerful and can run larger spreadsheets more effortlessly. The next stop would be to move the Mariadb databases to the cloud on a Linux VM. As for having all the data offline, I could perhaps replicate the cloud database to my laptop database, but I haven’t worked that out yet. Because I am using MySQL/Mariadb, I am guessing it will be easier to do other approaches in the future with regards to IOS apps etc. But it is simple to install PHPmyadmin, which provides a website front end to your database.
Excel is the Swiss Army Knife of data tools. It's one of the few Microsoft programs I can honestly recommend. That being said, I'm glad you're getting the word out that it isn't always the best solution for a given problem, even if we wish it were.
In my previous position, I used VBA to automate a lot of excel sheets, a couple Access dbs, and even the front-end to our mainframe since it supplied a com interface. The result was a mass of spaghetti code and different pieces of the "application" spread through out the network with different versions of each being file being saved to associates local machines. It was a nightmare.
The problem with most companies that I've experienced is a lack of support for production level employees. They don't want average users deploying and managing a sql server. So instead we use power query as a main data consumption tool and have a "database" workbook and separate user level dashboards that pull from that data workbook.
Ding ding ding! Love this as I have felt the same in the companies I've seen. We need to invest more in these production level employees to better set up the entire organization.
The other 'scary' software that gets out of hand in many companies is MS Access. It too is NOT designed for large implementations. There are a ton of issues caused by use of that platform, especially in multi-user deployments. It, like excel, seems to be a great and easy way to get things put together solving issues... but long term a real solution should be used. I have felt all the pain you express in this video, and far more than one time. Often I have been the person asked to clean up after years of use and many ingrained issues caused by these kinds of improper solutions. SQL for data, and using other tools for data entry and dashboards... 100% agree.
tbh spreadsheets are ALWAYS a nightmare to deal with. How did you clean this data? Which is the most current version of the file? how did you calculate this column? did anyone manipulate this data illegally (financial fraud sends greetings)? how do I replicate this analysis on my own identical dataset? How will I port this to our production processes? Just use R or python with a predefined environment for everything. Put every data processing process under source control. dont rely on employee generated data, use the source-controlled process and process data straight off the immutable central DB. It's a simple process, but it eliminates so god damn many problems.
Hi, I must confess to being guilty of using excel when it was more appropriate to use a data base and trying to compensate with heavy use of formulas and VBA. I’ve been out of the workforce a few years now and thinking of getting back into it. I’m going to spend some time learning a better way though. I think part of it for me was I was self taught and fixing legacy excel tools and automating repetitive tasks without thinking if it was the appropriate tool. I ran into the limits of excel a lot.
I have been stumbling on such problems with excel for a while now and can totally relate. I am a Simulation-Control Systems Engineer and in reality don't really need excel. But I have programmed thousands of lines of code and I am quickly growing tired of it (eyes hurting :P ). I use excel to make my sort of custom GUI for my work. At times wherein you have a problem with the speed, excel is capable of being integrated with other tools. You can even trigger stuff from excel and then get your results for visualization. Great video by the way! Well structured and did not feel bored at all while watching!
Yeah, completely agree! As much as I have my complaints about excel, it can do some pretty powerful things! BUT to do those things can be more burdensome sometimes than using another tool. Glad you found my video entertaining! That was what I hoped with this!
Because of that I divide my sheets on several sections. Each sheet one data type. Customer, product, order and one sheet that combines and does the calculation. I use cloud and integrate a link into the sheet. I have no data loss and even if one sheet gets corrupted and or the data is too much, I can divide it to a specific date and change the maths and algorithm in the sheet in accordance.
Spreadsheets are great for small scale projects, and very easy to learn. When expanding a project like your example, you could have used a DataBase and some python scripts to take the workload off the spreadsheet. That said, there's a point to exploring new solutions. Good insight. But absolutely preposterous title :D
Oh my gosh. That is the perfect example of technical debt. Easy to start, but the project's complexity it will catch up and starting from scratch with more suitable tools will be more needed.
hey Luke, Just finished my bachelors in economics and heading into data analysis field. Looking forward towards new content to stay motivated and exited
You've described two roles one is a insight analyst and the other an MI BI Analyst. One does reporting excel and makes slides. The other is BI building dashboard using something like SSRS or Power BI for dashboards. The issues arise when teams build in house dashboards using insight analysts that generally use Excel. The reason excel and these dashboard won't go away is because of cost. It's cheaper to make an excel tool to do the day to day than to build a whole CRM system. It's easier for analysts to use Excel because management use Excel it has a lower entry bar and analysts that know excel are cheaper. If management knew Jupiter notebook or python then the bar would be higher... It would be great if business would use the right tool for the job but they don't because of cost and available knowledge...
Very much agree with this. With my company I don't think cost is an issue, as we have all available tools. The main problem is the "available knowledge" that you referred too. It doesn't seem management wants to learn these new tools and therefore those under them don't invest the time to learn these tool and we end up using the wrong tools. Thanks for your insight my dude, very much appreciate this!!
Also want to say, you cannot teach everybody to use SQL (this is the job of database administrator), but you can make some intermediate files to connect user with data, when he just open the same excel file and see fresh result. I do this with power query.
great video! I major in Information System and I have been taught some lessons such as Power BI, C++ and I don't know how to connect these subjects and how these ones serve my future job. Now I understand better. Thank you very much
Luke, I agree with you. I would not file information into Microsoft Excel, unless number-crunching is to be applied to the data. I do simple filing and indexing jobs in my favorite program, which is Microsoft Access. I use several objects which are the tables, queries and reports to make the data "shine." Very seldom, I use the Forms tool because my data are private. The program can perform basic mathematics computing. I love Microsoft as a relational database.
I am having the same problem with excel. I want to move on to SQL for its limitations even though is a great tool. YOu are doing great content! Thank you!!!!
The thing is there is such a steep learning curve for these programs you are suggesting whereas Excel is so easy to use, and its more or less free. I like to use Apache Open Office.
I understand your issue... but I don't fully agree with this. Excel may be slightly easier to learn based on it's interface but it still takes a learning curve. At least in my past I've used it in school so i've had to learn it to become familiar. For the other programs, I feel they can be just as easy to learn if one invests the same amount of time that they invested in learning Excel. Its unfortunate but employers/schools usually don't demand you to learn these other programs so consequentially we don't try and implement them more.
I think Excel’s Power Query puts a decent dent in a few of these arguments. All transformations inside Power Query are done in incremental steps that are easy for co-workers to see how you got there. Filtering/Transforming/Merging SQL tables, combining other excel books/.csv files, even using PQ to merge data from tables in the current workbook can produce quickly and robustly crunched numbers that can be performed time and again by simply opening the workbook and clicking refresh all.
I agree that Power Query is pretty powerful. It definitely competes with SQL and Python in this capacity... But as far as the other opportunities that SQL and programming languages offer it's worth learning other tools besides excel to get these new found benefits that I mention in the video.
@@LukeBarousse , I appreciated the wisdom and wanted to stick up for PQ mostly because I have been dragging my feet learning python or power bi. Im such a visual learner I can’t cross the border to pure code. Me+ MS Flow + SQL + EXCEL = 2 Getha 4 Eva!
@@mrdamgoode Yeah, it can be a humbling start... just have to find something interesting that will keep you engaged! Good luck in your learning journey!
I work in gov't and excel is the go to and if I were to make other departments use SQL instead of excel it would be chaos, because no one uses sql at work or knows it but me. So, I'm surprised by at least the point made in the video around 4:28. Honestly though most departments in my agency don't work with large enough data to really use anything else, so excel makes sense and around here and no one does anything fancy either. Crazy now that I think about it but I suppose you don't really need to do anything crazy with simple data other than add/multiply and avg.
For small businesses. I dont understand why csv and tsv files are not encouraged more. It took me 10 minutes to train my friend into how to open and edit a tsv file properly in their favorite spreadsheet program. The best thing to do is teach some basic sql and add it in as a plugin to the spreadsheet program. The biggest difference between an enduser and a software developer is that the enduser expects their program to work even if they put in ambigious data. A developer knows that exactness is required. Once I got my friend trained on the concept of yes the program will react differently if you put an uppercase vs a lowercase letter as a filename and special symbols may or may not affect the output, they were able to troubleshoot their own issues. My favorite software is things like xidel. It's a commandline based sql query engine that can process all manners of tsv,csv,txt,sql files. Especially large ones. Also regular users like visual dashboards, they are way easier to learn how to make in microsoft excel than asking to them say use html/css. They wont be able to write the display logic to generate all those pretty visuals.
Yeah I def think csv's can be easier!! I think its' like you showcased here though that a little training is required to showcase the benefit. Keeping on spreading the good word about other tools!!
Why not use Access as a database? It's like SQL and has direct support inside of Excel (and I believe a lot of functions built in that encapsulate it). Also VBA is what slows down startup speeds, if you aren't basing your multicell calculations on constants but function calls, it's going to eat up RAM and CPU time.
This was nice video. Me and some friends are trying to store data(but spread sheet was not good for it because there was too much permutations and some stuff are still not officially decided like naming way to count some size etc. so starting to store the stuffs is unmotivating and and we're just some kids who knew each other over the internet
This reminds me of the coronavirus cases going unreported in he UK at the start of the pandemic. They were using Excel, and the amount of data was much greater than Excel could handle.
i struggle with the transition to databases. Working for a large company where everyone just knows Excel, there are data warehouses but there has to be an extremely well documented business need and process to go through with IT to even create tables there. I could always host the database on my local machine, but then my co-workers wouldn't be able to access them. Tried databases in the cloud environments (Azure, AWS, GCP) but once again, big company requires absolute business need to even create access to these tools. Thus, we're just stuck using Excel as a 'database' to connect to consumption tools like Power BI and Tableau. It's so painful to create this type of change as just an analyst in a large organization.
5:17 If honestly, nobody wants to dig into complex formulas to understand how it works. Including me. It's more simple to write new formula may be it will become the same complex, but you've passed its structure and you know how it works. :) 5:21 And this is not bad result, when everybody trust you.😄
I just started watching this video and as a software developer that focuses on ETL and SQL databases for a variety of data verticals...I want to scream...a lot. I have to stop watching now, and I want to say I'm so so sorry for all of you whose school didn't teach you how to do things right.
@@LukeBarousse I also wanted to scream, I even shared this with my girlfriend He started the whole thing in the wrong way, he does have alot of practice with excel, not gonna argue about that But, to think you could use excel as database in the first place is beyond me... Then he uses excel + SQL... SENDING THE SQL PASSWORD TO EVERYONE But wait, he then switches to python + SQL... Again, sending the password to everyone one, imagine a malicious employee ? My god, how this guy is employed is a mystery
@5:20 "Anybody else did not know how it worked". I've seen large companies grow dependency on Excel-based "databases" and models that were developed by individuals within the company. They then held the company to "ransom" with that .... these state-owned companies with frequently changing management heads allowed that crap to go on for YEARS. Little wonder when those companies failed ..... from many more instances of this type of poor behaviour. Good job on the video
I've noticed very similar things. Funny enough, I actually got called by a previous employer recently to help them with an issue in an excel file I made... 5 YEARS AGO 🤦🏼♂️...
I remember a university project. I pissed an entire class off. We were doing a class wide project (only like 6 or so students). Everyone did their data cleaning and manipulation in excel. I wrote an entire small Java program to do everything for me that way I could do it all again fairly quickly if the rules of the project needed to change. Turns out the data contained several null values which excel would hide. Meanwhile my program was crashing because of the null values. Three months in and I made the entire class start over because I did something different.
I use spreadsheet SERIESSUM function for all my math projects...coders without math rely on plugins and libraries without understanding the math process.
Agree! There probably are many "work-arounds" to alleviate some of my problems with Excel. BUT that wasn't the point of this video. The point was that Excel is not a one size fits all tool and instead in data science we should be exploring and using other tools that may be better for the job
The problem really comes when you can only use Excel because IT refuse to install better tools for the job. I think this is why many people use Excel and VBA for solutions Some of your concerns could be solved with using pure VBA solutions. But languages with a large ecosystem of analytics libraries are ofc a better choice if you can use them.
The UK National Health Service used Excel for storing covid case data and quickly found that the numbers were not climbing as expected because they ran out of rows. Had they used the right version of Excel spreadsheet they would actually have been OK, but they used the plain XLS version that has a very small row limit. And yes, I like Python for SQL queries.
If(B=1,like the video, subscribe), if this formula does not convince you to use excel, nothing will. Almost 50k, the panda man has come a long way since that M1 mac video, congratulations
The problem for me is we do not have access to SQL server. Can only use Google Drive or Office, so database = Spreadsheet. Have started to dabble in using SharePoint List for database with Power Apps, but this requires user has access to list. So frustrating not having SQL server. Using Google Sheet and Web App running as Owner (me) allows me to hide database from user, but still have to use Spreadsheet.
Yeah. Learning datascience with 365datascience, Udemy, Datacamp, UA-cam and some blogs. Was not sure to transfer from Excel/Sheets to RDBMS/SQL/Python/R. Your video gives confidence to shift. Many thanks for your videos.
Shows how things have changed to a reliance on spreadsheets, what is being taught these days? Whereas in the early - mid 80's when I was starting out with computers in business we would automatically use databases for heavy datasets and/or a relevant programming language depending on job. This was also a thing 35 or so years ago and not many people learnt the lesson. Seen many companies come to grief with this.
This is good to know David, thanks for sharing this! To add I don't mean to place blame on my university schooling but there was no mention of using databases/SQL for tasks. They mostly used Excel in classes and even fewer classes used programming (I'm a business analytics major BTW). It took working through these problems on my own to find out that I needed to do (what you stated above) on my own....
@@LukeBarousse computing in all aspects has been simplified and made easier in all aspects in the last 4 decades, you don't need to buy a new printer when you use a new spreadsheet or word processor because the new program doesn't support yours and there's no driver available. Using WordStar with no WYSIWYG 40 column text and toggle switch instructions as your only easy word processor as a right of passage. Thing is, like any trade using the correct tool for the job is essential. Probably your university tutors hadn't been exposed to a cut throat business environment such as financial services and haven't had to adapt or get fired, a great way to improve your thinking about problems.
@@davidmarsden9800 Ha! Very much agree with this. Yes I think a lot of my university instructors have a lack of experience with business environments contributing to this!
Hello Luke, First of all, I want to thank you for all of your videos. They're great. I'm just finishing my degree title in Economics and I want to keep adding some knowledge to my CV. I think Analytics-related stuff is great. A lot of people (and specially here in Argentina) says that a Excel course is a must in everyones carreer. So, as a way to start the path of Data Analytics learning... do you know a course in Excel to be recommended? Thank you!
Highly recommend you check out datacamp: lukeb.co/DataCampSub They have a course on excel (specifically spreadsheets) www.datacamp.com/tracks/spreadsheet-fundamentals
Since I never allow Excel users to have access to any raw data, I always create a VBA interface. For something like your project I would have gone straight to C# for the interface/UI and used SQL server for the storage.
@@LukeBarousse There are likely some better BIG DATA options, but I enjoy using C# for client side applications and I find SQL an easy and popular solution for server side storage and access. Although I confess I have NOT delved into no sql solutions like what Amazon uses.
@@RollinShultz This is interesting. I haven't heard of many people using C# for a client app, so I need to look into this more. I've always used something like power bi and tableau
@@LukeBarousse I always associate those with Excel and spreadsheets. especially when Power BI is an add-in for Excel. I haven't used it yet, but I probably would if I chose to do the project in Excel. If I was going to branch off of Excel to DB, I would likely and have chosen MS Access, but there are as you know severe size limitations there as well. I have my Diabetes centered Health Tracker in Excel with a VBA UI, but if I ever decide to either share it or market it I will most likely re-vise it as C# with probably a MySQL DB. I might also wish to include APIs that access food item nutritional stats to automate the "Meal Building" part. I would expect the re-visioning to go much quicker since for me C# is much easier than VBA and resources for when I get stuck are more plentiful.
Thks & interesting, I had the same problem 2 decades ago (circa 80s). I repeatedly warned 2 spaghetti-coder/analysts that their data mgt would screw them (really they had no data mgt plan). Soooooooooooo a year later my boss cornered me & told me to fix their data mgt nightmare or else ..... Their all their data barely fit into an Excel DB, it was barely searchable, & I basely slept for 2 weeks. Although I was the hero in the-end, I was more relieved my career was destroyed. Lesson painfully learned: 1st&2nd. A simply/solid data plan before you start & all during your project 3rd. Separate the client & developer interfaces, data manipulation coding, DB, & archival somehow/way (no-matter what hecklers tell you) 4th. Just run-away screaming when your co-workers do the exact opposite Oh, it's too-bad SQL has so little coding ability within it (ex: mainly just string manipulation & arithmetic).
Nice video! Curious: did you try locking the cells to avoid unwanted changes in the Excel file? If so, what limitations/problems, if any, did you face?
In all honesty, the majority of my uses of spreadsheet software are not actually for anything like analytics or databases- I mostly just use google sheets to prototype various things, quickly develop arrays that I can use in other programs, making character sheets for tabletop RPGs and keeping track of my projects. I also use it for lots of things it's probably really not made for- like, using conditional formatting to make pixel art. I also just find it very entertaining to use spreadsheets in really strange, unintended ways, like making chess games and card games or, literally, developing a top-down rpg.
Agree to disagree. I used VBA for many years and felt that although it could compete in some ways with programming language, it can't compete with a database storage option (such as SQL), or a visualization software (like tableau)
My Coworkers want me to go live on a small island in the South Pacific, for even thinking of using SQL. NO NEW SOFTWARE training. I've tried to explain the exact details of spreadsheet overuse that the video brings out, but it falls on deaf ears.
I laughed at your first sentence but then got sad realizing that your coworkers won't listen to you. Keep on spreading the knowledge my friend, eventually they will turn to better tools!
Sure, at enterprise level, alternative/better options to spreadsheets are readily available, and there's a person or two within the company that can facilitate the transition. But at SME level, these options don't always make sense, and it's simpler to just use spreadsheets.
The primary reason you're struggling to use it as a database is because you aren't using it to write data to separate text files, which would be necessary, and most easily-transferable. Learning programming is great, but the built in tools in excel are definitely still useful.
I feel like this video was made about me lol. Last year I pushed Google Sheets to the limit by creating an extremely complex sheet that parsed a 90 thousand line XML file, into cells so I could cross reference that data with my database (in a different tab of course) that would display it to the user where they would use it to do more functions. It worked, but it was slow. I’m glad I did it because I had to learn a little JavaScript to accomplish what I needed to do. That branched off to me learning Python and creating the same project into a website version that I could easily expand using a template that would dynamically change. Now, I am in the process of creating a standalone windows application that does the same functionality, much faster, and much more stable. It’s crazy to think I started with a spreadsheet a year ago.
I mostly use it for text manipulation. That is, pulling things like lists of names, adding a domain to create an email address, and spitting that lot into a CSV for use with other apps, typically powershell. TEXTJOIN() is my best friend some days.
Give the functional experts (supplier analysts) a self-explanatory BI solution (Tableau/Power BI/ Spotfire, Qlik). Then, centralize the storage of data on a SQL server or Access Database. ETL performed by an Admin. There is no need to complicate. And increase the cost/risk/and reduce the effectiveness of a solution. The functional experts know how to analyze their data. It’s not the job of programming or self-appointed data scientist. But instead, the technology department performs the ETL function and corrects when the analysts find dirty data.
Microsoft equipped it's Office package with Access, which can do same job of SQL, but in smaller size, and with programing language and SQL queries. Why you did no use it ?.
This is a good question. I feel that Microsoft is slowly killing off the Access product. I've found that it's getting even more and more limited support recently and hasn't been getting as much attention as their other SQL services to prevent competition with in its own product offerings.
@@LukeBarousse I would love to learn about what your thoughts are about it you check it out. I run a website development company and I hope to develop a way that I can run the backend of my sites with something like airtable. I'm just learning how to program so I have a ways to go lol.
At the risk of being laughed at, I use MS Access for storing my data (I use it personally, not shared!!!). I find it ideal as lightweight database with a terrific query builder. And yes, I know MS threatens to kill it for the past 20 years, which is shameful because the apps they develop with O365 are VERY limited in functionality. And not all of us are data analysts with acces to a SQL server, so we need stuff like Access or Excel. And the title "stop using excel for Everything" immediately got my attention (everybody else in my departement is using flat lists with Excel)
MS Access isn't really a bad solution per se. Plus you know the tool so I don't really think there is any harm in using a tool you know for your personal data storage. I still use excel for a lot of my personal data storage.. Yeah I know the feeling too of everyone using flat lists with Excel... unfortunately all too well. 😭
Nowadays, data analysts are moving to free programs like Python and R. And if you lose access to excel for small scale lists, libreOffice and OpenOffice are free.
I couldn't agree more! This video is great, both at providing where excel is great but also bad. I have a weird love hate with excel and spreadsheets, macros and VBA. I think you did a great job at discussing some of the things I dislike 😅. Thanks for a great video.
Awe Ben thanks for the kind words my dude! I have the same weird relationship with excel. Love how powerful it can be with VBA and macros but then hate the efforts of maintaining it... 😭
@@SeattleDataGuy I may have to pick your brain on this in the future! I'm currently working on a DE solution (using Airflow, Python, and SQL) and am completely overwhelmed 😅
Can you recommend a laptop that doesn't cost too much but also has the necessary space and speed needed to download and run to various applications needed to be a data scientist or software engineer. I currently have a MacBook pro A1502 but it just doesn't have the space I need, which means I haven't been able to work on many projects. What would you recommend?
Is Microsoft Access any good of a Database? Or does it make more sense to actually get something like Postgres or MS-SQL? I ask because MS-Office 365 includes Access by default, so might use something that's baked in than add or pay for another module.
From what I've seen Microsoft is phasing out Access and I don't think it will be available in the future. I think we need to start looking to other products, so that's why I don't recommend it
I feel that MS Access is slowly being killed off my Microsoft (they want to focus on their other offerings). I didn't feel the need to mention it as I feel in the near future it's going to become obsolete.
What's your favorite tool to use?
excel because it is widely used in the companies i worked at. the statistics add-on was a great move for excel. but cleaning the data with excel is not fun.
For simple data storage, you can use SQLite, it is much simpler to transfer the data around
airtable... its a mix of ease of use, powerful features, and "large" data, and some automation too
@@HarridiIlmanTovid I'll ahve to check this out!! Thanks for sharing!
@@firiguito Yeah I like the google suite as well. So intuitive and well integrated!
YOU MISSED THE CHANCE OF NAMING THE VIDEO "STOP SPREADING SPREADSHEETS"
🤣😂 bahahah! This is good! Maybe I still change it? 🤔
@@LukeBarousse then my comment wouldn't make sense
@@420planttechguy 🤣 Great point!!! ha I'll keep it then ... for now
hahaha great!
Dont spread sheets if you want to excel 😂😂😂
Can confirm - 1 milion rows are just not enough to list all the possible pizza toppings and combinations 🍕
😂 This is so the truth!!!
We got a counting and permutation problem right here
Chicken testicles is a good one
I would say that Excel is like an Swiss army knife you can do a little bit of everything but are you going to cut down a tree for god's sake use a chainsaw
bhahaha!! LOVE THIS!! great analogy!
Here, here!
This is me trying to get my employers to switch to SQL. They'd have Excel files with millions of rows, the files themselves would be GB's in filesize, and everybody would be trying to edit the file at once on an online hard drive, causing it to keep getting corrupted over and over again, and there was barely any security.
🤣 hahah! I only laugh because I've been there too!! Someday they will learn... someday...
That's where solutions like airtable comes in. Wish they had offline/local version.
Bro this comment sent chills down my spine
“What up, my name is, and I cover these topics in this channel” got me to subscribe so fast. I hate the “please subscribe, it helps the channel!!!”
Like… yeah, I know. But why should I subscribe? You just did it flawlessly. 10/10.
Awe my dude, thanks so much for this! Also agree... don't like wasting peoples time asking them to push subscribe. haha
Oh good god 😭😭. Power bi and SQL server would have made your life so much easier.
Dude tell me about it!! It would have solved so many issues!!! 🤣
True. Wouldn't have learnt as much though. :)
It would be nice to see an example on how you implemented SQL in your work.
Nice video as always
Heck yeah! Just trying to inspire! Thanks for the kind words Walid!!
@@LukeBarousse Thanks for the quality content!
I'm just happy you didn't say excel stopped working, switched to access. nice vid
Ha, never!! Thanks for watching!!
I agree you on this.. My laptop heats like anything after loading heavy spreadsheet.. SQL database made life easier..
Ha I know the pain. My fan kicks on in my computer when I load up a large spreadsheet!
Lol my agency boss used to get me to build tools in excel when the client didn't want to spend money on a 'proper' developer!
Yep, I've had similar issues as well... so frustrating!!!
Can definitely relate to that....
Hello Luke. I got three certificates from google & coursera of data analyst , and five more to go. It was bit fun and bit tough learning this new course. Your motivation led me to know how wonderful is the world with DATA.
Sainatha! Thanks for the kind words my dude, I really appreciate it. Keep up the great work on your journey in data science!!
Pandas is great for data analytics work although I do like SQL
For social types of data, graph databases are better than relational dbs
Thanks for sharing this!!
Pandas 🐼 is my favorite bcs I don’t need to open excel to write excel sheets. It’s great to work with data from different sheets
So helpful, I've been trying to see the benefits of SQL and R over Excel. Probably just haven't had to deal with large enough datasets just yet.
Thanks Dylan! Yeah I would say larger datasets and also repetitive tasks are when SQL and R really start to show how much better they are than Excel!
Its not just about dataset size. The nice thing about code based data processing is you can track everything you or other person did to the data. Its quite hard to track if somehow other person change value of a certain cell by mistake in excel.
Complex interrelated data also benefit from a database approach, regardless of the amount of data you have, so it is not just the size of data. You can have a series of tables that relate to each other via primary and foreign keys and then with SQL queries you can very quickly combine elements of different tables. Databases are also good for maintaining data integrity vs spreadsheets. Until you spend sometime learning basic SQL and play around with databases it is difficult to fully appreciate what you might be missing for your needs.
@@henrylawson430
Is there a way to easily access, add and sync data to databases cross platform (windows, ios, android) with support for offline access? Like, google sheets is so convenient for all these that I don’t think I can replace it..
@@BakrAli10 at the moment I use Libreoffice Base to connect to a Mariadb (MySQL clone) server on an Ubuntu laptop. So when I take the laptop, with me the data goes with me. My Windows PC also runs Libreoffice and can connect to the laptop, I just need to have the laptop running. This is useful because my Windows PC is more powerful and can run larger spreadsheets more effortlessly. The next stop would be to move the Mariadb databases to the cloud on a Linux VM. As for having all the data offline, I could perhaps replicate the cloud database to my laptop database, but I haven’t worked that out yet. Because I am using MySQL/Mariadb, I am guessing it will be easier to do other approaches in the future with regards to IOS apps etc. But it is simple to install PHPmyadmin, which provides a website front end to your database.
Excel is the Swiss Army Knife of data tools. It's one of the few Microsoft programs I can honestly recommend. That being said, I'm glad you're getting the word out that it isn't always the best solution for a given problem, even if we wish it were.
Heck yeah... very much agree and with the analogy of a swiss army knife!!
Can you show us how to do data analysis in excel? You have a way of breaking things down and making things easy to digest.
Awe thank you! Let me see what I can do on this topic!
I see RP templates everytime I close my eyes. In a good way 😍
Haha, yeah they definitely changed the way I thought about nutrition. Some crazy science behind them!
In my previous position, I used VBA to automate a lot of excel sheets, a couple Access dbs, and even the front-end to our mainframe since it supplied a com interface. The result was a mass of spaghetti code and different pieces of the "application" spread through out the network with different versions of each being file being saved to associates local machines. It was a nightmare.
Ha! Thanks for sharing this Evan! It can understand your pain!
Me: Agreed
Also me: using Google Sheets as a database for my app
😂 I mean its free and easy.... don't blame you ... hahah
@@LukeBarousse I just use it for small projects. It's quite robust. Especially for Android apps
@@RackaApps Interesting tactic for an app!
He is precisely correct. I am proudly competent in Excel and love it but have encountered and know well these very limitations.
The problem with most companies that I've experienced is a lack of support for production level employees. They don't want average users deploying and managing a sql server. So instead we use power query as a main data consumption tool and have a "database" workbook and separate user level dashboards that pull from that data workbook.
Ding ding ding! Love this as I have felt the same in the companies I've seen. We need to invest more in these production level employees to better set up the entire organization.
I edit video with Google Sheets. It’s much better than my old AVID Media Composer.
😂
Hi Luke, nice video, I will show it to every my customer before project, but I set dislike as GAS for G-spreadsheets is my best skill.
Thanks for your honesty, but what do you mean about "its GAS is my best skill"?
@Super Mario Understandable!
The other 'scary' software that gets out of hand in many companies is MS Access. It too is NOT designed for large implementations. There are a ton of issues caused by use of that platform, especially in multi-user deployments. It, like excel, seems to be a great and easy way to get things put together solving issues... but long term a real solution should be used.
I have felt all the pain you express in this video, and far more than one time. Often I have been the person asked to clean up after years of use and many ingrained issues caused by these kinds of improper solutions. SQL for data, and using other tools for data entry and dashboards... 100% agree.
My dude!! ⭐️🙌🏼 Thank you so much for sharing this as I don't thing people understand the long-term effects that using Excel for everything can cause
tbh spreadsheets are ALWAYS a nightmare to deal with. How did you clean this data? Which is the most current version of the file? how did you calculate this column? did anyone manipulate this data illegally (financial fraud sends greetings)? how do I replicate this analysis on my own identical dataset? How will I port this to our production processes?
Just use R or python with a predefined environment for everything. Put every data processing process under source control. dont rely on employee generated data, use the source-controlled process and process data straight off the immutable central DB.
It's a simple process, but it eliminates so god damn many problems.
Can you show us how you would do an excel report or dashboard using sql/python/tableau?
This is actually a great tutorial idea. Let me see what I can do on this!
I'm not an expert, but in my humble experience, i've found python's "openpyxl" library super helpful when working with excel sheets.
@@hernanfuriasse3055 I also find pandas is good for working with excel sheets. Thanks for sharing this
@@LukeBarousse Yes! Pandas is also great! it never cease to amaze me the incredible libraries python has
Pretty please 🙏
I was waiting for the part when he would say , Hey I have SQL 😀
Thanks Karan!!
Hi, I must confess to being guilty of using excel when it was more appropriate to use a data base and trying to compensate with heavy use of formulas and VBA. I’ve been out of the workforce a few years now and thinking of getting back into it. I’m going to spend some time learning a better way though. I think part of it for me was I was self taught and fixing legacy excel tools and automating repetitive tasks without thinking if it was the appropriate tool. I ran into the limits of excel a lot.
I have been stumbling on such problems with excel for a while now and can totally relate. I am a Simulation-Control Systems Engineer and in reality don't really need excel. But I have programmed thousands of lines of code and I am quickly growing tired of it (eyes hurting :P ). I use excel to make my sort of custom GUI for my work. At times wherein you have a problem with the speed, excel is capable of being integrated with other tools. You can even trigger stuff from excel and then get your results for visualization.
Great video by the way! Well structured and did not feel bored at all while watching!
Yeah, completely agree! As much as I have my complaints about excel, it can do some pretty powerful things! BUT to do those things can be more burdensome sometimes than using another tool.
Glad you found my video entertaining! That was what I hoped with this!
6:45 I always protect my projects from editing.
Just open, make changes with vba form and close with saving without asking. :)
Because of that I divide my sheets on several sections. Each sheet one data type. Customer, product, order and one sheet that combines and does the calculation. I use cloud and integrate a link into the sheet. I have no data loss and even if one sheet gets corrupted and or the data is too much, I can divide it to a specific date and change the maths and algorithm in the sheet in accordance.
This seems like an interesting approach! Thanks you for sharing this.
Spreadsheets are great for small scale projects, and very easy to learn.
When expanding a project like your example, you could have used a DataBase and some python scripts to take the workload off the spreadsheet. That said, there's a point to exploring new solutions. Good insight. But absolutely preposterous title :D
Thanks for this Rovsau!
or get off the tricycle and use golang with sqlite or nim with sqlite or rust with sqlite
Oh my gosh. That is the perfect example of technical debt. Easy to start, but the project's complexity it will catch up and starting from scratch with more suitable tools will be more needed.
Technical debt, I've never heard of this concept before, but it sounds like it fits!
hey Luke, Just finished my bachelors in economics and heading into data analysis field. Looking forward towards new content to stay motivated and exited
This is awesome to hear! Economics and data analysis is a winning combination in my eyes. I think you're on to some fun things!
@@LukeBarousse Yes, as a beginner it's quite exciting. Hoping to get advice from an expect like you in the future (if you don't mind).
@@manaspatil5698 Not at all, i'm always answering in the comments!!
You've described two roles one is a insight analyst and the other an MI BI Analyst. One does reporting excel and makes slides. The other is BI building dashboard using something like SSRS or Power BI for dashboards. The issues arise when teams build in house dashboards using insight analysts that generally use Excel. The reason excel and these dashboard won't go away is because of cost. It's cheaper to make an excel tool to do the day to day than to build a whole CRM system. It's easier for analysts to use Excel because management use Excel it has a lower entry bar and analysts that know excel are cheaper. If management knew Jupiter notebook or python then the bar would be higher... It would be great if business would use the right tool for the job but they don't because of cost and available knowledge...
Very much agree with this. With my company I don't think cost is an issue, as we have all available tools. The main problem is the "available knowledge" that you referred too. It doesn't seem management wants to learn these new tools and therefore those under them don't invest the time to learn these tool and we end up using the wrong tools. Thanks for your insight my dude, very much appreciate this!!
Also want to say, you cannot teach everybody to use SQL (this is the job of database administrator), but you can make some intermediate files to connect user with data, when he just open the same excel file and see fresh result. I do this with power query.
You encompassed a lot of issues! What is “simple VBA”? Sounds pretty hefty
Thanks Dave!
VBA is a dog with fleas
great video! I major in Information System and I have been taught some lessons such as Power BI, C++ and I don't know how to connect these subjects and how these ones serve my future job. Now I understand better. Thank you very much
Gald this helped!! I had the same questions before, so you are not alone!
They are really good for editing CSV. Also I keep a super well engineered sheet to manage my budget on Google sheets.
Thanks for sharing this!
Luke, I agree with you. I would not file information into Microsoft Excel, unless number-crunching is to be applied to the data. I do simple filing and indexing jobs in my favorite program, which is Microsoft Access. I use several objects which are the tables, queries and reports to make the data "shine." Very seldom, I use the Forms tool because my data are private. The program can perform basic mathematics computing. I love Microsoft as a relational database.
This is good to know! Thank you for sharing this!
@@LukeBarousse You are welcome and thank you for writing to me.
I am having the same problem with excel. I want to move on to SQL for its limitations even though is a great tool. YOu are doing great content! Thank you!!!!
Heck yeah, so glad you got use out of this video!
The thing is there is such a steep learning curve for these programs you are suggesting whereas Excel is so easy to use, and its more or less free. I like to use Apache Open Office.
I understand your issue... but I don't fully agree with this. Excel may be slightly easier to learn based on it's interface but it still takes a learning curve. At least in my past I've used it in school so i've had to learn it to become familiar. For the other programs, I feel they can be just as easy to learn if one invests the same amount of time that they invested in learning Excel. Its unfortunate but employers/schools usually don't demand you to learn these other programs so consequentially we don't try and implement them more.
I think Excel’s Power Query puts a decent dent in a few of these arguments. All transformations inside Power Query are done in incremental steps that are easy for co-workers to see how you got there. Filtering/Transforming/Merging SQL tables, combining other excel books/.csv files, even using PQ to merge data from tables in the current workbook can produce quickly and robustly crunched numbers that can be performed time and again by simply opening the workbook and clicking refresh all.
I agree that Power Query is pretty powerful. It definitely competes with SQL and Python in this capacity... But as far as the other opportunities that SQL and programming languages offer it's worth learning other tools besides excel to get these new found benefits that I mention in the video.
@@LukeBarousse , I appreciated the wisdom and wanted to stick up for PQ mostly because I have been dragging my feet learning python or power bi. Im such a visual learner I can’t cross the border to pure code. Me+ MS Flow + SQL + EXCEL = 2 Getha 4 Eva!
@@mrdamgoode Yeah, it can be a humbling start... just have to find something interesting that will keep you engaged! Good luck in your learning journey!
I work in gov't and excel is the go to and if I were to make other departments use SQL instead of excel it would be chaos, because no one uses sql at work or knows it but me. So, I'm surprised by at least the point made in the video around 4:28. Honestly though most departments in my agency don't work with large enough data to really use anything else, so excel makes sense and around here and no one does anything fancy either. Crazy now that I think about it but I suppose you don't really need to do anything crazy with simple data other than add/multiply and avg.
Thanks for sharing this perspective!
20 years ago I used to tell people convenience me why you'd want to use excel over a database for nearly anything.
Your guidance is so helpful!! Thank you!!!
Thanks for this Jennifer!
My man working on the nutrition spreadsheet for Renaissance Periodization!
Ha! Love me some RP templates.. back in teh day when they just gave you paper templates I was trying to make it easier to use!
For small businesses.
I dont understand why csv and tsv files are not encouraged more. It took me 10 minutes to train my friend into how to open and edit a tsv file properly in their favorite spreadsheet program.
The best thing to do is teach some basic sql and add it in as a plugin to the spreadsheet program.
The biggest difference between an enduser and a software developer is that the enduser expects their program to work even if they put in ambigious data. A developer knows that exactness is required.
Once I got my friend trained on the concept of yes the program will react differently if you put an uppercase vs a lowercase letter as a filename and special symbols may or may not affect the output, they were able to troubleshoot their own issues.
My favorite software is things like xidel. It's a commandline based sql query engine that can process all manners of tsv,csv,txt,sql files. Especially large ones.
Also regular users like visual dashboards, they are way easier to learn how to make in microsoft excel than asking to them say use html/css. They wont be able to write the display logic to generate all those pretty visuals.
Yeah I def think csv's can be easier!! I think its' like you showcased here though that a little training is required to showcase the benefit. Keeping on spreading the good word about other tools!!
Why not use Access as a database? It's like SQL and has direct support inside of Excel (and I believe a lot of functions built in that encapsulate it). Also VBA is what slows down startup speeds, if you aren't basing your multicell calculations on constants but function calls, it's going to eat up RAM and CPU time.
I feel that Microsoft is slowly trying to get rid of Access to promote its other services. Because of this loss of support I don't recommend it at all
This was nice video. Me and some friends are trying to store data(but spread sheet was not good for it because there was too much permutations and some stuff are still not officially decided like naming way to count some size etc. so starting to store the stuffs is unmotivating and and we're just some kids who knew each other over the internet
This reminds me of the coronavirus cases going unreported in he UK at the start of the pandemic. They were using Excel, and the amount of data was much greater than Excel could handle.
I need to look into this story, I didn't' know that
i struggle with the transition to databases. Working for a large company where everyone just knows Excel, there are data warehouses but there has to be an extremely well documented business need and process to go through with IT to even create tables there. I could always host the database on my local machine, but then my co-workers wouldn't be able to access them. Tried databases in the cloud environments (Azure, AWS, GCP) but once again, big company requires absolute business need to even create access to these tools.
Thus, we're just stuck using Excel as a 'database' to connect to consumption tools like Power BI and Tableau. It's so painful to create this type of change as just an analyst in a large organization.
I feel your pain Brad. Ive encountered very similar issues. keep pushing for change!
From the beginning Excel is not for database storing, it is Access job.
Agree
Awesome insights for someone like me trying to figure out the data world and it's tools, thanks
Glad it was helpful, Xavier!
5:17 If honestly, nobody wants to dig into complex formulas to understand how it works. Including me. It's more simple to write new formula may be it will become the same complex, but you've passed its structure and you know how it works. :)
5:21 And this is not bad result, when everybody trust you.😄
I just started watching this video and as a software developer that focuses on ETL and SQL databases for a variety of data verticals...I want to scream...a lot. I have to stop watching now, and I want to say I'm so so sorry for all of you whose school didn't teach you how to do things right.
Ha! Thanks for this!
@@LukeBarousse I also wanted to scream, I even shared this with my girlfriend
He started the whole thing in the wrong way, he does have alot of practice with excel, not gonna argue about that
But, to think you could use excel as database in the first place is beyond me... Then he uses excel + SQL... SENDING THE SQL PASSWORD TO EVERYONE
But wait, he then switches to python + SQL...
Again, sending the password to everyone one, imagine a malicious employee ?
My god, how this guy is employed is a mystery
@5:20 "Anybody else did not know how it worked". I've seen large companies grow dependency on Excel-based "databases" and models that were developed by individuals within the company. They then held the company to "ransom" with that .... these state-owned companies with frequently changing management heads allowed that crap to go on for YEARS. Little wonder when those companies failed ..... from many more instances of this type of poor behaviour.
Good job on the video
I've noticed very similar things. Funny enough, I actually got called by a previous employer recently to help them with an issue in an excel file I made... 5 YEARS AGO 🤦🏼♂️...
I never understood benifits of spreadsheets. Its just so much more intuitive and natural to write scripts for automation.
Yeah... I feel like they are almost addicting. Once you start it's hard to stop
I remember a university project. I pissed an entire class off. We were doing a class wide project (only like 6 or so students). Everyone did their data cleaning and manipulation in excel. I wrote an entire small Java program to do everything for me that way I could do it all again fairly quickly if the rules of the project needed to change. Turns out the data contained several null values which excel would hide. Meanwhile my program was crashing because of the null values. Three months in and I made the entire class start over because I did something different.
🤣 Love this!! Always challenge the norm!
I use spreadsheet SERIESSUM function for all my math projects...coders without math rely on plugins and libraries without understanding the math process.
I've never heard of this function... I'll have to check it out
Some of the challenges you mentioned could be alleviated using validated excel files (using something like eInfoTree software by Cimcon)
Agree! There probably are many "work-arounds" to alleviate some of my problems with Excel. BUT that wasn't the point of this video. The point was that Excel is not a one size fits all tool and instead in data science we should be exploring and using other tools that may be better for the job
The problem really comes when you can only use Excel because IT refuse to install better tools for the job. I think this is why many people use Excel and VBA for solutions
Some of your concerns could be solved with using pure VBA solutions. But languages with a large ecosystem of analytics libraries are ofc a better choice if you can use them.
The UK National Health Service used Excel for storing covid case data and quickly found that the numbers were not climbing as expected because they ran out of rows. Had they used the right version of Excel spreadsheet they would actually have been OK, but they used the plain XLS version that has a very small row limit. And yes, I like Python for SQL queries.
This is interesting, I didn't know this!! Ha the many issues of excel. Love me some python and SQL integration!
It was actually column limit. They had put records on colums instead of rows. Poor design is detrimental for any tool you are going to use even DBMS.
@@clarencebitegeko7079 They used columns? In that case they deserved every bit of flak thrown at them and they should have been fired.
If(B=1,like the video, subscribe), if this formula does not convince you to use excel, nothing will.
Almost 50k, the panda man has come a long way since that M1 mac video, congratulations
Heck yeah! Thanks for the formula Sai, I'm going to have to use it in my next excel sheet!! ha. We've come a long way from earlier this year!!
I wish I could take those course in coursera. Our country does not yet have international online payment system.
What about using a VPN to access coursera? Not sure if this helps solve your problem...
The problem for me is we do not have access to SQL server. Can only use Google Drive or Office, so database = Spreadsheet. Have started to dabble in using SharePoint List for database with Power Apps, but this requires user has access to list. So frustrating not having SQL server. Using Google Sheet and Web App running as Owner (me) allows me to hide database from user, but still have to use Spreadsheet.
My dude I very much understand your frustration. I've encountered similar issues. Sharepoint lists aren't bad options, but def aren't as good as SQL.
Anyone who worked in IT for a short time was either facepalming or yelling "Use a database!" at the screen after watching the video for 2 minutes.
😂
Exactly what I am facing now. Instead of Microsoft Excel using Google sheets.
Awe sorry to hear that. Hopefully, it's not too late to switch.
Yeah. Learning datascience with 365datascience, Udemy, Datacamp, UA-cam and some blogs. Was not sure to transfer from Excel/Sheets to RDBMS/SQL/Python/R. Your video gives confidence to shift. Many thanks for your videos.
@@tsbatm No problem at all! good luck on your data science journey!!
Shows how things have changed to a reliance on spreadsheets, what is being taught these days? Whereas in the early - mid 80's when I was starting out with computers in business we would automatically use databases for heavy datasets and/or a relevant programming language depending on job.
This was also a thing 35 or so years ago and not many people learnt the lesson. Seen many companies come to grief with this.
This is good to know David, thanks for sharing this! To add I don't mean to place blame on my university schooling but there was no mention of using databases/SQL for tasks. They mostly used Excel in classes and even fewer classes used programming (I'm a business analytics major BTW). It took working through these problems on my own to find out that I needed to do (what you stated above) on my own....
@@LukeBarousse computing in all aspects has been simplified and made easier in all aspects in the last 4 decades, you don't need to buy a new printer when you use a new spreadsheet or word processor because the new program doesn't support yours and there's no driver available. Using WordStar with no WYSIWYG 40 column text and toggle switch instructions as your only easy word processor as a right of passage. Thing is, like any trade using the correct tool for the job is essential. Probably your university tutors hadn't been exposed to a cut throat business environment such as financial services and haven't had to adapt or get fired, a great way to improve your thinking about problems.
@@davidmarsden9800 Ha! Very much agree with this. Yes I think a lot of my university instructors have a lack of experience with business environments contributing to this!
Hello Luke,
First of all, I want to thank you for all of your videos. They're great. I'm just finishing my degree title in Economics and I want to keep adding some knowledge to my CV. I think Analytics-related stuff is great.
A lot of people (and specially here in Argentina) says that a Excel course is a must in everyones carreer. So, as a way to start the path of Data Analytics learning... do you know a course in Excel to be recommended?
Thank you!
Highly recommend you check out datacamp: lukeb.co/DataCampSub
They have a course on excel (specifically spreadsheets)
www.datacamp.com/tracks/spreadsheet-fundamentals
@@LukeBarousse Thanks!
I agree with his points but where execl is amazing is as a user interface for users to interact with data.
Yep... I agree with this... so easy to get up and running with it!
Excel is like a Swiss knife which you can use everyday for anything, but for serious construction works you should use more professional tools.
Since I never allow Excel users to have access to any raw data, I always create a VBA interface. For something like your project I would have gone straight to C# for the interface/UI and used SQL server for the storage.
That would be a nice combo!! I like your way at approaching this problem!
@@LukeBarousse There are likely some better BIG DATA options, but I enjoy using C# for client side applications and I find SQL an easy and popular solution for server side storage and access. Although I confess I have NOT delved into no sql solutions like what Amazon uses.
@@RollinShultz This is interesting. I haven't heard of many people using C# for a client app, so I need to look into this more. I've always used something like power bi and tableau
@@LukeBarousse I always associate those with Excel and spreadsheets. especially when Power BI is an add-in for Excel. I haven't used it yet, but I probably would if I chose to do the project in Excel. If I was going to branch off of Excel to DB, I would likely and have chosen MS Access, but there are as you know severe size limitations there as well.
I have my Diabetes centered Health Tracker in Excel with a VBA UI, but if I ever decide to either share it or market it I will most likely re-vise it as C# with probably a MySQL DB. I might also wish to include APIs that access food item nutritional stats to automate the "Meal Building" part. I would expect the re-visioning to go much quicker since for me C# is much easier than VBA and resources for when I get stuck are more plentiful.
Thks & interesting, I had the same problem 2 decades ago (circa 80s). I repeatedly warned 2 spaghetti-coder/analysts that their data mgt would screw them (really they had no data mgt plan). Soooooooooooo a year later my boss cornered me & told me to fix their data mgt nightmare or else .....
Their all their data barely fit into an Excel DB, it was barely searchable, & I basely slept for 2 weeks. Although I was the hero in the-end, I was more relieved my career was destroyed.
Lesson painfully learned:
1st&2nd. A simply/solid data plan before you start & all during your project
3rd. Separate the client & developer interfaces, data manipulation coding, DB, & archival somehow/way (no-matter what hecklers tell you)
4th. Just run-away screaming when your co-workers do the exact opposite
Oh, it's too-bad SQL has so little coding ability within it (ex: mainly just string manipulation & arithmetic).
I like lesson number 4... hahah. Thanks for sharing this Tom! This was awesome to read
Nice video! Curious: did you try locking the cells to avoid unwanted changes in the Excel file? If so, what limitations/problems, if any, did you face?
Yeah i've done this before. I feel it brought on even more complications. Dashboarding tools are the way to go IMO
In all honesty, the majority of my uses of spreadsheet software are not actually for anything like analytics or databases- I mostly just use google sheets to prototype various things, quickly develop arrays that I can use in other programs, making character sheets for tabletop RPGs and keeping track of my projects. I also use it for lots of things it's probably really not made for- like, using conditional formatting to make pixel art.
I also just find it very entertaining to use spreadsheets in really strange, unintended ways, like making chess games and card games or, literally, developing a top-down rpg.
Thanks for sharing this!!
Excel VBA can solve many issues that you mention.
Agree to disagree. I used VBA for many years and felt that although it could compete in some ways with programming language, it can't compete with a database storage option (such as SQL), or a visualization software (like tableau)
My Coworkers want me to go live on a small island in the South Pacific, for even thinking of using SQL. NO NEW SOFTWARE training. I've tried to explain the exact details of spreadsheet overuse that the video brings out, but it falls on deaf ears.
I laughed at your first sentence but then got sad realizing that your coworkers won't listen to you. Keep on spreading the knowledge my friend, eventually they will turn to better tools!
Sure, at enterprise level, alternative/better options to spreadsheets are readily available, and there's a person or two within the company that can facilitate the transition. But at SME level, these options don't always make sense, and it's simpler to just use spreadsheets.
Correct...
I use it to create vector graphics and initialize mysql databases. (I wrote a mod)
The user friendly nature is helpful for app development.
The primary reason you're struggling to use it as a database is because you aren't using it to write data to separate text files, which would be necessary, and most easily-transferable.
Learning programming is great, but the built in tools in excel are definitely still useful.
Completely agree that built in tools within excel are still useful! Was just hoping to showcase that other solutions are available!
I feel like this video was made about me lol. Last year I pushed Google Sheets to the limit by creating an extremely complex sheet that parsed a 90 thousand line XML file, into cells so I could cross reference that data with my database (in a different tab of course) that would display it to the user where they would use it to do more functions. It worked, but it was slow.
I’m glad I did it because I had to learn a little JavaScript to accomplish what I needed to do. That branched off to me learning Python and creating the same project into a website version that I could easily expand using a template that would dynamically change.
Now, I am in the process of creating a standalone windows application that does the same functionality, much faster, and much more stable.
It’s crazy to think I started with a spreadsheet a year ago.
Heck yeah!! Look where you are now with your data science journey! Thats crazy, such a similar transition into more complex tools!
@@LukeBarousse I agree. I find it very fun and challenging to figure things out. It’s a great hobby. Thanks for the video!
@@kylerbriskey372 No problem, thanks again for sharing!
I do a lot of weird things with spreadsheets, but that's mostly for fun and engineering.
Thanks for sharing Shaun!
I mostly use it for text manipulation. That is, pulling things like lists of names, adding a domain to create an email address, and spitting that lot into a CSV for use with other apps, typically powershell. TEXTJOIN() is my best friend some days.
@@randombloke82 Thanks for sharing this!!
Trying to explain this to stakeholders who are joined at hip to Excel is such a struggle lol 😂
I feel your struggle all too well 😢
Give the functional experts (supplier analysts) a self-explanatory BI solution (Tableau/Power BI/ Spotfire, Qlik). Then, centralize the storage of data on a SQL server or Access Database. ETL performed by an Admin. There is no need to complicate. And increase the cost/risk/and reduce the effectiveness of a solution.
The functional experts know how to analyze their data. It’s not the job of programming or self-appointed data scientist. But instead, the technology department performs the ETL function and corrects when the analysts find dirty data.
Thanks for sharing your experience with data sharing. Very much a viable approach you shared here!
Microsoft equipped it's Office package with Access, which can do same job of SQL, but in smaller size, and with programing language and SQL queries.
Why you did no use it ?.
This is a good question. I feel that Microsoft is slowly killing off the Access product. I've found that it's getting even more and more limited support recently and hasn't been getting as much attention as their other SQL services to prevent competition with in its own product offerings.
I work in Japan and so many companies overuse excel for anything
Same here in the USA.. ha!
Have you ever used Airtable? If so what are your thoughts on it?
I have not! I'll have to check this out!
@@LukeBarousse I would love to learn about what your thoughts are about it you check it out. I run a website development company and I hope to develop a way that I can run the backend of my sites with something like airtable. I'm just learning how to program so I have a ways to go lol.
Great video as usual mate.
Aww thanks so much for this!
At the risk of being laughed at, I use MS Access for storing my data (I use it personally, not shared!!!). I find it ideal as lightweight database with a terrific query builder. And yes, I know MS threatens to kill it for the past 20 years, which is shameful because the apps they develop with O365 are VERY limited in functionality. And not all of us are data analysts with acces to a SQL server, so we need stuff like Access or Excel. And the title "stop using excel for Everything" immediately got my attention (everybody else in my departement is using flat lists with Excel)
MS Access isn't really a bad solution per se. Plus you know the tool so I don't really think there is any harm in using a tool you know for your personal data storage. I still use excel for a lot of my personal data storage..
Yeah I know the feeling too of everyone using flat lists with Excel... unfortunately all too well. 😭
Nowadays, data analysts are moving to free programs like Python and R. And if you lose access to excel for small scale lists, libreOffice and OpenOffice are free.
I couldn't agree more! This video is great, both at providing where excel is great but also bad. I have a weird love hate with excel and spreadsheets, macros and VBA. I think you did a great job at discussing some of the things I dislike 😅. Thanks for a great video.
Awe Ben thanks for the kind words my dude! I have the same weird relationship with excel. Love how powerful it can be with VBA and macros but then hate the efforts of maintaining it... 😭
@@LukeBarousse It's a weird trade off and now being more of a pure DE...I pretty much just prefer Python and SQL for everything.
@@SeattleDataGuy I may have to pick your brain on this in the future! I'm currently working on a DE solution (using Airflow, Python, and SQL) and am completely overwhelmed 😅
@@LukeBarousse Happy to provide any advice I can!
Can you recommend a laptop that doesn't cost too much but also has the necessary space and speed needed to download and run to various applications needed to be a data scientist or software engineer.
I currently have a MacBook pro A1502 but it just doesn't have the space I need, which means I haven't been able to work on many projects. What would you recommend?
Check out the description above where I have some recommended laptops!
Is Microsoft Access any good of a Database? Or does it make more sense to actually get something like Postgres or MS-SQL?
I ask because MS-Office 365 includes Access by default, so might use something that's baked in than add or pay for another module.
From what I've seen Microsoft is phasing out Access and I don't think it will be available in the future. I think we need to start looking to other products, so that's why I don't recommend it
Me who uses Excel to do basic math instead of the calculator: "What do you mean, everything?"
🤣🙌🏼
If you protect your worksheets then users can't change them. That way you can share them with a much larger user base.
Yeah you can... but it also complicates other things as well
I saw a video of someone pitching Google Sheets as a database recently
I think I saw this as well!
Try power query and the data model
I have!! A good option, but still feel that other tools may be better
Since I started with databases, they were a way to graph data from one.
This is a great point Michael, thanks for sharing. Excel does make it easy to connect to databases and visualize.
All you need is just powerful comp!😁
this only applies on excel right? not on google spreadsheet?
I noticed there is no mention of Access database. How come?
I feel that MS Access is slowly being killed off my Microsoft (they want to focus on their other offerings). I didn't feel the need to mention it as I feel in the near future it's going to become obsolete.