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.
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.
I generally agree with the logic -- I don't like using spreadsheets for anything complex, or that has the shelf life of more than, say, a month. I can remember my logic for putting together all the complex formulas for a while, but eventually I have to recreate my own logic, reverse engineering everything, and it's frustrating.
But when one of your arguments is "Excel has complicated nested formulas that are difficult to follow" I don't think showing off a Python one-liner as an alternative is the greatest example. That one-liner is doing about five things that could have been separated into steps to show off the relative clarity and flexibility of the approach. It is better...that example just doesn't do a good job showing it.
I don't use spreadsheets much myself. But I'm not against them as a means of interfacing with non technical people used to using them.
And I have used them as an alternative to a UI with very poor UX that developers came up with for a team of material scientists to use. This thing was basically unusable. Sometimes, it's just easier to go where the users are rather than to try to get them to use something new or different. These people were dealing with a lot of specifications in PDF form. Loads of tables basically. So, spreadsheets were a good fit as that was what they were doing anyway. They loved it. Soon after they discovered databases, python and sql. So there is that.
The reason spreadsheets are widely used is because nothing better really came along that did the same job for everyone. There were of course lots of tools for specific use cases that are somewhat widely used now. But most of these tools are fairly niche compared to spreadsheets.
These days getting data in and out of spreadsheets is not that big of a deal with AI coding tools. A lot of white collar workers are starting to use those. So, switching to something better/appropriate is much less of a challenge now than it used to be.
I don't really agree, moreover I haven't had the same experience at all.
I find engineers almost never using spreadsheets, ever for clearly tabular data. My coworkers certainly never use nested-ifs. Many times they will put a crappy table into jira instead, which multiple team members will be overwriting each other's work.
I have a love-hate relationship with spreadsheets and Excel.
Spreadsheets are good for ad-hoc tasks on small-to-medium amounts of data (let’s say the old 64k row limit) of medium-to-low complexity. In practice, that covers a lot of tasks.
The problem is that Excel is the only flexible tool many people have so they bend it to do everything in lieu of anything better.
I've been tasked to rewrite a spreadsheet as a dedicated app.
Most of what is in TFA is correct but it only begins to describe the problem.
My takeaway is very simple: the world runs on broken spreadsheets that are full of bugs and wrong assumptions. And the PowerPoint presentations are shown to business people taking decisions based on the wrong numbers coming from those spreadsheets. And it's the world we live in.
> It was common for the data to be split over two or more sheets.
And when the sheet are separated, for example, by years, that's even more bugs. Stuff is counted twice. Others not at all. The concept of when an entry should be "closed" when it's opened on year X and closed on year "X + 1" is something that bewilders spreadsheets users.
> and errors as the spreadsheets grew or were adapted to new data
Errors in spreadsheets are the big one: spreadsheets are full of errors.
> Another problem with spreadsheets is that the amount of data they can contain is more limited than when you use other data analysis workflows.
Another gigantic issue is that the notion of time is broken in dynamic spreadsheets: not because a spreadsheet cannot be written to correctly deal with it. But because the spreadsheet users don't know how to properly model how values relates to varying time (typically the spreadsheet shall work, for the cell that fetches the value, for the time value of "now" and that's it).
> I know there are lots of people who love using spreadsheets.
It's because you can cosplay being an actual programmer when you're not. The result, sadly, is exactly what you'd expect: buggy spreadsheets full of broken assumptions.
Which are then sent to those meaning real business to be rewritten as dedicated apps...
P.S: as of now I'm working on finance stuff... Same old story: a spreadsheet that has overstayed its welcome, it became gigantic. It's a pure mess of fetching values (and fetching way too many values, which creates technical issues) and bogus little things left and right. So what are we doing, again? Porting that spreadsheet to a proper dedicated app that can, correctly, deal with a proper amount of data, while fixing all the little glitches and gotchas too complicated to fix in a spreadsheet.
There are spreadsheet authors who would have been programmers in "another life" and it's usually possible to get thru to them re: the kinds of issues that plague spreadsheets. Those people are a pleasure to work with. I remember showing a junior finance guy how to use "JOIN" to overcome performance problems with his ad-hoc VLOOKUP-based re-implementation. He "got it" and things got a lot better.
OTOH, there are people who have no background in computational thinking or software development churning out spreadsheet nightmares w/ no regard to accuracy or maintainability. Anecdotally, they seem to be the people most attracted to glitzy formatting features, "no code" automation tools, and frightening nightmares of "linked" spreadsheets.
It depends, excel is a powerful tool but in nowadays tech it is outdated. I remember years ago I used google sheet as a backend, it did the storing like a database, but also the auto web scrapping into that db after the filtration, everything was running on auto mode for free, and UI communicated through google API. Obviously nowadays it’s not worth it with all these shiny tools out there.
A caveat first, most of the data I work with is under 10k rows. YMMV.
I find Excel slower than Python for analysis but I share a lot of data modelling with non technical people, and we both like being able to verify the results with pivot tables and functions typically (I need my work to be easily “defensible” because often facts are different from someones “feelings”).
So workflow has become something like:
plan -> code -> generate outputs -> generate an XLSX -> verify with pivots/functions ->share with stakeholders
Python pipeline does the transformation and modelling, excel is review/verification, sharing layer.
The (sorta) issue is that the workbook becomes an output, not a source of truth.
People can add pivots and review tabs, but I ask that they provide corrections/feedback outside of generated sheets (otherwise those changes will disappear the next time I run the pipeline and recreate the workbook).
I tried working on this idea at one point. My conclusion is that doing it sensibly ends up removing all the advantages of spreadsheets which makes them so widespread (as the beginning of this article discusses so well).
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.
This should have either been a Word template that used mail merge to insert the data into fields or an Access form.
>You can do horrifying and amazing things with Excel.
I've never seen a better explanation of the tool than that one sentence
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.
I generally agree with the logic -- I don't like using spreadsheets for anything complex, or that has the shelf life of more than, say, a month. I can remember my logic for putting together all the complex formulas for a while, but eventually I have to recreate my own logic, reverse engineering everything, and it's frustrating.
But when one of your arguments is "Excel has complicated nested formulas that are difficult to follow" I don't think showing off a Python one-liner as an alternative is the greatest example. That one-liner is doing about five things that could have been separated into steps to show off the relative clarity and flexibility of the approach. It is better...that example just doesn't do a good job showing it.
My beef with spreadsheets is their use as manually updated tracking tools, whose inconsistent formatting makes automation tricky.
Luckily AI can update junky spreadsheets but I think "don't" is the best position I've heard yet.
how you know they aren't serious people? Numbers is referenced almost as many times as Excel in the blog post.
First sentence of the post?
>My fundamental rule is Don’t, but a single word wouldn’t make for much of a blog post.
I don't use spreadsheets much myself. But I'm not against them as a means of interfacing with non technical people used to using them.
And I have used them as an alternative to a UI with very poor UX that developers came up with for a team of material scientists to use. This thing was basically unusable. Sometimes, it's just easier to go where the users are rather than to try to get them to use something new or different. These people were dealing with a lot of specifications in PDF form. Loads of tables basically. So, spreadsheets were a good fit as that was what they were doing anyway. They loved it. Soon after they discovered databases, python and sql. So there is that.
The reason spreadsheets are widely used is because nothing better really came along that did the same job for everyone. There were of course lots of tools for specific use cases that are somewhat widely used now. But most of these tools are fairly niche compared to spreadsheets.
These days getting data in and out of spreadsheets is not that big of a deal with AI coding tools. A lot of white collar workers are starting to use those. So, switching to something better/appropriate is much less of a challenge now than it used to be.
I don't really agree, moreover I haven't had the same experience at all.
I find engineers almost never using spreadsheets, ever for clearly tabular data. My coworkers certainly never use nested-ifs. Many times they will put a crappy table into jira instead, which multiple team members will be overwriting each other's work.
I have a love-hate relationship with spreadsheets and Excel.
Spreadsheets are good for ad-hoc tasks on small-to-medium amounts of data (let’s say the old 64k row limit) of medium-to-low complexity. In practice, that covers a lot of tasks.
The problem is that Excel is the only flexible tool many people have so they bend it to do everything in lieu of anything better.
I've been tasked to rewrite a spreadsheet as a dedicated app.
Most of what is in TFA is correct but it only begins to describe the problem.
My takeaway is very simple: the world runs on broken spreadsheets that are full of bugs and wrong assumptions. And the PowerPoint presentations are shown to business people taking decisions based on the wrong numbers coming from those spreadsheets. And it's the world we live in.
> It was common for the data to be split over two or more sheets.
And when the sheet are separated, for example, by years, that's even more bugs. Stuff is counted twice. Others not at all. The concept of when an entry should be "closed" when it's opened on year X and closed on year "X + 1" is something that bewilders spreadsheets users.
> and errors as the spreadsheets grew or were adapted to new data
Errors in spreadsheets are the big one: spreadsheets are full of errors.
> Another problem with spreadsheets is that the amount of data they can contain is more limited than when you use other data analysis workflows.
Another gigantic issue is that the notion of time is broken in dynamic spreadsheets: not because a spreadsheet cannot be written to correctly deal with it. But because the spreadsheet users don't know how to properly model how values relates to varying time (typically the spreadsheet shall work, for the cell that fetches the value, for the time value of "now" and that's it).
> I know there are lots of people who love using spreadsheets.
It's because you can cosplay being an actual programmer when you're not. The result, sadly, is exactly what you'd expect: buggy spreadsheets full of broken assumptions.
Which are then sent to those meaning real business to be rewritten as dedicated apps...
P.S: as of now I'm working on finance stuff... Same old story: a spreadsheet that has overstayed its welcome, it became gigantic. It's a pure mess of fetching values (and fetching way too many values, which creates technical issues) and bogus little things left and right. So what are we doing, again? Porting that spreadsheet to a proper dedicated app that can, correctly, deal with a proper amount of data, while fixing all the little glitches and gotchas too complicated to fix in a spreadsheet.
There are spreadsheet authors who would have been programmers in "another life" and it's usually possible to get thru to them re: the kinds of issues that plague spreadsheets. Those people are a pleasure to work with. I remember showing a junior finance guy how to use "JOIN" to overcome performance problems with his ad-hoc VLOOKUP-based re-implementation. He "got it" and things got a lot better.
OTOH, there are people who have no background in computational thinking or software development churning out spreadsheet nightmares w/ no regard to accuracy or maintainability. Anecdotally, they seem to be the people most attracted to glitzy formatting features, "no code" automation tools, and frightening nightmares of "linked" spreadsheets.
It depends, excel is a powerful tool but in nowadays tech it is outdated. I remember years ago I used google sheet as a backend, it did the storing like a database, but also the auto web scrapping into that db after the filtration, everything was running on auto mode for free, and UI communicated through google API. Obviously nowadays it’s not worth it with all these shiny tools out there.
A caveat first, most of the data I work with is under 10k rows. YMMV.
I find Excel slower than Python for analysis but I share a lot of data modelling with non technical people, and we both like being able to verify the results with pivot tables and functions typically (I need my work to be easily “defensible” because often facts are different from someones “feelings”). So workflow has become something like:
plan -> code -> generate outputs -> generate an XLSX -> verify with pivots/functions ->share with stakeholders
Python pipeline does the transformation and modelling, excel is review/verification, sharing layer.
The (sorta) issue is that the workbook becomes an output, not a source of truth.
People can add pivots and review tabs, but I ask that they provide corrections/feedback outside of generated sheets (otherwise those changes will disappear the next time I run the pipeline and recreate the workbook).
Seems like rules for using spreadsheets more strictly would suffice. Call this abstraction TypeSheet maybe?
I tried working on this idea at one point. My conclusion is that doing it sensibly ends up removing all the advantages of spreadsheets which makes them so widespread (as the beginning of this article discusses so well).