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.
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 :).
> 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.
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.
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.
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?
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.
... No, my comment is specifically targeted at the example of making simple boxes, lines, and text. There are trivial tools for making diagrams where the barrier of entry is lower, and the ease of use is higher. I provided reasons why those tools aren't being used more.
The barrier to entry may be lower if you consider a user who has never used either software and is trying to make a decision. But if you have a user who has used Excel for the past 2 decades and is comfortable with it, low barrier to entry is still higher than just "go with what I know."
> Most of what you term as poor IT policies is just what (in my personal experience) just users finding something that works for them.
No, what I am terming "poor IT policies and organizational practices" is people being restricted as to what tools they are permitted to use in a blanket way that doesn't consider task specific needs, and having imposed difficult, inefficient, bureaucratic processes to get either permission to use other tools or support from the people that are permitted to use other tools, and/or which result in poor quality of support when those authorized to other tools do end up supporting them (often, again, because of inefficient, bureaucratic processes.)
You can disagree with my perception that this one of the biggest problems with spreadsheets, but please don't try to redefine what I'm actually talking about.
I apologize. I tried to make it clear that I was giving my opinion, via my personal experience. I'm sorry if you somehow feel I was trying to redefine what you are actually talking about. I was not. I was just trying to engage in conversation with you about people and spreadsheets. And maybe your terms, from what you have seen (or maybe you are talking about some kind of official research into why people do what they do with spreadsheets) are more true than what I've seen, but honestly, in my 25+ years of working with various businesses, I've yet to come across someone who worked strangely with spreadsheets because of company restrictions. Again, and I cannot stress this enough, this is just from my single data point, my own personal view. Not trying to redefine what you're talking about, just saying my opinion.
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.
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.
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.)
>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.
-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.
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.
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.asp...
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.
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.
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.
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.
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.
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?
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.
We're doing the latter (on a rather small scale, mind you), transitioning existing Excel-based applications to scalable, robust architectures: http://excelkoenner.de/ (German-only right now, sorry)
- 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.