6 ms·

Ask HN: What are the biggest problems with spreadsheets?

29 points by filipstachura 11y ago

s_kilk 11y ago
In my experience working in some "speadsheet heavy" businesses: - complecting 'data' and 'presentation of data' together - lack of tools to help with 'correctness' - lack of version control - horrifying and error-prone programming model, both hard to write and hard to reason about. - spreadsheets full of pivots and charts rapidly become the 'truth' of a business, but without any checks and balances to ensure that what they show is actually true, leading to confusion and great badness.
- harperlee 11y ago
  I would add a couple of things: I'd like asserts / rules / invariants / constrains that alert me when they are no longer valid. Everyone implements this with conditional formatting to get them red. Related, I don't like having three coding spaces: the cell value, the format formula, and possibly vba. Also, the whole book/sheet/cell hierarchy is very limited. Complex formatting is complicated when a simple fix would do: just let me compose views of several sheets, so I can put tables side-by-side without the need for them to be aligned, break when I add columns to one table, etc. Support for tree-like structures is done typically by merging parent cells to span more. Just support trees as an alternative to tables! If the layout on point 3 would exist, this would be beautiful. A structure would be book/object-set-layout/cell, where objects can be: a tree, a table, etc. Formatting is part of an object and can be more easily templated, code is referenced, not created, in the objects, and is all apart - vba style. ResolverOne had this last one right. Having all code apart would mean easier version control, and easier separation of logic/presentation/data. Lastly, you could link objects from separate locations and authorization, and have a federated data model. But now I'm just overarchitecting :).
- noxToken 11y ago
  > horrifying and error-prone programming model, both hard to write and hard to reason about. I don't remember where I read this, but I heard that the basic Office Suite (Word, Excel, PowerPoint) is written with office workers in mind first. That is, Word isn't written for authors, Excel isn't written for database managers/big data specialists, and PowerPoint isn't written for motivational speakers. I think the idea of the basic Office Suite is that a user could grasp the basic functionality of the software without having to undergo any training. Second to that, tools and other functionality for those with specific needs (e.g. pseudo-database functionality, basic data visualization in Excel) could use the software where the project was small enough. Excel was given great programming features without a proper programming language to support it. This isn't to say that these programs are not feature packed or bad. They're kitchen sinks that want to make everyone happy.
  - collyw 11y ago
    The problem is that they are too easy to use. As such Excel gets used for everything data related. Access would be a far better choice in many of the cases I have seen, but it just doesn't make any sense unless you have had some sort of introductory course on databases.
dragonwriter 11y ago
The biggest problem with spreadsheets is that they are often used in lieu of more appropriate tools because of poorly considered IT policies and organizational practices which leave no other tools available to users and fail to provide adequate support for users on some tasks for which programming is required and other tools are more suitable.
- nekopa 11y ago
  Actually, I think this is the biggest strength of spreadsheets, that no one has taken advantage of yet. Most of what you term as poor IT policies is just what (in my personal experience) just users finding something that works for them. And software developers don't seem to take note of this. You are right that there are lots of better tools for tasks users use excel for, but 2 which spring to mind for me that devs haven't paid attention to are form/table design and databases: Form design: It used to (ok, still does) drive me crazy when I see excel used to make flyers and forms to be printed out. But for the users what could be easier than drawing a box on the screen, adding a line weight, and then drawing other boxes inside that using the convenient spreadsheet grid? Why hasn't someone designed an app that works that simply? Databases in excel are another monstrosity, but for a lot of users, all they want is a simple lay of listing things which they can then sort, and maybe sum a few totals on. Firing up a complete RDBMS is over kill for this use case. So I see the biggest problem with spreadsheets as why aren't we learning from the way people use them and putting this into action?
  - davelnewton 11y ago
    There are tools that do that, even more simply than Excel does. That's a separate issue, e.g., app discovery, corporate approval in some cases, and people unwilling to use the best tool for the job.
    - filipstachura 11y ago
      Or maybe those tools lack some features and that's why people decide to improvise with spreadsheets?
debacle 11y ago
There needs to be an easier way to transition from spreadsheets to an application. A spreadsheet that was based on programming principles rather than business principles would be fantastic. Also, a spreadsheet that was much more scriptable for someone who didn't want to learn VB.NET would be a dream, as a programmer. Disclaimer: I don't generally pay for software out of my own pocket.
- marekr 11y ago
  Do you mean transition from specific spreadsheets you would already have for that task? Or in a wider sense, e.g. as a company - gradually transition from using spreadsheets to using applications for some of the tasks?
  - debacle 11y ago
    I mean I should be able to take a spreadsheet, properly configured, and turn it into a CRUD app through some sort of wizard. Or, as a programmer, I should be able to massage an existing spreadsheet (to confirm data types, maybe add some validations) and have that be able to be wrung through a CRUD app generator. People use spreadsheets because they combine business logic, data, and a simple user interface into a single document. 99% of spreadsheets should actually be CRUD apps (in my experience, at least) but it's not worth the hassle of going that route until you already have close to 30 MB of unstructured, manually edited data.
  - BjoernKW 11y ago
    We're doing the latter (on a rather small scale, mind you), transitioning existing Excel-based applications to scalable, robust architectures: http://excelkoenner.de/ http://excelkoenner.de/ (German-only right now, sorry)
- digikata 11y ago
  I think that used to be Microsoft Access, but as a product I think it lost focus on the easy transition. I remember as an intern using a pre-Microsoft FoxPro (all text GUI) that was so easy to setup as an app backed by a database with CRUD forms & reports.
tmaly 11y ago
I have generated thousands of reports in the form of excel spreadsheets. Here are my biggest gripes 1. lack of good meta data so I can process spreadsheets after they have been reviewed. Right now I use named ranges with a keyword but its very limiting. 2. lack of a good build in scripting language that is easily to use, has good 3rd party libraries, and makes automation easy. I am not big on excel macros, it would be nice to have a lua or python built in as an addon. 3. difficult/not straight forward to interact with external data sources. It would be great if I could easily pull data in from a web service. I guess if #2 was there, this might be easier.
- sankethkatta 11y ago
  2. Checkout Data Nitro (YC S12) https://datanitro.com/ https://datanitro.com/ It allows you to script excel with Python.
- Velox 11y ago
  Depending on what you are trying to "script" with it, Add-Ins for Office might be good to check out? They are more focused on user interaction rather than scripting currently, but may be useful? They are written using HTML and JavaScript: https://msdn.microsoft.com/en-us/library/office/fp142185.aspx https://msdn.microsoft.com/en-us/library/office/fp142185.asp...
Bostonian 11y ago
In spreadsheets you often repeat the same formula many times. In a programming language you would not write C2 = B2/B1 - 1 C3 = B3/B2 - 1 C4 = B4/B3 - 1 ad infinitum. You would store the B's and C's in arrays and write a loop, or use array operations in languages that supported them. In Excel, if you have stock prices in column B, you compute stock returns in column C by entering "=B2/B1 - 1" in cell C2 and copying the formula for the whole column. But nothing stops you from inadvertently changing the formula in cell C100.
- pc86 11y ago
  Not exactly a fix to what you're referring to, but if the data is formatted as a table the formula is copied to every cell automatically.
  - ForEnglandJames 11y ago
    Have used exactly this feature in a freelancing gig to help contain data entry/formula copying errors. I just wish it were less obtuse to set up.
- jotux 11y ago
  >But nothing stops you from inadvertently changing the formula in cell C100. I'm not sure how recent this feature is but excel will actually warn you in you change a formula in the middle of a series of cells that all use the same formula. I was getting "inconsistent formaula" warnings just yesterday when I was trying to make a spreadsheet.
- eggoa 11y ago
  You can use array formulas to make one formula operate on many values at once. In your example, there'd be one same formula in C2:C4 which would be {=B2:B4/B1:B3-1}. When I had to use Excel a lot I would follow this pattern as much as possible. (As a side effect, every normal Excel user I worked with hated maintaining my workbooks.)
- misterdata 11y ago
  Use Excel's tables feature, and it will more or less force a column to contain the same formula for each row (or a numeric value). In these formulas you can refer to other cells in a row by column name. https://support.office.com/en-ca/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c https://support.office.com/en-ca/article/Overview-of-Excel-t...
brudgers 11y ago
Product Owner Perspective: Spreadsheets are extremely powerful, highly flexible, ubiquitous and very easy to use. If that's a problem then we need more problems. It is trivial to make a trivial spreadsheet but the last real competitor to Excel was Borland Quattro Pro in the early 1990's. Google has to give away their product.
- SteveLAnderson 11y ago
  Person that works with a product owner that likes spreadsheets perspective: Spreadsheets are an incredibly painful way to track product work. We really track our work other ways that actually help us, then, we duplicate that tracking by pasting it into the spreadsheet so that you are happy.
  - brudgers 11y ago
    Tracking product work is just one use case. The power of spreadsheets is their flexibility and general applicability. I would be sympathetic to your plight, but I had a boss who wrote letters in Lotus 123 in the DOS days. There was no place from which to copy and paste because there was no multi-tasking. The only upside is that Lotus 123 was better than edlin.
jtfairbank 11y ago
Hospital staffing schedules are created and distributed via excel. It's really bad... and part of why healthcare is so expensive. I started ReSchedule Med [0] to tackle this problem, starting with medical education (because they have the most complicated schedules). [0]: https://reschedulemed.com https://reschedulemed.com
jaaames 11y ago
-Write locks/file ownership. Bob maintains the master schedule spreadsheet and no one else is allowed to touch it. Mary gets given access and leaves it open and goes to lunch and Bob can't update it for the rest of the afternoon. -Multiple sources of the truth. I've seen spreadsheets used in lieu of professional estimating tools for pricing jobs, and been abused because I wasn't use the "latest version" of the spreadsheet that got cc'ed around at 11pm called estimate July 24 v11a LATEST.xlsx -Fudge factors and spaghetti formulas. You can take a nearly complete data set and essentially manipulate it to tell any story with inconsistent formulas, hidden columns, references from other sheets etc. -VBA extends capabilities but is has some serious performance issues, doesn't implement objects correctly, doesn't handle errors well, code can't be version controlled. -Gets used to do a poor job of purpose built software that the business has licenses for, like estimating/scheduling/MRP because of a lack of training. -Business logic usually isn't apparent or documented and has to be reverse engineered to be understood. -Propagates silos within businesses as they decentralise data and tend to diverge.
- martinwnet 11y ago
  Does Google Sheets not solve a lot of these issues?

29 points by filipstachura 11y ago

In my experience working in some "speadsheet heavy" businesses: - complecting 'data' and 'presentation of data' together - lack of tools to help with 'correctness' - lack of version control - horrifying and error-prone programming model, both hard to write and hard to reason about. - spreadsheets full of pivots and charts rapidly become the 'truth' of a business, but without any checks and balances to ensure that what they show is actually true, leading to confusion and great badness.

harperlee 11y ago
I would add a couple of things: I'd like asserts / rules / invariants / constrains that alert me when they are no longer valid. Everyone implements this with conditional formatting to get them red. Related, I don't like having three coding spaces: the cell value, the format formula, and possibly vba. Also, the whole book/sheet/cell hierarchy is very limited. Complex formatting is complicated when a simple fix would do: just let me compose views of several sheets, so I can put tables side-by-side without the need for them to be aligned, break when I add columns to one table, etc. Support for tree-like structures is done typically by merging parent cells to span more. Just support trees as an alternative to tables! If the layout on point 3 would exist, this would be beautiful. A structure would be book/object-set-layout/cell, where objects can be: a tree, a table, etc. Formatting is part of an object and can be more easily templated, code is referenced, not created, in the objects, and is all apart - vba style. ResolverOne had this last one right. Having all code apart would mean easier version control, and easier separation of logic/presentation/data. Lastly, you could link objects from separate locations and authorization, and have a federated data model. But now I'm just overarchitecting :).
noxToken 11y ago
> horrifying and error-prone programming model, both hard to write and hard to reason about. I don't remember where I read this, but I heard that the basic Office Suite (Word, Excel, PowerPoint) is written with office workers in mind first. That is, Word isn't written for authors, Excel isn't written for database managers/big data specialists, and PowerPoint isn't written for motivational speakers. I think the idea of the basic Office Suite is that a user could grasp the basic functionality of the software without having to undergo any training. Second to that, tools and other functionality for those with specific needs (e.g. pseudo-database functionality, basic data visualization in Excel) could use the software where the project was small enough. Excel was given great programming features without a proper programming language to support it. This isn't to say that these programs are not feature packed or bad. They're kitchen sinks that want to make everyone happy.
- collyw 11y ago
  The problem is that they are too easy to use. As such Excel gets used for everything data related. Access would be a far better choice in many of the cases I have seen, but it just doesn't make any sense unless you have had some sort of introductory course on databases.

dragonwriter 11y ago

The biggest problem with spreadsheets is that they are often used in lieu of more appropriate tools because of poorly considered IT policies and organizational practices which leave no other tools available to users and fail to provide adequate support for users on some tasks for which programming is required and other tools are more suitable.

nekopa 11y ago
Actually, I think this is the biggest strength of spreadsheets, that no one has taken advantage of yet. Most of what you term as poor IT policies is just what (in my personal experience) just users finding something that works for them. And software developers don't seem to take note of this. You are right that there are lots of better tools for tasks users use excel for, but 2 which spring to mind for me that devs haven't paid attention to are form/table design and databases: Form design: It used to (ok, still does) drive me crazy when I see excel used to make flyers and forms to be printed out. But for the users what could be easier than drawing a box on the screen, adding a line weight, and then drawing other boxes inside that using the convenient spreadsheet grid? Why hasn't someone designed an app that works that simply? Databases in excel are another monstrosity, but for a lot of users, all they want is a simple lay of listing things which they can then sort, and maybe sum a few totals on. Firing up a complete RDBMS is over kill for this use case. So I see the biggest problem with spreadsheets as why aren't we learning from the way people use them and putting this into action?
- davelnewton 11y ago
  There are tools that do that, even more simply than Excel does. That's a separate issue, e.g., app discovery, corporate approval in some cases, and people unwilling to use the best tool for the job.
  - filipstachura 11y ago
    Or maybe those tools lack some features and that's why people decide to improvise with spreadsheets?

debacle 11y ago

There needs to be an easier way to transition from spreadsheets to an application. A spreadsheet that was based on programming principles rather than business principles would be fantastic. Also, a spreadsheet that was much more scriptable for someone who didn't want to learn VB.NET would be a dream, as a programmer. Disclaimer: I don't generally pay for software out of my own pocket.

marekr 11y ago
Do you mean transition from specific spreadsheets you would already have for that task? Or in a wider sense, e.g. as a company - gradually transition from using spreadsheets to using applications for some of the tasks?
- debacle 11y ago
  I mean I should be able to take a spreadsheet, properly configured, and turn it into a CRUD app through some sort of wizard. Or, as a programmer, I should be able to massage an existing spreadsheet (to confirm data types, maybe add some validations) and have that be able to be wrung through a CRUD app generator. People use spreadsheets because they combine business logic, data, and a simple user interface into a single document. 99% of spreadsheets should actually be CRUD apps (in my experience, at least) but it's not worth the hassle of going that route until you already have close to 30 MB of unstructured, manually edited data.
- BjoernKW 11y ago
  We're doing the latter (on a rather small scale, mind you), transitioning existing Excel-based applications to scalable, robust architectures: http://excelkoenner.de/ http://excelkoenner.de/ (German-only right now, sorry)
digikata 11y ago
I think that used to be Microsoft Access, but as a product I think it lost focus on the easy transition. I remember as an intern using a pre-Microsoft FoxPro (all text GUI) that was so easy to setup as an app backed by a database with CRUD forms & reports.

tmaly 11y ago

I have generated thousands of reports in the form of excel spreadsheets. Here are my biggest gripes 1. lack of good meta data so I can process spreadsheets after they have been reviewed. Right now I use named ranges with a keyword but its very limiting. 2. lack of a good build in scripting language that is easily to use, has good 3rd party libraries, and makes automation easy. I am not big on excel macros, it would be nice to have a lua or python built in as an addon. 3. difficult/not straight forward to interact with external data sources. It would be great if I could easily pull data in from a web service. I guess if #2 was there, this might be easier.

sankethkatta 11y ago
2. Checkout Data Nitro (YC S12) https://datanitro.com/ https://datanitro.com/ It allows you to script excel with Python.
Velox 11y ago
Depending on what you are trying to "script" with it, Add-Ins for Office might be good to check out? They are more focused on user interaction rather than scripting currently, but may be useful? They are written using HTML and JavaScript: https://msdn.microsoft.com/en-us/library/office/fp142185.aspx https://msdn.microsoft.com/en-us/library/office/fp142185.asp...

Bostonian 11y ago

In spreadsheets you often repeat the same formula many times. In a programming language you would not write C2 = B2/B1 - 1 C3 = B3/B2 - 1 C4 = B4/B3 - 1 ad infinitum. You would store the B's and C's in arrays and write a loop, or use array operations in languages that supported them. In Excel, if you have stock prices in column B, you compute stock returns in column C by entering "=B2/B1 - 1" in cell C2 and copying the formula for the whole column. But nothing stops you from inadvertently changing the formula in cell C100.

pc86 11y ago
Not exactly a fix to what you're referring to, but if the data is formatted as a table the formula is copied to every cell automatically.
- ForEnglandJames 11y ago
  Have used exactly this feature in a freelancing gig to help contain data entry/formula copying errors. I just wish it were less obtuse to set up.
jotux 11y ago
>But nothing stops you from inadvertently changing the formula in cell C100. I'm not sure how recent this feature is but excel will actually warn you in you change a formula in the middle of a series of cells that all use the same formula. I was getting "inconsistent formaula" warnings just yesterday when I was trying to make a spreadsheet.
eggoa 11y ago
You can use array formulas to make one formula operate on many values at once. In your example, there'd be one same formula in C2:C4 which would be {=B2:B4/B1:B3-1}. When I had to use Excel a lot I would follow this pattern as much as possible. (As a side effect, every normal Excel user I worked with hated maintaining my workbooks.)
misterdata 11y ago
Use Excel's tables feature, and it will more or less force a column to contain the same formula for each row (or a numeric value). In these formulas you can refer to other cells in a row by column name. https://support.office.com/en-ca/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c https://support.office.com/en-ca/article/Overview-of-Excel-t...

brudgers 11y ago

Product Owner Perspective: Spreadsheets are extremely powerful, highly flexible, ubiquitous and very easy to use. If that's a problem then we need more problems. It is trivial to make a trivial spreadsheet but the last real competitor to Excel was Borland Quattro Pro in the early 1990's. Google has to give away their product.

SteveLAnderson 11y ago
Person that works with a product owner that likes spreadsheets perspective: Spreadsheets are an incredibly painful way to track product work. We really track our work other ways that actually help us, then, we duplicate that tracking by pasting it into the spreadsheet so that you are happy.
- brudgers 11y ago
  Tracking product work is just one use case. The power of spreadsheets is their flexibility and general applicability. I would be sympathetic to your plight, but I had a boss who wrote letters in Lotus 123 in the DOS days. There was no place from which to copy and paste because there was no multi-tasking. The only upside is that Lotus 123 was better than edlin.

jtfairbank 11y ago

Hospital staffing schedules are created and distributed via excel. It's really bad... and part of why healthcare is so expensive. I started ReSchedule Med [0] to tackle this problem, starting with medical education (because they have the most complicated schedules). [0]: https://reschedulemed.com https://reschedulemed.com

jaaames 11y ago

-Write locks/file ownership. Bob maintains the master schedule spreadsheet and no one else is allowed to touch it. Mary gets given access and leaves it open and goes to lunch and Bob can't update it for the rest of the afternoon. -Multiple sources of the truth. I've seen spreadsheets used in lieu of professional estimating tools for pricing jobs, and been abused because I wasn't use the "latest version" of the spreadsheet that got cc'ed around at 11pm called estimate July 24 v11a LATEST.xlsx -Fudge factors and spaghetti formulas. You can take a nearly complete data set and essentially manipulate it to tell any story with inconsistent formulas, hidden columns, references from other sheets etc. -VBA extends capabilities but is has some serious performance issues, doesn't implement objects correctly, doesn't handle errors well, code can't be version controlled. -Gets used to do a poor job of purpose built software that the business has licenses for, like estimating/scheduling/MRP because of a lack of training. -Business logic usually isn't apparent or documented and has to be reverse engineered to be understood. -Propagates silos within businesses as they decentralise data and tend to diverge.

martinwnet 11y ago
Does Google Sheets not solve a lot of these issues?