So we’ve looked at some of the benefits, and there were some excellent comments.
But what is the single biggest downside to spreadsheet usage?
I was thinking the management of the uncontrolled duplication and ‘versionitis’ (good description Marcus), but actually I’m not sure that is unique to spreadsheets. I think it is most severe in spreadsheets, but I’ve seen that with Word docs too (‘which version of the spec are you working too? oh no, you should be using the one thats on my personal drive!’).
I reckon the biggest disadvantage of spreadsheet use is it encourages people to tangle up the data and a specific view of the data (eg a report). I think it was the Smalltalk community that introduced the MVC model (model, view controller), which seems pretty fundamental these days. MFC still uses the MVC approach, and I’m sure plenty of other dev tools do too. Access does for example (in fact all the database products I can think of do), you have tables for storing data, and queries or reports for seeing different views.
This lack of separation of data and view seems to me to be a barrier stopping many people making massive improvements in their use of data. I certainly think it explains why so many spreadsheet users can’t understand Access, even after training. I think it also explains why people often struggle with pivot tables.
I totally accept this feature is also one of the benefits, and is a contributory factor in the development speed associated with spreadsheeting. As with most things, sensible use will maximise the benefits whilst minimising the negative impacts.
What do you think about the separation issue? what do you think is the biggest spreadsheet downside?
Cheers
Simon
Smurf on Spreadsheets 
Friday, 16th March, 2007 at 1:02 pm |
Hi Simon,
I gave my views about disadvantages (including “Versionitis” – love that phrase!) in the last post, so I won’t repeat myself! (is that a sigh of relief?)
Regards data separation – I do what I recommend. I hold data in separate tables that are hidden, locked, and as secure as possible. I then build specific “extract” tools that users can access. The user selects what they want (e.g. specific results for a period), and it will format it for them in a nice and easy-to-understand way. These are also locked… If anyone wants to see the raw data, there is a “data mirror” which will pull what they’re interested and show them that data – but not in the raw data tables!!
So, although a user can save a report copy etc, and mess with it until it resembles nothing like the original, at least the raw data is as safe as possible, and the template tools are as well.
The very idea of letting users anywhere near the raw data fills me with abject terror!
As an aside, I do struggle with pivot tables, but that’s mainly (I think) because I came to VBA from a VB6 background, rather than to VBA from Excel as many users do – so my knowledge of the Excel functions is still very, very limited compared to many of the people who frequent this site.
Which is why I keep coming back and getting very useful info, for which you all have my gratitude! =:-)
Friday, 16th March, 2007 at 4:43 pm |
Hi all,
Excel is a presentation layer and therefore underlying data should be kept away from this layer. It does not necessarily mean that all data should stored in ‘databases’. XML- and old fashion textfiles are good places to hold data in.
I believe that the biggest downside are:
– that data and code is not separated
– security
This is the area where VSTO makes it worth to work with.
Kind regards,
Dennis
Friday, 16th March, 2007 at 11:32 pm |
For application in which spreadsheets make any sense other than as a handy grid control for display, VSTO is gross overkill. The underlying flawed concept here is that you get the worst of both worlds: you write lots of hybrid OO-procedural code, and you still have a spreadsheet that needs formulas and other stuff. If you have to write code anyway, why bother with the spreadsheet piece at all? If you’re only using Excel as a presentation layer, there are cheaper grid controls and report generators.
Check out http://integrals.wolfram.com/ as an example of a highly technical application that requires nothing more than a browser interface. My point being that if you’re going to perform the real processing outside Excel anyway, why inflict anything more than a browser interface on your users? Because it’s easier to ‘present’ using Excel? It’s elementary to fetch data from XML files with various & sundry schemas, but it just SO HARD to write browser/HTML user interfaces?
And even if you do write a wonderful VSTO application with an Excel front end, Excel 2007 will inflict its @#$%&*! ribbon on you with no way of hiding it. (Collapsing it still leaves the ribbon tabs, QAT and metastasized Office icon wasting pixels.)
While I agree that data which users don’t enter but fetch from online data sources should be separated from calculations and presentation layers, user entries are necessarily tied to the presentation layer and impractical to separate from some of the top-level calculations.
Saturday, 17th March, 2007 at 10:49 am |
Harlan,
>>If you have to write code anyway, why bother with the spreadsheet
>>piece at all?
Because clients require it.
>>My point being that if you’re going to perform the real processing >>outside Excel anyway, why inflict anything more than a browser >>interface on your users?
I can agree on that but solutions with dbs/cubes behind a Browser UI is not always suitable because it always require access to the intranet.
Kind regards,
Dennis
Saturday, 17th March, 2007 at 6:16 pm |
>I can agree on that but solutions with dbs/cubes behind a Browser UI is
>not always suitable because it always require access to the intranet.
If the data sources for the databases/cubes are on the intranet, you’d always need access to the intranet to access them, no?
If you mean databases/cubes stored locally on each machine (a potential data integrity/synchronization nightmare), it’s still possible to use a browser interface, but it’d require running backend processing software as intranet-like services. Unix-like systems can manage this (running web servers/daemons on ‘client’ machines, then accessing them from browsers on the same machines as localhost or whatever the machines’ domain names may be). Can’t Windows?
Sunday, 18th March, 2007 at 3:42 am |
Excel spreadsheets are email-able “Access” databases are not. That simple fact has messed with peoples heads too.
Excel is like water — Access is like a stone
Sunday, 18th March, 2007 at 10:33 am |
Hi Simon,
I don’t feel the MS Word comparison is entirely compatible. As a solutions platform they have their differences. Example: If a solution is template based (which is typical with MS Word), changes to the master template are propagated to the children. Change a paragraph style in the parent and it’s visible to the children. This isn’t the case with Excel, XLS children are oblivious to changes in an XLT parent. But I do agree that versionitis can occur in any uncontrolled, file based environment.
Side Note/
I can’t claim the term ‘versionitis’ as my own. I’ve used it so much
for so long it’s part of my vernacular even though I recall know where
I got it from. I think it’s a great word – some one out there deserves
credit, just not me.
/End Side Note
For me, potentially the biggest disadvantage of the Excel development environment is confidence. Yup, confidence. I’ve seen people’s eyes glaze over in Access courses when I mentioned 3NF or showed an ER diagram People will recoil and whither in intimidation. Not so Excel. If you can handle a calculator then Excel is your Oyster. After a few examples of COUNTIF, SUMIF and PMT and most students are Rockets Scientists in the making.
Let’s jump for a moment – I remember one uni lecturer telling us that one degree doesn’t make you smarter than another. What each degree does though, is teach you to think in a particular way (paradigm); in a way that has been ratified over a period of time by a body of knowledge by experienced practioners.
Enter Excel. As a trainer I saw that of the 100% of student who did the ‘Intro’ course, 50% would do intermediate. 10, maybe 20% would do Advanced. However, more than the number who attended advanced training are attempting advanced stuff in Excel. Doing a St. John first aid course does not prepare you for surgery.
So what we have is a populous who are inadequately trained or self-taught (guilty as charged, your Honour) who are attempting surgery with Excel after doing a First Aid course. These are accountants, trained in accounting. Bankers trained in international finance. However, few of these people can expand the acronym ‘SDLC’. It’s not in their training. As Excel’s strength comes from its flexibility, so does its failing. It’s powerful, yet simple, enough to be seductive to those who don’t know how to handle the grunt beneath the bonnet.
Case Study: The more I learn about Excel (typically a daily occurrence) the more I look back at prior projects and wince. As your grandfather lamented; “If only I’d known what I know now when I was your age”. I think I’m rambling now…
P.S.
Sorry EK-A-CELL, I’ve got to disagree with you there. Yes, Access databases can be emailed. What makes Access a more sinister platform, as mentioned above, is inadequate training. People with no knowledge (or understanding) of relational database principles are hacking away at business dependant database solutions.
Cheers – Marcus
Sunday, 18th March, 2007 at 5:22 pm |
Harlan
I think you may just have explained the niggling doubts I have had about VSTO. Don’t get me wrong I love the technology, but I think I agree with Harlan – is it really appropriate for spreadsheets?
I have never liked ‘dictator’ type wbs or add-ins, I think if you need to disable Excel features you should probably use a grid control.
I struggle to see an identifiable market for .net/office developer tools at the moment. 5 to 10 years maybe, but things could be very different by then.
I think we agree on the separaton issue, and the presentation is almost a personal choice thing. I have never seen another client as rich as Excel, have you? I worked with a guy once who spent months writing a javascript table sort feature for their web app. I just laughed, its one line in VBA, its tested, and its fast. He is the perfect example of a Visual Studio dev who wouldn’t dirty his hands using Excel as a component, he’d rather write from scratch (and so would most other VS devs).
cheers
Simon
Sunday, 18th March, 2007 at 9:41 pm |
Hi Simon,
“he’d rather write from scratch”
I think that some of this comes from two sources:
1. Perception: MSO dev is Mickey Mouse and couldn’t do what I need; and
2. Ignorance: You mean I could have done this with the OWC Grid control?
The OWC (OFfice Web Components) are very under-rated. Having ‘OFfice’ in the name seems to devalue them in some peoples’ eyes.
“client as rich as Excel”
I’ve had ‘real’ developers ask if I’ve ever worked with COM without realising that Excel is one big COM object with a rich hierarchy of objects, properties and methods as you’re going to get.
Monday, 19th March, 2007 at 3:10 pm |
Two things piss me off
1. Authors that say; Only the most trivial spreadsheets don’t use arrays.
2. Really really really looooooong conditional formulas that really don’t do much! And waste your time learning in then first place.
Monday, 19th March, 2007 at 5:25 pm |
Marcus – Mickey Mouse is spot on, that is exactly the attitude. On the plus side though I think that is why sys admins don’t bother locking down VBA.
I think Excel is one of the biggest (most properties/methods) COM components around?
Eek-a-cell – I don’t like any long formulas, but ones that dont do anything would be even more unwelcome.
Monday, 19th March, 2007 at 6:08 pm |
Simon, Harlan et al,
>>is it really appropriate for spreadsheets?
When large corporates port themselves to .NET and their developing department(s) are requested to use .NET then the question is quite obsolete.
Marcus,
OWC is a really underestimated package but keep in mind that MSFT made it totally wrong when they released the first version (9.0) with their license setup. Except for that MSFT has never pushed for the OWC package and now they don’t develop it further.
Kind regards,
Dennis
Monday, 19th March, 2007 at 11:16 pm |
Answers to questions may become obvious, but the questions don’t become obsolete. Looks like there’s an assumption that those corporate development departments were already doing most of their development work in Excel. Not where I work.
Tuesday, 20th March, 2007 at 12:14 am |
Office Web Components were very good, ruined by licencing I’d say. (they were really OIC – Office Intranet C..) There is no 2007 version so they are a bit of a dead end now I think. (as you said Dennis)
Re VSTO – I personally like working with this stuff, but really who are the customers? Most Office devs prefer the simplicity of VBA, most VS devs only want to do web apps. Where is the demand for .net/Office solutions going to come from? There seems to be strong demand for .net grid controls, judging by the variety available.
I don’t know of any corporate dev depts that would touch Excel. Its end user driven all the way in my experience. Maybe MS have seen something I’ve missed.
Maybe Excel services will get those corp devs interested?
Tuesday, 20th March, 2007 at 3:24 am |
Jim has an excellent explanation on MVC here:
http://moverve.com/blog/2007/03/separation-of-data-and-view/
I should have tried harder, but now I dont have to – Thanks Jim!
Cheers
Simon
Tuesday, 20th March, 2007 at 3:57 am |
Excel services? So you can treat multimegabyte spreadsheets as black boxes rather than write efficient code to do the same thing? Gosh, that get’s my juices flowing!
Excel is screwy enough as a mixture of UI and functional programming language, but mixing the two at least provides immediate gratification in terms of recalculation upon entry. Take away the UI, and you’re left with the world’s least efficient functional programming language. The only justification I’ve heard is that, “well, there’s all these spreadsheets out there already.” Fine. Use *EXISTING* spreadsheets with Excel services. Any developer suggesting using Excel services with *NEW* spreadsheet models should be fired on the spot.
Tuesday, 20th March, 2007 at 9:57 am |
Intresting stuff. Not sure a aggree with all of it! ;-)
I’ll only say one thing, I wrote a big dictor app – a modeling tool – a while back, it was “fun” but i dont think i would really undertake it agian. getting the UI to function (i used workbook sheets as the UI) was a pain, and not really good enough – maybe UF might have been better? But i think i’d look to do this in .Net or VB6 now, probally .Net to be fair.
Thursday, 23rd August, 2007 at 5:18 pm |
I’m an old lag, I confess it, and even worse, a computer techie by training not an accountant. I can just about accept the rationale for multipage workbooks and “canned” facilities like optimisation, pivot tables and the like as they, and the ready availability of functions to perform all sorts of tricky numerical computations, can save vast amounts of work and time.
The problem is that integrating a fairly low-level programming language (Basic) is simply an invitation to use the combination for things to which it is totally unsuited and extremely error prone.
All this was gone through years ago with notably assembly code and Fortran and the hard lessons learned. It does not follow simply because it is easy to prototype an application quickly with a tool like Excel that it is suitable for the development of error-free production code.
Wednesday, 11th February, 2009 at 12:26 pm |
Advantages of spreadsheets;
• It is a very useful tool for business to log their daily transactions and expenditures.
• Spreadsheets can compute by using some formulation for large amounts of data or repeated calculations.
• Allows the use of different functions including the SUM, AVERAGE, MIN, MAX, COS, COUNT, etc
• It can also be a very good tool for employee’s database to track down and record their salaries.
• Data can easily be visualized in form of charts.
• Spreadsheets answer “What if” questions.
• Computations can be done very quickly.
• Data can easily be updated in a spreadsheet than on paper-based accounting sheets.
• Spreadsheets are frequently used for financial information because of their ability to re-calculate the entire sheet automatically after a change to a single cell is made.
Wednesday, 11th February, 2009 at 1:01 pm |
One disadvantage of spreadsheets is that;
If you make an error in your formula, every single calculation done on that spreadsheet will be wrong.
http://kamz.110mb.com/index.php?p=1_3_Contact
Wednesday, 17th June, 2009 at 11:43 pm |
To my point of view a spreadsheet should not have any of its disadvantages because for me using a spreadsheet to do my project is that: easier to perform calculations ie,functions and formulas can be used,easy to represent data on charts and graphs ie,interpreting data from charts and graphs and comparing the relationship between all data items.
Wednesday, 17th June, 2009 at 11:50 pm |
wel for me,i don’t have any disadvantages of spreadsheet coz professional presentation do occur ie,accounts and other data is professionally presented using a spreadsheet nd also faster access in using mathematical operation.xoxo