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 is another thing Apple does right. Pages allows you to turn off the inline text and use only absolutely positioned non-floating text boxes. In this mode It’s more similar to desktop publishing tools than word processing tools.
> You can do horrifying and amazing things with Excel. Samsung’s Austin fab, at least circa 2020-ish, was generating machine labels with it.
Generating machine labels seems useful, but I don't think that feels particularly horrifying or amazing. Just like, yeah, that's something it should be able to do.
A former coworker was previously employed in high finance in London, his entire job was keeping the rickety lattice of interlinked spreadsheets across the company working and alive. People would type in a number in one spreadsheet, and after several hours, another number would appear via the lattice in a more influential spreadsheet.
The company made money, so it worked, but he swears is caused him to start prematurely balding. Maybe a convenient excuse, but also plausible.
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.
The key value is that it’s a visual tool with native usability affordances addressing the needs of novices as well as power users — we have too few of such tools.
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.
> 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.
How? Do you have all the values from the semantic versions lying around for a comprehensive test in the engine?
But also those 100 chars can be in 10 lines with comments, so you can fire the engine on specific parts to help with verification
As a spreadsheet user first, this just reads as programmer prefers programming.
Many times the spreadsheet is a collaborative document. One way to use spreadsheets is you use it for a number of iterations, get your outcome quickly, let stakeholders give feedback and adjust the approach. Then once it becomes stable, then you turn it into a database/app/script. Errors in spreadsheets are obvious to more users than errors in code because your work is shown, not hidden.
Taking a csv file and writing bash is not the answer. If you are doing that, you should probably get back to work/research.
Coders get a little sniffy about Excel sometimes, but it's so good at achieving 80%-ness across multiple axes that I can't see it being displaced.
- the lowest barrier to entry for pretty much anyone in corporate life. Boot PC, there it is.
- rapid data exploration. Just put something in a cell. Play around.
- extensive self-, community-, pro-, and AI help.
- an impressively powerful programming engine. Well, two really, one of which survives after 30 years and multiple murder attempts by Microsoft. You can build complete applications, UIs, database front ends, etc.
- serious tooling for power users via toolpaks and plugins
- just enough tooling to add visual polish to outputs.
Excel isnt the best at many things (I still pine for Improv, tbh) but it's good enough at a lot of things for that not to matter, and its so easy to get started, the allure is hard to ignore.
Gsheets comes close, but still (to my mind) smells amateurish for power users. Maybe that's my problem rather than Google's.
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.
> They force name and number everything which is exactly like using single letter variables, they are only allowed for simple things.
This isn't true, named ranges have existed for longer than many (most?) HN readers have been alive. As far as I'm aware they were in the first version in 1985.
You mean the second row right? Then i have to freeze the top row that isn't the top row?
I understand there is no way back but it was a poor design choice. If you see a table on a website no one would put the alphabet in the top row. That you get a thousand columns by default also is a poor choice.
The thing is supposed to be data entry, organisation, presentation, calculation and data storage in one. Very ambitious, not a bad result but also not good at any of it. It makes relatively simple things terrifying lol
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 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.
The difference is that the Python one-liner calculates the entire thing whereas in Excel a complicated formula only calculates one cell, and may be different from cell to cell (unless you use array formulas in Excel, which are rare).
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.
There's a lot here I agree with and a lot I disagree with. If I'm doing any kind of serious data work I'm probably not reaching for a spreadsheet for things like processing, manipulation, statistical analysis. But if I'm presenting tabular data, or god forbid editing it? I'm using a spreadsheet --- and I've built a whole side project out of making working with CSVs as tabular data as pleasant as opening my favorite text editor.
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).
Numbers is fine. Its main innovation is creating multiple tables in a single visible sheet, so that people who want to look at two logically separate things side-by-side have a nice experience and adding a row in one table won’t affect the other.
That said the gist of the article is that don’t use spreadsheets for things that are too complicated. If you restrict yourself to simple things then Numbers is perfectly cromulent with a nicer UI than Excel.
Numbers is essential, because it understands dates, better handles CSV files, and doesn't corrupt long numeric UIDs.
But, you're right: Excel is for the complicated stuff. I recommend Numbers to the vast majority of users in our company, who mainly want to organize tabular data, and will never do any calculations with it. The financial folks are welcome to knock themselves out with their Excel wizardry. That separation of concern / purpose works well for both.
totally agree that its "fine" - but no one who is serious is doing their data work / anything else in Numbers.
>gist of the article is that don’t use spreadsheets for things that are too complicated.
the OP appears to have never, ever, met a problem where his solution wasn't "make it way more complicated than it needs to be" - go look at the first page of his blog for proof.
Excel succeeds in the business world because users interact with visible data directly on the screen rather than relying on abstract database storage and retrieval.
Non-technical workers can build functional tools rapidly, outperforming traditional software development speeds massively. Engineering CS types completely miss the point on this. If Im making a model for finance I need to see the data live, update fast, get my charts done without messing about with matplotlib etc. Get to solutions and decisions.
Spreadsheets are still prolific bc they enable rapid prototyping, visibility and flexibility that is to this day completely unmatched.
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.
"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."
FWIW, I think this should be seen as a failure of the software industry more than as a failure of those people. Pretty obviously, the vast majority of people have "no background in computational thinking or software development" and so they use what they can and in many cases, that's a spreadsheet.
If the software industry saw fit to offer something that was technically better while still being something they could actually use, they'd use it. But it hasn't.
I certainly didn't mean to sound over pejorative about the "non-programmer" spreadsheet authors. They're people trying to get stuff done using IT automation and that's a laudable goal to me. Sometimes their best intentions end up making nasty work for others later. Their hearts are in the right place.
I have mixed feeling about spreadsheet nightmares as a failure of the software industry. I don't think any amount of well-designed, technically better software is going to attract the particular stripe of people who see the ends w/o concern for the means. I feel like that kind of person is usually unwilling to learn anything that doesn't appear immediately in service of their goal (even if that learning would end up helping them in the future).
Short of AGI that to dictate their requirements to I don't think there's a technically better solution for some people. Even then, AGI would have to ask clarifying questions which, in my experience, this type of person regards as tiresome and needless. Trying to get these people to discuss corner cases, hard-and-fast business logic, and even basic workflow is painful.
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 was a semi-power user once upon a time--even before Excel itself came on the scene. But latterly, spreadsheets were and are just a convenient way to create tabular information even if there aren't calculations involved, much less complex ones.
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).
When you add sufficient rules to spreadsheets to make them more well-behaved, they just become UIs for SQL tables stored in a SQL database. Instead of autofilling formulas they just become generated columns. Pivot tables are views or materialized views with GROUP BY.
Unfortunately I’ve never found a SQL UI tool that optimizes for fast data entry in interactive use. Most UI don’t even allow the use of arrow keys to move between cells.
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.
Many of the issues mentioned are about poorly skilled people doing poorly skilled things that can happen in any environment
("None of them—not a single one in over a decade—used cell names to help make the formulas easier to understand." have you never seen variables named a, b, c? Same thing with nesting- just like you can add newlines and spaces in the formula bar, you can cram everything in a single line in your programming language unless it enforces whitespace)
But that doesn't translate to "you", you can follow the rules to make the formulas readable, you don't need to follow the bad guys?
Though in general yeah, the tooling you have to do anything complicated in a well architectured way in Excel is just awful
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.
>You can do horrifying and amazing things with Excel.
I've never seen a better explanation of the tool than that one sentence
“After all, He-Who-Must-Not-Be-Named did great things – terrible, yes, but great.”
This should have either been a Word template that used mail merge to insert the data into fields or an Access form.
Yeah. People usually forget that Word also supports tables. In my experience people think, I need tables therefore it’s Excel.
Tbf trying to position things in Word is enraging. I get the appeal of having an explicit grid.
This is another thing Apple does right. Pages allows you to turn off the inline text and use only absolutely positioned non-floating text boxes. In this mode It’s more similar to desktop publishing tools than word processing tools.
Really? That’s awesome! I’ve never bothered to use it, because GSuite is right there, but I will definitely keep that in mind.
Access gives you an explicit grid in its report designer.
Excel is already a solid grid without a separate report designer being necessary.
Except the OP had to write VBA code to link it to a database. That is almost guaranteed to be a brittle solution with hardcoded cell IDs.
I mean nothing should be an Access form, ever. :)
After learning Access in university I agree. Just toss it into the ocean...
> You can do horrifying and amazing things with Excel. Samsung’s Austin fab, at least circa 2020-ish, was generating machine labels with it.
Generating machine labels seems useful, but I don't think that feels particularly horrifying or amazing. Just like, yeah, that's something it should be able to do.
A former coworker was previously employed in high finance in London, his entire job was keeping the rickety lattice of interlinked spreadsheets across the company working and alive. People would type in a number in one spreadsheet, and after several hours, another number would appear via the lattice in a more influential spreadsheet.
The company made money, so it worked, but he swears is caused him to start prematurely balding. Maybe a convenient excuse, but also plausible.
> You can do horrifying and amazing things with Excel.
A friend of mine writes all of their correspondence in it.
Can you expand more on this? Is it just for drafts or do they track all their correspondence in Excel?
They literally compose and print their letters in Excel.
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.
The key value is that it’s a visual tool with native usability affordances addressing the needs of novices as well as power users — we have too few of such tools.
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 have just recently learned there is a great feature for this, which I really miss in LibreOffice, since I came to know it exists:
https://support.microsoft.com/en-us/excel/display-the-relati...
https://support.microsoft.com/en-US/Excel/see-links-between-...
These really helped me to review low-to-mid complexity sheets where inserted rows and incorrectly copied formulas messed up stuff.
> 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.
How? Do you have all the values from the semantic versions lying around for a comprehensive test in the engine?
But also those 100 chars can be in 10 lines with comments, so you can fire the engine on specific parts to help with verification
As a spreadsheet user first, this just reads as programmer prefers programming.
Many times the spreadsheet is a collaborative document. One way to use spreadsheets is you use it for a number of iterations, get your outcome quickly, let stakeholders give feedback and adjust the approach. Then once it becomes stable, then you turn it into a database/app/script. Errors in spreadsheets are obvious to more users than errors in code because your work is shown, not hidden.
Taking a csv file and writing bash is not the answer. If you are doing that, you should probably get back to work/research.
Coders get a little sniffy about Excel sometimes, but it's so good at achieving 80%-ness across multiple axes that I can't see it being displaced.
- the lowest barrier to entry for pretty much anyone in corporate life. Boot PC, there it is.
- rapid data exploration. Just put something in a cell. Play around.
- extensive self-, community-, pro-, and AI help.
- an impressively powerful programming engine. Well, two really, one of which survives after 30 years and multiple murder attempts by Microsoft. You can build complete applications, UIs, database front ends, etc.
- serious tooling for power users via toolpaks and plugins
- just enough tooling to add visual polish to outputs.
Excel isnt the best at many things (I still pine for Improv, tbh) but it's good enough at a lot of things for that not to matter, and its so easy to get started, the allure is hard to ignore.
Gsheets comes close, but still (to my mind) smells amateurish for power users. Maybe that's my problem rather than Google's.
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.
> They force name and number everything which is exactly like using single letter variables, they are only allowed for simple things.
This isn't true, named ranges have existed for longer than many (most?) HN readers have been alive. As far as I'm aware they were in the first version in 1985.
Joel Spolsky's "you suck at Excel" is more than a decade old but still relevant: https://youtu.be/JxBg4sMusIg
You mean the second row right? Then i have to freeze the top row that isn't the top row?
I understand there is no way back but it was a poor design choice. If you see a table on a website no one would put the alphabet in the top row. That you get a thousand columns by default also is a poor choice.
The thing is supposed to be data entry, organisation, presentation, calculation and data storage in one. Very ambitious, not a bad result but also not good at any of it. It makes relatively simple things terrifying lol
No, I mean that you give a name to a cell of "Price" or "ExchangeRate" and then you write formulas as =Price*ExchangeRate rather than =A13*C8
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 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.
The difference is that the Python one-liner calculates the entire thing whereas in Excel a complicated formula only calculates one cell, and may be different from cell to cell (unless you use array formulas in Excel, which are rare).
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.
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.
There's a lot here I agree with and a lot I disagree with. If I'm doing any kind of serious data work I'm probably not reaching for a spreadsheet for things like processing, manipulation, statistical analysis. But if I'm presenting tabular data, or god forbid editing it? I'm using a spreadsheet --- and I've built a whole side project out of making working with CSVs as tabular data as pleasant as opening my favorite text editor.
Honorable mention: https://www.theguardian.com/politics/2020/oct/05/how-excel-m...
That one time NHS had data loss because of XLSX limits
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).
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.
Numbers is fine. Its main innovation is creating multiple tables in a single visible sheet, so that people who want to look at two logically separate things side-by-side have a nice experience and adding a row in one table won’t affect the other.
That said the gist of the article is that don’t use spreadsheets for things that are too complicated. If you restrict yourself to simple things then Numbers is perfectly cromulent with a nicer UI than Excel.
Numbers is essential, because it understands dates, better handles CSV files, and doesn't corrupt long numeric UIDs.
But, you're right: Excel is for the complicated stuff. I recommend Numbers to the vast majority of users in our company, who mainly want to organize tabular data, and will never do any calculations with it. The financial folks are welcome to knock themselves out with their Excel wizardry. That separation of concern / purpose works well for both.
totally agree that its "fine" - but no one who is serious is doing their data work / anything else in Numbers.
>gist of the article is that don’t use spreadsheets for things that are too complicated.
the OP appears to have never, ever, met a problem where his solution wasn't "make it way more complicated than it needs to be" - go look at the first page of his blog for proof.
Excel succeeds in the business world because users interact with visible data directly on the screen rather than relying on abstract database storage and retrieval.
Non-technical workers can build functional tools rapidly, outperforming traditional software development speeds massively. Engineering CS types completely miss the point on this. If Im making a model for finance I need to see the data live, update fast, get my charts done without messing about with matplotlib etc. Get to solutions and decisions.
Spreadsheets are still prolific bc they enable rapid prototyping, visibility and flexibility that is to this day completely unmatched.
There still isn't really any equivalent of Excel for the things serious excel users use it for. In large part because Excel is a build system
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.
"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."
FWIW, I think this should be seen as a failure of the software industry more than as a failure of those people. Pretty obviously, the vast majority of people have "no background in computational thinking or software development" and so they use what they can and in many cases, that's a spreadsheet.
If the software industry saw fit to offer something that was technically better while still being something they could actually use, they'd use it. But it hasn't.
I certainly didn't mean to sound over pejorative about the "non-programmer" spreadsheet authors. They're people trying to get stuff done using IT automation and that's a laudable goal to me. Sometimes their best intentions end up making nasty work for others later. Their hearts are in the right place.
I have mixed feeling about spreadsheet nightmares as a failure of the software industry. I don't think any amount of well-designed, technically better software is going to attract the particular stripe of people who see the ends w/o concern for the means. I feel like that kind of person is usually unwilling to learn anything that doesn't appear immediately in service of their goal (even if that learning would end up helping them in the future).
Short of AGI that to dictate their requirements to I don't think there's a technically better solution for some people. Even then, AGI would have to ask clarifying questions which, in my experience, this type of person regards as tiresome and needless. Trying to get these people to discuss corner cases, hard-and-fast business logic, and even basic workflow is painful.
Obligatory link to Joel Spolsky’s 2015 You Suck at Excel talk: <https://www.youtube.com/watch?v=JxBg4sMusIg>
Thanks!
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 was a semi-power user once upon a time--even before Excel itself came on the scene. But latterly, spreadsheets were and are just a convenient way to create tabular information even if there aren't calculations involved, much less complex ones.
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).
When you add sufficient rules to spreadsheets to make them more well-behaved, they just become UIs for SQL tables stored in a SQL database. Instead of autofilling formulas they just become generated columns. Pivot tables are views or materialized views with GROUP BY.
Unfortunately I’ve never found a SQL UI tool that optimizes for fast data entry in interactive use. Most UI don’t even allow the use of arrow keys to move between cells.
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.
Many of the issues mentioned are about poorly skilled people doing poorly skilled things that can happen in any environment
("None of them—not a single one in over a decade—used cell names to help make the formulas easier to understand." have you never seen variables named a, b, c? Same thing with nesting- just like you can add newlines and spaces in the formula bar, you can cram everything in a single line in your programming language unless it enforces whitespace)
But that doesn't translate to "you", you can follow the rules to make the formulas readable, you don't need to follow the bad guys?
Though in general yeah, the tooling you have to do anything complicated in a well architectured way in Excel is just awful
[dead]