All spreadsheet stories are horror stories. ;) Glibness aside, spreadsheets serve a very specific purpose: Everyone needs software; but Professional software developers are very expensive (and...
All spreadsheet stories are horror stories. ;)
Glibness aside, spreadsheets serve a very specific purpose:
Everyone needs software; but
Professional software developers are very expensive (and let's be real, we have a bad attitude—however justified—about the sort of programming that's typically done in spreadsheets); but also
Spreadsheets are sufficiently accessible that non-programmers can pick them up and meet their own needs, and also rarely well-controlled by business processes.
Of course, the net result is non-programmers writing software in an environment neither designed nor well-suited for it, which is why every non-trivial spreadsheet "application" is an incomprehensible, buggy trainwreck. However, trying to ban spreadsheets is either futile or starts us on an endless treadmill of programmable tools; programmability is incredibly powerful, and thus will always be a desirable feature, but the difficulties with managing it are evidently inherent. (We wouldn't have "software developer" as an independent discipline if software development were easy to get right.) So to me, the much more interesting question is, how can you make the situation less awful without trying to throw out spreadsheets as a tool?
This question is pretty open-ended, but I have a few thoughts:
Excel is a mess. I realize I'm tempting fate, but it feels almost pessimal in the space of tabular data tools. It carries along bugs from earlier versions of itself, and even from its '80s predecessor Lotus 1-2-3, gleefully mangles data on input, and generally does not set its users up for success. And then to reinforce all that, its incredible popularity means it becomes the template on which all other spreadsheet software is based! We need to lose Excel, try our best to forget it ever happened, and set spreadsheet users up on less actively hostile foundations.
Spreadsheets should structure their data more like a table and less like a sheet of graph paper. The latter is convenient for screwing around ("oh, I'll just put this temp value in this cell over here"), but rapidly becomes a liability the moment you need any kind of structure in your spreadsheet.
Speaking of structure, the ability to straightforwardly specify a meaningful schema for your spreadsheet. It's possible to set restrictions on cell values in Excel, but Excel being Excel, this is by cell (not, as would be much more reasonable, by row or column), and it's not easy to use the feature, and significantly more difficult to get it right.
This is why Access has been part of Office for decades; to allow users to develop applications if they really need to. It replaces the data structures of a spreadsheet with an actual grown-up...
This is why Access has been part of Office for decades; to allow users to develop applications if they really need to. It replaces the data structures of a spreadsheet with an actual grown-up database, so your data is going to be treated right.
But unfortunately excel has so much capability that every problem appears to be excel-shaped.
Your second bullet point is, I think, one of the most important. I learned recently that google sheets does not even have a way to define a table. You can do filter ranges, and named ranges, but...
Your second bullet point is, I think, one of the most important. I learned recently that google sheets does not even have a way to define a table. You can do filter ranges, and named ranges, but then you have to remember to update every place where you define the “table” if you change it.
Excel actually supports tables, which is great.
Even better, in my opinion, is Apple pages. It is admittedly much less powerful than excel. But it does use the dedicated tables paradigm that you mention.
I recently got my job to start using Airtable. It’s a SAAS mixture of a database and spreadsheet that is super powerful. Most importantly, it is accessible for non programmers.
I think we can agree these issue are all very common, even when they don't lead to loss of millions of dollars. I see some of these issues in my own spreadsheets. The most complex one I've been...
I think we can agree these issue are all very common, even when they don't lead to loss of millions of dollars. I see some of these issues in my own spreadsheets. The most complex one I've been managing is my budgeting spreadsheet.
Which honestly shouldn't be too complicated as it is basically 3 parts. Income aggregation, subtracting expense and savings goals, and then calculating distributions to accounts from there. But excel has a way of getting away from you and something that should be simple turns into a fragile web of cell references that is hard to maintain or modify.
EUSprig are the EU Spreadsheet Risks Interest Group. The Horror Stories are examples of some of the problems caused by using, sometimes misusing, spreadsheet software. EUSPRIG also have research...
EUSprig are the EU Spreadsheet Risks Interest Group. The Horror Stories are examples of some of the problems caused by using, sometimes misusing, spreadsheet software. EUSPRIG also have research (and they've commissioned some themselves) about spreadsheet errors. http://www.eusprig.org/basic-research.htm
I like the fact that they link to resources for best practice, and they're careful to avoid saying "this is easy" -- they seem to recognise that anything involving humans is complex. http://www.eusprig.org/best-practice.htm
All spreadsheet stories are horror stories. ;)
Glibness aside, spreadsheets serve a very specific purpose:
Of course, the net result is non-programmers writing software in an environment neither designed nor well-suited for it, which is why every non-trivial spreadsheet "application" is an incomprehensible, buggy trainwreck. However, trying to ban spreadsheets is either futile or starts us on an endless treadmill of programmable tools; programmability is incredibly powerful, and thus will always be a desirable feature, but the difficulties with managing it are evidently inherent. (We wouldn't have "software developer" as an independent discipline if software development were easy to get right.) So to me, the much more interesting question is, how can you make the situation less awful without trying to throw out spreadsheets as a tool?
This question is pretty open-ended, but I have a few thoughts:
This is why Access has been part of Office for decades; to allow users to develop applications if they really need to. It replaces the data structures of a spreadsheet with an actual grown-up database, so your data is going to be treated right.
But unfortunately excel has so much capability that every problem appears to be excel-shaped.
Your second bullet point is, I think, one of the most important. I learned recently that google sheets does not even have a way to define a table. You can do filter ranges, and named ranges, but then you have to remember to update every place where you define the “table” if you change it.
Excel actually supports tables, which is great.
Even better, in my opinion, is Apple pages. It is admittedly much less powerful than excel. But it does use the dedicated tables paradigm that you mention.
I recently got my job to start using Airtable. It’s a SAAS mixture of a database and spreadsheet that is super powerful. Most importantly, it is accessible for non programmers.
I think we can agree these issue are all very common, even when they don't lead to loss of millions of dollars. I see some of these issues in my own spreadsheets. The most complex one I've been managing is my budgeting spreadsheet.
Which honestly shouldn't be too complicated as it is basically 3 parts. Income aggregation, subtracting expense and savings goals, and then calculating distributions to accounts from there. But excel has a way of getting away from you and something that should be simple turns into a fragile web of cell references that is hard to maintain or modify.
EUSprig are the EU Spreadsheet Risks Interest Group. The Horror Stories are examples of some of the problems caused by using, sometimes misusing, spreadsheet software. EUSPRIG also have research (and they've commissioned some themselves) about spreadsheet errors. http://www.eusprig.org/basic-research.htm
I like the fact that they link to resources for best practice, and they're careful to avoid saying "this is easy" -- they seem to recognise that anything involving humans is complex. http://www.eusprig.org/best-practice.htm
This is awesome and horrifying. There are tons of stories over on The Daily WTF about abuses of spreadsheets if you're into that sort of thing.