STOP using Spreadsheets for Everything!

Key Takeaways

spreadsheet softwares took off last century as they were solving a problem that allowed users to better access and manipulate data that was previously done via paper accounting as we've moved into this century we've seen spreadsheet applications such as excel and google sheets become even more supercharged to gain more market share unfortunately we have started to stretch this tool beyond its capabilities by using it for problems it wasn't intended to solve such as large-scale storage of data incomprehensible formulas built on other formulas and productionized dashboards for vital business decisions for those working in data analytics we need to look to better options what up dad nerds i'm luke my channel is all about tech and skills for data science and in this video today i wanted to go over my past experience with using spreadsheets in my job as a data analyst and specifically go into some of the pain points that i've experienced with using this tool beyond its capabilities although the majority of my past experience with spreadsheets has been with microsoft excel the same can be applied to other spreadsheet software such as google sheets so with that let's get into the first case that i wanted to go through and that was sharing an example of how you could properly use spreadsheets and i'm going to go over one using microsoft excel while in school some teammates and i decided to collaborate on a project that built a tool in microsoft excel that did some basic analytics around food and nutrition the tool itself utilized some very basic data that then could be adjusted and analyzed based on the need of the user itself this tool used some simple vba to perform some of the computations and then also provided this in a easy to read dashboard for the user as this was a short school project that required us to rapidly prototype and didn't need us to elevate and develop the tool anymore after this microsoft excel was actually the perfect tool for the job and allowed us to build a solution pretty quickly overall i wanted to show that spreadsheet software such as excel can and possibly should be used in situations where you need to do some quick analysis or quick prototyping so now let's fast forward to another scenario where i was working on my first project as a data analyst in my current company for this project i was tasked with building a tool that could track and analyze the performance of my company's suppliers with my recent success of using excel in college i thought that this would be the perfect software to use for this job so over the course of a few months i began building a tool that stored all the pertinent data of our suppliers performed some mild to advanced mathematical operations using vba and then i had a dashboard to visualize the results for my team my boss was pretty thrilled with my final deliverable and so we went forward with distributing it to our team of about 10 individuals to actually start using it in their daily workflow unfortunately this is where things started to get worse and so now let's go through three main problems that i encompassed with using excel for this tool so the first real problem that i encountered was data storage issues and this was mainly my own causing was that i was using excel as a database if you will to store all of the different supplier data within some of the sheets of the excel file itself because of the large amount of data i was storing in it the file became extremely large and it became even a burden to open up the file later on in life as we added more and more data to it additionally pretty quickly after launching this file with the team we began to reach some of excel limits specifically excel and most spreadsheets have a row limit of around a million rows that you can store data within so i had to come up with ingenious methods to actually circumvent this and store more data within the sheets overall i was finding out the hard way that excel is not the best at being a database or a data storage engine for all of my projects since this one i've decided to shift to a more sustainable approach of storing data and that's using sql databases such as postgres or sql server here's a few good reasons why i've decided to use it within my workflow first storing this data in a centralized location like a sql database allows users to go to a central location to access this and to query the data and so like the excel file example i don't have to load all the data every time i can just query the results that i want next sql unlike excel doesn't have a as small of a row storage limit so you're not going to be concerned with adding new data and reaching some sort of limit and then finally centralizing this location of all the data inside of a database and allows an administrator to easily update the data and then the users to easily get that updated data quickly the second main issue that we encompassed was around reproducibility and also troubleshooting specifically we found it hard to actually manage all the different calculations and analytics that were going on in the background of the excel sheet because of how excel works to where you can do formulas and also reference other formulas it was actually pretty hard to trace back how some calculations were being performed and anybody else besides me really didn't know how it worked so people just trusted me at face value that all the calculations were correct and nobody double checked me additionally with these complex formulas that where we would reference other cells or other cells in other sheets it was hard to troubleshoot and go on a path to actually understand what was going on and this ultimately led to to the sheet itself being very slow in performance overall i've found that excel wasn't really designed to handle large or complex computations that are cells built upon cells it really slows the program down i found something like a programming language like python or r is better suited at doing this analytics programming languages allow you to run some type of script or notebook that allows you to follow in a systematic manner of performing analytics that somebody else could go in and actually read and follow and troubleshoot with this standardized approach at analyzing and manipulating the data you can also use it to transition to analyze other stuff so in our case we were analyzing suppliers performance we could have thus shifted that script over or that python over to analyze our customers performance okay so the third and final issue i encompassed with using excel for this project were evolved around using it to share and allow people to access the data via dashboard although excel files can be shared via things like microsoft teams or sharepoints when you start sharing analytics via an excel file you can run into a few issues specifically it's hard to build dashboards in excel to prevent your users from going in and breaking your solution so they could go in and adjust some formatting or they could go in and move buttons or delete buttons or even delete formulas and this is overall going to affect the experience of other users so overall excel is good for sharing your work with you know a limited small group of people whenever you start going beyond beyond a few people you need to start looking at actual dashboard solutions such as power bi or tableau i find these tools are really great because they provide a method to have a centralized location for your dashboard where users can go in and they can't really manipulate or mess with the dashboard thus affecting the experience for other users and then finally i feel it also can be built in a more intuitive manner so that way users understand how to use it a lot easier than some dashboard built in excel so that is the three main issues i think you can encompass if you're using excel for all of your different analytical needs when i started exploring and utilizing other tools i felt like this actually freed up so much more of my time from having to utilize it for troubleshooting or problem resolution as i was using a tool that was more designed for this job so the tools i've referenced in this video such as sql python tableau and power bi are all tools that i've learned over the course of a few years through applying it in my normal daily workflow i've found that using things like e-learning platforms and books and projects are the key drivers and helping me actually learn these tools and apply them in my job if you're completely new to all these tools and want a recommendation for an e-learning platform i highly recommend you check out google data analytics certificate that's hosted by coursera this course itself focuses on sql r as the programming language and then tableau as the visualization tool along with some other options and trainings around spreadsheets themselves this course is 39 us dollars a month and takes a few months to complete full disclosure i am a member of their affiliate program so i have affiliate links for this course below for those more advanced users that may have more experience within data analytics or you have experience with a lot of these tools already i highly recommend that you check out some sort of e-learning platform that can provide you with an opportunity to do a deeper dive into each one of these tools to learn more for me personally i pay for subscriptions to both coursera plus and also data camp and both those platforms provide an opportunity for me to go in and access the courses of the e-learning platforms as an example of how i use it recently i was using the python for everybody course in coursera and i was learning about apis in python for a project i was doing for one of my youtube videos that i have a link for above and recently i've even shifted to a new course where i'm learning about postgres because i've started to use sql more in my role as a data analyst because as you can see i'm accessing multiple courses in this coursera example i go with the coursera plus which allows me to have access to a wide range of courses all for a single fee of 59 us dollars per month if i just paid for each one of these individual courses i'd actually end up spending a lot more so that's why i'm really a fan of if you're in a position where you already know or have a lot of knowledge on data analytics and want to learn more joining some sort of e-learning subscription service so that way you can have that access to those courses so bam as always if you got value out of this video smash that like button and with that you