For example: you can have an Excel file with 100k lines and have a formula accidentally replaced by a static number on line 87456. Nobody will ever find it.
I make this point when chatting with new team members that say "let's just use X," without really understanding how X works, and I use Excel as the analogy. Most people know how to use an equation field. Most people don't consider writing a spreadsheet programming, but it is.
And just like other types of programs, you can have really insidious bugs, and you need to consider how you'll address those, or address changes in the underlying or internal components of the system.
A famous one is that floating point math in excel can give you different answers for "is this the same as that" depending on how you write the equation, for example.
When we launched our excel compatible spreadsheet we diligently formalized every function and wrote tests to verify behavior. It was beautiful.
Then we started receiving lots of bug reports from users because our calculations often didn’t match what they were seeing in Excel. Older Excel versions had lots of bugs and Microsoft had been carrying them forward because at that point it was “better” to carry the errors forward and display results people expected rather than fixing them and having everyone confused by the changed results.
So we copied their behavior as best we could. I wish I had the list still but it was long and full of weird edge cases which caused formulas to be incorrect.
https://www.joelonsoftware.com/2006/06/16/my-first-billg-rev...
I believe Google made the same choice with Google Sheets even.
Is this specific to Excel? Floating point math can do that regardless of the platform. Addition on floats isn't associative!
No. And yet they still have situations where excel itself will differ from ieee 754. See https://learn.microsoft.com/en-us/office/troubleshoot/excel/...
IIRC, he was pretty proud of being able not only to have a regression test suite, but also being able to do TDD-excel :)
You can add conditional formatting, VBA procedures, additional formulas etc. to do whatever validation you like. However, you need a software engineer (or someone who thinks like one) to properly implement that, and it’s not exactly fun.
[1] https://support.microsoft.com/en-us/office/lock-or-unlock-sp...
There are workarounds using VBA, but not via Typescipt - the Excel JS API knows no clipboard.
This sounds like an assumption worth validating, because it sounds like an easy product to sell for lots of money.
Source: SO works in a field where they handle other people's money and payroll. Not a small mom & pop shop either.
The employees use Excel sheets handed over from "someone" and just plug in the numbers.
In Britain they messed up the covid numbers because they used the wrong format to store their infection numbers and lost all lines after 65534...
> This sounds like an assumption worth validating, because it sounds like an easy product to sell for lots of money.
Unfortunately, it's not so simple. I've worked in exactly this field, getting such institutions/companies to adopt this kind of software is ridiculously hard.
The people are stubborn and many have had experiences with subpar enterprise software. If they are billing clients, any additional step in software is akin to a national tragedy to them. They will complain something is "broken" if it doesn't fit their mental model of what a software should be before trying anything new.
Another thing to consider: we are conditioned to believe that the web browser is a "universal runtime", i.e. it's the thing that everyone has, so you should target it. But actually, it's not the only one. When considering Windows computers (which is all that matters in offices), Excel is a kind of dark-matter universal runtime, for dark-matter IT[1]. Better, it's one that doesn't require a persistent server, or logins, and it's peer-to-peer, in a sense. Everyone has Excel installed, everyone has an org email account, therefore everyone can email xlsm files back and forth, no extra setup required (try doing that with exes). No SaaS offering has that kind of caveman convenience. And you might think -- that's horrifying. But email is the way communication/collaboration actually happens in the median bureaucracy, so Excel's ubiquity and file-orientedness is an insurmountable strength.
If these tools need any type of user interaction they won't use it.
How many people still right align text per spacebar or tab if they are pros?
The main problem here is that Excel is so very flexible, and everything else is not (generalizing here, but it gets the point across). So the after the third time you are using Excel to handle the exceptional cases, you start to wonder why you are not using it everywhere else.
Of course the counter-problem is that it is so flexible that it accommodates your mistakes without pointing them out.
no they don't. Not even for billion dollar scale accounting
> because it sounds like an easy product to sell for lots of money.
Is it Excel though? Because if not, then you're going to have the mother of all uphill battles trying to get it sold.
It would be akin to trying to convince a giant company with hundreds of millions of lines of Java code to rewrite into golang.
There were low ranking people who recognized how stupid this all was, and pointed it out. But hardly anyone with decisionmaking power even understood the problem. Nobody had even heard of git. So nothing changed. It was the dark ages, and a major factor in why I quit.
You can also build your sheets properly, so formulas are secured from changes, or "draggable" - so even if someome breaks them you correct them.
The people who answer to you dont know much about Excel. What makes me wonder how much they know about programming.
Excel is a program that "gives power to the people" so there are tons of crappy sheets, that often mix data with calculations and so on. But you can make good sheets if you try. And know how to.
Other comments are amateur hour.
There are lots of bad programs too, but programming is not so democratic.
Also it is very convenient to blame a mistake on Excel.
I'm one of the other commenters you called "amateur hour". I know about all of those things you mentioned.
>array formulas, using formula inspector, Excel marks inconsistent formulas too. You can use data validation for selectors, you can lock cells. You van use pivot tables for summaries.. named cells, perhaps tables (type of data collection).
Problem with all of these is some combination of:
- they suck
- boomers don't know how to use them
- you can't use them for compatibility reasons
Array formulae: boomers don't know how to use them, and the good ones aren't backwards compatible with older Excel versions or LibreOffice/Sheets (major problem when dealing with ppl outside the org)
Formula inspector: I've never found this to be useful
Excel marks inconsistent formulae ... inconsistently. It fights you when you're trying to do something slightly non-standard but otherwise perfectly legit, because it's just pattern-matching. Sometimes you have formulae that are fine, then you close and re-open the file and they're marked as inconsistent. Or vice versa. It leads to alarm fatigue. And boomers don't know what it means.
Data validation for selectors: boomers don't do this (are you seeing a pattern?). More importantly, they don't interact properly with macros. And it's difficult to express mutually-constrained inputs.
Pivot tables for summaries: boomers actually do know these, but the problem is they're not reactive. They don't refresh when the data source changes.
Locking cells: there's no convenient way to tell at a glance which cells are locked and which aren't (yes I know about the "Find All" way, that's not convenient).
Named cells: inconvenient, no namespacing (well there is a kind of namespacing but it's implicit), you can have duplicates of the same name that refer to different things, or dead ones that point to nothing, and there's no good way to debug it.
Tables: these are great, but sadly aren't compatible with LibreOffice/Sheets. Column labels cannot be dynamic. You can't put the summary row at the top, only the bottom. They will fill down a formula even when you don't want them to, there isn't a way to turn that off afaik. They don't interact properly with array spill formulae.
>You can also build your sheets properly, so formulas are secured from changes, or "draggable" - so even if someome breaks them you correct them.
People can and will fuck up even the best-made sheets in irrecoverable ways, that are difficult/impossible to detect automatically within Excel. Yes, we locked all the cells except for inputs, password-protected the sheets, used data validation, used tables, used named references, we did all that stuff you're meant to do and more. It wasn't enough: we'd get back the filled-in sheets and there would be broken references every-fucking-where. I've forgotten exactly what they were doing (it might have been from dragging cells around), but whatever it was, I had to write Python scripts to diagnose it by looping through all the cells and comparing the formulae and sheet layout against a known good copy.
Also it is common knowledge that MS tries to get rid of open spreadsheets - only allowed competition are google sheets. If your Excel doesnt have array formulas - please get a newer version. Even 2003 had them.
I dont claim that Excel doesnt have problems, also it could be better - but there are ways to make more robust calculations.
On a side note Excel and the dreaded (obsolete?) VBA are one of the last democratic things that allow non-programmers to build things.
Personally I hate tables, you can in fact add some random crap in one of the random cells.
Lots of your criticism are valid, Excel could definitely be better. But now we dont have better.
As someone trying to learn SQL, the syntax of typical queries(one page long, not some basic ones) is horrible - the language is completely not designed for tasks when you glue 20 tables together... are those queries easy to understand / inspect? IMHO no.
it's the other side of the coin for "last democratic thing that allows non-programmers to build things" (which I sympathize with). you can't just ignore the capabilities of the userbase demographics.
>Also it is common knowledge that MS tries to get rid of open spreadsheets - only allowed competition are google sheets.
it's not microsoft's fault LibreOffice doesn't have tables. OnlyOffice has them. there's a feature request thread in the LibreOffice archives, someone asked for excel-style tables to be implemented. the LO devs didn't even know what they were(!), people had to explain it to them. dev response was lukewarm. there doesn't seem to be any great push to implement it.
https://bugs.documentfoundation.org/show_bug.cgi?id=132780
>If your Excel doesnt have array formulas - please get a newer version. Even 2003 had them.
I mean the new ones. the dynamic array formulae with the spill behaviour. SEQUENCE, FILTER, SORT, UNIQUE, etc. those are only in office 365.
>As someone trying to learn SQL, the syntax of typical queries(one page long, not some basic ones) is horrible - the language is completely not designed for tasks when you glue 20 tables together... are those queries easy to understand / inspect? IMHO no.
no disagreement there, SQL is not a good language. have you tried M?
Also you act as if there werent any programming horror stories. Very often programmers want to rewrite the code they got from othera.
**
I am not sure which one is M and which one is DAX. Those are the PowerBI ones. In my opinion they are horrible. The idea of "steps" makes sense, but the gui that breaks steps is horrible. Also the syntax is horrible.
**
MS at some point had pivot tables, pivot tables with data model, power pivot, powerBI stand-alone app, "old" sql query editor. Now they try to add powerBI standalone back to Excel.. partially (on a side note I hate that poweBI exports from stand alone app to Excel are so horrible), and they got rid of the old SQL editor. If you want to write SQL in Excel you might as well use notepad..
PowerBI was supposed to be a step forward. OK it allows to build things, but is in my opinion horrible. M/DAX are horrible to use for typical operarions such as;
Changing source
Combining two sources
Joins
Joins on joins on joins
Joins
Syntax is supposed to be explicit, but it is horrible to write and horrible to read
> and have a formula accidentally replaced by a static number on line 87456
Surely this specific case could be validated externally with a script too.
But there is no built-in tool you can use to say to excel "this column must always be a number, don't ever allow it to be autodetected as a date" or "this column must always have a formula, if the formula doesn't exist or is a static number, alert the user".
You can lock cells, but who bothers doing that in the real world.
If some user wants to view it, then the script can create a valid Excel file.
In its basic form you perform the same calculation on the totals as you did on the roots and subtract it from the result. The answer must be zero. I know it is harder on some kinds of data, but it can prevent a huge amount of every day errors.
How do you manage to require "a handful of clicks" to do it?
It's still a pretty minor warning and it only requires two clicks to ignore.
If you wanted automatic formula auditing, I'd recommend using R1C1 reference style so formulas using relative references are independent of the cell's location, then use FORMULATEXT function, and an array formula applied over the whole range you want to check.
Then you could be sure.
That's indeed one of the basic suggestions in Spolsky's "You Suck At Excel":
Of course a custom website backed by a properly schema'd SQL database would be better, but that takes money, time and someone needs to keep it updated.
People use Excel because it's on everyone's computer by default, they have full permissions to do anything with it without needing IT approval and it's surprisingly powerful when you get into it.
And if a client happens to have a really weird non-standard requirement for how their payroll is done, any accountant can easily create a custom Excel sheet just for them - instead of waiting 6 months for an external consultant company to provide them with a custom tool that does 80% what they need.
But if you want reliability, just don't use Excel because making it bulletproof is pricier than just using SQL for reporting and analysis.