Spreadsheets Are a Siren Song: Why I Avoid Them
My Rules for Using Spreadsheets
Dr. Drang explains his fundamental rule for spreadsheets: don't use them. Drawing on years of analyzing data sets sent as Excel files, he argues that spreadsheets encourage poor organization and errors as they grow. He demonstrates how Python, Pandas, and SQLite handle a 2-million-row dataset that exceeds Excel's row limit, and contrasts these tools with spreadsheet pivot tables.
The simplicity of the typical click-and-drag method of assembling a spreadsheet encouraged poor organization and errors as the spreadsheets grew or were adapted to new data.
- sgarland
You can do horrifying and amazing things with Excel. Samsung’s Austin fab, at least circa 2020-ish, was generating machine labels with it. These are paper cards, roughly 2” x 4”, which carry the following information:
* Machine name / number
* Owning technician’s name, shift, photo, and phone number
* Owning engineer’s name, shift, photo, and phone number
My team had something like 250 machines spread across 4 shifts. We had to redo cards anytime we gained or lost someone on any team, which often included rebalancing the workload. Luckily, someone had made an Excel macro that connected to a MSSQL DB that had employee information (IIRC, it didn’t have confidential information like pay in it; I think it was used for generating badges), pulled all of the required fields, and automatically filled the template. Send them to the printer, cut them apart, and go place them.
My contribution while there was to implement a crude pathing optimization that tried to assign contiguous machines to a given technician, to minimize the amount they had to walk for weekly checks. It kinda worked; better than nothing, anyway.
- alexandrehtrb
1) Today I learned that named cells exist in Excel.
2) Excel is great for technical and non-technical people. You can teach Excel even to 10-year-olds (I first used it when I was a student around that age).
3) 1,048,576 rows are more than enough for 99% of Excel use-cases.
4) Excel can connect to external data sources, like databases and APIs, which can perform heavier and more complex data operations, taking this responsibility away from the spreadsheet. SQL queries and procedure calls can be embedded inside Excel spreadsheets.
5) There are some Excel skills, like named cells and VBA functions, that most people don't know. It's a matter of sharing knowledge with them, so they can make better spreadsheets.
- LMKIIW
The humble spreadsheet is the most robust data analysis tool of our lifetime and the two exceptions provided by the author (fit-on-screen data, temp storage) ignores a variety of uses that several comments itt reference.
The key value IMO - as other have said - is that it's sharable to non-technical folks.
- sherburt3
Reviewing the individual formulas in an excel report is psychotic. Normally what I would do when checking someone elses work for errors is spot check a couple of the values in a column and calculate them by hand to see if they matched up. Either that or just implement the step myself in excel and see if I got the same output.
That's a lot like reviewing someone's regular expression. Like I'm not going to read through a 100 character regex to see if it matches semantic versions, I'm just gonna pull it up on a regular expression tester and see if it does what it should.
- econ
While putting formulas in cells is confusing a single use script somewhere on the file system is also terrible and looking at it isn't all that obvious what is going on either. If you put everything in a db you need the ancient art of SQL Kung Fu. Great if you can, to bad if you can't. I like joins, it instantly confused the hell out of the uninitiated. You can make the query complicated enough that even a seasoned champion needs a warm up before lifting.
I remember my first thought looking at excel. They force name and number everything which is exactly like using single letter variables, they are only allowed for simple things. Using row numbers is even worse. Each additional col of numbers makes it harder to find things and it invites mistakes.
I'm not complaining, each solution survived because it has great advantages. JSON and XML have their place too ofc.
My gut says that after learning the advantages and disadvantages we should be able to make something better.
I put CSVs in html documents, use JS, run from the file system, output is CSV usually. I haven't tested the limit of html files but if you put a comment at the bottom the rendering engine ignores it efficiently.
One more bad solution for your collection.