Absolutely right. Excel (and spreadsheets in general) are bad social media/groupware/workflow tools... who knew? This just in: word processors make for a lousy DBMS... film at 11!
Points for their SEO/clickbait headline: it hooked me... this time (-1 one added to my personal WSJ lifetime value calculation)
> A year ago, Mr. Bell’s team spent hours distributing hundreds of Excel spreadsheets to regional and unit leaders each month for planning and performance tracking of the company’s 415 U.S. restaurants, he said. Now the same process takes minutes.
Then I believe it should say stop abusing Excel.
Excel can be an incredibly useful tool, but it's definitely not universally useful. If you're using it to manually collate data from 400+ sources, you're doing it wrong.
> A year ago, Mr. Bell’s team spent hours distributing hundreds of Excel spreadsheets to regional and unit leaders each month for planning and performance tracking of the company’s 415 U.S. restaurants, he said. Now the same process takes minutes.
Reads to me like, "last year Mr. Bell's team spent hours hammering in nails by smacking them with the wooden part of the hammer's handle"
This is the point that's going to get missed a lot. In the corporate world Excel is the proverbial hammer to nearly every use case involving data manipulation, regardless of how well suited it is for the task.
Edit: I just noticed that I'm the third person to reply to you and we all referenced hammers.
I don't know that Excel was the wrong tool for this task. I'm not sure why the spreadsheets couldn't have been distributed and consumed automatically by an Excel application, or at least by helper applications that were fed by and/or into Excel sheets. An Excel spreadsheet is a good enough form; and I don't think replacing one with a webform or pdf form would be any better.
edit: my favorite thing about using excel is that instead of spitting out an answer, you can spit out an Excel sheet that shows a lot of the work itself, therefore partially explaining the answer to the people that have to consume it. The product of your program can often be another program, giving the person receiving the report the opportunity to tweak or add new parameters to your output. Basically currying accounting calculations.
Many moons ago large bank I worked at purchased a company and soon it became apparent that their entire business ran on a Lotus 1-2-3 spreadsheet with 30,000 lines of really dense, uncommented, unindented, spaghetti LotusScript code spread over 2 or 3 functions. The kicker is it would only run in the 16-bit edition of the product for whatever reason. So we had to have a VM to run it in because Windows 2000 had a problem with either Lotus 1-2-3 16 bit edition or the spreadsheet itself.
So yeah, stop abusing Excel - Lotus 1-2-3 in this case.
The core complaint of not having a single source of truth seems more about how the company handles data/filing rather than Excel itself.
Maybe I'm a bit bias having used Excel my entire career and very dependent on it, but everytime I've seen companies steer away from the desktop application with 0365/sheets/tableau its simply not as useful for adhoc data analysis and reporting.
I agree. Those are great products, if you have the staff to support and use them, but this is rarely the case. Every time I see a company implement them, they think it's going to be a silver bullet to reporting and analytics, and most of the time it just causes a mess, because no one really knows how to properly use them, or if someone does, that person doesn't have the bandwidth to keep up with the entire organization's needs. Excel is amazing, it's relatively easy to use, has immense power for dealing with calculations and graphs, and it is cheap. An organization's problem isn't excel, its the fact that they keep 20 copies of the same data and no one is keeping track of which is the source of truth.
Every email reply becomes an independent fork of the data inside the spreadsheet. The issue is storing and sending the file locally, and the solution is storing it in a centralized location. Cloud storage is often used towards this end, but on-premises file shares work very well too.
This is one of those solved problems that companies inexplicably keep working to solve again.
It's such a bizarrely difficult uphill battle. We have Office 365, with a damned good web editor, and the ability to save directly to OneDrive/Sharepoint from the desktop client, with the versioning that that supports, and we still end up with shit emailed all over and files with version-12.xlsx and -$DATE.xlsx saved here, there, and everywhere.
The web editor might be good enough today but it used to choke and wouldn’t open/render all kinds of stuff that’s normal in financial spreadsheets. Thats not even getting into what a shit show SharePoint has been over the years. Microsoft has slowly eroded trust in anything but email sharing and (if there haven’t been missteps within an org with them) file shares.
How do you even HAVE a source of truth? You email a spreadsheet to two people, and the both want to work on it... which one is the source of truth? They each have modified the original, and we want to keep both changes.
I mean, this is the problem that distributed version control was designed to solve. Emailing a spreadsheet around and calling one a source of truth doesn’t solve the simultaneous edits problem.
Totally agreed. Especially when the alternative is abusing an ERP for ad hoc reports or using a journey map approach that captures the top X use cases, but misses what is actually needed.
I happen to use and advocate for R/RStudio for ad-hoc work (I spin it as "excel on steroids" when I am trying to convince someone). But it is hard to pull people away from something that has worked for them.
The alternatives to excel in the past have been really, really unpleasant to use. A now EOL product from Oracle comes to mind. It is called "Discoverer"-- that's right, "Discover" + "er". It is a turgid POS requiring extreme suffering and handholding/permission granting from your local friendly DBA-- all this to produce 90's era bar charts that are all but impossible to tweak.
IMO if you really want non-tech users to become more technical and use DBs as single source of truth, use KNIME. It still needs proper reporting addons though, but you can export data, even to tableau.
I saw a presentation about KNIME but was unfamiliar with many of the processes that it allows one to string together to build a workflow, so the notion of building a workflow myself seemed very daunting. Do you think it would allow one to build workflows for basic stuff and gradually grow in their confidence with it?
The presentation mentioned that the company is/was working on reporting add-ons, I think.
Oh yeah, I'm pretty dumb and I managed to do very complicated stuff. It seems a bit tricky to "get" at the beginning, but it's pretty easy.
The trick is to learn what node does what, because there are lots of them. You can also download community nodes for additional behaviors. Once you learn the basics, it's pretty straightforward.
I learnt by trial and error, looking for examples and info about nodes, and now I have some workflows with data from DBs, files, APIs and all at once.
>its simply not as useful for adhoc data analysis and reporting.
<rant>
You wanna know what's even more useful? Code notebooks.
I store everything from my sql queries to my processing pipeline to my models, and my vizualizations. Most notebooks have slide-show modes now so your presentation to business stakeholders is simple to prepare as well, and with the right plotting packages you can draw interactive charts that do all the fancy stuff. And when it's time to update the analysis, you just re-run the notebook.
But business analysts are too freaking lazy to just learn to write some python or R, and they're perfectly happy to get paid to do the same task over and over again when it comes time to making updates.
Do you have any way you'd recommend of getting multiple users working together with code notebooks? I've messed around with Jupyter, but that's about it.
We usually just send .ipynb files around because of the stuff we do, we all usually have the same libraries.
Installing a notebook server is an option though. IBM will be happy to let you pay to have them help, or you can do it the sane way with continuum analytics.
Most business analysts who are pulling this kind of data have a finance background. To say that they're "too lazy" because they don't want to do their work using Python seems a bit harsh.
In practice what I've seen is that IT and finance departments don't even let business analysts write their own SQL queries for fear that they'll write bad ones that bring the database server to its knees.
>In practice what I've seen is that IT and finance departments don't even let business analysts write their own SQL queries for fear that they'll write bad ones that bring the database server to its knees
I use jupyter a good amount, but even in a low user environment there's a big worry about version control and code sharing/review
I know there's " automatically check stuff into git" sort of models but would really like an integrated Jupyter + GitHub + Google Drive system so that it's easier to track what's happening and to find older versions of stuff.
I desperately need some tool I can tell non-tech people to use where they can input arbitrary structured data.
After the data is there, in any format the user wants, as long as it is structured somehow, millions of programs can be written to use that data and combine it, but somehow the data has to be written FIRST. It is not feasible to come up with the schema, write a software based on that schema and then give it to the non-tech people.
That's a hard question to answer and depends on your size and what you really need.
For small companies, Excel may really be all that you need. But at a certain size, you might want to use something like PowerBI instead since it will also pull directly from the database and you can share the dashboards you create very easily. Large companies like the article are referring to might be able to get by with PowerBI (Microsoft would certainly argue there are still plenty of places where it works) or ERP/HR tools more specifically designed for certain uses.
I agree. A lot of these tools are coming about because people don't understand how to use excel and frequently Tableau/Power BI is not a better alternative, unless you have no clue about Excel.
My current role is at a large company, and a significant portion of my work is getting Excel reports which are broken and fixing them. Most I've never seen working. Some reports have been broken for years but need to be resurrected.
To add a couple more:
- Use actual tables. (Select data+headers, Insert -> Tables -> Table). In most cases the data is tabular but not formatted as a table. Making this one change makes the formulas self-documenting, and rather than selecting rows 2:5000, it will automatically accommodate more data as it's added. Tables can also be named (click in the table, Table Tools [Design] -> Properties -> Table Name). E.g. tbl_sales. This allows nice formulas like
=SUM(tbl_sales[Sales])
or
=SUMIF(tbl_sales[Region],"West",tbl_sales[Sales])
At a certain point, it really needs to be SQL, but between this and pivot tables you can greatly extend the time before a database is needed.
- Add a sheet with instructions. Explain hiccups which may be encountered, and how to fix them.
- Add comments to VBA. There is about 1 person at my facility (other than me) who adds any comments at all. Most use the Macro Recorder then add in the loops and logic as needed. Makes it really tough to figure out.
- Protect cells which hold calculations. I recently saw a spreadsheet which has been giving sub-optimal output for years because the ranges used in a function were messed up and copied down every week.
- Use the built-in styles (Home -> Styles). Same goes for Word (Heading 1, Heading 2, etc.). It makes spreadsheets way more straightforward to read if there are some cells using Explanatory, Input, and Note styles, and it's less abrasive to read. I particularly like the Input style because you can tell users "You'll only need to update the orange cells".
- Use multi-line formulas. Alt+Enter while editing a cell allows multiple lines on a single formula. You can also improve the readability by adding four spaces at the start of the new lines. E.g.
This seems like an alternative to Airtable and Fieldbook. Am I wrong?
I know these tools and I like them, but this wasn't what I was talking about in my comment. I meant something more flexible and more arbitrary, like a key-value store non-tech people could edit.
I think the problem is they're using Excel as a database as opposed to a spreadsheet. That's fine at small scale, it's easier to manage and even easier to get going, but Adobe scale? They should advise "Stop using Excel for what it's not".
"Instead, companies are turning to new, cloud-based technologies from Anaplan Inc., Workiva Inc., Adaptive Insights and their competitors.
The newer software connects with existing accounting and enterprise resource management systems, including those made by Oracle Corp. or SAP SE. This lets accountants aggregate, analyze and report data on one unified platform, often without additional training.
Adobe switched to Anaplan early last year and many of the tasks previously performed in spreadsheets are now done in the system, maintaining “one source of truth,” Mr. Garrett said."
I’ve worked in many large companies, and I have never seen one using SAP or other large tools as single sources. It always has been excel. From my experience due to 3 reasons
- excel is much more flexible, I can add a column, delete those I don’t want etc
- speed: many of those large corporate systems are just damn slow, maybe because of stupid configurations, but still often very slow an annoying
- analysis: It's easy to do some basic analysis without studying CS
Definitely, great point. Tools like Tableau/Power BI make it very easy to get a misleading version of the truth, since it can very easily include misleading data points and/or make calculations in a fashion that leads the user to misinterpret KPIs.
I have - worked on getting Hyperion FM implemented across ~30 countries fed by about 40 ERP systems.
All outputs were generated by a .Net application generating read-only Excel spreadsheets (which might have 30 tabs and up to 15,000 values on a tab) - was done this way to meet the very complex reporting requirements. All the ~20 report packs in the company were produced with one click in a simple web app.
Ah-hoc querying of Hyperion was done from Excel using the SmartView plugin.
Actually, the main reason is that SAP is expensive as hell. The starter package is $1400 per user (one time) or $410 per user per year. And that's just the software. Add consulting services that are always necessary to actually make it fit your organization and you're easily looking at millions of dollars in capital spend.
I use Excel because my boss wants to use Excel, because all his bosses have wanted to use Excel, because all their previous bosses have wanted to use Excel.
That's how it works in the electronics manufacturing industry, anyway.
The issues raised in this article are far less about failings of Excel but rather typical failures of business to plan long term and put effort/resources into comprehensive solutions.
I would argue that the most commonly used programming language is Excel. But few of the people using it realize they're programming.
It's a brilliant reactive data programming model that makes intuitive sense to non-technical users. They feel empowered to use it to solve problems right now with a computer. They experiment with it, try things, Google how to do more things- just like any programmer does. And they feel capable of doing this because they don't know they're programming.
Within the Amazon warehouse world, I have seen incredible innovation using it. An acquaintance of mine got into development by using it to help save soldiers lives while serving in Afghanistan[0].
I agree that mission critical data needs to get out of it and into a centralized system, but I still feel Excel is an incredible tool in any business.
I've been working on a spreadsheet which is more amenable to embedding in programs (compiling to an IR which can be translated to high level languages or [eventually] compiler IRs like those of LLVM or GCC). I worked at a company where months were spent recreating models from a spreadsheet produced by subject matter experts, and this was a major cost.
A spreadsheet optimized for integration into software build processes would probably create more than a billion dollars of real value every year with even limited adoption.
While it may be true that Excel is overused, I believe that the spreadsheet is chronically underused as a model for software development.
Will be, "soon". It turns out it's difficult to get the basics of a spreadsheet right. I made the mistake of trying to use an existing spreadsheet application (like LibreOffice Calc or Excel) as the source format early on.
That's quite cool, thanks for the link. Tables seem to be a more popular model for finance and similar, but it's always interesting to see new data models like this.
This could allow for more interesting things like gathering fields from records into a meta-table from a relational DB, that would definitely be a valuable next step.
Mathematical functions, easy formatting, and more anything a purpose. Basically it does thinga you don't want a spreadsheet to do. It's more akin to a mind mapper and there are easier to use tools for that.
It’s a good foundation for future open-source development, uses wxWidgets toolkit for cross-platform desktops. Author has graphics background, now busy at BigCo.
After seeing the level of interest here, I set up a (tentative) github org where I'll put the repo for the evaluator when it has some reasonable subset of the desired functionality.
I'll join in with a bit of show-and-tell of my own, if I may.
I'm working on a plugin for Excel for dev's and other technical people. It lets the user use SQL and C# in Excel, connect to various types of databases and move data in either direction, and provides a runtime for automation.
The idea is basically to let tech users use their existing skills to make life easier. Main areas of use are:
- process (live) data in Excel via SQL and C#/linq
- move data between Excel and databases
- fetch data from various sources using .net (e.g. get data from a AD, file system, REST etc. in a few lines of code)
- replace VBA with C# and .NET for automation
It's also very useful for prototyping. Business logic goes inside C# scripts, and Excel takes care of user input, displaying results, data binding (formulas and graphs) and data storage.
Yeah, I've always thought with the types of integration hooks you're talking about that Excel could serve as a front end for many enterprise/back office apps.
My first heavy use of Excel was in a thermodynamics class in college. We modeled heat gradients for various HVAC systems with Excel. After that, I used it to calculate stresses and strains of different structures using each cell as a node in finite element analysis model.
I did something like this in 2002 for a VFX house; but it was loading a DLL that hot loaded other DLLs, queried the routines and exposed them as Excel functions. Through that I pulled in a stochastic modeling framework I wrote, and pulled in 5 years of the studio's timecard records to create a production forecasting system, right down to selecting the correct staff for each position, with backup people, for 1200 digital artists. The Excel "spreadheet" was something like 250 MB when saved with a film production in it.
Yes...but. You can't do a SQL join between the data in an excel table and a remote database via a nice GUI front end using just .NET and ODBC. I am evaluating this product at the moment, and it looks really useful for Finance/analyst types
Yes, I saw your tool before. I recommended it to somebody a couple months ago and they're very happy with it for their use case.
It is brave of you to integrate directly with Excel, and I commend you for that. Maybe some day I will work up the courage to do something other than basic one-way import.
Interesting: optimized for integration is what I've been doing too. If you've got a minute to take a look at the links in my profile I'd be keen to hear your feedback.
When I consulted at a health insurance company (a Blue Cross Blue Shield licensee with 1000 employees, 200 IT staff) about 10 years ago, I was absolutely horrified that the system of record for health care plan data was Excel spreadsheets. They had staff who would update the claims processing system based on thousands of spreadsheets. When you called customer service, the CSR app would auto-open the member's plan information XLS file to the CSR rep. It got the job done - but it was the wrong tool for the job.
We recommended they implement a PDM (product data management) system - it would have saved them millions of dollars (considering they had a staff of 20 whose sole job was to update the claims processing system by hand based on these spreadsheets.) Unfortunately when the 2008 financial crisis happened, management balked at the initial outlay. It's quite possible they're still using Excel to this day.
The health insurance people of various sorts who use this setup are the ones with domain knowledge. With an excel program, they can (1) understand how it works (2) add to it, modify it (3) create a copy and break it (4) debug it (5) have ideas about how it could work differently…
I’m not saying that excel is the tool for the job (sounds like it is definitely not), and those advantages erode as the setup gets more complex and/or has more people using it. But, there are reasons excel gets used this way.
The alternative is often rough. Software projects like this have high failure rates. Getting “requirements” out of users’/clients’ heads and into programmer’s is difficult. It means the standard (natural?) gap between domain knowledge and programming knowledge is restored. That has costs.
I’m going to repeat again that I am not advocating health insurance companies run policies and claims in excel. It is a bad idea, almost certainly. I am saying that we should be curious at why they are doing it this way. I think it’s a “good” question in that it leads to interesting places.
If the excel team was blessed with a plague of inspiration tomorrow morning, Is there a way they could make it so that excel is the right way to manage an insurance business?
> The alternative is often rough. Software projects like this have high failure rates. Getting “requirements” out of users’/clients’ heads and into programmer’s is difficult. It means the standard (natural?) gap between domain knowledge and programming knowledge is restored. That has costs.
The alternative doesn't have to have a high failure rate. I was brought in to help reboot their in-house implementation of a product data management system - the core issue was they didn't have any real competency in software development (though they believed they did.) Ultimately I recommended they abandon the in-house effort and instead proposed using an off-the-shelf PDM system, we evaluated quite a few decent options over the course of six months and found several that met the business's requirements. It would have been the right thing to do and I believe it would have been successful. Culturally the IT department staff was plagued with not-invented-here syndrome. It was a generally dysfunctional IT group and it's quite likely that Excel got it's start as the system of record because the business needed to get the job done, and the IT department was unable or unwilling to deliver.
Alternatively you misunderstood the business requirements and would have cost then a fortune on a failed project.
Alternatively you were brought in by management to make cost cutting recommendations - and your solution wasn't cheap enough.
Alternatively the company accounted for the enormous risk of the project succeeding but actually being worse than what they had in place.
You really don't know the politics of this.
Edit:
Another alternative is that your plan would have resulted in sufficient job losses that the company was worried that it would demotivate other employees and damage the company more than the cost savings.
I upvoted your post because I think those alternatives are interesting to consider, but _you_ know far less about the politics of the situation than the OP.
It's entirely possible that the OP was exactly correct, or close to correct. It's also quite likely that at least one of the factors you mentioned (or others) was also at play.
OP here - the director that brought me in was (and still is) a straight shooter. There's no question as to why I was brought in. While she had the support of the CIO, the rest of the directors spent more time protecting their kingdoms than trying to modernize their systems. Fortunately for both myself and the director, we moved on from that organization.
On top of what you're saying, the alternative is frequently a heap of under specified, under tested (and only manually tested!), copy-pasted and shoddy quality Cobol/Java/Oracle/C#/SQLServer/PHP/MySQL code.
Yes, the performance will generally be better (1 minute to run a report, not 1 hour), but I'm not sure things will generally be better. As you said, the business people can at least modify the Excel. These systems? Not so much, without an usually massive support contract (Yaaaay! for us IT people, I guess).
People sometimes make a false equivalent and imagine that the Excel jungle will be replaced by a Google-developed, 90%+ unit tested, refactored and cleaned periodically bunch of code.
> the business people can at least modify the Excel
I'm not convinced that's a good thing. If they can change the Excel then they can also fuck up the system, and if you fuck up a system like this then it's horrendously difficult to unfuck. I think systems like this look great when they're working, but the second they go tits up you realise why people are horrified by them.
It's not so much being able to view or modify code that's the problem, but the fact that any modifications to an excel spreadsheet are inherently running in production. Open source has many benefits, but while open source projects may accept pull requests there is very few which put contributions straight into the latest build without review.
That's not the issue at all. The issue is that these systems invariably have no review process, no testing, and no versioning (at best they'll have each spreadsheet versioned with no real way to know what each version contains). And this makes them incredibly fragile.
And these user-developed systems are often great...until someone accidently deletes or changes some data and it becomes a mess.
Unfortunately, many businesses then find they have no way to know the extent of the data lost or who did it. And there’s no proper backups. Happens over and over thoughout the world.
Excel’s a great tool and there are lots of terrific uses for it. But what the article describes isn’t one of them. If we’re being honest, this isn’t the first time the issue’s been discussed and the companies likely aren’t actually switching away from excel, but rather supplementing it.
In fact, the article seems to be more a “hit job” / “marketing piece” for Anaplan Inc., Workiva Inc. and Adaptive Insights.
Make it easy to interoperate with databases and/or revision control painlessly for the end user and the developer. The users are fine with excel, it's the attendant issues like centralizing data, running automated services off of the data, backing it up, and keeping an audit trail that created pain when I worked in healthcare.
If excel online was better, a lot of that would be pretty doable.good points.
But..I do think that if we're thinking in terms of "users" and "developers" then we're no longer talking about excel. The whole reason excel is used this way is the continuous spectrum of sophistication.
Also because Excel is both an IDE for developing spreadsheets, and a GUI container for operating them. Splitting dev time and run time environments would help with control. When a dev supplies a user with a traditional .Net GUI app, they don't enable the user to hack the app by supplying a copy of VisualStudio alongside. Effectively, this is what Excel does.
It's no good if you fix excel by making it not excel. The whole reason excel is so popular is the intuitive, unintimidating but terribly wrong way it mixes data, code and GUI. Functions in the data, constants repeated in every row, hard coded variables all over the place. If you're just supplying a GUI and hiding the code, the user can't change, add or debug anymore. The point is that they can code, even if they don't know they are coding.
The trick is to think up a way of shipping VisualStudio, but making it less terrifying to engineers, managers and such.
>If the excel team was blessed with a plague of inspiration tomorrow morning, Is there a way they could make it so that excel is the right way to manage an insurance business?
No... but I think the converse might be possible. If those with the programming knowledge sought to replace the Excel system but in a way which actually preserved the useful features of the Excel solution. Dirty, filthy things that programmers don't like. No referential integrity, data duplication, deferring decisions to human beings rather than lists of rigid criteria, etc. If they could swallow their bile for just a bit, they could build a system just as useful and flexible and introduce tools to add in the things like data consistency and referential integrity - tools that the user would initiate, thus accepting responsibility for the restrictions such things impose on themselves and the data.
That is not, unfortunately, how it is usually done. (I am currently literally working on a program to replace a 40K row Excel sheet that crashes multiple times a day which has a workflow centered around it that involves slicing and dicing the sheet, passing it off to multiple different groups for separate editing, etc... not sure I will be able to sell the team on this approach, however I do believe it is sound.)
"If the excel team was blessed with a plague of inspiration tomorrow morning, Is there a way they could make it so that excel is the right way to manage an insurance business?"
What a professional software developer's workflow has and Excel lacks:
* principled way of sharing code across a team with distributed source control (or similarly powerful tool),
* automated regression tests,
* input validation,
* continuous deployment tools to automatically deploy working code (default mode for Excel I guess), and a mechanism to roll back to a previous version (not so much),
* build and dependency management tools for bringing in libraries from a vast open source ecosystem,
* systems for reporting bugs and feature requests and tracking their progress,
* networked database that is the shared "source of truth" for all users,
* security and auditing,
* hardware capacity planning (run locally or in AWS? what are the cost trade offs?)
So sure, get the health insurance people doing all of those things, and have Microsoft figure out how to do all of them well in Excel, and congratulations, they are now software developers. None of these require using a "real programming language", at least not directly.
That's great (though I'd quibble about a few of them, eg I was doing input validation in Excel 25 years ago, and boy do I feel old now), but while you're learning to do all that great stuff you're not learning anything about the domain you want to work in.
OK, I don't know who wants to work in health insurance but the basic point is Excel is a good tool for people who have specialist knowledge in a particular domain and want to automate their work. Programmers know the best way of programming but frequently overestimate their understanding of other people's problems.
You underestimate how difficult it is to work with at least some software companies, how expensive it is, how difficult negotiation it takes to convince quite a few nerds that user actually has business case for that requirement. And many software companies produce poor quality and are very slower then necessary despite using all buzzwords and all nighters. Managing even outsourced projects takes learning and expensive failures are nor an exception. All in all, the software we all collectively produce is still very expensive and unreliable.
Even absent that, it is difficult to explain what users need to coders who have zero idea about the domain. Unless it turns out they are the kind that just know user is idiot the moment she has different opinion on her own job, in which case expect needed time to tripple.
The homegrown excel is awesome analysis once the company decides to move on. It is concrete, you can generate test cases from it, it has features.
One other point is let's say you do create some fancy new system to handle the task. It's likely going to erode and be surpassed by new computing technology at which point you're looking at an expensive legacy migration which could prove to be costly and you have the same downsides you mentioned.
Excel upgrades don't seem to break previous files as severely and have the backing of Microsoft.
| I am saying that we should be curious at why they are doing it this way.
If you hand a caveman a machine gun, he will club you to death with it. IOW, the casual ability for domain experts to screw up a spreadsheet is enough of a reason to decide that excel is a terrible solution to this problem.
I would make a different caveman analogy. Hand a caveman a gun. He probably won't be a better rifleman than you (or someone). Really bad form and technique. But, he'll probably be a way better hunter than you, shoddy rifle technique or not.
Caveman were better than you at a lot of things. They knew how to find squirrels
The problem is switching would mean transitioning to a generalized system that they would have to work around. The Excel system looks like a nightmare to the HN crowd, but it was built around their business processes. It's basically a boutique app designed specifically for their job, it would be very hard to find a general product that does the job as well.
I've seen this a lot where a consultant will swoop in and tell them that they need to use Peoplesoft or something and the organization spends millions of dollars and years of retraining to get a system that has a lot more friction in their actual workflow and is never quite as good for getting the job done.
Of course there are often side benefits that can't be overlooked, like a much more sane backup solution, interoperability with other providers, etc... But in the end most of the employees will tell you straight up that the old system was better. Too much of the domain knowledge was baked into the old system and doesn't make the transition to the new system.
They should be careful though: excel allows a maximum of 1,000,000 rows. I have seen more than one major crisis occur when a spreadsheet ran out of space, yet all of it was needed together to do daily business.
1,048,576 rows to be precise. ;) Though you'll run into performance issues in more complex spreadsheets once you get into the 100,000 row area, or even 10,000 or so. Then you'll see power user departments switch to the more opaque but more performant Access.
They switch to Access until they hit the data size limit of 2GB. Then they come back to IT who has to deal with the mess that you get when you allow domain experts to build databases.
Domain experts are not the best people to build databases (says a domain expert who's failing miserably at that task right now), but database experts are sort of terrible at understanding domain problems.
It seems to me that many programmers don't appreciate that industry practices or regulatory requirements impose quirky requirements on domain specialists that everyone knows are far from optimal but which need to be complied with anyway if the business is to turn a profit or retain its licensure.
Someone mentioned a health insurance example above: there's 50 states, with 50 different sets of insurance regulations, and then some additional territories, federal regulations, and then wird business practices of healthcare providers who are subject to their own 57 varieties of regulation, and there are powerful economic and political interests that get in the way of burning it all down and replacing it with a single system designed by a committee of one patient, one doctor, one nurse, one pharmacist, one lawyer, and one economist.
And even that highly simplistic requirements committee went through 3 versions while I was writing it up.
The domain experts already have lots of complex knowledge and experience they need to automate. They don't want to learn about databases. Unfortunately, the reverse is equally true.
> It seems to me that many programmers don't appreciate that industry practices or regulatory requirements impose quirky requirements on domain specialists that everyone knows are far from optimal but which need to be complied with anyway if the business is to turn a profit or retain its licensure.
I think only those that turn into consulting understand it properly, because they aren't confined into a cubicle coding all day long without direct feedback how the real world actually looks like.
At most there is some feedback coming from ticket management system or the product manager.
I feel your pain but not everything built in Access becomes an unwieldy, klugey monstrosity. When Access solutions are born, their useful life, evolving reasons for use and expected storage requirements aren't always well-defined. It's only after users begin to embrace something that works, and request new functionality, that these things become clearer.
When faced with a decision b/w using a RAD tool to create something outside of IT, or spending $500,000 and waiting 6 months so real devs can deliver something Finance needed yesterday, that may not be needed tomorrow, the choice is an easy one.
Your generalization isn't entirely unfair; IT is often called on to fix a mess, but some Access solutions are built by people who understand database design/normalization and can put together a easily-maintainable application that never hits storage limits or requires upgrading to a SQL Server-managed back-end.
Corporate IT never lays eyes on many Access projects that aren't crap.
I worked at a major PBM and saw the same thing--stupid mess of Excel spreadsheets and Access databases. Major systems still on AS/400 with file drop off as the mechanism for communications instead of queues. Oh and one major system still running on a DEC Alpha OpenVMS system that required replacement hardware purchases on the gray market used equipment side.
PowerPoint, while popular, has no where near the widespread use of Excel. In most businesses, PowerPoint is the sort of thing you use once a month, once a week maybe if you are doing a lot of meetings. Excel gets used daily or even hourly by members of almost every department in businesses large and small.
I would agree with OP that Excel is the most-used programming environment of all time
You can do crazy shit in excel. After someone opens an excel sheet - download a webpage and save it to disk? no problem. Lets load other excel files in memory and run their scripts too! Don't worry about your computer fan - while we scan your disk for stuff. Just state "On Error Resume Next" and start scanning hardware. Company email accounts are stuffed with one off sketchy excel attachments.
The idea is that general Excel files do not run macros. They need to be saved with a special extension and you need to manually approve macros, with warnings if the file is in a location where other users can write to the file.
I have no idea how watertight this is though, it’s Microsoft quality (and some of the most ancient code they have) so I wouldn’t bet the farm on it.
I used to be a Lotus Notes specialist, and one interesting thing I noticed is that from the earliest versions it was designed specifically to be a "step up" from a spreadsheet (Lotus of course also made Lotus 1-2-3, which was the original killer-app spreadsheet before Excel took over).
Notes still let you see data as rows and columns and had very spreadsheet-like functions, many of which could be transferred directly from Lotus 1-2-3 - but it was a full client-server system which supported decentralized replication, built-in messaging and email, and a solid security model (yes yes I know it was also a crazy confused mess and a UI nightmare but that's a different topic).
Back in the day, organizations had teams going around finding the most useful ad-hoc spreadsheets and converting them into simple Lotus Notes databases. Other organizations would sometimes also give staff training in building simple Notes databases - not a whole lot more difficult than doing a spreadsheet - and then have expert teams come in and polish them up as needed. This made business software bottom-up rather than top-down, which turned out to be a whole lot more useful in a lot of cases.
In many ways, even after I left the Notes world for PHP and Rails and JS, my career in business software has still mostly been about converting spreadsheets into more "proper" applications.
Spreadsheets are the 'blue-green algae' of the software world: the simple base level that's everywhere and surprisingly essential.
FWIW we ended up in business software after one of our open source libraries to read and write spreadsheets (https://github.com/sheetjs/js-xlsx, demo http://oss.sheetjs.com/) ended up garnering lots of demand from businesses looking to build those "proper" applications
No. An domain-specific SaaS app takes all the power away from the people writing their spreadsheet programs. Only one app is needed, a hosted spreadsheet with backup and versioning an replication. The only domain-specific business is in writing plugins.
I agree that purpose-specific tools aren't the answer, unless the question was, "can I have a straight jacket?"
Flip side, my feeling is that, if an analysis was worth doing, it's probably worth doing right. And to do it right, it should probably be done in a way that one can show their work. I like where the R community in particular has gone in dropping the barrier to reproducible analysis to roughly the same place as Excel.
My friend's startup has a project that matches up with a lot of these requirements, called Coda. It's easy to query data and build visualizations, has a spreadsheet data model, and has an easier programming model. Excel did a great job getting people to "program" their spreadsheets though.
I also wonder about google sheets capabilities. It does work so well for many people working on one spreadsheet, except for undo. The major flaw I've seen is undo, because in google sheets it was global undo, not personal undo.
I would say a chef's knife is the ultimate kitchen multi-tool. Having one great knife you really know how to use for cutting everything is much more efficient than a bunch of different knives.
Of course the multitool is the single point of failure. If the chef isn't careful, that one great knife can be the vector that means ALL the customers get salmonella, not just the ones who ordered the chicken!
I wrote this in another thread, but I think the next move should be to create an Excel-compatible spreadsheet program with a few additional constraints inspired by programming. Make primary keys required, each column needs a type, etc. These are concepts I’ve successfully explained to non-technical coworkers to great effect. By enforcing them in a tool, I think you would get about 80% of the benefits of true software without all of the overhead that entails.
Airtable does something like this. I don’t know how excel-compatible it is, but the core of data types and something similar to primary keys are enforced in a pretty intuitive way, and basic formulas work fine. Haven’t done much advanced stuff with it.
This actually exists inside excel since 2010, it is called get & transform (known before as power query) and lives in the data tab. It allows the user to mash data from different sources (databases, csv, excel files, current workbook) and perform sql-like opérations : filter, join, etc. In a wisiwyg-record-macro fashion.
I find it very intuitive and the steps are easily reproducible as each transformation step is translated into a language derived from F#. It is a game-changer for business users that dare using it, as it is rigorous and precise like a classical SQL flavor, but also usable with zero training.
Since it was a very powerful tool but struggled getting traction, they are using it as the core query tool for power BI (alongside DAX for great dashboarding).
> Every spreadsheet shared in a business is an angel announcing another SaaS app still needs to be built.
It's a pithy quote, you'll get a very different reaction from a finance or small to med-sized business professional.
Excel is the ultimate maker studio that actually lives up to the promise of "build your own mini-app". I'm not saying it can't be improved on. It's just that the core model of flexibility is so compelling, pushing everyone into a SaaS app is invariably going to take away substantial power from the user.
Excel is an incredible swiss army knife when dealing with one person in one location on one computer, but the warts start showing up when any of those assumptions break.
One computer: once you decide you want to use your smartphone, for all but the most basic sheets there are problems with Excel mobile preview, even with Microsoft's official apps.
One location: Excel's lack of a universal time (showing different times in for users in different timezones) have led to gnarly workarounds. There was an attempt to correct for it in the XLSX format (cell type 'd') but it has its own problems.
One person: Excel has no inherent sync strategy. You are forced to farm out to email (and the inherent filename versioning nonsense) or use a system like SharePoint (which has its own problems you notice when dealing with people in different timezones across the world, like accidental file locking and data loss)
What's with all talk wrt Office 365 and web versions and syncing that goes on with that? Is none of that actually the case? I wouldn't be surprised, just don't know.
I hear you. I think One Computer and One Person issues have generally been solved by Office Online or Google Spreadsheets. I can't speak to difficulty around lack of universal time nor do I know how important of a requirement that is to most companies.
Any solution, SaaS or otherwise, will have flaws. It might solve Excel's weaknesses really well, but will likely lack its strengths.
Excel is still going strong in 2017 to a large degree because its strengths continue outweigh its weaknesses and that people have found reasonable ways, or add-on tools, to work around it's weaknesses.
It will be interesting to see in the coming decade if niche industry solutions or an even more incredible swiss army knife were to come around and dethrone the incumbent.
What I mean is that Excel is essentially a write-only language. It's very quick to prototype something for a semi-technical person, but auditing / code-reviewing an Excel sheet later is essentially impossible. The ubiquitous manual loop unrolling doesn't help readability either.
From my experience, this is more dependent on the team/process than Excel. I've worked in investments for several years and auditing Excel sheets was a core, well-run process at several companies I worked for. It was quite easy for smart, non-technical folks to follow formulas and equations to track down why their checks weren't passing.
A nice quote but getting data between SaaS apps is much harder than between Excel sheets, and modifying a SaaS app with that one little thing you need for your particular case is of course impossible.
Getting data between SaaS apps is not only easier, but formats like JSONAPI are far more powerful than pushing csv binaries that may or may not adhere to a spec.
> Every spreadsheet shared in a business is an angel announcing another SaaS app still needs to be built.
Really? I am working in company which is not handling super-sensitive data (i.e. not a Bank, for example) but I can assure you that if went around proposing to adopt a SAAS for what Excel is used internally I would not get very far.
No matter how good you are as a SAAS provider, our data stay in-house. End of story.
Many people on HN make the jump from "I would not buy that" to "There is not a market for that", but there is, in fact, a market for SaaSifying lots of things.
It's no value judgement if you want to continue using Excel for privacy, compliance, or security reasons. You do you. But SaaS companies get very good at developing privacy, compliance, and security stories, particularly as they move into enterprise. (Below enterprise it's less of a big deal, partially because customers care about them less and partially because small businesses are, as a judgement-free statement of engineering reality, abominably bad at data security.)
I agree with this. I work with a SaaS company that is also a use case for this scenario (www.Zenkit.com) and we haven't had too much push back on security. The cloud is so much more secure than it was a few years ago. On-premise is often less secure now than the cloud. If your company is still using Excel though, you probably wouldn't know that.
Yes - I agree, the point is that most of the time an Excel "app" is solving a very specific problem using data coming from some other (internally managed, usually legacy) system.
Like: I run a report, transform the result in .csv with some ultraedit macro, slurp it up in Excel and off I go.
(off I go can often being something like: upload the result to the same or a different legacy system, again in some custom format).
Maybe I can pester my own IT dept. to add an extra option to the report so that it spouts off a .csv directly. That's all, the rest still lives in Excel.
Then I am free to play with data as much as I like (take also in account that in some cases you want these in a Excel just to be able to manipulate them better, while the old app works record by record, you can make changes across a thousand records using the Excel interface... and keep also in mind that this is maybe something you need to do once or twice a year, when you renew your catalog prices or whatever).
Now, what is the cost of:
- going out on the market looking for a SAAS that can get my data exactly in the format I use internally, applies the required transformations and send the result back in a format I can use
- assess that the SAAS vendor is indeed trustable for my desired level of security, SOX compliance, etc.
- add one more vendor to my portfolio of vendors/licenses/purchasing orders
So the reason not to go to SAAS is actually a combination of security/external resource dependency/cost/bureacracy.
Where "cost" is mostly inertia+sunk cost fallacy. I am sure that a SAAS may cost less than the hours spent manually doing all the stuff I mentioned above (including mantaining the Excel spreadsheet) but the latter is a "hidden cost" because it something that happens infrequently and is part of the normal chores of whoever is using the Excel sheet itself.
While introducing a new SAAS app will be an IT cost (to identify/approve it, add a recurring subscription etc.)
True but there is a threshold. If the problem is small enough, you can't beat an Excel spreadsheet's flexibility and low startup cost.
Having worked with a number of finance teams, the biggest problem is not when Excel is used as an analytics tool but as a financial database. Excel as a source of truth for financial data is truly terrifying. Even financial audits are still often handled with custom data pulls exchanged over Excel spreadsheets.
Shameless plug: that's why we ended up developing one of those SaaS tools (http://modfin.io). It lets finance teams map their source data to a proper, easily auditable accounting ledger so that they don't need to do their "magic" in Excel.
I am a programmer and I use csv/Excel as the single-entry financial database for a small healthcare business ($500k revenue). The accountant is fine with it. Filtering and pivot tables are a joy in Excel, and I can still run Python scripts whenever I need (mainly to auto-categorize bank transactions). Most other businesses around here use QuickBooks, which has a proper system of accounts, but then I can't play with the data.
Apart from the generic question of the pros and cons of using Excel as a data store and how you solve issues around access control, concurrent access, backups, etc., financial databases have some additional requirements to be properly auditable. For example, one of the biggest differences is that the data should be immutable with only additions allowed. E.g. if you made an error and need to correct it (say you sent the wrong amount of money to someone or simply just had a bug), you should keep the record of the erroneous transaction and reverse it or book an adjustment.
You can try to institute the same controls over Excel, either directly in code or just implicitly in terms of how it's used, but they tend to break down pretty quickly with scale.
I'm guessing because of 1) lack of built in logging, 2) lack of change control and 2) difficult to automate testing and verification (because of it's relatively unstructured nature).
Excel as the source of truth for a financial system is particularly scary to me b/c it's so easy for someone/anyone to change entries or miscalculate so it's very difficult for me to get confidence in its completeness and correctness.
Completely agree about analytics and scripting, but I'd utilize the CSV/Excel reports as point-in-time analytics that can be tied back a structured source of truth (or a source of truth I can hold liable, e.g. a bank or credit card statement).
Logging: I just save a copy of the spreadsheet every time I make a tax filing. Do I need anything more fine-grained?
Validation: I'm confident that I can catch duplicated rows and mistyped amounts because Excel formulas verify that the sum of all transactions for each account equals the account balance, and that transfers between accounts add up to $0. Do I need anything more?
Point-in-time doesn't work because I need to keep track of info that doesn't show up in the bank statements. I run a Python script that adds pretty-prints names and adds categories, and I also manually enter check details, category adjustments, business purpose of meals and flight tickets, etc.
I don't use a proper database because to do so I'd have to give up Excel's nice features (or worse, switch back and forth between Excel and the database).
This doesn't scale well. If only one person edits the spreadsheet and that person knows how to save copies and make sure there is only one latest version it may work. If you give this to 10 people they will start making copies, saving those with different names ans save the copies on some server or on their own machines and after a while you don't have a single source of truth anymore. And you almost never have logging when many people edit copies of Excel documents.
That's true. Currently I handle scaling by automating nearly all the work. Most routine transactions are automatically categorized without manual input; I just skim the report. We modify 0-2 transactions/day, which is low enough for Dropbox to work well. This wouldn't work for a Fortune 500.
Oh I see so you are only reporting on cash receipts, so presumably there is an accounts receivable ledger to track the invoices raised with your cash received etc. Thanks for the reply
> Validation: I'm confident that I can catch duplicated rows and mistyped amounts because Excel formulas verify that the sum of all transactions for each account equals the account balance, and that transfers between accounts add up to $0. Do I need anything more?
I don't doubt that people have made mistakes involving spreadsheets. But these mistakes are not unique to spreadsheets. Switching to an enterprise system doesn't magically prevent data entry problems.[1] Similarly, you're as likely to implement an accounting function incorrectly in Excel as in SQL or Java.
I think that you need to show that Excel requires more data entry, or riskier data entry, or that Excel can't catch as many errors, etc. For now I'm not convinced.
Maybe I did not appreciate it properly, but I remember using Lotus Notes at my work a few years ago, and I can remember it being the most awful piece of software I have ever used. Admittingly I am not a specialist, more an employee trying to use it to do my job.
"You either die a hero, or you live long enough to see yourself become the villain."
In the mid-90's, I thought I was helping by bringing Notes in to the company I worked at, and people seemed to agree. A ticket tracking database we set up seemed to help. Many years later, after I left, I saw a presentation where Notes was cast as the problem, with a website proposed as the solution.
> I can remember it being the most awful piece of software I have ever used
Many people seem to say the same thing but the only problem I could ever see (in occasional use) was a non-standard UI which non-technical users had trouble grasping. What made it so awful?
Navigation, the UI, the way the databases where setup. I can remember that even basic things not working the way normal windows applications work (usability related things), but it was so long ago I can't remember the specific things, sorry. Just that it was awful.
Notes itself has some great functionality. The UI feels a little clunky - but generally you can do what you need to do.
The main problems I encounter is the way our IT department have deployed it. Many functions are not enabled or only partially working. As far as I am aware, we have no Lotus/Domino expert in our country; head office (other side of the world) has quite a few though. Any requests made through our regional helpdesk for fixes/improvements mostly go nowhere.
I like the idea of the replicated databases for email and other business functions. It should mean staff could switch to a different machine and quickly and painlessly get access to all their email and other data. However, there is a considerable amount of convoluted configuration required (the way it is currently configured) that appears to require local admin access that makes it impossible without helpdesk assistance.
I suspect many companies deployed Lotus/Domino is similar semi-functional ways that made it less useful than it could be.
The reason many companies migrated away from Lotus/Domino though was the risk of vendor lock in. My employer is stuck with a vast amount of business process captured in Lotus/Domino. They have been slowly migrating core functionality out to other web or networked database applications over the last 12 years. Even so, I don't see any move away from Lotus Notes for email for many years to come.
What would you use now if you needed the same sort of functionality? I find myself with some unexpected needs to build databases whose specifications and scope I'm still trying to figure out, and for now I'm missing about in Google sheets and figuring Fusion tables will probably be sufficient, but I'd be interested in any alternatives as long as they're secure and shareable.
As a Lotus specialist I can see why you'd think Lotus 1-2-3 was the original killer app spreadsheet and as someone who worked retail at the time selling UNIX and Microsoft machines I'd have to say we sold a ton of Lotus 1-2-3.
But we have to give Dan Bricklin and Bob Frankston their dues because Visicalc is the original killer app spreadsheet in the literal sense: people used to say they didn't know if Visicalc sold more Apples or if Apple sold more Visicalc. It truly was the program for which people bought the platform.
This is how I like to explain functional programming to developers today. When you say "Functional Programming", a lot of devs think "scary complicated thing". When you say "Basic excel spreadsheets" they go "anyone can do that!".
Excel is a very limited form of functional programming. It's sort of SIMD and it eschews side effects, but it has no first class functions which is extremely frustrated and -non-functional
Excel is simultaneously flexible enough to solve a wide array of problems and structured enough to enable larger tasks like slicing and filtering data. This article is trying to push for cloud-based silos that solve specific problems, but most of them fall apart in "real-world" scenarios because they aren't flexible enough to support the ad-hoc workflows people have developed over years.
The dystopia that I fear is an endless gauntlet of powerless employees droning 'the computer doesn't have a field for that' or 'the computer won't let me do that'. We already seem perilously close to this, putting the consistency of systems ostensibly built to serve the needs of society over and above the needs of society.
Completely agree. I think simply enforcing a version control would be sufficient to solve most Excel woes, however. It doesn't solve manual user error (a la Reinhart-Rogoff), but it's a good next step.
MS access is often a way better tool for what people do. The problem is that it requires a bit more work up front, a little more abstraction, and usually some programming. Having said that, most companies have people in IT that can help out something like that together for people but that's not their job.
In a database you don't "fill down". You build a query to generate what you want (enter the expressions once) and click on the "data sheet" view for the query. It's different, but the simplicity of referencing $A$5 in excel and using whatever cells you want as inputs is way simpler than any way you might take user inputs in Access.
I definitely agree that spreadsheets (of which Excel is just the latest) have drawn a lot of people into basic programming. But at the same time, it has stunted a lot of people's growth because of its sharp limitations.
Spreadsheets have a lot of similarities to the sort of code bases that as an industry we've been moving away from. E.g., no version control, no tests, opaque code, bugs are hard to find, write-only code, bit rot, random antique scripts used in critical spots, poor reusability, latest version is on somebody's hard drive somewhere, nobody knows which version is the right one.
I also think it's deeply unfortunate that there's no good path from spreadsheet master to real developer. As great as spreadsheets are, they're a cul de sac. I hope this new generation of tools fixes that. If not, somebody sure should.
I agree that mission critical data needs to get out of it and into a centralized system, but I still feel Excel is an incredible tool in any business.
You would have thought this would be obvious- it seems analogous to perl/python or other such Swiss army knife tools we use for quick one-off solutions that occasionally get pressed into permanent formal use where they shouldn't.
Python (and probably Perl too) work fine for permanent formal use as long as they are engineered as such. I have seen clear, well structured permanent code bases in python. The problem is just that people like coding one off prototypes in python, and those end up in prod; it's just as bad as when Java prototypes end up in prod, but who actually wants to write prototypes in Java, if given the choice?
Couldn't you argue the same thing about excel? It does not lend itself to well engineered permanent formal use, but you certainly could build a robust system with it.
The issue isn't that you can't program in excel the issue is that excel lacks many of the safeguards that have been built around programming in the last few years.
Version control for excel is terrible at best. Code review is basically non-existent and how could it be when logic is split over several cells and variables are named $A17 or B23.
I think the win with excel is its super simple interface, that said there is plenty of room to improve on the programming language interface.
Excel lets you name tables, cell ranges, or individual cells. I have seen spreadsheets put together by people who take the time to do this, and they are remarkably readable!
Way back before they added visual basic into it Excel had what was called a Macro language that was also built in sheets - just slightly different appearance (very wide cells) and altered functionality (to prevent you from doing numerical operations to your macro code). I think this was around version 4?
Anyway, it basically worked like a stack, with the currently selected cell being the instruction pointer. So when debugging you could either look at your data and watch blocks of numbers flashing in and out of existence or changing state, or look at the macro sheet and watch the pointer bounce around as it went to different branches or sat in loops or whatever. There were commands to turn screen updates on and off (which sped things up considerably) so as you got more and more of it working it would go from looking like some confusing nightmare to a smooth list of progress bars and passed sanity checks.
They moved to VBS because programmers were finding the cell model too restrictive/weird, and for integration with Access, but doing it all in cells was remarkably and pleasantly like assembly-level debugging. You'd hack something up quickly, figure out your algorithm, then name the ranges and use and index value, and you could lock ranges and what-all else. It wasn't as easy to read as a page of code (eg no whitespace) but the crudity of it pushed you towards that sort of 1-operation-per-line discipline that assembler demands and really forced you to think about semantic efficiency. I had macros consisting of thousands of cells of code with every single operation commented. You could, in turn, name the ranges that had macro code in them and do code profiling to find out where your performance bottlenecks were, and so on.
It's common to tell kids (and everyone) that they need to "learn to code." That is what Excel already is! And it's incredibly powerful, especially if you don't have any other coding skills.
But, of course, it's messy. When tens of millions of people can each write their own programs and easily modify them and change them to suit their own needs without needing to recompile or anything, then you get the mess which is Excel.
...and, in fact, that's also what you'd get if you taught everyone how to program Python in high school instead of Excel. You'd get tens of millions of non-interoperable messes of programs (which still get the job done!).
But that's fine. Give humans tools, and they'll find clever ways of using them that you don't intend. And it'll continue to drive people crazy. In some ways, the Excel hate (though obviously not unfounded) seems almost kind of like a bourgeoisie elitism. THIS is what it looks like when you give everyone the ability to code!
...and, in fact, that's also what you'd get if you taught everyone how to program Python in high school instead of Excel. You'd get tens of millions of non-interoperable messes of programs (which still get the job done!).
That's exactly what happened with Perl when everyone learned to use, modify, and develop CGI scripts in the early days of web-based application development. Dynamically-generated website content worked and evolved into the web we know today, but those scripts were awful and did a lot of harm to Perl's reputation.
I personally think that a lot of notoriety from Perl stemmed from insane overuse of regular expressions. I'm not talking simple and concise expressions doing ordinary useful things, but rather insanely long and intricate ones which one had to be at a god-expert-level on regex just to attempt to understand.
These kinds of expressions are what ultimately caused the derisive terming of Perl as "line noise". It was really unfair, to say the least, because there were plenty of ways to code in Perl and do so cleanly, with a minimal usage of regex.
Honestly, it seemed more like people were coding in regular expressions, and simply using Perl as a "run-time" wrapper for those expressions (and maybe those expressions existed first; I don't know). I'm not the greatest fan of regular expressions (to me - especially on more complex operations - it is almost like programming the tape of a Turing machine), but I do know they have their place in the world. However, because the "language" (if you will) of regex allows for an extreme number of operations and such, it almost succumbs to an "inner platform effect", allowing those with familiarity to roam free, to the detriment of future maintainers.
That said - none of this is new. If you go back to the beginning of microcomputing, you'll find the same thing happened with the various dialects of BASIC, as people self-taught themselves programming in that language, then spread the knowledge thru various means (magazines, books, and later bbs systems) - it became a horrible mess, even though it all pretty much "worked".
Regular expressions can get out of hand, but that's a developers (bad) choice. Perl lets you use multi-line regular expressions that ignore whitespace and allow end-of-line comments, which lets you properly document longer expressions if you need to use them.
Regular expressions are really powerful, and are so much better than the typical non-regex string operations most languages support. Combine that power with the ~= and qr operators, which make regexes as integral to the language syntax as == and "", and you get why Perl is so good at textual searching, parsing, and manipulation. It's easily what I miss most about Perl development since my career has moved to primarily C# and Javascript.
> I personally think that a lot of notoriety from Perl stemmed from insane overuse of regular expressions.
The problem was that Perl's performance on builtins(sorting and regular expressions in particular) was stunningly good while its performance on anything else was stunningly bad.
The idea that you would Decorate-Sort-Undecorate an enormous array because the overhead of passing a function to sort() was so much slower was ridiculous.
In the case of regexes, breaking apart the regex into readable chunks and processing the data multiple times was so much slower that everybody piled everything into the regex. Named captures didn't appear until 2007.
Passing a function into a sort() is going to be slow in any language, unless the language is able to inline the function. That depends on static analysis by the compiler, which is notoriously difficult in Perl.
The key, as you mentioned, is to use builtins, or more generally to use canonical expressions. There's always more than one way to write an expression, but Perl definitely has opinions about the correct way(s), and the Perl compiler does pattern-matching against the code to recognize many of the canonical expressions. They then get compiled to higher-performing IL code. This is why using things like the spaceship operator, <=>, will run much faster than equivalent verbose comparison code.
This is over-simplified, but I always thought about it this way: the Perl compiler generates "assembly code" for the "Perl CPU" to run. It can generate verbose assembly code using lots of low-level assembly commands, but it can also recognize commonly-used expressions and compile them to a single high-level assembly command, which can then run in a single "CPU cycle" instead of stepping through verbose code. Therefore, it was always worthwhile to learn how to write the commonly-used expressions in the common way, so that the compiler can recognize and optimize them.
I don't know if other languages have this kind of optimization. JIT compiling, as I understand it, does a quick and rough compile without optimizing on the first pass in order to start execution quickly, and then recompiles with more optimization on code blocks that are consuming a lot of runtime. I don't think any of the JIT compilers look for common expressions and apply pre-determined, hand-coded optimized function calls to them.
PHP always got heat for the same reasons (so much terrible, amateur advice replicating via StackOverflow et al.), and the only thing keeping Python from meeting the same fate is the fact that shoddy scripts written in Python generally don't get deployed to a public-facing platform for the world to abuse.
> ...and, in fact, that's also what you'd get if you taught everyone how to program Python in high school instead of Excel.
I disagree. Python leaves a readable instruction set, while Excel buries the logic in an opaque two dimensional grid of cells (plus or minus atrocious VBA). I think this gives a better ladder for people to improve their skills to the point of not producing unmaintainable garbage.
Not everyone structures programs in the same way, or puts functions in easily found locations, or names things usefully, or even approaches the problem in a way that makes sense to the majority of other people. For a concrete example, see this[1].
> Excel buries the logic in an opaque two dimensional grid of cells
It would be trivial to represent excel grid formulas as a set of predefined functions, or a special data structure that you register functions in, and then it could be in textual form. It wouldn't make it any easier to reason about, but then again control flow isn't always easy to reason about when a program is split into many different objects and functions, until you've internalized a fair amount of the program. Spreadsheets give people a very limited, but structured output and a very limited, but easily reasoned about way or processing. It's different than "normal" programming, but that difference allows for very intuitive use.
> I think this gives a better ladder for people to improve their skills to the point of not producing unmaintainable garbage.
How much work has been put into making spreadsheets easier to share with other people? I'm not sure the same culture has existed for spreadsheets as for programming, where people have needed to share and reason about complex algorithms and data structures. If it had, I'm sure we'd have much more in the way of how to structure your excel well so it can easily be consumed by others, or yourself in the future (if it doesn't already exist, which it may).
The grid of cells is your memory space. Coders like looking at a stack. Spreadsheet users like looking at the registers.
Which you prefer depends to some extent on which end of the problem you're coming from; if you're starting out with a lot of domain knowledge, a spreadsheet is attractive because you can look at the data, see whether the way it's being stored/processed is basically correct by spot checking it, and then automate. I'll still often reach for a spreadsheet if I don't know off the top of my head what the rules or relationships are and I need the equivalent of a whiteboard with logic.
If you have a top-down perspective and you want to automate a process whose fundamentals you know well, code is more comfortable. And you can go back and expand, modify, or adjust it later. But two things I don't like about code for handling large volumes of data are that I have to construct some sort of viewer to format the output (requiring more coding, or slower bug discovery if I press ahead without), and endless typing I like selecting things from menus and tool palettes because while I don't mind holding an instruction set in my head I don't like doing the same for every library I might want to use, especially remembering all the parameters. I hate hate hate writing glue code and want the computer to do that stuff for me.
Fortunately a lot of these problems seem to be receding thanks to tools like Jupyter and some of the promising Flow-based programming tools that let you easily visualize structure, code, and visualization without getting bogged down in details.
I should add that I prefer hacking to software engineering; that is, I'm only interested in programming as a means to an end of having a customizable tool for myself rather than building a product for other people, so I want spend as little of my time as possible on constructing the tool.
Craziest thing I ever found that someone did with excel was implementing a neural network using it. It was meant as a tutorial to teach how neural networks worked, and not meant for production systems, but I do wonder if anyone ever took it further.
Stands to reason that "everyone" is coding wrong. You wouldn't say people are programming just because natural language is turing complete, or because they are while physics is turing complete. One important aspect of information is structure, but programming classes at the low level merely teach syntax. So I'm thinking that therefore spreadsheets are unstructured. Sure that's elitist or at least academic - in comparison to amateurish.
have you tried to do scientific plots with Excel? I only tried libre calc, which admittedly might lack behind in a couple regards, but it's nigh impossible, no compare to scripting.
You could make the same argument about most programming languages. I hate to sound like I'm parroting Spolsky, but, he's right that the reason that the reason every developer ever wants to re-write the code they inherit is because it's easier to write code than it is to read it.
Excel is the Swiss Army Knife application - it isn't the best tool, but it's the most versatile and the one you have with you. Good insight about the relation to programming.
"The dynamic spreadsheet is a good example of such a tissuelike superobject. It is a simulation kit, and it provides a remarkable degree of direct leverage. Spreadsheets at their best combine the genres established in the 1970s (objects, windows, what-you-see-is-what-you-get editing and goal-seeking retrieval) into a “better old thing” that is likely to be one of the “almost new things” for the mainstream designs of the next few years.
"A spreadsheet is an aggregate of concurrently active objects, usually organized into a rectangular array of cells similar to the paper spreadsheet used by an accountant. […] A spreadsheet is a simulated pocket universe that continuously maintains its fabric; it is a kit for a surprising range of applications.”
Excel totally totally missed the wonderful opportunity to do so much better than Google Spreadsheets. They could have created a centralized shared way of creating, editing, sharing and storing data. Their limitations as it exists today are ludicrous at best - 10 max users? Give companies a way to keep the data in a centrally managed place with access control and auditing and users the ability to freely share their creations (and data)...
I think that excel is one of the best applications on the market.
Every single department in any company can benefit from knowing it well.
The problem is getting people comfortable and competent to know it well.
This is where I think google sheets fucking failed: they should have made an effort to make sure every single gmail user had a tutorial to know how to empower their curiosities and interests via google sheets.
It can be a really powerful one I agree. It's simple to use, a lot of people knows how to handle it. And it's probably one of the best tools to do some quick data analysis.
However, like every tool, it has its pros & cons. One of the main cons, IMHO, is that data tends to be mixed with processing a lot (but it also simplify usage, so...).
Overall spreadsheets have been really helpful. Retrospectively, there is a good reason why it triggered the personal computers boom in the 80ies. They are good adhoc tools that can help lot. And Excel is the dominant one.
However, there are tons of horror stories around Excel, worst case scenario, it has lead to wrong decisions affecting the world economy.
I've personally a few horror stories (fortunately, not affecting the world economy).
At my last job, we used an internal spreadsheet to evaluate costs and margins when answering contract bids. This spreadsheet was a monstrosity taking ages to compute and it was a nightmare to manage, sync, and share between all the participants.
I've also seen the traditional "on what did you work last week" spreadsheet, quite annoying to update, even more given that my workstation is under Linux...
I've also seen spreadsheets as specification documents, generating an xml file, which in turn, through some xslt, was used to generate C++/C++ headers files describing an interface between two sub-systems. The concept (automating spec to code translation) was really interesting. However, I don't know about you, but I personally hate to have MS Office in the tool chain used to compile a piece of software, specially when it targets Linux and only Linux...
But those are not the most terrifying spreadsheets I've seen. The most terrifying on was a giant spreadsheet basically managing every aspects of a huge project (thousands of piece of equipment, hundreds of sites). This document was so big it took several hours just to open it. It was like a giant CMDB of all the configuration items, and, trust me there were a lot. But it also did more, a lot more. It was generating configuration files for applications, background images for monitoring maps, firewall rules... It was also a tool that helped architect and size the number of equipment required for a given site. I probably don't know the full extent of its functionalities. Just to give you an idea of its complexity, at one point, even the number of cells in a whole excel document was reached (with Office 2003 however).
All the auto-generated stuff were always kind of wrong and had to be fixed by hand, which made for a repetitive, annoying and error prone process. Also, there was no clear naming convention, and it was a mess to take the generated stuff and load it in our automation scripts.
It was, de-facto, a huge piece of software with no versioning, tests or QA and it showed.
There was only one guy behind it, basically managing the beast full time. When he was sick or on PTO, the whole project was impacted. And when he left, it was a huge mess, nobody was able to take over.
Spreadsheets are great, but past an adhoc usage, please, please, please, implement something more serious. And also, keep track of Excel usage to avoid these kinds of monsters. Often there are real needs behind these beasts, but Excel is a really horrible choice in the long run.
Many of the larger businesses I have seen(not too many) have a disconnect between the subject matter experts that use Excel to codify their knowledge and the developers. The program(Excel workbook) often isn't big enough to warrant the dev costs when it works "now" but is too important not to in the long run.
I do think this is a tooling issue that could generate code based on Excel workbooks.
> I would argue that the most commonly used programming language is Excel.
I wouldn't say Excel is a programming language - unless you are referring to VBA attached to excel. Excel is a spreadsheet application. It's no more a programming language than Microsoft Word is a programming language.
> I would argue that the most commonly used programming language is Excel.
Anytime another programmer asks me what language they should learn I tell them Excel and they think I'm joking. Besides being a quick and dirty way to solve a variety of problems it helps you speak in the language of your users (if you work in B2B)
M$ft XL developer here. I attended a conference to meet our Excel guru users and it's fairly impressive what these non-coders achieve.
Excel is like an operating system to them, and they even create Excel files to "debug" there other Excel files, e.g. debugging the order of formula calculation and validating the output at each step in their property assets spreadsheets - without using any VBA
Honestly the article stinks like an advertorial of cloud vendors trying to push their wares. I agree that Excel isn't a good fit for many things, but blanket rules like 'no excel' seem counterproductive.
Why do we readers take these sorts of things at face value? You know the whole article is a sales pitch.
I suppose you either understand how PR works and simply disregard anything printed or broadcast and do your own research, and accept this system unlikely to change...
Or you get irritated by it, while waiting for the "inevitable" collapse of this promotional/propaganda model...
Honestly I don't know what is happening, but it seemed like the first option, which I've followed for a couple of decades is a good enough cure for disillusionment and depression. (Caused by investing in the second option)
Every excel spreadsheet is an error waiting to happen.
Implicit & unexplained business rules, no tests, no version control, unlabeled cell references, hardwired variables, everything is global, no encapsulation, no logs and no documentation.
Even assembly code is easier read and maintain than a complicated excel workbook.
> few of the people using it realize they're programming
An idea I've been toying with for a few months now is an automation app (read: window manager) whose UI is somewhere between Excel and GameMaker Studio's drag & drop UI. Like where you just have a giant grid/canvas, and where you can define "routines" and "variables" as groups of cells. If I could figure it out, I think it would open up the idea of an automation app to a broader scope, to people who aren't programmers, and I might be able to make some money off it. Especially considering I made like $500 in donations for Hydra back when I was doing more WM stuff for Mac, so there seems to be some excitement for the idea of programming your Mac, but perhaps it could be a living if I can figure out how to reach out to power users who aren't programmers. And it was your comment that gave me the idea of making it more like a grid instead of more like GameMaker. So, thanks!
I agree - it's also really useful as a prototyping language, since its accessibility allows domain experts to really make something that suits their workflow. My current company (Parsley, www.parsleycooks.com) would have been a lot harder to do without looking at the Excel sheets that the more tech-savvy and organized chefs use for logistics work.
In the book "A Small Matter of Programming"[0] (1993), Bonnie A. Nardi makes the case that there are really only two successful end user programming systems - spreadsheets and CAD.
I think that's just as true today, at least as far as business software goes.
UDFs are the real LPT. Much of gnarly spreadsheeting going around has to do with having to simulate loops with rows or just partition very large formulas (think option pricing formulas) into partial result cells.
he authors found that Microsoft Excel would often interpret “SEPT2”, which corresponds to the gene Septin 2, as “September 2nd”. The programme also tended to mistake identification codes like “2310009E13” for numbers in scientific notation—in this particular instance, the code would be read as 2.310009 times 101
Excel is fine for the home user. But the implicit conversions of input data can play havoc with any complex analysis.
Plus, it's limited precision can cause errors such as subtractive cancellation, etc. In order to correctly calculate complex formulae, the calculations must be done with an understanding of the limitations of the computers.
e.g. you don't calculate (a^2-b^2) for large 'a' and 'b'. Instead, you calculate (a+b)*(a-b). That has the same mathematical result, but is not affected by subtractive cancellation.
Preferences have become a pretty natural part of using a computer, I'm not sure why we can't rely on users trying that when they run into problems.
I would be worried about endless forking of defaults as a feature of newly built tools, leading to a bunch of new tools that will be harder to maintain.
If they can't manage implicit data casting preferences (if those exist, they may not), surely handing them something more complicated like R, MATLAB, or SQL is even worse.
This “change the settings” argument comes up from the Excelistas every time valid criticism is leveled at the tool. Can you point to this setting? No? I didn’t think so. That’s because it doesn’t exist. You’re arguing a hypothetical from a position of ignorance.
Interesting little article. I just read about Jeff Bezos initiative to push for all departments at Amazon to be accessible as service interfaces. Could there be real interdisciplinary growth by having academic fields concepts and data accessible in the same way? Maybe starting with papers. But then if every model developed was some kind of living running service in the cloud or hosted on .edu you could query or interact with
Not really. With care, you can usually get the format right temporarily, but it often changes for non-obvious reasons and there's no way to see that the format is wrong until it mangles your data.
In general, one of the worst things about Excel is there's no obvious way to lock anything to keep it from getting changed by accident.
This is what screwed me over as a herbarium curator (a herbarium is a type of natural history museum for plants):
Excel completely corrupts dates before the year 1900. It just boggles the mind -- if your spreadsheet has dates for 10,000 years from now that works fine, but if there are any dates from the 19th century or earlier, it can cause big problems.
> Excel shouldn't be used for much other than trivial things
Good luck with that, but it ain't gonna happen anytime soon.
Many "non-trivial" applications got their start at someone's desk in the form of a shitty excel spreadsheet with horrific macros. Is it optimal for "complex analysis"? No, but it doesn't matter because most things are computationally simple. The hard part is the intricate business logic and the timeliness of getting results and these things are not "trivial" at all.
Instead of complaining about how terrible excel is, the community here should be providing alternatives. And no, engaging a software team/consultant for a million dollars to develop bespoke applications or interfaces to enterprise systems for every little project isn't a viable solution for people who need to get stuff done pronto.
I agree with that. Modern software development has version control, unit tests, package managers, etc. Ideally we get support for some of those concepts in something that feels like a spreadsheet to the end user. (I'm not sure if it could ever be retrofitted to Excel)
As others have said, you can get version control with Excel by saving the file the SharePoint.
Some huge financial spreadsheets have sanity checks computations. With conditional coloring (turn this cell red if the sanity check fails) these can feel a lot like unit tests. However, these are mostly for business logic - so it's hard to see how a vendor could provide them.
In other words, it's not that excel doesn't have version control and unit tests - it's that the people who wrote excel spreadsheet sheets don't understand version control or unit tests.
> Instead of complaining about how terrible excel is, the community here should be providing alternatives.
Yeah... like I'm going to compete with MS by writing my own Excel replacement. That just isn't realistic.
What would be realistic is for MS to acknowledge that a large proportion of their customers use Excel for a particular purpose. And then tailor their software to the needs of their customer base.
But why would MS care? The researchers already bought Excel. So why "fix" it?
On top of that, no one is aware the the published papers are crap.
The real solution would be for journals to deny publication of papers based on shitty Excel analysis.
The phrases "avoid data silos" and "we want single source of truth" have been used repeatedly about Excel misuse/abuse/overuse for 20+ years. The WSJ story talks about the complaint but not about the underlying tension between data redundancy vs data manipulation.
Excel endures because it's the non-programmer's REPL for data. Like a Lisp REPL, using Excel is nimble and has an instant feedback loop. Click "A-Z" to sort ascending, click "Z-A" to sort descending. Move/hide/freeze columns. If one has intermediate-to-advanced skills, one can pivot the data on multiple axis. None of the centralized systems with even the slickest web GUIs can match Excel's capabilities.
That's why the centralized systems like SAP ERP, Microsoft Team Foundation, mainframe sytems, etc offer functionality to export to Excel spreadsheets. (Or they offer export to csv files -- which is 1 step removed from Excel.) With MS TFS, you can even re-import from Excel so in that case, Excel acts as a sort of offline data-entry client for the project management tool.
One middle ground between Excel data silos and the utopia of a centralized system is a shared document store like Sharepoint or Dropbox. The hierarchy would be something like:
1) Excel spreadsheets emailed around. Lots of redundancy; uncertainty of which version is canonical.
2) Excel xls file stored on Sharepoint with automatic version control. Employees must have discipline to only email links to the "one true" spreadsheet and never attach the actual xls file in the emails.
3) The data that was in Excel is migrated to centralized system and all data entry and reporting is done there. However, because the central systems are so cumbersome, this abstraction "leaks" back out to Excel because of the highly desired "export to Excel" functions! (This restarts the dreaded Excel "data silos" complaint cycle all over again.)
There isn't an IT department in the world that can develop centralized IT solutions fast enough to keep up with the ways employees slice & dice data. That's why Excel is "overused".
Can you easily integrate with externals systems via REST & SOAP? I've done some quick web searches on this topic and have never seen anything that jumped out at me.
I know Google Sheets gives you a little bit of this with Javascript embedding but I was searching for something more tightly integrated with Excel or LibreOffice.
>easily use Excel as a client to a central database and get the best of both worlds.
Yes it seems that way but many spreadsheets exist because the centralized system is an incomplete solution.
For example, a company might have a "human resources tracking" centralized system. (E.g. Ariba, Peoplesoft, etc). It has some standard features like ingesting resumes and extracting "skills" from parsing MS Word docs, etc.
However, the company happens to use a interviewing process where the candidate must pass (1) phone interview and (2) onsite with 5 peer programmers, and (3) hiring manager.
The hiring manager wants to "capture" all of those states (phone_passed=True/False, peer interviews with 5 different numerical scores, manager_pass=True/False). The centralized system doesn't have fields that capture all that so the hiring manager creates a spreadsheet that has those extra columns. Many spreadsheets out in the wild "fill the gap" between incomplete centralized systems and custom IT programming. An adhoc xls file can be created far faster than submitting a change request to the corporate IT department to customize Ariba -- which could take months (or never) to implement.
Or spreadsheets act as the "intersection" of data between multiple systems. Again, one purpose of centralized "data warehouses" was to eliminate Excel "data silos" but again, no IT department can create data warehouse solutions (which also includes all the custom reports) as fast as someone creating spreadsheets and just emailing them around.
Having a hybrid process where Excel acts as a round-trip offline client to a central db doesn't really eliminate the fundamental reason Excel (over)use keeps multiplying like rabbits.
This also illustrates another problem: all the programmers here are assuming perfect domain knowledge even exists in the first place.
When it doesn't, Excel will work fine (and will only cause disaster in individual cases). A "proper" unit-tested solution will either not work at all or just won't support half the use cases.
And without someone specifying the domain knowledge, there's nothing any programmer, no matter how well-versed in unit testing, agile, or ... can do about it.
Actually you run into the one of Excel's biggest problems when you do this, namely "Multiple versions of the truth". Here's how that happens: The central database often contains bad data. Users "fix" the bad data in their spreadsheet. Excel doesn't have the ability to write the updated data back to the database. Now everyone has their own versions of "the truth". There are products that attempt to solve this problem by adding the ability to write modified data back to the database. See for example Pebble Reports, http://pebblereports.com/features/ but this is a reporting program and does not have spreadsheet capabilities.
My first question was going to follow the poster above, and you answered it for me. I didn't realize Excel couldn't write back to the database. It shouldn't be too hard to develop an extension for that purpose, would it?
Aside from that — what about MS cloud services? I mean, maybe that's not appropriate for healthcare or certain businesses, but for others it allows there to be a single source of truth if the workflow is correctly tuned, no? I can produce a doc in my local instace, push to 365, edit remotely on the web, have somebody else edit remotely, and sync it back to my local instance without a hitch.
I use Office365 for that purpose regularly at my job. I'm a developer, but I have regular communication with editors, producers, project managers, division managers, operations managers, art directors, and so on. A lot of the information we share is tabled and needs to stay updated, and editable by each party involved. It's worked well for me in that regard.
"I didn't realize Excel couldn't write back to the database. It shouldn't be too hard to develop an extension for that purpose, would it?"
Well, more accurately, it can write back to the database, but normal usage of Excel won't afford that result. Normal usage of Excel you have an independent value.
It is also a dubious proposition as to whether we want people to actually write back to the central database; you'd be one stray macro away from disaster. You sorta want it if someone manually corrects something, but not if they operate on it programmatically, and now we're making things really complicated.
Hmm - and now that I've started digging into it, I see that this company has radically changed from what I knew of it. I had known it as a company which created software for designing and running reports from various data sources (whatever could feed it, honestly - excel, odbc, csv files, etc), which internally could be configured for more advanced usage using a language that had similarities to Visual Basic (it was OO long before VB, and geared for the reporting system, not general purpose usage). Before I left my employer who used it, they had just announced a Java-based system of "business objects" and some kind of tool to interact with data in an Excel-like manner (the tool essentially had all of the functionality of Excel at the time - including pivot tables), where changes would propagate thru the system. Plus interaction could only be done thru those objects, which were defined as "views" into the dataset.
So - after research this (just now!) - imagine my surprise at finding that this company now is named:
Apparently this started in 2004 (not long after I left my employer - and that it is Java-based also fits).
I'm not sure what is what with the company any longer? If I had to guess (probably wrongly), they spun off their Actuate reporting stuff and the other tools as open source (?), and are now concentrating on other things, possibly in the same vein?
> There isn't an IT department in the world that can develop centralized IT solutions fast enough to keep up with the ways employees slice & dice data. That's why Excel is "overused".
It wasn't very long ago that I realized that my two decades in IT could be summed up as: making proper applications out of Excel workbooks with feeble macros. I hate Microsoft as much as the next Linux zealot, but dang if they didn't create a world in which I've made a career. Where I'm at now, I'm drowning in opportunities to ameliorate horrific Excel-based workflows, and I'm fairly confident that there's enough work out there to last me until another 20 years to retirement.
>making proper applications out of Excel workbooks
Yes, exactly.
If we're being uncharitable, we can spin Greenspun's 10th rule[1] of programming as:
>"Any sufficiently complicated Excel spreadsheet contains an ad-hoc, informally-specified, bug-ridden, slow implementation of a proper centralized database."
Or, if we're being charitable, we frame it as an internal MVP (Minimum Viable Product):
>"Spreadsheets are the internal 'mvp' that proves the business value before you build the centralized systems. When the spreadsheet becomes unmaintable spaghetti formulas and the xls email workflow crushes under its own weight, that will give the company the evidence and the confidence to spend $1 million and migrate the spreadsheet to a proper centralized database."
Migrating too early before a business flow is properly understood could lead to that reduced cost but also a product that doesn't quite fit the actual needs.
I’ve just done exactly this: replace an old xls file which was sent each month by email with a simple web based application. The app includes some nice dataviz.
But then the people keep asking “how can I export to excel”. One user was copying an html table split in 20 pages one by one to excel.
So I found a elegant and simple solution: an excel spreadsheet linked to the PostgreSQL db of the app via ODBC.
Now users can just click on Refresh All in excel and get the latest data. I’ve also added an “click here to open” link in excel which opens the form with the row’s id in the web app.
Now everyone is happy: the data is kept centralized and available to everyone in real time. And users can make all the pivot tables and pivot charts that they want natively in excel.
Do not underestimate the power of ODBC with excel!
(may not work for big data though, but most business app do not contain more than a few thousand rows)
> There isn't an IT department in the world that can develop centralized IT solutions fast enough to keep up with the ways employees slice & dice data.
That's why the principal way you sell centralized systems to users isn't in the ease of manipulating results but in the ease / automation of data entry. The whole pain point and problem with Excel is in manual data entry, and having a single source of truth, even if you succeed in enforcing its property as a single source of truth, does not automatically solve disagreements between different human sources of data.
That's why tools like Grafana have interesting futures for business users, because they enforce a clear separation between data sources and data visualization, while offering a lot of control to the end user over statistical manipulations and visualizations that they can create, and keeping those visualizations updated in real time, so that they don't have to be re-prepared every time period for some new report.
Google Sheets is great and makes sense for a lot of vanilla use cases, but even in its current form, doesn't hold a candle to Excel running on a Windows machine when it comes to doing anything even slightly fancy or non-trivial.
Google Sheets and other similar web apps replicate most if not all of the Excel features you just listed, and it's centralized and more than one person can work on it simultaneously.
"One middle ground between Excel data silos and the utopia of a centralized system is a shared document store like Sharepoint or Dropbox." ... 100% agree, and add I find that Google Sheet is the best for this role: 1 source of truth, modification history, read/write permissions and shareable. I am now starting to use spreadsheets for use cases I would have strongly objected in the past. I'm surprised Google sheet isn't more mainstream.
For qualitative data management, but numerical analysis, 1 source of truth collaboratively - I use Atlassian Jira with great success.
Got a complex work stream?
Got thousand small problems that needs to be slowly figured out over 50 different main topics?
Got a team of 2,50 or 1000? No problem.
1. Just set up a bunch of jira entries while tagging them with the same labels
2. set up filters on these labels.
3. Set up dashboard(s) that give a complete realtime/update to any inquiring user.
Need to report status?
No problem, screenshot of the
Dashboard and done.
> The phrases "avoid data silos" and "we want single source of truth" have been used repeatedly about Excel misuse/abuse/overuse for 20+ years.
Yep. Even if they get centralize data via RDBMs like SQL Server or even god forbid Microsoft Access, office workers are going to want the front-end of that data to be excel.
There is too much knowledge investment in excel for many office workers to want to switch.
Excel endures because it's the non-programmer's REPL for data. Like a Lisp REPL, using Excel is nimble and has an instant feedback loop. Click "A-Z" to sort ascending, click "Z-A" to sort descending. Move/hide/freeze columns. If one has intermediate-to-advanced skills, one can pivot the data on multiple axis. None of the centralized systems with even the slickest web GUIs can match Excel's capabilities.
What about Business Intelligence products such as Hyperion? They can provide reports from a defined data model which gets data directly from the database. People can create their pivots and other transformations on the data fairly easily. Is their something that Excel offers that makes it much better than one of these Business Intelligence products in certain situations?
Excel offers a few things in most scenarios, primarily because that any enforced structure always implies lost flexibility, and one of the issues often quoted with Excel is the lack of enforcement of using the right structures and data sources.
A simple one: If I have been asked to do a report, but it turns out I don't have the correct access rights to some data, my manager can simply send it to me in an Excel sheet, or he can log an issue with IT to change the acess rights to a specific key/dimension/hierarchy.
The 'one true data source' move often correspond to tighter access controls when good auditing really would have been the correct solution, hence the above situation is fairly common.
"Stop using paper", says chief of a company that treats modified copies as if they were originals, "if we all carved our work on to this big stone in the middle of the courtyard then we'd be rid of our paper problems".
The CFO of a company tells the staff of the company to stop using Excel; the CFOs of several companies tell the staffs of those companies to stop using Excel.
I thought the spreadsheet revolutionized accounting in the 80’s, not specifically Excel.
My dad brought home an IBM PC in 82, using Multiplan for chemical mixing calculations. He thought it was the cat’s meow. Though Multiplan was the predecessor to Excel, the spreadsheet had been out since late 70’s.
I'm on the market for another startup idea and one of the most ubiquitous advice I'm hearing nowadays is that "there's a startup in every Excel sheet".
From that perspective WSJ is doing a great job in making the bed for these ideas: startups will eventually want to sell to those "Finance Chiefs" which is never an easy job.
Can anybody think of other examples of products or tools that fall into the category of: “many people use them, even though management tells them not to, because it helps them get stuff done.”
I’m not a mechanic, but is WD40 an example? Or maybe some tool like a hand held circular saw, in home construction?
Trello. In the last few jobs I've had there's almost always been a developer who has their own personal to do list on a trello board, even if we had robust tracking systems like jira and rally.
No, but in smaller groups, it's been a tool to get things organized for teams, then big ERP rollouts happen and managers start saying "stop using trello, use this instead".
Don't blame Excel if your processes suck. Excel is a tool, namely a Swiss army knife to edit and present data that is commonly used in business. It can be very powerful if you know what you are doing.
Swiss army knife is a good analogy. Sure, it does lots of things, but if you want to do really big work, you'll probably want a chef's knife, or a saw, or a pair of scissors, not miniature versions of each designed to fit in a small package.
That said, it's a great tool to survive in the woods :)
This article appears to be about how senior leadership wants to pay people that are not financial analysts to create reports that can then somehow be used by financial analysts (probably after exporting to CSV so they can work in Excel?)
This sounds great if you know exactly what reports you need and have the resources to build them, but I'm not really sure how this kind of initiative means "stop using excel". This article is advocating for a solid API / data management / data catalog program from what I'm reading.
I think all the features he listed as reasons to switch away from excel are in excel. IMHO the issue there may be more training, process and good data management.
I've worked with customers to setup support units specifically for Excel, helping their organisations develop and validate Excel spreadsheets and consider migration to more robust solutions when the time is right. As much as I dislike the general use of Excel there is an inescapable fact, Excel is a great source of bottom up innovation and ideas from non programmers which we should endeavour to capture and not just forbid.
> “I don’t want financial planning people spending their time importing and exporting and manipulating data, I want them to focus on what is the data telling us,” Mr. Garrett said
This!
I know this is reality in so many companies that don't have technically educated staff to tell them about more suitable ways to do it. So the teams get data from one department or system, manually transform data (in the editor via search/replace), and if they are "very sophisticated" they save it in MS Access, join tables, connect the dots, export back to Excel, analyse the data via Pivot Tables etc. And they do these steps repeatedly, every week. Without stopping and asking if there might be better solutions out there. (In German IT curriculums they call this "Medienbruch" = "media discontinuity"?)
I have seen this in so many companies and thought to myself: This is an excellent case for a consulting pitch. And I could literally transform their work.
And I am not talking about selling data warehouse solutions with ETL processes and the like.
Twenty years ago I realized I could do all these preprocessing and transformations with Perl. Now I do these kind of things with Python and Pandas etc. So there are so many solutions that are a huge improvement. Still these departments do it with Excel/VBA/Access.
"Executive 1 likes to see the data presented in formats A and B, Executive 2 likes to see it presented in formats C and D, and occasionally E, and Executive 3 will only look at it if you format it as F and cut/paste it to PowerPoint."
So you end up with 20 analysts who all have input, each manually re-formatting their own data in 6 different ways, making sure they all stay in sync. Then someone else collates it all together into 3 different exec-specific presentations, and double checks to make sure they tell approximately the same story. Welcome to life as an office drone!
I used to work as a corporate financial planning analyst and this hit way too close to home.
Then one executive who didn’t like the story would have one of his analysts pull the same data in a slightly different way and adjust it. The executive would then show it to the CFO and tell him that’s the real story. The CFO would turn to you and ask why his number is different than yours. Then you would spend hours with the analyst reconciling the data and building a waterfall chart to send to the CFO to explain the difference.
Then if you’re really unlucky the CFO says he wants to see the data both ways with a waterfall reconciliation every month.
Do you have any suggestions on where to get started with Pandas? I already know Python, but every time I try to get into Pandas I have a really hard time getting any traction. I'm particularly interested in using it to migrate Excel workflows.
I have seen many of these companies and potential "build a real database-backed web app" projects during my software consulting career.
This seems like a good idea at first: automate manual processes, have a real database with one source of truth and consistency rules, multiple people can access it at the same time, etc. You can do it if the process is very well known and fixed.
But most of the time a web app would cause more hassle for the companies than having a team of people doing the work "by hand" in Excel would be.
* The process and desired data schema might change every few months - or even weekly when doing something like marketing reports.
* Now they need regular maintenance - keeping servers up, doing and testing backups, applying security patches.
* But the problem being solved is not big enough to justify hiring a full-time team of software developers to maintain it forever.
434 comments
[ 2.5 ms ] story [ 275 ms ] thread[1] - www.paulgraham.com/submarine.html
Points for their SEO/clickbait headline: it hooked me... this time (-1 one added to my personal WSJ lifetime value calculation)
Then I believe it should say stop abusing Excel.
Excel can be an incredibly useful tool, but it's definitely not universally useful. If you're using it to manually collate data from 400+ sources, you're doing it wrong.
And the premise that things have changed enough to warrant reevaluating worn out business processes like Mr. Bell's here is true enough.
But dumping a tool because some out of touch C-level tool says so is a recipe for huge traction loss.
I mean ... people that use nail-guns for a living still have to use hammers.
> A year ago, Mr. Bell’s team spent hours distributing hundreds of Excel spreadsheets to regional and unit leaders each month for planning and performance tracking of the company’s 415 U.S. restaurants, he said. Now the same process takes minutes.
Reads to me like, "last year Mr. Bell's team spent hours hammering in nails by smacking them with the wooden part of the hammer's handle"
Edit: I just noticed that I'm the third person to reply to you and we all referenced hammers.
edit: my favorite thing about using excel is that instead of spitting out an answer, you can spit out an Excel sheet that shows a lot of the work itself, therefore partially explaining the answer to the people that have to consume it. The product of your program can often be another program, giving the person receiving the report the opportunity to tweak or add new parameters to your output. Basically currying accounting calculations.
Many moons ago large bank I worked at purchased a company and soon it became apparent that their entire business ran on a Lotus 1-2-3 spreadsheet with 30,000 lines of really dense, uncommented, unindented, spaghetti LotusScript code spread over 2 or 3 functions. The kicker is it would only run in the 16-bit edition of the product for whatever reason. So we had to have a VM to run it in because Windows 2000 had a problem with either Lotus 1-2-3 16 bit edition or the spreadsheet itself.
So yeah, stop abusing Excel - Lotus 1-2-3 in this case.
Maybe I'm a bit bias having used Excel my entire career and very dependent on it, but everytime I've seen companies steer away from the desktop application with 0365/sheets/tableau its simply not as useful for adhoc data analysis and reporting.
This is one of those solved problems that companies inexplicably keep working to solve again.
I mean, this is the problem that distributed version control was designed to solve. Emailing a spreadsheet around and calling one a source of truth doesn’t solve the simultaneous edits problem.
I happen to use and advocate for R/RStudio for ad-hoc work (I spin it as "excel on steroids" when I am trying to convince someone). But it is hard to pull people away from something that has worked for them.
The alternatives to excel in the past have been really, really unpleasant to use. A now EOL product from Oracle comes to mind. It is called "Discoverer"-- that's right, "Discover" + "er". It is a turgid POS requiring extreme suffering and handholding/permission granting from your local friendly DBA-- all this to produce 90's era bar charts that are all but impossible to tweak.
The presentation mentioned that the company is/was working on reporting add-ons, I think.
The trick is to learn what node does what, because there are lots of them. You can also download community nodes for additional behaviors. Once you learn the basics, it's pretty straightforward.
I learnt by trial and error, looking for examples and info about nodes, and now I have some workflows with data from DBs, files, APIs and all at once.
<rant>
You wanna know what's even more useful? Code notebooks.
I store everything from my sql queries to my processing pipeline to my models, and my vizualizations. Most notebooks have slide-show modes now so your presentation to business stakeholders is simple to prepare as well, and with the right plotting packages you can draw interactive charts that do all the fancy stuff. And when it's time to update the analysis, you just re-run the notebook.
But business analysts are too freaking lazy to just learn to write some python or R, and they're perfectly happy to get paid to do the same task over and over again when it comes time to making updates.
And don't get me started on source control.
</rant>
Installing a notebook server is an option though. IBM will be happy to let you pay to have them help, or you can do it the sane way with continuum analytics.
In practice what I've seen is that IT and finance departments don't even let business analysts write their own SQL queries for fear that they'll write bad ones that bring the database server to its knees.
I'll take data warehouses for 600, Alex.
I know there's " automatically check stuff into git" sort of models but would really like an integrated Jupyter + GitHub + Google Drive system so that it's easier to track what's happening and to find older versions of stuff.
I desperately need some tool I can tell non-tech people to use where they can input arbitrary structured data.
After the data is there, in any format the user wants, as long as it is structured somehow, millions of programs can be written to use that data and combine it, but somehow the data has to be written FIRST. It is not feasible to come up with the schema, write a software based on that schema and then give it to the non-tech people.
For small companies, Excel may really be all that you need. But at a certain size, you might want to use something like PowerBI instead since it will also pull directly from the database and you can share the dashboards you create very easily. Large companies like the article are referring to might be able to get by with PowerBI (Microsoft would certainly argue there are still plenty of places where it works) or ERP/HR tools more specifically designed for certain uses.
- treat inputs and outputs separately
- always use simple tables for input (no breaks, first lines are titles, basic vlookups for relationships, etc.)
- use pivot tables to summarize the tables, and build reports on top of the pivot tables.
EDIT: BTW, on a personal level, I've moved to CSV/Jupyter Notebooks combo, but I have no hope that my fellow non-tech workers will do the same...
To add a couple more:
- Use actual tables. (Select data+headers, Insert -> Tables -> Table). In most cases the data is tabular but not formatted as a table. Making this one change makes the formulas self-documenting, and rather than selecting rows 2:5000, it will automatically accommodate more data as it's added. Tables can also be named (click in the table, Table Tools [Design] -> Properties -> Table Name). E.g. tbl_sales. This allows nice formulas like
or At a certain point, it really needs to be SQL, but between this and pivot tables you can greatly extend the time before a database is needed.- Add a sheet with instructions. Explain hiccups which may be encountered, and how to fix them.
- Add comments to VBA. There is about 1 person at my facility (other than me) who adds any comments at all. Most use the Macro Recorder then add in the loops and logic as needed. Makes it really tough to figure out.
- Protect cells which hold calculations. I recently saw a spreadsheet which has been giving sub-optimal output for years because the ranges used in a function were messed up and copied down every week.
- Use the built-in styles (Home -> Styles). Same goes for Word (Heading 1, Heading 2, etc.). It makes spreadsheets way more straightforward to read if there are some cells using Explanatory, Input, and Note styles, and it's less abrasive to read. I particularly like the Input style because you can tell users "You'll only need to update the orange cells".
- Use multi-line formulas. Alt+Enter while editing a cell allows multiple lines on a single formula. You can also improve the readability by adding four spaces at the start of the new lines. E.g.
What I like to call the product is "excel on steroids".
You can check it at www.ninoxdb.de.
I know these tools and I like them, but this wasn't what I was talking about in my comment. I meant something more flexible and more arbitrary, like a key-value store non-tech people could edit.
Would it be feasible if you only had to write the schema and the software were generated based on the schema?
I was working on something like that based on https://postgrest.com/, but I'm now reconsidering the approach.
Advertising usually costs 80k.
The newer software connects with existing accounting and enterprise resource management systems, including those made by Oracle Corp. or SAP SE. This lets accountants aggregate, analyze and report data on one unified platform, often without additional training.
Adobe switched to Anaplan early last year and many of the tasks previously performed in spreadsheets are now done in the system, maintaining “one source of truth,” Mr. Garrett said."
> Instead, companies are turning to new, cloud-based technologies from Anaplan Inc., Workiva Inc., Adaptive Insights and their competitors.
Indeed Very fast into the article I classified it as advertisment.
Instead, companies are turning to new, cloud-based technologies from Anaplan Inc., Workiva Inc., Adaptive Insights and their competitors.
Could also be the updates in Excel.
Article doesn't mention companies by name: "The reporter didn't include any specifics! Lazy journalism!"
- excel is much more flexible, I can add a column, delete those I don’t want etc
- speed: many of those large corporate systems are just damn slow, maybe because of stupid configurations, but still often very slow an annoying
- analysis: It's easy to do some basic analysis without studying CS
I'm not implying this is due to nefarious reasons (though sometimes it is): sometimes the official version of the truth is just not adequate.
All outputs were generated by a .Net application generating read-only Excel spreadsheets (which might have 30 tabs and up to 15,000 values on a tab) - was done this way to meet the very complex reporting requirements. All the ~20 report packs in the company were produced with one click in a simple web app.
Ah-hoc querying of Hyperion was done from Excel using the SmartView plugin.
I use Excel because my boss wants to use Excel, because all his bosses have wanted to use Excel, because all their previous bosses have wanted to use Excel.
That's how it works in the electronics manufacturing industry, anyway.
It's a brilliant reactive data programming model that makes intuitive sense to non-technical users. They feel empowered to use it to solve problems right now with a computer. They experiment with it, try things, Google how to do more things- just like any programmer does. And they feel capable of doing this because they don't know they're programming.
Within the Amazon warehouse world, I have seen incredible innovation using it. An acquaintance of mine got into development by using it to help save soldiers lives while serving in Afghanistan[0].
I agree that mission critical data needs to get out of it and into a centralized system, but I still feel Excel is an incredible tool in any business.
[0]https://news.ycombinator.com/item?id=7950190
A spreadsheet optimized for integration into software build processes would probably create more than a billion dollars of real value every year with even limited adoption.
While it may be true that Excel is overused, I believe that the spreadsheet is chronically underused as a model for software development.
This could allow for more interesting things like gathering fields from records into a meta-table from a relational DB, that would definitely be a valuable next step.
https://github.com/exorgan
https://github.com/dgorissen/pycel
or perhaps
https://github.com/xuri/efp
I'm working on a plugin for Excel for dev's and other technical people. It lets the user use SQL and C# in Excel, connect to various types of databases and move data in either direction, and provides a runtime for automation.
The idea is basically to let tech users use their existing skills to make life easier. Main areas of use are:
- process (live) data in Excel via SQL and C#/linq
- move data between Excel and databases
- fetch data from various sources using .net (e.g. get data from a AD, file system, REST etc. in a few lines of code)
- replace VBA with C# and .NET for automation
It's also very useful for prototyping. Business logic goes inside C# scripts, and Excel takes care of user input, displaying results, data binding (formulas and graphs) and data storage.
It's commercial but freemium, the url is http://www.querystorm.com.
I posted about it a few times: https://news.ycombinator.com/item?id=15670030 https://news.ycombinator.com/item?id=11583488
Here's an intro video: https://vimeo.com/242216594
My first heavy use of Excel was in a thermodynamics class in college. We modeled heat gradients for various HVAC systems with Excel. After that, I used it to calculate stresses and strains of different structures using each cell as a node in finite element analysis model.
It is brave of you to integrate directly with Excel, and I commend you for that. Maybe some day I will work up the courage to do something other than basic one-way import.
We recommended they implement a PDM (product data management) system - it would have saved them millions of dollars (considering they had a staff of 20 whose sole job was to update the claims processing system by hand based on these spreadsheets.) Unfortunately when the 2008 financial crisis happened, management balked at the initial outlay. It's quite possible they're still using Excel to this day.
The health insurance people of various sorts who use this setup are the ones with domain knowledge. With an excel program, they can (1) understand how it works (2) add to it, modify it (3) create a copy and break it (4) debug it (5) have ideas about how it could work differently…
I’m not saying that excel is the tool for the job (sounds like it is definitely not), and those advantages erode as the setup gets more complex and/or has more people using it. But, there are reasons excel gets used this way.
The alternative is often rough. Software projects like this have high failure rates. Getting “requirements” out of users’/clients’ heads and into programmer’s is difficult. It means the standard (natural?) gap between domain knowledge and programming knowledge is restored. That has costs.
I’m going to repeat again that I am not advocating health insurance companies run policies and claims in excel. It is a bad idea, almost certainly. I am saying that we should be curious at why they are doing it this way. I think it’s a “good” question in that it leads to interesting places.
If the excel team was blessed with a plague of inspiration tomorrow morning, Is there a way they could make it so that excel is the right way to manage an insurance business?
The alternative doesn't have to have a high failure rate. I was brought in to help reboot their in-house implementation of a product data management system - the core issue was they didn't have any real competency in software development (though they believed they did.) Ultimately I recommended they abandon the in-house effort and instead proposed using an off-the-shelf PDM system, we evaluated quite a few decent options over the course of six months and found several that met the business's requirements. It would have been the right thing to do and I believe it would have been successful. Culturally the IT department staff was plagued with not-invented-here syndrome. It was a generally dysfunctional IT group and it's quite likely that Excel got it's start as the system of record because the business needed to get the job done, and the IT department was unable or unwilling to deliver.
Alternatively you were brought in by management to make cost cutting recommendations - and your solution wasn't cheap enough.
Alternatively the company accounted for the enormous risk of the project succeeding but actually being worse than what they had in place.
You really don't know the politics of this.
Edit: Another alternative is that your plan would have resulted in sufficient job losses that the company was worried that it would demotivate other employees and damage the company more than the cost savings.
It's entirely possible that the OP was exactly correct, or close to correct. It's also quite likely that at least one of the factors you mentioned (or others) was also at play.
Yes, the performance will generally be better (1 minute to run a report, not 1 hour), but I'm not sure things will generally be better. As you said, the business people can at least modify the Excel. These systems? Not so much, without an usually massive support contract (Yaaaay! for us IT people, I guess).
People sometimes make a false equivalent and imagine that the Excel jungle will be replaced by a Google-developed, 90%+ unit tested, refactored and cleaned periodically bunch of code.
I'm not convinced that's a good thing. If they can change the Excel then they can also fuck up the system, and if you fuck up a system like this then it's horrendously difficult to unfuck. I think systems like this look great when they're working, but the second they go tits up you realise why people are horrified by them.
Unfortunately, many businesses then find they have no way to know the extent of the data lost or who did it. And there’s no proper backups. Happens over and over thoughout the world.
Excel’s a great tool and there are lots of terrific uses for it. But what the article describes isn’t one of them. If we’re being honest, this isn’t the first time the issue’s been discussed and the companies likely aren’t actually switching away from excel, but rather supplementing it.
In fact, the article seems to be more a “hit job” / “marketing piece” for Anaplan Inc., Workiva Inc. and Adaptive Insights.
But..I do think that if we're thinking in terms of "users" and "developers" then we're no longer talking about excel. The whole reason excel is used this way is the continuous spectrum of sophistication.
It's no good if you fix excel by making it not excel. The whole reason excel is so popular is the intuitive, unintimidating but terribly wrong way it mixes data, code and GUI. Functions in the data, constants repeated in every row, hard coded variables all over the place. If you're just supplying a GUI and hiding the code, the user can't change, add or debug anymore. The point is that they can code, even if they don't know they are coding.
The trick is to think up a way of shipping VisualStudio, but making it less terrifying to engineers, managers and such.
No... but I think the converse might be possible. If those with the programming knowledge sought to replace the Excel system but in a way which actually preserved the useful features of the Excel solution. Dirty, filthy things that programmers don't like. No referential integrity, data duplication, deferring decisions to human beings rather than lists of rigid criteria, etc. If they could swallow their bile for just a bit, they could build a system just as useful and flexible and introduce tools to add in the things like data consistency and referential integrity - tools that the user would initiate, thus accepting responsibility for the restrictions such things impose on themselves and the data.
That is not, unfortunately, how it is usually done. (I am currently literally working on a program to replace a 40K row Excel sheet that crashes multiple times a day which has a workflow centered around it that involves slicing and dicing the sheet, passing it off to multiple different groups for separate editing, etc... not sure I will be able to sell the team on this approach, however I do believe it is sound.)
What a professional software developer's workflow has and Excel lacks:
* principled way of sharing code across a team with distributed source control (or similarly powerful tool),
* automated regression tests,
* input validation,
* continuous deployment tools to automatically deploy working code (default mode for Excel I guess), and a mechanism to roll back to a previous version (not so much),
* build and dependency management tools for bringing in libraries from a vast open source ecosystem,
* systems for reporting bugs and feature requests and tracking their progress,
* networked database that is the shared "source of truth" for all users,
* security and auditing,
* hardware capacity planning (run locally or in AWS? what are the cost trade offs?)
So sure, get the health insurance people doing all of those things, and have Microsoft figure out how to do all of them well in Excel, and congratulations, they are now software developers. None of these require using a "real programming language", at least not directly.
OK, I don't know who wants to work in health insurance but the basic point is Excel is a good tool for people who have specialist knowledge in a particular domain and want to automate their work. Programmers know the best way of programming but frequently overestimate their understanding of other people's problems.
Even absent that, it is difficult to explain what users need to coders who have zero idea about the domain. Unless it turns out they are the kind that just know user is idiot the moment she has different opinion on her own job, in which case expect needed time to tripple.
The homegrown excel is awesome analysis once the company decides to move on. It is concrete, you can generate test cases from it, it has features.
Excel upgrades don't seem to break previous files as severely and have the backing of Microsoft.
I can't believe I'm defending Excel.
If you hand a caveman a machine gun, he will club you to death with it. IOW, the casual ability for domain experts to screw up a spreadsheet is enough of a reason to decide that excel is a terrible solution to this problem.
Caveman were better than you at a lot of things. They knew how to find squirrels
I've seen this a lot where a consultant will swoop in and tell them that they need to use Peoplesoft or something and the organization spends millions of dollars and years of retraining to get a system that has a lot more friction in their actual workflow and is never quite as good for getting the job done.
Of course there are often side benefits that can't be overlooked, like a much more sane backup solution, interoperability with other providers, etc... But in the end most of the employees will tell you straight up that the old system was better. Too much of the domain knowledge was baked into the old system and doesn't make the transition to the new system.
>We recommended they get a system that manages their product data
Yep, I believe you are a consultant.
It seems to me that many programmers don't appreciate that industry practices or regulatory requirements impose quirky requirements on domain specialists that everyone knows are far from optimal but which need to be complied with anyway if the business is to turn a profit or retain its licensure.
Someone mentioned a health insurance example above: there's 50 states, with 50 different sets of insurance regulations, and then some additional territories, federal regulations, and then wird business practices of healthcare providers who are subject to their own 57 varieties of regulation, and there are powerful economic and political interests that get in the way of burning it all down and replacing it with a single system designed by a committee of one patient, one doctor, one nurse, one pharmacist, one lawyer, and one economist.
And even that highly simplistic requirements committee went through 3 versions while I was writing it up.
The domain experts already have lots of complex knowledge and experience they need to automate. They don't want to learn about databases. Unfortunately, the reverse is equally true.
I think only those that turn into consulting understand it properly, because they aren't confined into a cubicle coding all day long without direct feedback how the real world actually looks like.
At most there is some feedback coming from ticket management system or the product manager.
When faced with a decision b/w using a RAD tool to create something outside of IT, or spending $500,000 and waiting 6 months so real devs can deliver something Finance needed yesterday, that may not be needed tomorrow, the choice is an easy one.
Your generalization isn't entirely unfair; IT is often called on to fix a mess, but some Access solutions are built by people who understand database design/normalization and can put together a easily-maintainable application that never hits storage limits or requires upgrading to a SQL Server-managed back-end.
Corporate IT never lays eyes on many Access projects that aren't crap.
I would agree with OP that Excel is the most-used programming environment of all time
https://stackoverflow.com/questions/27419804/vba-excel-downl...
I have no idea how watertight this is though, it’s Microsoft quality (and some of the most ancient code they have) so I wouldn’t bet the farm on it.
Notes still let you see data as rows and columns and had very spreadsheet-like functions, many of which could be transferred directly from Lotus 1-2-3 - but it was a full client-server system which supported decentralized replication, built-in messaging and email, and a solid security model (yes yes I know it was also a crazy confused mess and a UI nightmare but that's a different topic).
Back in the day, organizations had teams going around finding the most useful ad-hoc spreadsheets and converting them into simple Lotus Notes databases. Other organizations would sometimes also give staff training in building simple Notes databases - not a whole lot more difficult than doing a spreadsheet - and then have expert teams come in and polish them up as needed. This made business software bottom-up rather than top-down, which turned out to be a whole lot more useful in a lot of cases.
In many ways, even after I left the Notes world for PHP and Rails and JS, my career in business software has still mostly been about converting spreadsheets into more "proper" applications.
Spreadsheets are the 'blue-green algae' of the software world: the simple base level that's everywhere and surprisingly essential.
Still true for many people in 2017. @patio11 probably said it best:
> Every spreadsheet shared in a business is an angel announcing another SaaS app still needs to be built.
https://twitter.com/patio11/status/655674551615942657
FWIW we ended up in business software after one of our open source libraries to read and write spreadsheets (https://github.com/sheetjs/js-xlsx, demo http://oss.sheetjs.com/) ended up garnering lots of demand from businesses looking to build those "proper" applications
Flip side, my feeling is that, if an analysis was worth doing, it's probably worth doing right. And to do it right, it should probably be done in a way that one can show their work. I like where the R community in particular has gone in dropping the barrier to reproducible analysis to roughly the same place as Excel.
I also wonder about google sheets capabilities. It does work so well for many people working on one spreadsheet, except for undo. The major flaw I've seen is undo, because in google sheets it was global undo, not personal undo.
Since it was a very powerful tool but struggled getting traction, they are using it as the core query tool for power BI (alongside DAX for great dashboarding).
Well done Microsoft.
It's a pithy quote, you'll get a very different reaction from a finance or small to med-sized business professional.
Excel is the ultimate maker studio that actually lives up to the promise of "build your own mini-app". I'm not saying it can't be improved on. It's just that the core model of flexibility is so compelling, pushing everyone into a SaaS app is invariably going to take away substantial power from the user.
One computer: once you decide you want to use your smartphone, for all but the most basic sheets there are problems with Excel mobile preview, even with Microsoft's official apps.
One location: Excel's lack of a universal time (showing different times in for users in different timezones) have led to gnarly workarounds. There was an attempt to correct for it in the XLSX format (cell type 'd') but it has its own problems.
One person: Excel has no inherent sync strategy. You are forced to farm out to email (and the inherent filename versioning nonsense) or use a system like SharePoint (which has its own problems you notice when dealing with people in different timezones across the world, like accidental file locking and data loss)
Any solution, SaaS or otherwise, will have flaws. It might solve Excel's weaknesses really well, but will likely lack its strengths.
Excel is still going strong in 2017 to a large degree because its strengths continue outweigh its weaknesses and that people have found reasonable ways, or add-on tools, to work around it's weaknesses.
It will be interesting to see in the coming decade if niche industry solutions or an even more incredible swiss army knife were to come around and dethrone the incumbent.
What I mean is that Excel is essentially a write-only language. It's very quick to prototype something for a semi-technical person, but auditing / code-reviewing an Excel sheet later is essentially impossible. The ubiquitous manual loop unrolling doesn't help readability either.
It also scaled fine to thousands of employees.
Really? I am working in company which is not handling super-sensitive data (i.e. not a Bank, for example) but I can assure you that if went around proposing to adopt a SAAS for what Excel is used internally I would not get very far.
No matter how good you are as a SAAS provider, our data stay in-house. End of story.
It's no value judgement if you want to continue using Excel for privacy, compliance, or security reasons. You do you. But SaaS companies get very good at developing privacy, compliance, and security stories, particularly as they move into enterprise. (Below enterprise it's less of a big deal, partially because customers care about them less and partially because small businesses are, as a judgement-free statement of engineering reality, abominably bad at data security.)
(off I go can often being something like: upload the result to the same or a different legacy system, again in some custom format).
Maybe I can pester my own IT dept. to add an extra option to the report so that it spouts off a .csv directly. That's all, the rest still lives in Excel.
Then I am free to play with data as much as I like (take also in account that in some cases you want these in a Excel just to be able to manipulate them better, while the old app works record by record, you can make changes across a thousand records using the Excel interface... and keep also in mind that this is maybe something you need to do once or twice a year, when you renew your catalog prices or whatever).
Now, what is the cost of:
- going out on the market looking for a SAAS that can get my data exactly in the format I use internally, applies the required transformations and send the result back in a format I can use
- assess that the SAAS vendor is indeed trustable for my desired level of security, SOX compliance, etc.
- add one more vendor to my portfolio of vendors/licenses/purchasing orders
So the reason not to go to SAAS is actually a combination of security/external resource dependency/cost/bureacracy.
Where "cost" is mostly inertia+sunk cost fallacy. I am sure that a SAAS may cost less than the hours spent manually doing all the stuff I mentioned above (including mantaining the Excel spreadsheet) but the latter is a "hidden cost" because it something that happens infrequently and is part of the normal chores of whoever is using the Excel sheet itself.
While introducing a new SAAS app will be an IT cost (to identify/approve it, add a recurring subscription etc.)
Having worked with a number of finance teams, the biggest problem is not when Excel is used as an analytics tool but as a financial database. Excel as a source of truth for financial data is truly terrifying. Even financial audits are still often handled with custom data pulls exchanged over Excel spreadsheets.
Shameless plug: that's why we ended up developing one of those SaaS tools (http://modfin.io). It lets finance teams map their source data to a proper, easily auditable accounting ledger so that they don't need to do their "magic" in Excel.
I am a programmer and I use csv/Excel as the single-entry financial database for a small healthcare business ($500k revenue). The accountant is fine with it. Filtering and pivot tables are a joy in Excel, and I can still run Python scripts whenever I need (mainly to auto-categorize bank transactions). Most other businesses around here use QuickBooks, which has a proper system of accounts, but then I can't play with the data.
You can try to institute the same controls over Excel, either directly in code or just implicitly in terms of how it's used, but they tend to break down pretty quickly with scale.
Excel as the source of truth for a financial system is particularly scary to me b/c it's so easy for someone/anyone to change entries or miscalculate so it's very difficult for me to get confidence in its completeness and correctness.
Completely agree about analytics and scripting, but I'd utilize the CSV/Excel reports as point-in-time analytics that can be tied back a structured source of truth (or a source of truth I can hold liable, e.g. a bank or credit card statement).
Validation: I'm confident that I can catch duplicated rows and mistyped amounts because Excel formulas verify that the sum of all transactions for each account equals the account balance, and that transfers between accounts add up to $0. Do I need anything more?
Point-in-time doesn't work because I need to keep track of info that doesn't show up in the bank statements. I run a Python script that adds pretty-prints names and adds categories, and I also manually enter check details, category adjustments, business purpose of meals and flight tickets, etc.
I don't use a proper database because to do so I'd have to give up Excel's nice features (or worse, switch back and forth between Excel and the database).
I'm sure that the people who made these billion dollar mistakes were pretty confident too... https://www.cio.com/article/2438188/enterprise-software/eigh...
"The error occurred when the accountant omitted the minus sign on a net capital loss of $1.3 billion" (Fidelity)
"There were honest mistakes made in a spreadsheet used in the implementation of a new accounting standard." (Fannie Mae, $1.36 billion loss)
There's a bunch of lower (million dollar) losses listed in the linked page too.
I think that you need to show that Excel requires more data entry, or riskier data entry, or that Excel can't catch as many errors, etc. For now I'm not convinced.
[1] https://www.accountingweb.com/aa/auditing/human-errors-the-t...
With new (or prospective) clients that are SMBs, one of the first things I ask is, "so, what are you still using spreadsheets for?"
In the mid-90's, I thought I was helping by bringing Notes in to the company I worked at, and people seemed to agree. A ticket tracking database we set up seemed to help. Many years later, after I left, I saw a presentation where Notes was cast as the problem, with a website proposed as the solution.
Many people seem to say the same thing but the only problem I could ever see (in occasional use) was a non-standard UI which non-technical users had trouble grasping. What made it so awful?
Notes itself has some great functionality. The UI feels a little clunky - but generally you can do what you need to do.
The main problems I encounter is the way our IT department have deployed it. Many functions are not enabled or only partially working. As far as I am aware, we have no Lotus/Domino expert in our country; head office (other side of the world) has quite a few though. Any requests made through our regional helpdesk for fixes/improvements mostly go nowhere.
I like the idea of the replicated databases for email and other business functions. It should mean staff could switch to a different machine and quickly and painlessly get access to all their email and other data. However, there is a considerable amount of convoluted configuration required (the way it is currently configured) that appears to require local admin access that makes it impossible without helpdesk assistance.
I suspect many companies deployed Lotus/Domino is similar semi-functional ways that made it less useful than it could be.
The reason many companies migrated away from Lotus/Domino though was the risk of vendor lock in. My employer is stuck with a vast amount of business process captured in Lotus/Domino. They have been slowly migrating core functionality out to other web or networked database applications over the last 12 years. Even so, I don't see any move away from Lotus Notes for email for many years to come.
But we have to give Dan Bricklin and Bob Frankston their dues because Visicalc is the original killer app spreadsheet in the literal sense: people used to say they didn't know if Visicalc sold more Apples or if Apple sold more Visicalc. It truly was the program for which people bought the platform.
Because of this "bit more work", it's also much less flexible in the long run.
Excel is not a great database, but it's a highly flexible one.
Spreadsheets have a lot of similarities to the sort of code bases that as an industry we've been moving away from. E.g., no version control, no tests, opaque code, bugs are hard to find, write-only code, bit rot, random antique scripts used in critical spots, poor reusability, latest version is on somebody's hard drive somewhere, nobody knows which version is the right one.
I also think it's deeply unfortunate that there's no good path from spreadsheet master to real developer. As great as spreadsheets are, they're a cul de sac. I hope this new generation of tools fixes that. If not, somebody sure should.
You would have thought this would be obvious- it seems analogous to perl/python or other such Swiss army knife tools we use for quick one-off solutions that occasionally get pressed into permanent formal use where they shouldn't.
Version control for excel is terrible at best. Code review is basically non-existent and how could it be when logic is split over several cells and variables are named $A17 or B23.
I think the win with excel is its super simple interface, that said there is plenty of room to improve on the programming language interface.
Excel lets you name tables, cell ranges, or individual cells. I have seen spreadsheets put together by people who take the time to do this, and they are remarkably readable!
Anyway, it basically worked like a stack, with the currently selected cell being the instruction pointer. So when debugging you could either look at your data and watch blocks of numbers flashing in and out of existence or changing state, or look at the macro sheet and watch the pointer bounce around as it went to different branches or sat in loops or whatever. There were commands to turn screen updates on and off (which sped things up considerably) so as you got more and more of it working it would go from looking like some confusing nightmare to a smooth list of progress bars and passed sanity checks.
They moved to VBS because programmers were finding the cell model too restrictive/weird, and for integration with Access, but doing it all in cells was remarkably and pleasantly like assembly-level debugging. You'd hack something up quickly, figure out your algorithm, then name the ranges and use and index value, and you could lock ranges and what-all else. It wasn't as easy to read as a page of code (eg no whitespace) but the crudity of it pushed you towards that sort of 1-operation-per-line discipline that assembler demands and really forced you to think about semantic efficiency. I had macros consisting of thousands of cells of code with every single operation commented. You could, in turn, name the ranges that had macro code in them and do code profiling to find out where your performance bottlenecks were, and so on.
Some HN comments here: https://news.ycombinator.com/item?id=12448545
It's common to tell kids (and everyone) that they need to "learn to code." That is what Excel already is! And it's incredibly powerful, especially if you don't have any other coding skills.
But, of course, it's messy. When tens of millions of people can each write their own programs and easily modify them and change them to suit their own needs without needing to recompile or anything, then you get the mess which is Excel.
...and, in fact, that's also what you'd get if you taught everyone how to program Python in high school instead of Excel. You'd get tens of millions of non-interoperable messes of programs (which still get the job done!).
But that's fine. Give humans tools, and they'll find clever ways of using them that you don't intend. And it'll continue to drive people crazy. In some ways, the Excel hate (though obviously not unfounded) seems almost kind of like a bourgeoisie elitism. THIS is what it looks like when you give everyone the ability to code!
That's exactly what happened with Perl when everyone learned to use, modify, and develop CGI scripts in the early days of web-based application development. Dynamically-generated website content worked and evolved into the web we know today, but those scripts were awful and did a lot of harm to Perl's reputation.
These kinds of expressions are what ultimately caused the derisive terming of Perl as "line noise". It was really unfair, to say the least, because there were plenty of ways to code in Perl and do so cleanly, with a minimal usage of regex.
Honestly, it seemed more like people were coding in regular expressions, and simply using Perl as a "run-time" wrapper for those expressions (and maybe those expressions existed first; I don't know). I'm not the greatest fan of regular expressions (to me - especially on more complex operations - it is almost like programming the tape of a Turing machine), but I do know they have their place in the world. However, because the "language" (if you will) of regex allows for an extreme number of operations and such, it almost succumbs to an "inner platform effect", allowing those with familiarity to roam free, to the detriment of future maintainers.
That said - none of this is new. If you go back to the beginning of microcomputing, you'll find the same thing happened with the various dialects of BASIC, as people self-taught themselves programming in that language, then spread the knowledge thru various means (magazines, books, and later bbs systems) - it became a horrible mess, even though it all pretty much "worked".
Regular expressions are really powerful, and are so much better than the typical non-regex string operations most languages support. Combine that power with the ~= and qr operators, which make regexes as integral to the language syntax as == and "", and you get why Perl is so good at textual searching, parsing, and manipulation. It's easily what I miss most about Perl development since my career has moved to primarily C# and Javascript.
The problem was that Perl's performance on builtins(sorting and regular expressions in particular) was stunningly good while its performance on anything else was stunningly bad.
The idea that you would Decorate-Sort-Undecorate an enormous array because the overhead of passing a function to sort() was so much slower was ridiculous.
In the case of regexes, breaking apart the regex into readable chunks and processing the data multiple times was so much slower that everybody piled everything into the regex. Named captures didn't appear until 2007.
The key, as you mentioned, is to use builtins, or more generally to use canonical expressions. There's always more than one way to write an expression, but Perl definitely has opinions about the correct way(s), and the Perl compiler does pattern-matching against the code to recognize many of the canonical expressions. They then get compiled to higher-performing IL code. This is why using things like the spaceship operator, <=>, will run much faster than equivalent verbose comparison code.
This is over-simplified, but I always thought about it this way: the Perl compiler generates "assembly code" for the "Perl CPU" to run. It can generate verbose assembly code using lots of low-level assembly commands, but it can also recognize commonly-used expressions and compile them to a single high-level assembly command, which can then run in a single "CPU cycle" instead of stepping through verbose code. Therefore, it was always worthwhile to learn how to write the commonly-used expressions in the common way, so that the compiler can recognize and optimize them.
I don't know if other languages have this kind of optimization. JIT compiling, as I understand it, does a quick and rough compile without optimizing on the first pass in order to start execution quickly, and then recompiles with more optimization on code blocks that are consuming a lot of runtime. I don't think any of the JIT compilers look for common expressions and apply pre-determined, hand-coded optimized function calls to them.
I disagree. Python leaves a readable instruction set, while Excel buries the logic in an opaque two dimensional grid of cells (plus or minus atrocious VBA). I think this gives a better ladder for people to improve their skills to the point of not producing unmaintainable garbage.
Not everyone structures programs in the same way, or puts functions in easily found locations, or names things usefully, or even approaches the problem in a way that makes sense to the majority of other people. For a concrete example, see this[1].
> Excel buries the logic in an opaque two dimensional grid of cells
It would be trivial to represent excel grid formulas as a set of predefined functions, or a special data structure that you register functions in, and then it could be in textual form. It wouldn't make it any easier to reason about, but then again control flow isn't always easy to reason about when a program is split into many different objects and functions, until you've internalized a fair amount of the program. Spreadsheets give people a very limited, but structured output and a very limited, but easily reasoned about way or processing. It's different than "normal" programming, but that difference allows for very intuitive use.
> I think this gives a better ladder for people to improve their skills to the point of not producing unmaintainable garbage.
How much work has been put into making spreadsheets easier to share with other people? I'm not sure the same culture has existed for spreadsheets as for programming, where people have needed to share and reason about complex algorithms and data structures. If it had, I'm sure we'd have much more in the way of how to structure your excel well so it can easily be consumed by others, or yourself in the future (if it doesn't already exist, which it may).
1: http://thedailywtf.com/articles/Python-Charmer
Which you prefer depends to some extent on which end of the problem you're coming from; if you're starting out with a lot of domain knowledge, a spreadsheet is attractive because you can look at the data, see whether the way it's being stored/processed is basically correct by spot checking it, and then automate. I'll still often reach for a spreadsheet if I don't know off the top of my head what the rules or relationships are and I need the equivalent of a whiteboard with logic.
If you have a top-down perspective and you want to automate a process whose fundamentals you know well, code is more comfortable. And you can go back and expand, modify, or adjust it later. But two things I don't like about code for handling large volumes of data are that I have to construct some sort of viewer to format the output (requiring more coding, or slower bug discovery if I press ahead without), and endless typing I like selecting things from menus and tool palettes because while I don't mind holding an instruction set in my head I don't like doing the same for every library I might want to use, especially remembering all the parameters. I hate hate hate writing glue code and want the computer to do that stuff for me.
Fortunately a lot of these problems seem to be receding thanks to tools like Jupyter and some of the promising Flow-based programming tools that let you easily visualize structure, code, and visualization without getting bogged down in details.
I should add that I prefer hacking to software engineering; that is, I'm only interested in programming as a means to an end of having a customizable tool for myself rather than building a product for other people, so I want spend as little of my time as possible on constructing the tool.
Yes, mixing the good old Excel dependency tree together with VBA macros creates truly dreadful and opaque spreadsheets.
It's obviously an exaggeration, but not by much.
Which I guess makes that test more about dedication and attitude than about raw skill.
"A spreadsheet is an aggregate of concurrently active objects, usually organized into a rectangular array of cells similar to the paper spreadsheet used by an accountant. […] A spreadsheet is a simulated pocket universe that continuously maintains its fabric; it is a kit for a surprising range of applications.”
– Alan Kay, “Computer Software”, Scientific American (1984). pp. 56–57. PDF: https://frameworker.files.wordpress.com/2008/05/alan-kay-com...
Republished as “Computer Software” (1984). pp. 6–7. PDF: http://home.cc.gatech.edu/guzdial/uploads/130/Alan%20Kay%20-...
Every single department in any company can benefit from knowing it well.
The problem is getting people comfortable and competent to know it well.
This is where I think google sheets fucking failed: they should have made an effort to make sure every single gmail user had a tutorial to know how to empower their curiosities and interests via google sheets.
It can be a really powerful one I agree. It's simple to use, a lot of people knows how to handle it. And it's probably one of the best tools to do some quick data analysis.
However, like every tool, it has its pros & cons. One of the main cons, IMHO, is that data tends to be mixed with processing a lot (but it also simplify usage, so...).
Overall spreadsheets have been really helpful. Retrospectively, there is a good reason why it triggered the personal computers boom in the 80ies. They are good adhoc tools that can help lot. And Excel is the dominant one.
However, there are tons of horror stories around Excel, worst case scenario, it has lead to wrong decisions affecting the world economy.
I've personally a few horror stories (fortunately, not affecting the world economy).
At my last job, we used an internal spreadsheet to evaluate costs and margins when answering contract bids. This spreadsheet was a monstrosity taking ages to compute and it was a nightmare to manage, sync, and share between all the participants.
I've also seen the traditional "on what did you work last week" spreadsheet, quite annoying to update, even more given that my workstation is under Linux...
I've also seen spreadsheets as specification documents, generating an xml file, which in turn, through some xslt, was used to generate C++/C++ headers files describing an interface between two sub-systems. The concept (automating spec to code translation) was really interesting. However, I don't know about you, but I personally hate to have MS Office in the tool chain used to compile a piece of software, specially when it targets Linux and only Linux...
But those are not the most terrifying spreadsheets I've seen. The most terrifying on was a giant spreadsheet basically managing every aspects of a huge project (thousands of piece of equipment, hundreds of sites). This document was so big it took several hours just to open it. It was like a giant CMDB of all the configuration items, and, trust me there were a lot. But it also did more, a lot more. It was generating configuration files for applications, background images for monitoring maps, firewall rules... It was also a tool that helped architect and size the number of equipment required for a given site. I probably don't know the full extent of its functionalities. Just to give you an idea of its complexity, at one point, even the number of cells in a whole excel document was reached (with Office 2003 however). All the auto-generated stuff were always kind of wrong and had to be fixed by hand, which made for a repetitive, annoying and error prone process. Also, there was no clear naming convention, and it was a mess to take the generated stuff and load it in our automation scripts. It was, de-facto, a huge piece of software with no versioning, tests or QA and it showed. There was only one guy behind it, basically managing the beast full time. When he was sick or on PTO, the whole project was impacted. And when he left, it was a huge mess, nobody was able to take over.
Spreadsheets are great, but past an adhoc usage, please, please, please, implement something more serious. And also, keep track of Excel usage to avoid these kinds of monsters. Often there are real needs behind these beasts, but Excel is a really horrible choice in the long run.
Related commitstrip:
http://www.commitstrip.com/en/2014/12/19/the-coder-and-the-b...
I do think this is a tooling issue that could generate code based on Excel workbooks.
I wouldn't say Excel is a programming language - unless you are referring to VBA attached to excel. Excel is a spreadsheet application. It's no more a programming language than Microsoft Word is a programming language.
Can we say "one way dataflow constraint system"? They're even called "spreadsheet constraints"...
Anytime another programmer asks me what language they should learn I tell them Excel and they think I'm joking. Besides being a quick and dirty way to solve a variety of problems it helps you speak in the language of your users (if you work in B2B)
Why do we readers take these sorts of things at face value? You know the whole article is a sales pitch.
Or you get irritated by it, while waiting for the "inevitable" collapse of this promotional/propaganda model...
Honestly I don't know what is happening, but it seemed like the first option, which I've followed for a couple of decades is a good enough cure for disillusionment and depression. (Caused by investing in the second option)
Edit: add quotes to inevitable.
Also known as functional programming
Indeed, I'd also argue that the most commonly used database is Excel. Although few people using it realize they're designing schema.
Implicit & unexplained business rules, no tests, no version control, unlabeled cell references, hardwired variables, everything is global, no encapsulation, no logs and no documentation.
Even assembly code is easier read and maintain than a complicated excel workbook.
An idea I've been toying with for a few months now is an automation app (read: window manager) whose UI is somewhere between Excel and GameMaker Studio's drag & drop UI. Like where you just have a giant grid/canvas, and where you can define "routines" and "variables" as groups of cells. If I could figure it out, I think it would open up the idea of an automation app to a broader scope, to people who aren't programmers, and I might be able to make some money off it. Especially considering I made like $500 in donations for Hydra back when I was doing more WM stuff for Mac, so there seems to be some excitement for the idea of programming your Mac, but perhaps it could be a living if I can figure out how to reach out to power users who aren't programmers. And it was your comment that gave me the idea of making it more like a grid instead of more like GameMaker. So, thanks!
I think that's just as true today, at least as far as business software goes.
0 - https://mitpress.mit.edu/books/small-matter-programming
Edit: UDF is user defined functions. What's LPT?
http://www.sciencemag.org/news/2016/08/one-five-genetics-pap...
https://www.economist.com/blogs/graphicdetail/2016/09/daily-...
he authors found that Microsoft Excel would often interpret “SEPT2”, which corresponds to the gene Septin 2, as “September 2nd”. The programme also tended to mistake identification codes like “2310009E13” for numbers in scientific notation—in this particular instance, the code would be read as 2.310009 times 101
Excel is fine for the home user. But the implicit conversions of input data can play havoc with any complex analysis.
Plus, it's limited precision can cause errors such as subtractive cancellation, etc. In order to correctly calculate complex formulae, the calculations must be done with an understanding of the limitations of the computers.
e.g. you don't calculate (a^2-b^2) for large 'a' and 'b'. Instead, you calculate (a+b)*(a-b). That has the same mathematical result, but is not affected by subtractive cancellation.
Can you not turn all of that off (and only need to turn it off once per sheet/file)?
Heck, even integration has been re-discovered and published:
https://fliptomato.wordpress.com/2007/03/19/medical-research...
If the tool doesn't work correctly by default. Then it's likely the wrong tool for the job.
The default conversions are fine for 99% of use-cases. But no one should mistake Excel for a robust data analysis tool.
I would be worried about endless forking of defaults as a feature of newly built tools, leading to a bunch of new tools that will be harder to maintain.
If they can't manage implicit data casting preferences (if those exist, they may not), surely handing them something more complicated like R, MATLAB, or SQL is even worse.
I'm not an Excelista, and you shouldn't have assumed that considering I asked several posts up if the setting existed.
In general, one of the worst things about Excel is there's no obvious way to lock anything to keep it from getting changed by accident.
Excel completely corrupts dates before the year 1900. It just boggles the mind -- if your spreadsheet has dates for 10,000 years from now that works fine, but if there are any dates from the 19th century or earlier, it can cause big problems.
Many "non-trivial" applications got their start at someone's desk in the form of a shitty excel spreadsheet with horrific macros. Is it optimal for "complex analysis"? No, but it doesn't matter because most things are computationally simple. The hard part is the intricate business logic and the timeliness of getting results and these things are not "trivial" at all.
Instead of complaining about how terrible excel is, the community here should be providing alternatives. And no, engaging a software team/consultant for a million dollars to develop bespoke applications or interfaces to enterprise systems for every little project isn't a viable solution for people who need to get stuff done pronto.
A real alternative to Excel is no small feat.
Some huge financial spreadsheets have sanity checks computations. With conditional coloring (turn this cell red if the sanity check fails) these can feel a lot like unit tests. However, these are mostly for business logic - so it's hard to see how a vendor could provide them.
In other words, it's not that excel doesn't have version control and unit tests - it's that the people who wrote excel spreadsheet sheets don't understand version control or unit tests.
Yeah... like I'm going to compete with MS by writing my own Excel replacement. That just isn't realistic.
What would be realistic is for MS to acknowledge that a large proportion of their customers use Excel for a particular purpose. And then tailor their software to the needs of their customer base.
But why would MS care? The researchers already bought Excel. So why "fix" it?
On top of that, no one is aware the the published papers are crap.
The real solution would be for journals to deny publication of papers based on shitty Excel analysis.
Excel endures because it's the non-programmer's REPL for data. Like a Lisp REPL, using Excel is nimble and has an instant feedback loop. Click "A-Z" to sort ascending, click "Z-A" to sort descending. Move/hide/freeze columns. If one has intermediate-to-advanced skills, one can pivot the data on multiple axis. None of the centralized systems with even the slickest web GUIs can match Excel's capabilities.
That's why the centralized systems like SAP ERP, Microsoft Team Foundation, mainframe sytems, etc offer functionality to export to Excel spreadsheets. (Or they offer export to csv files -- which is 1 step removed from Excel.) With MS TFS, you can even re-import from Excel so in that case, Excel acts as a sort of offline data-entry client for the project management tool.
One middle ground between Excel data silos and the utopia of a centralized system is a shared document store like Sharepoint or Dropbox. The hierarchy would be something like:
1) Excel spreadsheets emailed around. Lots of redundancy; uncertainty of which version is canonical.
2) Excel xls file stored on Sharepoint with automatic version control. Employees must have discipline to only email links to the "one true" spreadsheet and never attach the actual xls file in the emails.
3) The data that was in Excel is migrated to centralized system and all data entry and reporting is done there. However, because the central systems are so cumbersome, this abstraction "leaks" back out to Excel because of the highly desired "export to Excel" functions! (This restarts the dreaded Excel "data silos" complaint cycle all over again.)
There isn't an IT department in the world that can develop centralized IT solutions fast enough to keep up with the ways employees slice & dice data. That's why Excel is "overused".
You can very easily use Excel as a client to a central database and get the best of both worlds.
I know Google Sheets gives you a little bit of this with Javascript embedding but I was searching for something more tightly integrated with Excel or LibreOffice.
Yes it seems that way but many spreadsheets exist because the centralized system is an incomplete solution.
For example, a company might have a "human resources tracking" centralized system. (E.g. Ariba, Peoplesoft, etc). It has some standard features like ingesting resumes and extracting "skills" from parsing MS Word docs, etc.
However, the company happens to use a interviewing process where the candidate must pass (1) phone interview and (2) onsite with 5 peer programmers, and (3) hiring manager.
The hiring manager wants to "capture" all of those states (phone_passed=True/False, peer interviews with 5 different numerical scores, manager_pass=True/False). The centralized system doesn't have fields that capture all that so the hiring manager creates a spreadsheet that has those extra columns. Many spreadsheets out in the wild "fill the gap" between incomplete centralized systems and custom IT programming. An adhoc xls file can be created far faster than submitting a change request to the corporate IT department to customize Ariba -- which could take months (or never) to implement.
Or spreadsheets act as the "intersection" of data between multiple systems. Again, one purpose of centralized "data warehouses" was to eliminate Excel "data silos" but again, no IT department can create data warehouse solutions (which also includes all the custom reports) as fast as someone creating spreadsheets and just emailing them around.
Having a hybrid process where Excel acts as a round-trip offline client to a central db doesn't really eliminate the fundamental reason Excel (over)use keeps multiplying like rabbits.
When it doesn't, Excel will work fine (and will only cause disaster in individual cases). A "proper" unit-tested solution will either not work at all or just won't support half the use cases.
And without someone specifying the domain knowledge, there's nothing any programmer, no matter how well-versed in unit testing, agile, or ... can do about it.
Aside from that — what about MS cloud services? I mean, maybe that's not appropriate for healthcare or certain businesses, but for others it allows there to be a single source of truth if the workflow is correctly tuned, no? I can produce a doc in my local instace, push to 365, edit remotely on the web, have somebody else edit remotely, and sync it back to my local instance without a hitch.
I use Office365 for that purpose regularly at my job. I'm a developer, but I have regular communication with editors, producers, project managers, division managers, operations managers, art directors, and so on. A lot of the information we share is tabled and needs to stay updated, and editable by each party involved. It's worked well for me in that regard.
Maybe others have had different experiences?
Well, more accurately, it can write back to the database, but normal usage of Excel won't afford that result. Normal usage of Excel you have an independent value.
It is also a dubious proposition as to whether we want people to actually write back to the central database; you'd be one stray macro away from disaster. You sorta want it if someone manually corrects something, but not if they operate on it programmatically, and now we're making things really complicated.
Another company/product that I know about (though it's been over a decade since I last touched it) that attempted to fix this issue was Actuate:
https://en.wikipedia.org/wiki/Actuate_Corporation
Hmm - and now that I've started digging into it, I see that this company has radically changed from what I knew of it. I had known it as a company which created software for designing and running reports from various data sources (whatever could feed it, honestly - excel, odbc, csv files, etc), which internally could be configured for more advanced usage using a language that had similarities to Visual Basic (it was OO long before VB, and geared for the reporting system, not general purpose usage). Before I left my employer who used it, they had just announced a Java-based system of "business objects" and some kind of tool to interact with data in an Excel-like manner (the tool essentially had all of the functionality of Excel at the time - including pivot tables), where changes would propagate thru the system. Plus interaction could only be done thru those objects, which were defined as "views" into the dataset.
So - after research this (just now!) - imagine my surprise at finding that this company now is named:
https://www.opentext.com/
...and they also did something called "BIRT" - which was open source and part of Eclipse:
https://en.wikipedia.org/wiki/BIRT_Project
Apparently this started in 2004 (not long after I left my employer - and that it is Java-based also fits).
I'm not sure what is what with the company any longer? If I had to guess (probably wrongly), they spun off their Actuate reporting stuff and the other tools as open source (?), and are now concentrating on other things, possibly in the same vein?
Weird. But very interesting.
It wasn't very long ago that I realized that my two decades in IT could be summed up as: making proper applications out of Excel workbooks with feeble macros. I hate Microsoft as much as the next Linux zealot, but dang if they didn't create a world in which I've made a career. Where I'm at now, I'm drowning in opportunities to ameliorate horrific Excel-based workflows, and I'm fairly confident that there's enough work out there to last me until another 20 years to retirement.
Yes, exactly.
If we're being uncharitable, we can spin Greenspun's 10th rule[1] of programming as:
>"Any sufficiently complicated Excel spreadsheet contains an ad-hoc, informally-specified, bug-ridden, slow implementation of a proper centralized database."
Or, if we're being charitable, we frame it as an internal MVP (Minimum Viable Product):
>"Spreadsheets are the internal 'mvp' that proves the business value before you build the centralized systems. When the spreadsheet becomes unmaintable spaghetti formulas and the xls email workflow crushes under its own weight, that will give the company the evidence and the confidence to spend $1 million and migrate the spreadsheet to a proper centralized database."
[1] https://en.wikipedia.org/wiki/Greenspun%27s_tenth_rule
...along with understanding that migrating at earlier stage would cost only $100K.
But then the people keep asking “how can I export to excel”. One user was copying an html table split in 20 pages one by one to excel.
So I found a elegant and simple solution: an excel spreadsheet linked to the PostgreSQL db of the app via ODBC. Now users can just click on Refresh All in excel and get the latest data. I’ve also added an “click here to open” link in excel which opens the form with the row’s id in the web app.
Now everyone is happy: the data is kept centralized and available to everyone in real time. And users can make all the pivot tables and pivot charts that they want natively in excel.
Do not underestimate the power of ODBC with excel!
(may not work for big data though, but most business app do not contain more than a few thousand rows)
That's why the principal way you sell centralized systems to users isn't in the ease of manipulating results but in the ease / automation of data entry. The whole pain point and problem with Excel is in manual data entry, and having a single source of truth, even if you succeed in enforcing its property as a single source of truth, does not automatically solve disagreements between different human sources of data.
That's why tools like Grafana have interesting futures for business users, because they enforce a clear separation between data sources and data visualization, while offering a lot of control to the end user over statistical manipulations and visualizations that they can create, and keeping those visualizations updated in real time, so that they don't have to be re-prepared every time period for some new report.
Also Google Sheets has an API for stuff like Python.
Only the very advanced users turn to VBA programming.
Got a complex work stream? Got thousand small problems that needs to be slowly figured out over 50 different main topics? Got a team of 2,50 or 1000? No problem.
1. Just set up a bunch of jira entries while tagging them with the same labels
2. set up filters on these labels.
3. Set up dashboard(s) that give a complete realtime/update to any inquiring user.
Need to report status? No problem, screenshot of the Dashboard and done.
It's really great tbh.
Yep. Even if they get centralize data via RDBMs like SQL Server or even god forbid Microsoft Access, office workers are going to want the front-end of that data to be excel.
There is too much knowledge investment in excel for many office workers to want to switch.
What about Business Intelligence products such as Hyperion? They can provide reports from a defined data model which gets data directly from the database. People can create their pivots and other transformations on the data fairly easily. Is their something that Excel offers that makes it much better than one of these Business Intelligence products in certain situations?
A simple one: If I have been asked to do a report, but it turns out I don't have the correct access rights to some data, my manager can simply send it to me in an Excel sheet, or he can log an issue with IT to change the acess rights to a specific key/dimension/hierarchy.
The 'one true data source' move often correspond to tighter access controls when good auditing really would have been the correct solution, hence the above situation is fairly common.
Several CEO's (plural) tell their Staff (singular ) a CEO only has one set of employees.
The headline implies that all the CEO's have multiple sets of staff
My dad brought home an IBM PC in 82, using Multiplan for chemical mixing calculations. He thought it was the cat’s meow. Though Multiplan was the predecessor to Excel, the spreadsheet had been out since late 70’s.
From that perspective WSJ is doing a great job in making the bed for these ideas: startups will eventually want to sell to those "Finance Chiefs" which is never an easy job.
I’m not a mechanic, but is WD40 an example? Or maybe some tool like a hand held circular saw, in home construction?
And, other examples from the tech world?
Having tickets in JIRA is for the team to track and plan work that needs to be done, not for storing personal garbage.
That is literally all I did when I was a financial analyst and is how I became a programmer. This should be automatic, I thought.
That said, it's a great tool to survive in the woods :)
This sounds great if you know exactly what reports you need and have the resources to build them, but I'm not really sure how this kind of initiative means "stop using excel". This article is advocating for a solid API / data management / data catalog program from what I'm reading.
This!
I know this is reality in so many companies that don't have technically educated staff to tell them about more suitable ways to do it. So the teams get data from one department or system, manually transform data (in the editor via search/replace), and if they are "very sophisticated" they save it in MS Access, join tables, connect the dots, export back to Excel, analyse the data via Pivot Tables etc. And they do these steps repeatedly, every week. Without stopping and asking if there might be better solutions out there. (In German IT curriculums they call this "Medienbruch" = "media discontinuity"?)
I have seen this in so many companies and thought to myself: This is an excellent case for a consulting pitch. And I could literally transform their work.
And I am not talking about selling data warehouse solutions with ETL processes and the like.
Twenty years ago I realized I could do all these preprocessing and transformations with Perl. Now I do these kind of things with Python and Pandas etc. So there are so many solutions that are a huge improvement. Still these departments do it with Excel/VBA/Access.
"Executive 1 likes to see the data presented in formats A and B, Executive 2 likes to see it presented in formats C and D, and occasionally E, and Executive 3 will only look at it if you format it as F and cut/paste it to PowerPoint."
So you end up with 20 analysts who all have input, each manually re-formatting their own data in 6 different ways, making sure they all stay in sync. Then someone else collates it all together into 3 different exec-specific presentations, and double checks to make sure they tell approximately the same story. Welcome to life as an office drone!
Then one executive who didn’t like the story would have one of his analysts pull the same data in a slightly different way and adjust it. The executive would then show it to the CFO and tell him that’s the real story. The CFO would turn to you and ask why his number is different than yours. Then you would spend hours with the analyst reconciling the data and building a waterfall chart to send to the CFO to explain the difference.
Then if you’re really unlucky the CFO says he wants to see the data both ways with a waterfall reconciliation every month.
This seems like a good idea at first: automate manual processes, have a real database with one source of truth and consistency rules, multiple people can access it at the same time, etc. You can do it if the process is very well known and fixed.
But most of the time a web app would cause more hassle for the companies than having a team of people doing the work "by hand" in Excel would be.
* The process and desired data schema might change every few months - or even weekly when doing something like marketing reports. * Now they need regular maintenance - keeping servers up, doing and testing backups, applying security patches. * But the problem being solved is not big enough to justify hiring a full-time team of software developers to maintain it forever.