The Subtle Tyranny Of Spreadsheets

pipingguy writes "I found this link on a CAD-related mailing list which questioned the current state of spreadsheet usage. Since using spreadsheets is often only one step away from PowerPoint mastery, I thought it worthy of submission." An excerpt: "The second distortion caused by conventional spreadsheets is more subtle. It's described in a 1980s paper, written by university researcher Jeffrey Kottemann and others concerning what they called 'Performance, Beliefs, and the Illusion of Control.' The paper described an experiment in which subjects were asked to perform a planning task using different tools, some of them with elaborate what-if capability and others without it." Yup, it's a ZD/Yahoo link, but it raises good questions."

Comments

please everybody

[evil_one666]

for the love of god, stop misusing spreadsheets/excel as databases- They are for calculating numbers, not creating lists of things!!!!!!

Re:please everybody

[zyridium]

That makes absolutely no sense at all.

Excel is perfect for creating lists of things, and being used as a way of storing simple data...

If you want to use that data for other purposes or it is at all complex, then sure, don't use excel.

What is a set of numbers, what about a list of data with associated figures, get real...

Re:please everybody

[REBloomfield]

Problem being, that the data isn't 'related' in any sense, and when a user manages to move the data in one column, and then it's out of sync with everything else, they call me up and whine....

Re:please everybody

[zyridium]

Sure, but all of the functionality in a spreadsheet is targeted at working with data.

The best solutions with complex data are to embed or link to 'real' databases (or even other spreadsheets, they are after all just tables) from within the spreadsheet... But if you want to get so sensitive about it then you would only ever use a spreadsheet as an analysis tool, and never enter data.. which I think we would all agree is stupid for simple tasks :)

Re:please everybody

[baryon351]

I've ranted about something similar to this before, but occasionally in the print business I worked at we would get Excel documents to print.

No, they didn't want printed spreadsheets - people would lay out flyers, leaflets, posters and small booklets in Excel.

I can only guess their creative genius had to be instantly addressed and they picked the first app they could think of to lay it out on, and excel was just sitting there loaded at the time.

Re:please everybody

[biobogonics]

stop misusing spreadsheets/excel as databases- They are for calculating numbers, not creating lists of things

1. Blame AppleWorks first. Before excel it made spreadsheets like databases.

2. If you look at the history of the spreadsheet, you will see that VisiCalc was designed for "What If?" not large scale calculating work. I was taught that spreadsheets are for the display of information - not calculation.

3. Of course I don't even need a database for storing some kinds of information. An ordinary text file is actually good enough. For example my address book is a text file.

4. I think the greatest misuse of spreadsheets is in using them to consolidate financial data. It's seductive. You get to see what you are doing, you get visual feedback, but

a. data is not protected against alteration
b. formulas are not protected against alteration
c. there is no audit trail
d. you are using explicit formulas instead of looping over data files

5. Lastly, you can say to yourself when you use a spreadsheet, "Look Mom, I'm not programming." Pretty soon you are using Macros, then Word Basic then Visual Basic for Applications. Pretty soon you have a maintenance nightmare since you have spent more time getting immediate answers than you have spent in thinking about design.

6. Yet the usual database products are a disease in themselves. I think that relational databases are not the best for transaction processing. I prefer to use programming languages with built in database support.

7. Last, using a computer gives you the illusion that numbers are real. Printed numbers assume god like authority. But of course projections are not facts or reality, except perhaps in government or the business world!

Re:please everybody

[TelcusFreshbreeze]

Not that I condone using Excel for data handling but consider this. When most PC's come with Standard MSOffice (which includes Excel but not Access, which comes in Professional), what application are users gonna be doing all database type scenarios?

Microsoft have basically forced Excel to be the packhorse of data manipulation for the masses.

Re:please everybody

[supergiovane]

Excel as a database.

Re:please everybody

[zakezuke]

I find excel a wonderful powerful intermediary program because of it's ease of use to take a list of information that's delimited by a field, import it, export it delimited to another format.

I don't understand the problem using excel as a small database. If you outgrow it, just export the whole shabang... delimited by whatever your database software supports. Heck, there have been times I reccomended using excel when getting groups of 10 or more people together doing manual data entry. The data gets entered, it's organized, and easily incorperated.

No, excel is not a database, but a spreadsheet can be used for more thens then calculating numbers.

Re:please everybody

[Anonymous Coward]

I wish I could persuade my boss to give me data in an Excel spreadsheet rather than a PDF produced by Word. At least I can save a spreadsheet as CVS and parse it for entry into a database.

Re:please everybody

[MrIrwin]

"For example my address book is a text file." I knew an AiX programmer who joted down phone numbers and many other things using a script:

Add

All this script did was make a new script file called "key" which echoed the value.

So if you just typed the key at the command prompt the value came straight back (of course the *nix cmd line offers many variations for retrieval!).

Dread to think about inode usage if you did this on large scale thougth!

Re:please everybody

[apdt]

If you outgrow it, just export the whole shabang... delimited by whatever your database software supports.

You make it sound as though that's a trivial task. It can be as long as everyone who used the spreadsheet was disciplined about how they entered data. The problem is that that is rarely the case, and the spreadsheet doesn't enforce any data types etc.. Converting a series of data from a spreadsheet to a database can be a huge PITA. I've been there, it ain't pretty.

Heck, there have been times I reccomended using excel when getting groups of 10 or more people together doing manual data entry.

I'd be interested to know how you get 10 people sharing a spreadsheet. AFAIK most spreadsheet programs will only allow one person to open it at a time.... Of course then someone else selects the open a copy option because they need to be able to write to it, and you instantly have inconsistency or someone else's changes get overwritten.

No, a spreadsheet is not an option if more than one person is using it.

Re:please everybody

[Qube]

In the company I used to work for, they used Excel for drawing floorplans. The surveyors would go out and take pictures, then they'd come back with some sketched floorplans and stick it all in Excel, using coloured cells and arrows/circles/labels as they wanted, to show which pic was for which room. Then they'd embed that worksheet in their word doc.

It was horrible, and frequently they'd managed to change one small thing that would completely screw up the proportions of their worksheet.

I suggested several times that they got Visio or Autosketch or something, but they were too tight to pay for it, despite their average chargeout rates being 70gbp/hour and doing jobs worth 6 figures or more.

Re:please everybody

[LoboRojo]

Haven't you received 2-m long posters made on powerpoint? I've had! (to review, not to print...)

Re:please everybody

[sreeram]

Could you kindly expand on your argument?

There are already a lot of posts berating the use of Excel as a database. Yet, I have not seen a single clear argument why this is a Bad Thing. The closest someone has gotten to is saying how users might inadvertently delete columns or add unwanted formatting, etc.

That's really just the fault of the WYSIWYG mentality of MS Office applications (in certain cases, the formatting is a bonus, as you'll see below). I don't see anything inherently wrong with the "spreadsheet as a DB" concept.

Seriously, a spreadsheet IS a DB. Its rows and columns perform exactly the same functions as a DB's rows and columns. While a DB might have more features, such as primary keys, indexing and fancy querying, a spreadsheet fits the role if you don't want those extra features.

I should know. I use DBs extensively (MySQL and Oracle). I also use Excel quite a lot. I am in the statistics and decision analysis field, so I use DBs and Excel for a lot of number crunching.

But I also use Excel to store small lists. For example, I have in front of me a sheet containing conferences and journals (that are relevant to me), ordered by due date. Excel's conditional formatting allows me to highlight those conferences that are due soon and grey out those that are past. With a single click, I can sort based on other columns, such as ranking.

I fail to see why I should be forced to use the cumbersome SQL interface to do this. Unless I spend much time writing the necessary scripts, webpages and CGIs, I am not likely to get the same flexibility I have with Excel for manipulating the list. Excel does the job for me, with minimal effort.

I think a lot of people complaining here are doing so knee-jerk. Somehow, the attitude is that a DB is "sacred" and Excel is a bastard child. This is wrong. A DB is just whatever fits your purpose for storing data (or lists or whatever). It can be an Excel spreadsheet, an RDBMS, a flat text file or even an opaque file (think Data.fs in Zope). The wise man uses the right tools for the job, and doesn't slavishly adhere to misguided prejudices.

Re:please everybody

[Prowl]

If you outgrow it, just export the whole shabang... delimited by whatever your database software supports

Christ, you sound like a manager

Re:please everybody

[smittyoneeach]

I've written a couple of applications that use .xls files as an interface.
Idea being that you gan query some relational store, put lists of default values on a back tab, set named ranges to those lists, and then, on a front tab, use data validation to constrain the users to putting Correct Stuff in data rows.
Oh, and there is no macro code in the .xls, so we don't run afoul of security settings.
This is a back-to-the-future batch system. Blank forms go out as email attachments, and come back as email attachments. They are saved to a folder inexplicably named "inbox". When the time is right, we crack them open in turn and read them into our RDBMS, and then do reporting.
If the .xls form is simple enough, in MS Access, you can have an .xls link table stub, and 'mount' each response in turn, and excecute straight SQL to read it in. Very fast and secure.
More complicated stuff might require MS Access to instantiate Excel and open each .xls explicitely to map the response to the database.
I've opened some of these .xls forms under GNUMeric with great results.
Also, languages like Perl and Python can script COM objects like Access and Excel.
Furthermore, as this is very stand-alone, you could use SQLite without concurrency issues.
The biggest advantage of all is that you've blown off the whole web server mess. Obviously our problem domain is non-real-time, batch-able applications. But there are a lot of those. HTTP is great at what it does, but for shedule requests and what-I-did-this-week inputs (the two applications I've done in this mode), here is a way to do them that doesn't require much that isn't generally available and desktop-runnable.
The other key is that most business people are fairly cozy with a spreadsheet interface, and die rapidly confronted with an .mdb similar. So the fear factor is reduced.

Re:please everybody

[1010011010]

5. Lastly, you can say to yourself when you use a spreadsheet, "Look Mom, I'm not programming." Pretty soon you are using Macros, then Word Basic then Visual Basic for Applications. Pretty soon you have a maintenance nightmare since you have spent more time getting immediate answers than you have spent in thinking about design.

This is a common thing, in my (corporate) experience. Not much thought is put into how the business fundamentally goes about its tasks, but there is a lot of time spent, e.g., masturbating with time sheet data for salaried employees, etc.

Making things worse, Microsoft's tools encourage instant gratification over design: VBA, Office Macros, ASP and Visual Basic lend themselves not to rapid application devlopment, but stupid application development. It's so easy to tweak and reload that the "right" answer often ends up being the "easy" answer. It's development by instant gratification. The resulting "solutions" are often fragile and difficult to maintain. It's like Powerpoint for Programmers (referencing Tufte), in that the cognitive model of the tools distorts the outcome as much or more than it helps produce it. I'm not convinced that these convenience tools result in less time spent in development, either; quite the opposite. I think any amount of time spent in design and planning will be outweighed by all of the re-work that will usually have to be done because of the mindset the tools engender. This is overlooked because planning isn't a source of instant gratification (it seems to drag on forever, as it requires actual thinking) -- whereas development with tools like these is a source of instant gratification, thus masking their own consumption of your time.

Re:please everybody

[JanneM]

This is not intended as a flame or anything, but then what do you recommend?

I use Gnumeric for (among other things) a list of movies I have (about 80-100 rows). The fields are Movie name, category, and who (if anybody) has borrowed it at the moment.

Another spreadsheet "database" I have is an expanding table of the time taken for me to bicycle to work every morning; it is sort of fun (and motivating) to plot the long-time trend. The flexibility of the spreadsheet also allows me to experiment with various ways of displaying the data.

So, for the first application, I could use a simple text file. That would of course not really improve on using the spreadsheet, and the spreadsheet has better UI for editing single fields, compared to a text editor. Using a "real" SQL database, on the other hand, would be horrendous overkill for something this small, as would using some dedicated application just for tracking stuff like this.

For the second one, the choices are even fewer; if i can't plot the data, it's not usable at all. So, no text files, and no generic SQL interface.

So, _what_ should I use to keep track of these lists (and plot and mangle the data, in the case of my travel times)? I haven't found any other tool that comes close, but I am certainly open for suggestions.

Re:please everybody

[sdemelo]

This is quite true. For the typical user (usually not a /. reader), data is also much easier to understand and manipulate using Excel. Searching and manipulating in Access is much less intuitive, which makes Excel more popular and people are less likely to use Access, which means it's not as familiar ... you get the idea.

Re:please everybody

[Threni]

---
There are already a lot of posts berating the use of Excel as a database. Yet, I have not seen a single clear argument why this is a Bad Thing. The closest someone has gotten to is saying how users might inadvertently delete columns or add unwanted formatting, etc.
---

Some people are too hung up on what something was designed for, and overlook what it could be used for. Presumably they're against the Wright brothers use of bicycle parts for the construction of the first plane also.

Re:please everybody

[Advocadus Diaboli]

Excel is perfect for creating lists of things, and being used as a way of storing simple data...

Yes, I have a colleauge that thinks like this. The result is an Excel sheet that if you want to make it fit on one sheet of paper you'll need a microscope to read cells. And since he's updating this "information" every week you really would like a sort of diff week-1.xls week-2.xls to find out what changed. My time is too precious to search a thousand cells if they may contain information relevant for my job or not. So this document perfectly fulfills the ISO900x criteria but is not usuable for anyone else than the author.

Re:please everybody

[ChiaBen]

You shouldn't be forced to use SQL for manipulating data, you should be restrained from using Excel. ;) The reality of the differences between a spreadsheet and a database is that a spreadsheet lacks the data constraints (relationships) necessary to keep a user from entering bad data. A database can control this (data integrity) to a large degree (depending on your datamodel design).

An example I fight with daily is product attributes. I maintain a n ecommerce database with about 180,000 products, each of which would have, say, a color. The problem is that if I import data from a spreadsheet it might randomly insert spaces in the data (i.e. "Black " or " Black" instead of "Black"), whereas if I get the data entered through our tools, the user selects from a list of colors, and only if the choice doesn't exist do they add a new one.

You mention how people are doing a knee-jerk that 'DB's are sacred'. Yes, they are. So are spreadsheets, the problem is that people bastard-ize their use and end up confused about why they both exist, and how to use them.

Database = Data storage, data consistency, ease of data maintenance
Spreadsheet = Data analysis, data redundancy, lack of data integrity.

That's how I see it, anyhow.

Re:please everybody

[Rich0]

As long as your DB can fit in a single table, doesn't need transactional support and journaling, and doesn't need to be multi-user (at the same time), then excel can potentially do just fine.

For a quick list of some sort it is generally OK.

On the other hand, I've seen tons of spreadsheets with columns like:
Home Phone, Home Address, Business Phone, Business Address, Home Phone 2, Home Address 2, etc...

In cases like that the contact info should be in a child table with a 1-to-many relationship. Actually, if you have multiple customers in the same household maybe it should be many-to-many...

And that is where databases come into their own - they encourage better design of how data is stored, and when the database grows it makes data a lot easier to get at and manipulate.

If you only have 100 rows it really doesn't matter what you store it in. You probably would be able to store it with paper and pencil with little trouble...

Re:please everybody

[GigsVT]

If they sent it as a PDF, then he'd have never known that they originally made it in excel.

I also work at a printer. Crappy sent in files are a real problem, even though we wouldn't touch a Word or Excel doc with a 10 foot pole, our artists don't even have MS office installed.

Re:please everybody

[DataCannibal]

"Yet the usual database products are a disease in themselves. I think that relational databases are not the best for transaction processing. I prefer to use programming languages with built in database support."

I hope that I never have to book tickets using a credit card in any systems that you've been within ten miles of.

I also hope that your customers never trust you with business critical data.

If you can't see why, think about how you enforce data integrity and security if a customer asks you (or more likely someone else) to write, say, a new web interface to your data?

Re:please everybody

[morelife]

I use Excel constantly to do layouts for invoices, estimates, cards, presentation, etc. because of the precise sizing control. It looks professional, not cheesy at all, some of the stuff looks like it came from a printer.

As for the database aspect, Excel is well suited for a database table layout, that's one of it's principal uses. Not a relational database, but just simple tables, it great at. There's no reason you couldn't have an address book with hundreds of entries and a dialog box front end made with macros. I did this in the past, worked great.

Re:please everybody

[Awful Truth]

Your point #5 brushes with the real problem. I work in a large -- very large -- financial organization, and we often see users sneaking business code into various 'documents.' Their favorite is, of course, Excel.

When we ask our users, "why?" the answer is always "it's too much trouble to deal with you technology folks." They're willing to forgo robustness, auditing, data validation, etc. in order to escape the technology bureaucracy: Getting budgets and resources, all those damn planning meetings, dealing with System Administrators, and so on. They generally know the risks and limitations of using Excel but feel the advantage of getting quick results is entirely worth it.

Re:please everybody

[B'Trey]

This is an example of one person who does not understand how to organize information in a useful manner. It's a valid point, and quite frustrating if you're the poor sod who has to try to make sense out of his mess, but it doesn't invalidate the idea of using spreadsheets as simple databases. I've had to work with real databases that were designed by people who quite obviously had no clue what they were doing and had never heard of the concept of normalization. That isn't a strike against databases, it's just an indication of users in severe need of training.

Excel as a simple database has a number of advantages. It's portable - most business users have access to Excel, and OpenOffice imports Excel sheets quite well. It requires limited knowledge to get some use out of it. Even unsophisticated users can usually manage a simpe search for the data they're looking for, and can update records. A well designed Access database can be easy to use, but it's much less portable. Not all users have Access, and I'm not aware of any Linux app which will open an Access database directly. (You can export the database from Access and then import it into MySQL or other database server, but that's obviously a great deal more work.) A database run on any of various servers with a custom front end, either web based or not, can be easy to use but is obviously much more difficult to attach to an email.

If you need the power and robustness of a relational database, then use one. But for simple data collection functionality, particularly when portability is an issue, a spreadsheet works well.

Re:please everybody

[sumdumass]

I guess a real question would be, Is this the only way he knows howto do it? and is the real database's just not user friendly enough.

I remeber back in the dos days we had a dos invetory system that took data from a database and presented it in a psread sheet like way. Maybe the whole thought process is the magic behind the sceenes were the user doen't comprehend that there is more to it. i know i tryed to recreate the invetory system at home to catalog some music cd's and when I couldn't do it the way the other one did I found out why. Most people probally can't comprehend the differences between database that uses a spreadsheet like front end and the macro power of a spread sheet.

Re:please everybody

[JanneM]

Um, why do you want to fit it on a single sheet of paper? It's like saying "I tried to fit the book on a single sheet of paper and it's unreadable".

Re:please everybody

[generic-man]

PDFCreator gives you "print to PDF" capability in Windows. It's free software.

Re:please everybody

[archen]

It's called an ODBC connection. Just create a connector to you db of choice (I use Posgresql all the time). Most spreadsheets (Excel and OO Calc) then allow you view the database table through the spreadsheet.

I think the problem is that people use Excel as the database, not as a front end for a database (which it does okay). 90% of the time most people would be far better off using MS Access instead of the way they use Excel. The difference being that you have to make table fields in Access, whereas Excel you can just start typing away.

Re:please everybody

[4of12]

but stupid application development.

Stupid, yes, from the standpoint of maintainable code, efficient use of computer resources, best algorithms, etc. Absolutely stupid.

However, Stupid Application Development, however SAD, is often very useful in getting answers right now for people without a clue about intelligent application development, i.e., most of the people sitting in front of computers these days.

I think the best you can do under these circumstances is to have the underlying tools be more modular with interchangeable components and in layers of libraries. That way, when Joe Stupid's off-the-cuff application outgrows its context, John Intelligent can refit a new engine under the hood without Joe Stupid being any the wiser. I know Excel is not designed that way, but it ought to be, so Stupid and Intelligent Application Development can all work together for the best.

Re:please everybody

[jtwJGuevara]

The problem is that for everyone who is a non-techie and has never handled a large sized database or have been involved in extracting data from relational databases, they don't see the difference at all between a database and a spreadsheet. In the eyes of your average joe user, they both have rows and columns so they both must be used to store rows of information!

A real life example that still gets on my nerves to this day is when a co-worker in different department who knew I was a "computer person" asked me about creating an excel database. After trying to correct her about 3 times to no avail, I just gave up and said "sorry, I don't really work with Microsoft Office" and ran away.

Re:please everybody

[plover]

You write this like it's a bad thing that you can't import your non-sanitized data directly into a database.

Obviously you have never dealt with trying to give the clients what they want after someone else has polluted the database with their crappy imported data.

Here's a real example: our clients wanted to find all the KING size bedsheets. We looked them up and found that we've got those in 'KING ','King ','KNG ', 'K ', '76X80', and ' KING'. Sure, we had ten KING sized bedsheets that matched their request. But when our providers complained that their clients couldn't find the king sized bedsheets in these other styles, we had to point out that they filled the database with non-normalized values. Their solution was not to normalize their data ("that is too much typing for poor us, boo hoo,") but rather to tell us to give the clients a pull-down with every size of bedsheet we carry so they can pick it themselves. So we did, and now all the clients have to sift through literally 20 different abbreviations for four standard sizes. And God help the poor customer who just wants a king-sized bedsheet.

In the case you mentioned, you are a provider of data, not a consumer. As such, you are responsible for providing valid data; that is, data that will work for the consumers. In many (most?) cases, the data providers are not the data consumers. So there is a burden of responsibility on the providers to use data that will make sense to the clients. In our business, we have hundreds of providers who are assumed to be knowledgable and responsible for this data. We have tens of thousands of clients who are just trying to do their jobs, and finding things like king sized bed sheets is just one tiny, untrained, unrewarding aspect of their day. If you give them a search box and they type 'KING' and get many results (but not the one they want) they will rightly assume they did everything right and that the merchandise doesn't exist. They made no mistake, other than trusting that the data made sense.

And you whine because your DBAs won't let you import an unchecked spreadsheet. Cry me a river pal, but get your ass typing. Your unverified data is worse than worthless. It makes the real clients look stupid in front of the customers. And if that's not enough to make you care, look at it this more selfish and pragmatic way: if the clients can't find your merchandise, they can't sell it. You'll be the one justifying to your boss why nobody sold any ' KING' sized sheets.

Re:please everybody

[Night Goat]

One time when I was working at the computer lab help desk in college, I had a guy who was writing a paper in Excel, one word per cell! He'd just type a word, hit Tab, type the next one, and so on. The question he had was "How do I doublespace my paper?" I was dumbstruck.

Re:please everybody

[heck]

There are already a lot of posts berating the use of Excel as a database. Yet, I have not seen a single clear argument why this is a Bad Thing

I would berate anyone who used Excel IN PLACE OF what a database should be doing.

If you have a small team or just one person using a spreadsheet to make sense of numbers, that's fine. If you're storing numbers that many people have access to/crunch; you generate reports from those numbers and many people have access to the underlying numbers (and the code to generate the reports); or several other scenarios - you're using the wrong tool. Yes, the spreadsheet can do it - and it can work - but taking the extra time to do a DB and write code to do what you're doing through a spreadsheet will ultimately save you time and money.

Case in point 1 (spreadsheets used incorrectly): buddy of mine works for a car dealership. Someone wrote a monstrosity of an Excel spreadsheet that crunches numbers and creates reports. Every time they change the report layout or the way data is calculated my buddy has to scramble around updating all of the desktops with the new spreadsheet (yes, its on a share, but he still has to scramble around to fix issues)

Case in point two (a decent use of a spreadsheet where a more complex tool could do the job): My team has an objectives list, the tasks, who the tasks are assigned to, dates, etc. in an Excel spreadsheet. "Ah ha!" you say - that's something that should be in Project or in a database.

Why?

Project is overkill for what we - the team members - do. And its damn expensive to put on each of our desks. We document the tasks (and add links to the docs as we write the docs); sometimes we split the tasks up; and we mark down when we start and complete the tasks. That's our objectives for the spreadsheet. Da boss man (project manager) takes what we have in the spreadsheet and updates Project to do time forecasting, hour tracking, etc. (project manager does stuff we don't need to do - we just want to know what the task is, where the specs are, who is doing something, has it been done, etc.) Yes, there is duplication of work (we're entering some things twice, in effect) but Project doesn't (easily) do some of what we need to do and for the rest of what we need in "task list" Project is overkill.

My job is to look at the job, decide what tools I could use, and then to use the right tool for the job. I can often use flat text files, relational databases, spreadsheets, HTML, CSS, Java, C, C++, RPG, etc. - my job is to decide (based on known requirements and based on what I think future requirements may be) what to use. I very very rarely would recommend a spreadsheet to do anything complex. It's too hard to maintain data integrity; it's too hard to maintain version control; it's just too hard to maintain in comparison to some of the other choices. It will work for one time projects or small scale. It will even work for large scale. But just because it will work doesn't mean its the best solution.

Re:please everybody

[sreeram]

I get your general point, which is something I didn't dispute in the first place - that a DB gives you many more features than Excel.

But, what I don't get is people completely denying the use of Excel as a DB, and I don't even mean "simplistic DB". Excel really does have plenty of stuff for most common end-user DB needs.

For example, you (and others) complain that Excel doesn't provide strong typing of data. Quite the contrary. You can specify that a column can only contain dates, or numbers, or one of a predefined list of items, etc. Look under the "Data -> Validation" menu. Your specific example of product colours is easily handled by this.

Re:please everybody

[cavemanf16]

I agree wholeheartedly, which is why I can't yet use OpenOffice's spreadsheet app, Calc, all that much. For one, it doesn't hold more than 32k rows, and for two it doesn't have a very good "PivotTable/PivotChart" tool right now. I use Excel a lot to get aggregated data from millions of rows down to a more manageable 10's of thousands. From there I can begin analyzing seasonality trends, control charts, histograms of that data, etc. No database I know of has this kind of stuff built right in so that it's easy to manipulate like it is in Excel.

If OpenOffice could make their spreadsheet app as stellar in it's power and formattability (I know, not a real word) as its Writer app, then I'd ditch MS Office in an instant.

I've been preaching this for a while now on /. and I know I should be doing it on OpenOffice.org too, but I don't have that kind of time. Beef up that DataPilot, damnit!!!

Re:please everybody

[ibennetch]

There are other good suggestions here (a linux box running samba, works well and is pretty easy; and PDFCreator), but I'll throw in one more: Cute PDF Writer
Works real well; installs as a virtual printer probably like your "print to pdf" program that you're shelling out money for...CutePDF is free and works like a charm; however it requires installing GhostScript on the PC.

Re:please everybody

[smittyoneeach]

I submit that you're blaming the tool, not the user.
Technologies cited may lend themselves to myopic, tactical uses, but that is an unavoidable side effect.
You could, in the same spirit, blame the vagina for prostitution.
Furthermore, you don't offer an alternative. Do you want an MSWorks-type dumbsheet for the masses? What reasonably useful system do you propose when the cheesesheet isn't packing the heat? Something with an Emacs-derived keyboard interface for macro coding to keep out the riff-raff?
What about the heuristic problems that are simply going to be a muddle while requirements evolve, where total hackability is a feature? We treat design as some sort of Revealed Truth, a magic wand that will Save Us From The Fury of the Spaghetti Code. Ahem.
As noted elsewhere, making it easy for the usele^H^Hr to drum up business is far more feature than bug.

Re:please everybody

[DataCannibal]

I've no idea. What kinds of access is available for getting into these system and how are the data intgrity rules enforced ?

For your average RDBMS there are lots e.g. for Oracle there are SQLPLUS, Pro*C, ODBC, JDBV , ADO, etc etc. If anyone can get at your data through one of these methods and start twiddling with the data or data structures then you need to make sure that they can't twiddle things that they're not supposed to twiddle with and, if they are allowed to twiddle, then they musn't be able to break things. If you rely on implementing data rules in your client or middleware layer then someday someone will come along and build another client that twiddles around where ever it likes.

Re:please everybody

[Anonymous Coward]

I'm pretty sure he meant that the data can be sanitized before importing into the database. It's just the matter of a few sanity checks in the import routine. Sheesh.

Or, hell, make that cell on the input sheet a combobox holding only the valid entries. Jeez man, there's more than a handful ways to skin that particular cat.

He was saying he's glad he doesn't work there, because if the people that do work there can't solve this simple problem then the company is probably in big trouble.

Re:please everybody

[nyssa]

1. Blame AppleWorks first. Before excel it made spreadsheets like databases.

I believe Lotus 1-2-3 predates AppleWorks. It combined spreadsheet calculation, graphing, and data management into one program. Its success in the marketplace spawned many other "integrated" programs such as AppleWorks. Now that multi-tasking operating systems and inter-application communications are the norm, such integrated applications have dropped to the status of budget-buy suites.

Re:please everybody

[whittrash]

You are right on. I work with spread sheets from time to time and Autocad. They both have a similar problem when you get a complicated project. Without a clear normalized data structure you end up making a mess. People end up linking documents in ways that are incomprehensible to anyone but the original author. In the end, sometimes even the original author has problems. Finding errors is impossible. This may work for small projects, but for large team based projects it is a nightmare. People don't think of it as a database, if they did it would probably be more organized. Often times people manhandle a quick fix, but that just makes matters worse in the end. It is a huge waste of time and leads to shoddy quality. The biggest Excel error I have seen was made by a contractor for a cool $1 million. What do you say to that? OOOPs, my bad!

Re:please everybody

[Anonymous Coward]

It'd actually be pretty easy in this situation... just select-all and double the row-height.

Re:please everybody

[old man of the c]

We developed a web-based application that used Excel to format invoices for the clients. No computation was done by Excel. That was all done by the business logic using data stored in a real database. Excel was used entirely for its formatting capabilities. The really nice thing was, each customer could specify, through an administrative form, how they wanted things laid out (what data goes into which cell, where to display the company logo). When they wanted to print an invoice, they just clicked on an image button, Excel popped up with all the data filled in, and they could modify and print from there. The customers really liked it. As others have stated, it was something they were familiar with.

Re:please everybody

[Daytona955i]

You do know you can use perl (know for it's regular expressions) and very easily interface with just about any database?

Oh and you can use html or even tk to make an interface. The other great thing about perl is that when your company finally figures out windows does _not_ have a lower TCO and you switch to linux you can still use perl.

Excel is just another bloated app from a company that really knows bloat. I worked for one company that used excel to generate some reports and it took forever. (in comparison to a database with a good front end.)

Excel is ok when the data set is small but if you have a lot of data or a lot of calculation, use something else.

Re:please everybody

[cayenne8]

"I've had to work with real databases that were designed by people who quite obviously had no clue what they were doing and had never heard of the concept of normalization. That isn't a strike against databases, it's just an indication of users in severe need of training....A well designed Access database..."

Well, I find that Access itself is the cause for so many 'badly designed databases' that I've had to take apart, and redesign for a true RDBMS like Oracle. I've often joked that I wished that ms access would be banned from every desktop....because it gives power to those who don't understand databases. Generally, a PHB, puts together a 'database' to use for something in the office. Most of them I've seen, are one and two tables for everything....and many of them freeform text fields. No primary keys (joins? what are they?). Well, he shows it to others..they start using them...and soon it becomes the office defacto standard....multiple versions of them floating around.

Then, the PHB says 'hey, lets put this on Oracle'. Well, the "I" get the mess....and they just can't understand why it takes so long....just throw it on there. Yet, I have to interview him/others to learn the business rules they are wanting with this...create a normalized data model for this, instantiate it.

Then, comes the fun part...having to stand on your head to write scripts to parse, massage, and clean the data from the access mess...to insert into the real database.

Hey, I know it keeps me in a job...but, man, if they'd just leave it to the 'pros'...they'd get a good product out faster, that is flexible, scaleable, modular....and works for a good GUI front end, and most importanly, efficient and accurate report generation.

Re:please everybody

[morelife]

Use FileMakerPro or other relational database tool for this. It's easier than Excel to set up the layout, and it's much easier to integrate the database (say of employee names and office phone numbers) that lies behind the page.

Doing a small address book in Excel, was cheaper than buying a copy of Filemaker Pro.

It was a lot more interesting in Excel, because I wrote the macros and designed the dialog box layout for the front end. You didn't know you could do that in Excel -- DID you?:) Yeah, I made a nice dialog box with the necessary fields, about 25-30 of them, and that's where you'd do your queries, and enter new records too. Actually it served the purpose wonderfully, and I could easily add fields that did math based on contacts/calls etc, which I did, to nice effect. That was a few years ago...

And then I went heavily into drug and alcohol abuse, lost all my clients and contacts, and didn't need a database after all.

Re:please everybody

[Advocadus Diaboli]

Um, why do you want to fit it on a single sheet of paper? It's like saying "I tried to fit the book on a single sheet of paper and it's unreadable".

First of all because the "paperless office" is still an illusion. And I really prefer to go to a meeting with a piece of paper instead of relying on a laptop computer and then act like the other 10 fools in the meeting that play around with their laptop computers instead of focussing on the meeting.

Second because if someone uses a sort of database then he should be aware that one requrest that I have for a Database is that I can select the few records that I need and don't need the "noise" of 1000 other records. In my case that Excel-Sheet is a sort of database for PC hardware platforms and usually you want to focus on one mainboard without being disturbed by the other 50 mainboards, the 20 graphic cards and the 50 network controllers etc.

Of course I don't need to fit a book into one piece of paper (BTW: I once got a microfilm bible that was stamp size and was the whole bible, readable with a good microscope) because books are usually good organized. You have a table of contents and an index and even if I need 3 informations from the book I can put 3 bookmarks into it and good. On a "excel wallpaper" you just get lost when you can't limit the data to what is really of your interest.

The main problem with that thing was simple that in AB234 there was a deadline for a mainboard and from week "X" to week "X+1" the deadline changed and the Excel sheet was the medium to communicate this change. And then there was the "I don't know why you fuss around, the plans were there for 48 years on your local office at alpha centauri" (in memorian Douglas Adams) complaints when you didn't notice something. :-)

Re:please everybody

[OceanBarb]

4. I think the greatest misuse of spreadsheets is in using them to consolidate financial data. It's seductive. You get to see what you are doing, you get visual feedback, but

a. data is not protected against alteration

Arrrghhhhh! If you've ever lived through an entire department sharing an evolving unprotected spreadsheet with a new tab per month, used to generate monthly, quarterly and annual management reports and criefings for senior management, and had some *helpful* person decide to *update all links*, you have truly known the meaning of despair.

Re:Mods, please mod parent up.What, no Tux? (Happi

[flaez]

back in 1997 when I was a physics exchange student in Glasgow, they made me solve a *quantum machanics* problem using excel! it was ridiculous. I kept the spreadsheet just for its absurdity (it's the only .xls file on my entire harddrive)

Sometimes a little education is worse than none

[heironymouscoward]

A manager at a company I worked for was presenting figures for the last year. He showed the financial breakdown for each division, with the profit being calculated as a percentage for each division. At the bottom, there was a summary line showing the total figures for the company and including the "average profit" for the company.

Which he had calculated by summing the profit column and dividing by the number of divisions.

I mentioned that this was producing a somewhat unrealistic figure, with a couple of small divisions showing very good profit margins and the largest department showing a slight loss. "No, that's the mathematical definition of 'median'," he answered.

Re:Sometimes a little education is worse than none

[Vengie]

I always loved the term "measures of central tendency" since most people won't know what you're talking about, and those who do will know you're bullshiting but won't call you on it since you didn't insult their intelligence.

Re:Sometimes a little education is worse than none

[MichaelKaiserProScri]

Well, actually your guy computed the MEAN, not the MEDIAN. Mean is "sum of datapoints divded by number of datapoints". Median is "the center datapoint in an ordered set". To get median, you sort the data and take the center most datapoint if there are an odd number of datapoints and mean of the 2 center most if there are an even number of datapoints.

And on another note, if you have a summary report with each line having a median on it you can not get the grand total mean by taking the mean of the medians! It's even worse if you try to take the median of the medians! To get the grand total you have to go back to ALL the data points, order them, and take the central one. However if you do this, there is not a "pointy haired boss" around who can figure out why the "numbers don't add up"....

This is not an issue of spreadsheets, this is an issue of PHB's not understanding basic math.

Re:Mods, please mod parent up.What, no Tux? (Happi

[Lord of Ironhand]

a *quantum machanics* problem using excel

What's so strange about that? Both are highly unpredictable, so it should work pretty well.

Spreadsheets in the workplace

[faldore]

The only possible explanation I can think of for some of the Excel sheets I have seen in the workplace, is that the poor fool who wrote it didn't have a clue how to use Access. The kicker is when they come to me to write code to automate it. Excel is simply evil. The only thing its good for is making pretty charts.

Re:Spreadsheets in the workplace

[ekidder]

Excel is also really good for making d20 character sheets :)

The underlying problem...

[Sanity]

...is that most people don't really understand statistics, and tools like spreadsheets help people to forget this reality by blinding them with lots of authorative looking numbers.

The question is whether a tool can ever be a substitute for a good understanding of statistics and probability - or whether it will always be a case of monkeys playing with ever more sophisticated typewriters...?

Re:The underlying problem...

[theparanoidcynic]

I'd say that the answer is maybe. For example, I have absolutely no idea how a standard deviation is actually calculated, but I know what one is and I know how to make Matlab do one.

Re:The underlying problem...

[kidgenius]

No. A good statistics knowledge is fundamental in making sure that the data you are putting in is valid. As the old adage goes, "Garbage In, Garbage Out."
If you are just mindlessly putting stuff together and say "I think a median/mean/standard dev would go good here" then it's obvious that you shouldn't be doing statistical analysis. Also, after the numbers have been calculated, you need to understand what the significance of them are. I work in a highly statistical field (Reliability Engineering) and I will say that at times it really is a black art. Things may at first look good/bad, but until you sit down, and think about what it all means, you will have way of knowing whether what you just got out of your analysis is "correct."

Re:The underlying problem...

[misterpies]

Do you really know what the standard deviation is? For example, you know that most standard tools for calculating standard deviation it assume the data has a Gaussian distribution. But what if your data poisson distributed, or hypergeometric, or maxwell-boltzmann...Of course if you're taking the standard distribution of a set of averages then you're safe because the distribution of the mean is almost always Gaussian. but then, you knew that, right?

The real problem with statistics is that everyone thinks they understand them, and almost nobody does (including me, the above is a very hazy memory from high school and my first year of uni).

Re:The underlying problem...

[DarkSarin]

Read my sig. It came from someone describing the blind use of stats without really knowing what one is doing.

I am in another fairly statistics heavy field, but one where many people are not mathematically inclined. This leads to a lot of people doing exactly what you describe.

Personally I think even tools like SPSS (which is heavily used in my field) are dangerous because they lead one to doing analyses that don't make any sense given the data. SAS is better, as is S-Plus/R, since these require some understanding of your data.

By the way, if you can't calculate a particular stat by hand, or have never had to, then you really have no business using it or claiming you know what it is. Even a simple standard deviation is useless if you don't know what you are doing.

Means are probably the most dangerous and misused beasts. As an example, a university published in its campus paper that the average communications major from their university started with a salary of $80,000. The trouble is that this included a NBA starting player.

Now, you tell me, how many people that can calculate a mean would even stop to wonder about that. And if something that simple would get through, you had better believe that standard deviations get abused!

As a final note, excel should NEVER be used to calculate ANY statistics beyond a mean, since it uses patently WIERD formulas that DON'T always work (compare the answers against those gotten with SPSS or SAS for more complicated work and you'll be shocked).

Re:The underlying problem...

[mst76]

Do you really know what the standard deviation is? For example, you know that most standard tools for calculating standard deviation it assume the data has a Gaussian distribution. But what if your data poisson distributed, or hypergeometric, or maxwell-boltzmann... [...]

That is not correct. The standard deviation of a random variable is the square root of its variance. The variance is the squared expectation of the centralized variable (variable minus its mean). Calculating the variance of a random variable involves integrating (or summing) the probability density function p(x) times x^2. If it's a well known distribution, one would usually look it up in a book, or try to solve the sum or integral (by hand or with Maple or Mathematica). Note that the only thing you need to calculate the standard deviation is the distribution or density function, no actual data is involved.

The things listed as mean and standard deviation in Excel are sample means and sample standard deviations. If you have a list of numbers, and you assume that they were drawn from some distribution with finite expectation and variance, you can calculate the sample mean (simple average), which is an estimate of the expectation. Then you substract this mean from all your numbers and take their squares. The average of that is an estimate of the variance of the distribution, take the square root for the standard deviation. The nice thing is that these estimates converge to the expectation and variance regardless of distribution. If you do make the additional assumption of Gaussianity, you can also say something about the rate of convergence and the distribution of the estimate. But the basic formulas in Excel and such for sample mean and variance work for any distribution that has a mean and variance.

Re:The underlying problem...

[dr_canak]

"excel should NEVER be used to calculate ANY statistics beyond a mean, since it uses patently WIERD formulas that DON'T always work (compare the answers against those gotten with SPSS or SAS for more complicated work and you'll be shocked)"

amen to that brother,

And I agree that stats packages in the hands of people not familiar with statistics is a disaster. I see it on an almost daily basis, where people have the stat package in front of them and just assume the output is correct without any understanding if the numbers make sense or not. They just go right down to the p-value and call it day. It's almost shocking to me the errors I've seen people make in their data entry and analysis that simply go unchecked. And i've seen very bright, competent people fall into this trap time and time again. It really makes me question just about all research in any field for reasons too numerous to mention here.

And forget about using Excel to do stats (which is a horrible idea). Even stats packages in the hands of knowledgable users can be dangerous. As an example, a few years back I was learning Minitab and SigmaPlot. I had some analyses from an SPSS run that i was running through Minitab and SigmaPlot for nerdy interest. Low and behold, the results were different. Fortunately, two of the three matched (SPSS and SigmaPlot) which led me to believe Minitab was wrong in its calculation. And this wasn't some esoteric procedure. This was a curvilinear multiple regression. So I email Minitab with all my results, programs, and data. Sure enough, there was an error in their calculation of the R^2 which they knew about! They just hadn't issued a patch yet, and worse hadn't made their users aware of the problem.

So now as a rule, I try and double check everything I do, whether it be with SPSS, SAS, Minitab, EpiInfo, Excel, etc... to ensure this doesn't happen again.

jeff

The Bosses don't want to hear probabilities

[farghen]

"The expected Year 1 profit is $1 million, but there's a 30 percent chance of losses for the first two years."

Unfortunately or not, this is not what the bosses want to hear. They want to know that profits will be $1 million. Perhaps the spreadsheets have not adapted to uncertainties for a reason.

The cost of everything

[pubjames]

This reminds me of something a successful businessman told me about accountants: "Accountants know the cost of everything, and the value of nothing".

A problem occurs when people look at a spreadsheet of accounts and think it represents a business. It doesn't. A classic illustration of this is Marks & Spencer's returns policy. If you buy a pair of trousers from Marks & Spencers and then once you've got them home decide they don't fit or whatever, you can return them, no questions asked. To an accountant, this is just a cost. There is no identifiable figure in the accounts that you can point to and say, there's the benefit of that cost. And yet many people shop there because of the policy.

Re:The cost of everything

[misterpies]

'Fraid your friend's not very original. The original quote is from Oscar Wilde: "a cynic is a man who knows the cost of everything and the value of nothing"(

Re:The cost of everything

[tehcyder]

I am an accountant, you insensitive clod!

I think people are missing the point with their criticism of spreadsheets here, they are extremely useful for their intended purpose, which is basically replacing the traditional pen-and-paper analysis sheets. You should try adding up forty eight columns of 80 numbers each by hand (yeah yeah, uphill both ways, I know) and making it total correctly first time, then you will know why spreadsheets are so well loved by us number-crunchers.

Also, a set of financial accounts (on a spreadsheet or handwritten) is of course only a representation of a business and not the business itself, but you have to produce them for legal and practical reasons. And at the end of it all, an audited set of accounts tells a businessman a lot more about how their company is doing than a Power Point presentation by the sales or marketing director.

What's annoying..?

[manavendra]

I really fail to see the point in these posts about a spreadsheet program (be it Excel), not being a database.

Maybe there is a genuine need for a database program (and I use this term here loosely) that provides an interace as easy to use as spreadsheet? Not every user is a programmer, and the vagaries of the any DBMS are well known. Besides, no end-user wants to meddle with software administration.

Maybe the users use it as a database, simply because it provides an easy means of storage and manipulation of trivial data? Not ever user (not in every case, at least) has a million records to work on.

Yes, spreadsheet tools may not have capabilities such as porbability distributions or statistical measures. How many naive users need them? Oh, the average executive might need them to project forecasts, but then, is there a tool that allows this? Conversely, if this limitation has been identified (and I'm sure this must have been identified in the past and by others, as well), why do we not see this being incorporated in any mainstream spreadsheet? (hint: there probably is not enough critical mass of users demanding such a feature).

The other point listed in the article - "the worst nightmare of those who justify IT's return on investment - spending extra money on a more time-consuming product that yields absolutely no measurable improvement?". Well then, perhaps in that given scenario, the need wasn't evaluated correctly? Or maybe such a complexity wasn't required after all?

It's easy to point out the missing features/capabilities from any software, but if it's not asked for by average/most users, it will take a long while to be incorporated (if at all). Yes, this however leaves the issue of errors introduced by the use of such spreadsheets, whether tacit or implicit. In either cases, it would be due to the user being unable to find the right tool to model the problem, or not being able to understand the problem correctly and hence not taking into account as many (if not all) parameters involved.

Re:What's annoying..?

[manavendra]

The same person who does the Excel spreadsheet design could learn to use MS Access fairly easily. It's not that hard to use to do simple stuff

While above might be true for people with technical background, it's incredibly difficult for those who have barely any technical knowledge (other than being able check their email, create their presentation, write their letter, etc). Is it yet another tool, and brings with itself yet another set of complities.

You already have Excel. Chances are you have MS Access as well. It can make you that easy to use, eatrure rich front-end. It can be the backend as well, although preferrably you could use it to talk to a SQL server. It can talk to just about any database server you want over ODBC, be it Oracle, MS-SQL, MySQL(yuck), or whatever

Oh definitely, MS Access would come typically bundled with the entire Office suite, but how many executives really are able to get their head around it? You and I may have heard of ODBC, and the capability to connect to several databases, but an average "joe" user? That's asking too much IMO.

The whole point being why add more complexity to the end-user - which is something they are least interested in.

R&D

[pubjames]

Another thing that suffers from this type of mentality is long term R&D. Japan has had many very long term R&D projects which has been criticised by outsiders as being too long term.

I've just been watching a Japanese robot demo on the TV. Very impressive. I think the fruits of there long term investment in robotics R&D will be seen in the next decade.

Boneheaded AD undercuts itself

[rufusdufus]

Of course this is actually an advertisement for a specific software package. But whats funny is that the story undercuts itself: It explains that people are wasting their time doing detailed future predictions with spreadsheets. Then it goes on to push this particular product as a way of doing detailed future predictions using statistics. But they never make the case that making predictions is good anyway, while they do provide evidence that its a waste of time!

I dont know anyone who uses their spreadsheets for doing any kind of predictions. Everyone I know uses it just like the old-fashioned pen-and-paper..spreadsheet! Its a way of accounting for the here-and-now. How many businessmen don't understand their business prospects better than a garbage-in-garbage out number crunching computer?

A spread sheet is not...

[Technician]

A spread sheet is not a stastics program. However if your office bundle includes a hammer, everyting starts to look like a nail. Excell does math, It's the hammer that makes stastics look like a spreadsheet problem. Enough said? Hammer - nail, Excell - spredsheetable data. For stastics programs look here for a list of some real stastics programs. They are not spreadsheets.

http://www.wch.org.au/CEBU/software.htm

I guess it's kind of like trying to write HTML with MS Notepad. It can be done, however other tools make the job easer.

Problem is the type system?

[shic]

Spreadsheets have been and will always continue to be an extraordinarily powerful ad-hoc tool for those wishing to tabulate data with automated calculations. They are worse than useless if, for whatever reason, the user has no savvy approach to the problem at hand, or if the model which requires manipulation has no concrete representation.

After many years with little use for a spreadsheet (previously having used Supercalc and Lotus 123) I was shocked by corporate state of the art. Specifically, I was disturbed by the type system employed to represent cell values and by the way in which formatting settings can so easily obscure the values actually being processed. The way in which Excel handles dates seems particularly horrific... and OO-Spreadsheet just mimics the same mistakes. I was also amazed that modern spreadsheets haven't started to use extensible libraries to represent new data types. It seems a no-brainer for a spreadsheet to make use of pluggable C# or Java classes to allow domain specific types to be manipulated in the context of a spreadsheet environment. Am I missing something - or have we not only failed to advance the art (as suggested by the article) but actually taken several steps backwards?

Financial Planning

[awol]

The scope of the article is really limited to the use of spreadsheets in financial planning (forecasting). For which the criticisms of the author and the material he cites are pretty valid. Indeed we all have our pet hates when it comes to how the tool is used (you have no idea how much of the financial world is ruled by this spreadsheet or the other driving trading decisions!) however, the tabular representation of data is not inherently broken and it behooves the computer scientists amongst us to ask why this form has usurped the database for the representation of simple datasets and all to frequently complex ones.

Re:Financial Planning

[kfg]

Because when you open up a spreadsheet you see a nice piece of graph paper, and people know how to use graph paper to do things.

It is a simple, obvious and accessable tool usable by the masses who are never going to learn how to normalize a database or pay someone to do it for them.

Here's a use for a spreadsheet you might not have run across before. Composition of structured verse. It actually works quite well as a writing/ editing tool.

Spreadsheets are Swiss Army Knives. The tools on a Swiss Army Knife are rarely the best tool for the job, but any kid can pick one up and use it to accomplish something crude but useful. That's why Swiss Army Knifes exist in the first place.

Most people are never going to rise above the level of using their computers like a Swiss Army Knife.

However much it might annoy us.

KFG

A better article on the same point...

[Singletoned]

"When you're holding Excel, everything looks like a spreadsheet" by Yoz Grahame

I particularly enjoyed it, and it made me wonder why I've always hated Excel. maybe it's time to forgive...

(I always used to like Pipdream on the Archimedes though. That was a combined spreadsheet and word processor).

Re:Kill them all

[tiled_rainbows]

I'm guessing that you've never tried to create a table in Microsoft Word. Yeah, it might seem a bit daft, but at least Excel won't randomly change your text styles, automatically adjust the margins so that half the table is off the paper, insert page breaks through the middle of rows, etc, etc, etc.

If I want a decent table in a Word document I have in the past been driven to embed an Excel file in it.

Indoctrinating Excel

[joonasl]

I think that the root of the problem is that many people who are not IT professionals are thought only how to use spreansheets (MS Excel) and word processors (MS Word) in college/university. Since their "toolkit" is so limited, they tend to do all possible tasks using those programs, even if they are not the best possible choices. I currently work in technology solutions branch one of the big consulting companies, and you can't belive what the business major managers use Excel here for.

So far I have seen Excell used for issue mangement, system requirement repository, time tracking, time estimation, code dependency tracking, system reference data and configuration data repository, ...
..and in 99% of the cases the spreadsheets don't even use the SUM function.

Re:Kill them all

[Jackdaw Rookery]

People do this, certainly in my experience, because the only database they have for use is MS Access.

So really I don't blame them for avoiding that utter POS software.

You have to remember that people are stuck working within the confines of whatever software the business deems 'acceptable'. Although it would be great if we were all on Linux/OSX at work we're not.

Excel isn't a DB! It's a FS!

[beacher]

To anyone that has Excel '97 - On a new Worksheet, Press F5. Type X97:L97 and hit enter. Press the tab key. Hold Ctrl-Shift. Click on the Chart Wizard toolbar button. Use mouse to fly around - Right button forward/ Left button reverse.

Excel 2000? Under file menu, do 'Save as Web Page'. Say 'Publish Sheet' and 'Add Interactivity'. Save to some htm page on your drive. Load the htm page with IE (don't give me any grief over this one- you're already screwing around with Excel so I don't want to hear it ). You should have Excel in the middle of the page. Scroll to row 2000, column WC. Select row 2000, and tab so that WC is the active column. Hold down Shift+Crtl+Alt nad click the Office logo in the upper-left. If you have DirectX, you will be playing what looks like spy hunter. Use the arrow keys to drive, space to fire, O to drop oil slicks, and when it gets dark, use H for your headlights. -B

Re:Kill them all

[wllf]

What about people or (even worse) companies who are determined to send data in Excel sheets and it has to be processed automatically. Columns being deleted because the data typist thinks it is no longer needed, adding columns because there is more 'important' info to add, align the zipcode into its column using spaces after the address (hard one to spot!) and of course the very popular extra comments at the bottom of the data breaking the import routines.

Suspect citation

[dtmos]

A quick google search reveals evidence of only one paper (but not the paper itself, unfortunately) entitled, "Performance, Beliefs, and the Illusion of Control", see, e.g., here:

Kottemann, J.E., Davis, F.D., & Remus, W.R. (1994). Computer-assisted decision making: Performance, beliefs, and the illusion of control. Organizational Behavior and Human Decision Processes, 57, 26-37.

Note that this paper was published in 1994; it's not a "1980s paper" as cited in the article. Careless errors like this make one wonder what else in the author's train of thought is similarly researched. Perhaps he's just incorporating incertainty into his references, too--or, maybe he considers 1994 to be statistically similar to the 1980s?

Problem with spreadsheets

[Anonymous Coward]

"Enhancements" of spreadsheets over the last few years have not involved any substantive improvements in functionality, but have primarily just involved enhancing their "typesetting" capabilities, that is, the ability to change fonts, insert special formatting, and to otherwise make tables look "pretty."

I put "enhancements" in quotes because I am skeptical that this actually represents a true improvement of either the quality of the information or user efficiency in finding and using information.

These so-called improvements gloss over the continuing problems that plague spreadsheet users:

• Spreadsheet models encourage the use of "spaghetti" logic, where cells point to cells that point to cells, and can grow into random networks of calculation logic;
• They permit lots of easy off-by-one errors;
• They generally are difficult to verify/audit;
• They do not provide good tools for managing data either in terms of consolidation or searching for specific detail;
• Perhaps most importantly, despite their convenience, spreadsheets are not a robust repository for information.

I have seen one multinational enterprise that (believe it or not) built a budgeting system atop sets of dozens of departmental spreadsheets that they would roll up into a master budget; while it's a neat extension of the technology, only a fool would try to use this to run a large enterprise. One bad link in one subsheet, and the whole house of cards could fall down. (And the "top" vendor these days, Microsoft, isn't noted for building products that are of industrial grade robustness.)

The last few points point towards where I would like to see spreadsheets go. They have been, and are very good at producing ad-hoc, one-off reports. This is a proper use of spreadsheets.

They are often being used instead as repositories for information that really ought to be managed by a database management system of some sort.

What spreadsheets should do is to allow, nay encourage, the use of data extracts from external sources, notably relational databases. The use of named ranges (which are a venerable feature from at least as early as Lotus 123 v2.01) is of assistance; Lotus Improv was a rather complex-to-use test platform for improved "modelling" whose functionality included database extraction.

Using external repositories permits the benefits of:

• A single repository that can be kept correct, rather than a multitude of mutually incompatible data stores;
• Data synchronization (a restatement of the last);
• All the good RDBMS "stuff" like:
  • Field validation,
  • Maintaining field relationships,
  • Transaction logging,
  • Centralized backups,
  and perhaps even more sophisticated things such as
  • Data modelling and
  • Stored Procedures/Triggers

In effect, the real point I would propose is that the task of building a spreadsheet should involve some data modelling, with thought not just about the report at hand, but also about where the data comes from and perhaps should go to.

Re:Problem with spreadsheets

[GileadGreene]

These so-called improvements gloss over the continuing problems that plague spreadsheet users:

• Spreadsheet models encourage the use of "spaghetti" logic, where cells point to cells that point to cells, and can grow into random networks of calculation logic;

Yes, yes. And programming languages with only gotos are inherently evil. But with a discipline on the part of the user it is possible to build maintainable systems.

• They permit lots of easy off-by-one errors;

Very true, and I have seen it happen any number of times. It's always a good idea to build in validation checks that provide some feedback on how "sensible" the numbers coming out are.

• They generally are difficult to verify/audit;

Well, yes and no. The "trace dependents" and "trace precedents" functionality in Excel can be quite handy for that kind of thing. I've also seen a regression testing tool (developed in VBA) that checks one spreadsheet against another to ensure that the page-to-page links have remained the same (i.e. the interface is consistent) and flags any changes. Detailed validation of the numbers produced by each sheet still had to be done by hand, but it was setp in the right direction.

• They do not provide good tools for managing data either in terms of consolidation or searching for specific detail;

No argument there.

• Perhaps most importantly, despite their convenience, spreadsheets are not a robust repository for information.

That depends to a certain extent on how you are using them.

While spreadsheets get their most heavy use in the financial world, they have gained a lot of popularity in the engineering world, particularly for doing the computerized equivalent of "back-of-the-envelope" calculations. While I was initially pretty skeptical of this concept (being a diehard Matlab fan), I have to admit that for quick exploration of various design options using simplfied models things like Excel are king. Most of the value comes from the same things that are cited as problems for the financial world: that its easy to tweak your assumptions until you get the results you want, and do it with immediate feedback. Bad for the financial world perhaps, but in the early requirements/design phase it becomes an incredibly valuable tool for rapid trade-space exploration. I guess the difference between engineering and finance is that in engineering the resulting numbers are validated in later design phases that use more rigorous tools - that helps to filter bad assumptions before they produce disasters. This kind of spreadsheet-driven conceptual design is very popular in the aerospace industry: JPL, NASA-Goddard, NASA-JSC, The Aerospace Corporation, ESA, NGST (ne TRW), Boeing, Ball Aerospace, Spectrum Astro, and AFRL, to name just a few, all use some variant of it in one way or another (often in teams that use inter-linked workbooks).

Spreadsheets are the worst sort of hack programmin

[Anonymous Coward]

Spreadsheets suffer from programming flaws that we've ruthlessly stamped out in programming languages.

Some of these flaws are :
- Cryptic names for fields
- No comments
- No obvious flow of control
- No modularisation
- No capability to test spreadsheet sub-components in isolation
- No capability to do a diff to see what's changed between versions

Spreadsheets also add flaws of their own, such as unlocalised references.

If we had to design the worst possible "programming language" we'd be wise to look at spreadsheets for an example of what to include.

Open standards and how to enforce them

[Trurl's Machine]

I was recently on the market for a new car (hoorrray!). I shortlisted three vehicles for me to consider and I asked the salespeople of the respective companies to mail me data on service plan, warranty, replacement part prices etc. on all the three vehicles. I got two replies with Excel documents and one with a printer-friendly PDF.

I am all for open standards in communication, but what shall I do? Send a reply to the salesman "you f*ing Microserf moron, I don't want your car if you force me to buy a bloody spreadsheet just to read how much do you charge for a goddamned air filter?" But is it wise to choose a car just because of the software that a salesman uses?

Finally I picked the one that was described in PDF. It was a coincidence - a decisive factor was actually that the make of that car constantly tops in the consumer surveys, while the other two are just about average. But then I started to think - maybe that's not a coincidence after all? Maybe this make tops in surveys just because it's policy is to make all stages of customer experience as convenient as possible and they ask themselves the question that other car salesmen don't ask - "what if my prospective client does not use Microsoft Excel(TM) or Microsoft Word(TM)?".

Maybe it is possible for us to vote with our wallets against proprietary, closed standards?

Re:Kill them all

[Zocalo]

OK, hand up. I've done this. I had a Word document that contained maybe two - three pages of text followed by a series of tables of data - total size of maybe 5MB. I wanted the document on my Clie for reference, but not at the 3MB Docs2Go was rendering it at, and PDF was even worse. So I dumped it into an Excel workbook, one worksheet per table for fast access and used odd numbered rows on the first page for the text, one paragraph per cell. With a decent left justified cell width, word wrap enabled and the grid turned off it looks fine. New file size: 300kB. Conclusion: Word's table markup is sub-optimal to say the least...

Similarly, I don't have any problems with using Excel as a basic flatfile database (never relational though, I'm not that insane) where the visual layout of the data is more important than the flexibilty of querying. That said, on a basic flat-file database you can actually perform some quite sophisticated filters using Excel's auto-filter function.

I don't think the problem is with using a spreadsheet as a word processor, database, or any of the other uses it can be shoehorned into. The problem is simply that people correctly see a spreadsheet as a jack of all trades, but forget that this implies it's a master of none, with the possible exception of what it was designed for: crunching tabulated numbers.

Sometimes it's expedient

[hyc]

I wanted to model the characteristics of a turbocharger I was planning to install in my car. It seemed to me a spreadsheet was the ideal way to try various scenarios. Of course, modeling a turbo requires entering lots of lists of numbers. I had to fight with it, but despite my years of programming experience, figuring out Excel was easier and faster than writing my own custom app for the job.

Turbocharger Spreadsheet

Now I can just enter engine size, compression ratios, etc., select from a variety of compressor maps, and presto - power curves computed without breaking a sweat.

Re:So here's a question...

[manavendra]

Oh I couldn't agree more about the presence of a product niche that has a spreadsheet-esque interface, but not only enforces relationships, but also provides all the snazzy features (statistical operations, et al).

The big questions here are then how many users actually need this, and how many naive users correctly understand the concept of "relationships" between data and the enforcing rules?

I have a sneaky feeling these features are missed mostly by developers tyring to squeeze that extra bit of functionality from a spreadsheet (be it to impress, or out of sheer laziness) :-p

Re:Kill them all

[fucksl4shd0t]

Well, for really small lists, it's an easy way to store it. Especially when someone with very little computer knowledge couldn't even begin to create an SQL statement.

How's this?

I created a spreadsheet in KSpread (awesome program, that) to plan my menu for Texas Brand Barbecue. So I used it to estimate all of my costs and my gross sales. I'm a perfect example of who this article is about, but I think I'm above the sort of planning the article is talking about. :) (I intentionally went conservative on sales and liberal on costs. I could be wrong in the end, but if I'm going to err, I prefer to err where my error makes profit rather than loss)

Then I needed a list of equipment to start up and to estimate the cost of all this equipment. So I switch to another sheet in the same workbook and create this list. A quick little formula gives me a total.

Aha, so now I wanted to keep all my data in one place, and the next few pieces of data were tabular in nature, but no formula attached. I needed a list of local area farmer's markets, locations, dates and times, market coordinator, and contact phone number (website and email if available). So what did I do? Well, I made a new sheet in the workbook and put my table there. Now I refer to it whenever I need to call someone on the list, or if a market falls through (that process is over, now) I can easily find another market for that day.

Spreadsheets, as another poster put it, are for the presentation of data. For my purpose, I could've taken a couple of weeks to write a program that would have less than half of the functionality of my spreadsheet, but why bother? The spreadsheet is there to do the job.

This isn't saying that I wouldn't like to have something better, and I intend to home-grow a better solution. First I have to write a driver that will let me download transactions from my cash register to my database, though. The program gets complex after that, but the intent is to replace my spreadsheet with it. (And release it as open source, of course. Doesn't give me a competitive edge worthy of note, and others could benefit by it)

Re:Spreadsheets are the worst sort of hack program

[kidgenius]

- Cryptic names for fields
- No comments

Umm.....Excel can add comments to individual cells, and you can rename columns/rows to something arbitrary.

Discrete Event Modelling

[Anonymous Coward]

At university, I am taking a course in business modelling. We use Simul8 s/ware to generate thousands of monti-carlo 'runs', then analyse the results as if they were real data.

But it's not real data! It's completely determistic, even with a pseudo-random generator. The only things we deal with are simple supply-chain networks, which are just malkov-chains with a few probability distributions. We're using 2000 pounds worth of s/ware to solve high-school statistics problems :-/

You'd get the same results, and have real justifications for the numbers, by using an HP Calculator and a pencil. Alarmingly our lecturers have yet to explain what any of the distributions mean, but they keep using words like 'proof' and 'verify'.

I'm back to linearly regressing my calculated data. It's insane, they're all insane, one day the sane people will rule, wibble ...

Re:Mods, please mod parent up.What, no Tux? (Happi

[lxs]

I believe the term for that is 'Spreadsheet Physics'

In the early '90s when I was a student, and teaching physics by computer was still in it's experimental phase, one of the things they had us do was solve simple numerical problems (trajectory of a ball with air resistance etc.) using Quattro Pro. It did work, but it was not much faster or easier than programming it directly in C or FORTRAN and using GNUplot to draw the nice pictures, so as far as I know, they scrapped the program.

What really pisses me off...

[rasteri]

... is people who use excel as some kind of DTP package. I used to work in a school, and the deputy principal used to make report cards by typing them into excel, and fiddling around with the cell formatting properties until it looked right. Except it didn't, and when he couldn't make something work he used to DEMAND that I help him (some things were just impossible to acheive without using Publisher or something). The worst year was when he saved them all as CSV files (one of his friends told him it saved space or something) and it was up to me to get them back. Urgh...

Excel is fine for statistical predictions

[gomel]

From the article:

The first distortion is the use of point values and simple arithmetic instead of probability distributions and statistical measures. So far as I know, there's no off-the-shelf spreadsheet product--certainly none in common use--that provides for input of numbers as uncertain quantities, even though almost all of our decisions rest on forecasts or on speculations.

I am a student of this university : http://www.sgh.waw.pl/
Currently I am having a course in the use of Excel for prediction purposes. We do a lot of different case studies. We use Monte Carlo simulations, statistical tests, Markov chains and so on. We always discuss risk (variance, value-at-risk and so on). Excel is our basic tool and it is fine. We use different tools for specific purposes: Best-Fit for distribution fitting.

It is not a flaw of the tool, it is a flaw of the user. As someone said, give a monkey a PC instead of a type writer and you will get digital bullshit. I can only demand that people without proper education are not allowed to deliver multi-million business forcasts.

Re:Kill them all

[Urkki]

And same goes for graphics. Never ever use MS Word drawing functions for, well, for anything really. You can never be quite sure what happens if you for example resize the image... Or type more text into a textbox... Or breathe...

Always insert Powerpoint slide into the document. Unless you really need something Powerpoint can't do of course, in which case you should use whatever actual drawing program you need to.

Re:The question does not deserve mod pts, but answ

[misterpies]

>> And what the *fsck* does it mean, even if the data has a Gaussian distribution?

that's what wikipedia is for :)

it's a measure of the width of the distribution. Given a gaussian distribution, a random measurement will occur within one standard deviation of the mean with a probability of around 68%. Or to put it the other way round, if you have data and are trying to calculate the distribution, there's about a 68% chance that the true value of the mean falls within 1 s.d. of the value you calculated.

If you don't have a gaussian distribution, you can still calculate a standard deviation but it will not have the same meaning with respect to the probability of you having got the right mean.

This is of particular relevance to spreadsheets, since they're often used to do calculations on financial data such as stock prices and most financial data is not gaussian--it's 'log normal', meaning that the logarithm of the data values are gaussian, but not the data itself. So most people doing standard deviation calculations on such data are probably completely misinterpreting the results...

"Powerpoint Mastery"

[foobsr]

Since using spreadsheets is often only one step away from PowerPoint mastery.

Erm .. for "Powerpoint Mastery" have a look at Tufte "The Cognitive Style of PowerPoint".

Yes, I know it was discussed here before (as I guess), but still - it is worth a mention.

CC.

Article is an advert

[kahei]

1 -- the article is a content-free advert for Whitebirch's financial toolkit

2 -- Excel is an incredibly powerful and important piece of software which many if not most large corps can't do without. There is no alternative to it. The fact that it's unpleasant to use is beside the point -- nobody has been able to come up with a better (or even comparable) replacement. In my experience, there is a large segment of the IT community that is pathologically unable to focus on business needs enough to understand this.

Re:Article is an advert

[Mordaximus]

There is no alternative to it.

Have you been living under a rock? There are plenty of alternatives. The short list would include (I know there are plenty more, but most coporate users run Windows, so listing Gnumeric etc. is moot):

• OpenOffice.org calc (which I find far superior to Excel.)
• Corel Quattro Pro.

Most users just need something that performs a function on a row or column of data. Many corporate users _think_ they need Excel to do it. When in reality, they could use an application with far less feature bloat.

Re:Article is an advert

[gkuz]

nobody has been able to come up with a better (or even comparable) replacement

I call bullshit. How old are you? How many PC software products in that space do you remember? Javelin was both excellent and revolutionary. Lotus Improv was close (but not close enough) to a GUI Javelin. Both used the spreadsheet paradigm as a sort ow "window" into real data. Both failed because the average PC-using simpleton wanted the "simplicity" of 1-2-3. 1-2-3 was overtaken by Excel because their GUI versions sucked worse than Excel did, and then once Excel got a foothold, the MS juggernaut took over. But there were "comparable" and better replacements 10 or more years ago. Strange as it may seem now, there once was an actual abundance of choice in "office productivity" applications.

Re:Article is an advert

[kahei]

There are plenty of alternatives
Most users just need something that performs a function on a row or column of data

Mm, this is what I meant by an inability to focus on business needs. It is not possible (with a reasonable amount of effort) to generate and splice together real time futures price streams based on bloomberg data, a C maths library, and parameters modified on the fly by the user, with OpenOffice. It is with Excel. This is the sort of task that needs doing. Other people in the company may be able to get by with OO, which is nice, but the world can't switch until an alternative to what Excel does appears.

Just saying that OO does what _you_ have decided the dumb ol' users need does not bring that day closer.

I _really_ hate Excel a _lot_. I don't ask people to stop doing business until open source catches up, though.

Re:Article is an advert

[Watts Martin]

I remember Improv and thought it had some great ideas. (There's an 'experimental' spreadsheet for Mac OS X I've seen modeled on it, but unfortunately it hasn't really gone past the basic core concept yet.) And nobody should claim that Excel is a replacement for Matlab or SPSS or even TK!Solver, no.

But I think a lot of the Slashdotians bashing Excel really haven't dug into it very deeply. For data analysis, rather than statistical analysis, Excel can frequently compete with or even trump programs like Crystal Reports. Pivot Tables are amazing for quick data summary and analysis, and as far as I know Excel was the first spreadsheet to implement them. And to earlier comments I saw talking about how data should be stored in databases, that's mostly what I used Excel for at two different jobs: reporting on data stored in SQL databases. Excel can query directly (or through the rather unlovely Microsoft Query). Recent versions of Excel can even create OLAP-style cubes for summarizing great amounts of data.

The discussion about Monte Carlo simulations sort of groped toward a limitation of the spreadsheet model -- they have deterministic inputs, and without extensions they can't run simulations very well. (I imagine it'd be possible to address that to some degree using Visual Basic, but it'd be painful; using a plugin like Crystal Ball 2000 would make it much less so.) But this isn't a criticism of Excel. It's a criticism of using Excel for tasks it's not appropriate for. I have a lot of dislike for Microsoft Word, but for the most part, I don't begrudge the fact that it can't do true digital typesetting. The fact that people try to use it for typesetting and layout anyway is an indictment of people who try to use it in that manner. (That people are frequently conditioned to believe that there is no world of business applications past Microsoft Office is a valid concern, but I think it's still a different issue.)

A little skeptical

[astrashe]

I saw my first spreadsheet on an old Osborne computer. My dad knew a guy who bought small banks, and he had the Osborne and VisiCalc.

Before this guy could buy a bank, he had to value them, and his valuations were always based on a few guesses (predictions) -- what interest rates would be, or whatever (I don't know exactly how he did it).

He told me that when he started doing this stuff with a normal calculator, a pencil, and paper, changing a guess took him a couple of days. Then he got a programmable calculator, and managed to cut it down to about 5 hours. With VisiCalc, it took a few seconds.

The point being that both the programmable calculator and the spreadsheet software gave him an edge in his work -- they made him better at buying banks. They paid for themselves.

*If* no one is using the sorts of software described in this article, and *if* the software really does make you better at making decisions, people should be able to use it to buy banks (or whatever) and do a better job than their competitors. It should give you a leg up in the market place.

That's exactly what happened with spreadsheets. That's why they're popular. A lot of dumb people have started to misuse them, apparently (that sounds plausible to me), but there's no denying that they have provided and continue to provide enormous value to users.

If this new stuff is better, then why isn't Warren Buffet using it? If the answer is "because he's too dumb", why doesn't someone else start using it, and outperform Buffet?

Who cares how people use Excel?

[Anonymous Coward]

You know, whenever we're talking about software like P2P file sharing, or freeware DVD drivers, or software that opens Adobe files for backups, the Slashdot crowd tends to be firmly in the "don't punish the technology for abuse by the users" camp.

And then we have these PowerPoint, Excel, yada yada threads where the Slashdot crowd tends to be firmly in the "don't punish the users, it's the fault of these evil software applications" camp.

What's up with that?

Re:Who cares how people use Excel?

[billbaggins]

I can explain it in one sentence: No one is trying to outlaw MS Office.

Re:Kill them all

[the_womble]

A bloated MS App has lots of functionality that does not actually work right so you embed documents from other bloated MS apps in it.

So MS dominates the office software market becuase people really need the functionality they offer, right? After all why would anyone want to use something lightweight with a clean UI (like Lyx for example) which makes you use another application just to draw a diagram?

spreadsheets for ultra critical work

[Anonymous Coward]

Having worked as a front-office developer in a very large bank i can give a good example of how spreadsheets can be misused Excel spreadsheets were used by all traders on the desk i was supporting. They did not want to move to any other tool because only spreadsheets gave them the flexibility they wanted. The spreadsheets were absolutely HUGE, think direction 20 or more tabs, all with hundreds of DDE Links to Reuters RICS - complicated formulas hanging off these links producing tables of data each time a DDE link updated (about once a second on average). We had to install gigabytes of ram and dual CPU's desktops for them just so they could run their spreadsheets. Sure excel would crash every now and then, but not often enough to switch to a new solution.
IT tried to introduce new more stable trading tools without success, not flexible enough-did not calculate "their" prices correctly-blahblah. Controlling tried to impose new tools on them to get a grip on their price calculation- all very difficult when the only data source is a "spreadsheet".
The most insane thing that we tried was to write a spreadsheet parser that would traverse all cells, build a dependency graph, reparse the formulas inside to translate this to another programming language. Needless to say this failed.

Re:spreadsheets for ultra critical work

[mccalli]

IT tried to introduce new more stable trading tools without success, not flexible enough-did not calculate "their" prices correctly-blahblah.

Err...I too work doing rates-related stuff for major banks. Blah blah blah??!! That's the entire point of the rates business - that's why those traders are employed, because they can tweak their prices to make a profit from the market.

Controlling tried to impose new tools on them to get a grip on their price calculation- all very difficult when the only data source is a "spreadsheet".

It did what? Really? A cost centre tried to impose inadequate tools (your own admission - not flexible enough) on to people who were actually generating cash for the bank? And they rejected it did they? Good Lord, how terribly surprising.

Sorry, but I'm utterly shocked at the cavalier attitude displayed here. I work doing a very similar job to the one described (writing tools to control rates pricing), and I tell you now that wandering in to our profit-producing users and saying that their rules are a load of 'blah blah blah' would, quite correctly, get me booted out of the City forever.

Cheer,
Ian

Re:Mods, please mod parent up.What, no Tux? (Happi

[Prowl]

if you think that's perverse

Probabilities and spreadsheets

[Advocadus Diaboli]

For all those who think that there is no exact result but a lot of probabilities that has to be taken into account I recommend the following book:

Waltzing with bears by Tom DeMarco and Timothy Lister. And yes, they provide spreadsheets to calculate probabilities.

Why not to use Excel as a DB

[linuxtelephony]

One of the biggest reasons is the sort function combined with [l]user error.

If a spreadsheet has more columns that fit on the screen, and is used by more than one person, at some point you can almost count on someone highlighting some, not all, of the columns and then sorting the highlighted columns, and saving the file. When that happens, the highlighted columns are sorted, the rest are left as is. Worst, the next person to use the file doesn't always realize the corruption has occurred.

This was a problem in Office 97 and earlier. I think it was a problem in Office 2k, but I don't remember. I have not tested this on Office XP or 2003.

Re:Why not to use Excel as a DB

[alasdair]

Good news: it's fixed in Office XP (Excel 2002 SP2). Selecting Sort highlights all the content in your table by default.

Excel Cluster!

[mclearn]

I swear to god. You can't make this stuff up. Our financial institution actually ran (past tense -- I'm converting it) a cluster of PCs all running Excel for pricing hugely complex financial products.

After finally getting my hands on the underlying VBA code, I printed it out. It was 56 pages of data movement (copy this piece of data from here to there). The actual pricing code was built as an add-in module and used as a formula.

The only reason for this system's existance is that several years ago someone heard about clustering PCs. They decided that it would be cool to do it with MS Windows and Excel. Gah. It's been an expensive mistake.

don't forget simulations...

[Goose Bump]

I do systems and IC design, RF mostly. A couple of years ago I was doing work for a very large semiconductor company. They were using an excel spreadsheet to do the majority of their system level simulations. It was a very intricate piece of work that had been revised by one individual over many years, spanning a couple of different employers. The company purchased Agilent's ADS and was having trouble getting engineers to abandon their tried and true excel spreadsheets.

I must admit, in some ways, for some simulations I preferred it as well. With ADS most components were black boxes, when input to output didn't behave as expected you didn't have much info to debug with. At least with the excel spreadsheet all the equations were there to study.

It dawned on me about half way through the project that the reason most of the engineers preferred the excel based system was in large part because it was (bear with me) open source. Many times in simulation you aren't sure if the problem is your model, or the simulation package. Analysis was much easier (or trustworthy) when all the calculation methods were easily viewed and tested. (All this is also why I use Octave!)

The author of the spreadsheet is now working at another high profile semiconductor company. Word around the campfire is that they are using the spreadsheet now too.

Excel

[nuggz]

I can't think of anything you can't do in excel.
You could make the first 4 pages with formatting, then simply import the csv values into a third source page.

I appreciate that Excel gives me the capability to make simple semi automated forms that look nice.
Prevents simple errors, and they're easy to use.

Problem is intractability.

[twitter]

The problem raised is intractability and that has no solution. The problem bemoans the fact that people use spreadsheets without understanding error propagation! Duh, in math terms it's been described:

The limit of Engineering as GPA goes to zero is MBA.

Typically, math is the skill that drives that GPA down. OK, the bad joke is starting to look like a flame, and it's true that clueless big dogs with their sensless five year plans make me angry, but please - this is a joke. Everyone has got their skill set.

The simplest example of a bad problem for a spreadsheet is billiards. Momentum transfer is easy but predicting a billiards game is impossible. Yet businessmen make this kind of mistake all the time. There is no cure for this kind of bad judgement and it's good that the people at ZDnet have pointed it out. I just wish they were not trying to promote statistical packages that people are not likely to understand as a substitute for good judgement.

Please!

[pkaral]

The classical Slashdot debate features something-stupid-done-or-said-by-non-IT-savvy-gene ral-managers, and then the appropriate bashing by IT-savvy Slashdotters. If there were a similar forum where my profession were in majority, they would probably be bashing this very thread right now (I am an economist and business manager).

Just like, say, PERL or Java, spreadsheets can be used well, and they can be used poorly. Furthermore, people with good "technical" Excel skills can produce lousy spreadsheets with little analytical value, and vice versa. I have seen some fantastic spreadsheets which have totally revolutionized the way people saw a problem. At an insurance company I worked with, they used a huge spreadsheet to do a simulation of the effects on every single customer of a planned, dramatic price increase. The result: They realized that the price increase would have much less impact than they feared. Thus, the product was kept and the employees kept their jobs. The thing with the spreadsheet was that it was developed in fast trial-and-error loops, which meant that their run-once-per-night SAS tools were not an option (this was 7 years ago).

(I have, by the way, also seen people spend 3 months on developing a mega-spreadsheet for assessing the value of a company, only to use the wrong assumption for a critical value and thereby introducing an error of about 40% in the valuation [that critical value being the discount rate]).

I can assure all the concerned citizens of this forum that there is indeed a lot of excellent, first-rate Excel usage out there. Analytical power beyond our wildest dreams is at the fingertips of people without skills in programming at any lower level. This, believe it or not, is a good thing, because anyone who has dedicated himself to becoming great at programming is probably less skilled in disciplines such as financial analysis.

Sure, there is "bad code". Sure, people get a false sense of control. Sure, this new tool puts too much options in the hands of people who do not know how to use them. But how would that be untrue of other IT tools or programming environments? What does it matter that they use Excel as a database, as long as it gets their work done easier than getting an SQL education and then doing it "right"?

Biases are part of all decision-making (as even economists are realizing). So what if that is the case in Spreadsheet World, too?

Re:Kill them all

[tiled_rainbows]

VBA for Excel is very slow, but the figures you quote suggest that something is wrong in addition to the inherent slowness of the product. Have you checked the code doing the lookup? It might have been written badly.

One bottleneck that often happen in VBA for Excel routines is that the application tries to keep the screen refreshed while the routine is crunching, which slows things right down. If it's not there already, you should get a big improvement in speed by putting

application.screenupdating = false

at the beginning of the routine, and

application.screenupdating = true

at the end, which will mean that the screen will freeze when you start the routine, and only redraw once it's all finished.

Spreadsheets early appeal

[RetiredMidn]

A couple of anecdotes (I worked at Lotus for a few years starting in 1983):

There was an issue of PC World that came out in late '83 or '84 that surveyed readers on which applications they used in various categories. Lotus 1-2-3 ranked third (something like 17% of respondents) in the word processing category. (This was not a mistake; it turned out that some users of 1-2-3 found it easier to enter a few paragraphs of text into a column of cells and use the Range Justify command, than to exit 1-2-3, change floppies, and launch WordPerfect.)

When I started at Lotus, my wife was a buyer for a local retail chain. She had to do quarterly plans where she distributed a fixed number of dollars over various styles of merchandise among several branches of stores; she had been doing this in rows and columns with pencil, paper, and eraser. I built a model for her to do this using 1-2-3, and several days work was reduced to hours; her peer buyers would visit us quarterly to take advantage of the new tool.

My model was flawed; I formatted the calculated values to 2 decimal places, and 1-2-3 rounded the displayed values accordingly. As a result, the actual sum of a column of calculated values was not equal to the sum of the displayed values. (A further example of the ongoing weakness of spreadsheets, and of my own carelessness; my numeric methods prof would have been ashamed of me.) (It should also be noted that my wife caught the mistake by eye, without even doing the math herself; I had been so trustful of the tools that I hadn't bothered to challenge the results. Another lesson learned.)

Eventually, the store's IT department rolled out their own application on the division's single 3270 terminal. My wife still prepared her model at home (since time in the 3270 seat was hard to come by), and transcribed the printed results into the terminal at work. The IT application required her to enter not only the table values but the calculated sums at the end of each column and row. If a sum did not match the contents of its row or column, the IT app reported an error, but did not provide the correct value, nor even state which of the thirty-odd values was incorrect!

Re:please everybody..stop

[farmgeek]

You're endangering my revenue stream.

If it weren't for those organically grown excel/access nightmare programs most companies would never think of hiring a programmer. They hire us after they build those things up to their final catastrophic state and realize that they need somebody to come in a fix it up right.

I don't know about you, but if it weren't for homegrown messes like that it never would have occurred to me that anyone needed a program to import proposed insurance fee schedules and munge those values against previous fee schedules and usage data to decide whether or not a particular contract was worth considering. That sort of stuff isn't normally on my radar. Thanks Mr. P for building that supersized crappy excel program to do that witt, so I could re-write it!

Excel is no good for Monte Carlo simulations.

[twitter]

Currently I am having a course in the use of Excel for prediction purposes. We do a lot of different case studies. We use Monte Carlo simulations, statistical tests, Markov chains and so on. We always discuss risk (variance, value-at-risk and so on). Excel is our basic tool and it is fine. We use different tools for specific purposes: Best-Fit for distribution fitting.

I suggest you seek another university. A spread sheet is not even an adequate tool for teaching Monte Carlo (MC).

MC simulations typically have value only when used in large runs. Without a reasonable number of simulations, you usually end up with very poor statistics for your outcome. A single shoot of the roulet wheel tells you nothing. Ten million shots can come close to simulating an acutal event.

Spreadheets are absolutely the worst tool imaginable for such a task. Spreadsheets are good for simple calculations with well know quantities where you can check the intermediate results and make sure you have not made a bonehead mistake. They are best for back of the envelope, simplified model sanity checks. What you want for MC are any of the premade specialty packages, usually written in FORTRAN, that run as a batch process and have been extensively peer reviewed. I can imagine a dinky spreadheet MC tool with inputs for numbers of runs and odds, but with the underlying math hidden. UGH, you can't tell what it's doing! Anyone trying to cowboy a spreadsheet "solution" to this kind of problem is wasting their time.

I can only demand that people without proper education are not allowed to deliver multi-million business forcasts.

I second that demand.

Not surprised that techies don't get it

[salesgeek]

Spreadsheets are critical tools for "knowledge workers" because they allow them to explore ideas, analyze information and identify trends. The problem is that most "knowledge workers" are competant at some aspect of doing business and not at developing appropriate software tools. It is a problem when a spreadsheet is used as a multiuser shared data application. Spreadsheets allow:

* Entrepeneuers to financially model their business plan.
* Calculations to be performed more accurately than say, in the margin of a ledger pad.
* Simple busines processes to be tracked and managed using a computer instead of say, a legal pad.
* Executives to summarize and categorize and drill down to analyze information from a database (pivot tables)

At the end of the day, I've found that spreadsheets are not the cause of business mistakes. When there is a spreadsheet failure, there are ususally a couple of fundamental problems:

* Lack of attention to detail
* No oversight or validation
* Numbers are not reliable to begin with
* No one bothered to actually do a what-if using a reasonable range of scenarios - they only looked at the rose colored one.

Re:Not surprised that techies don't get it

[mentatchris]

I totally agree. Excel is a powerful tool, we use it regularly in the modeling class I'm taking for my MBA.

I program full time for a living, and while I find some of the interfaces to Excel inane, I can't help but marvel at the remarkable number of uses the tool has. Some things are very easy in excel that require much more time and knowledge to do in other software. And everyone has and can use Excel to some degree.

Don't judge it for what it isn't. You should judge it for what it is.

It is a fairly easy to use tool that allows end users to answer their own questions and solve problems without relying on programmers or engineers.

The Microsoft Fat Chicks

[Latent Heat]

I like it when the dude celebrates "processing an impossible amount of data" using Excel and other Microsoft products and the two fat chicks come out and dump the whole water cooler tank of water on him.

Spreadsheets and Engineering

[dixontw]

I normally lurk, but need to say that I don't think spreadsheets are inherently evil. I am an engineer at a large oil company, and we use spreadsheet models for a number of processes. Typically the spreadsheet is used as an interface, since everyone is familiar with it. The "number crunching" is done with VBA. I know that among the readership of /. VBA is a dirty word. For an engineer, though, it is not that bad of a tool. Not particularly fast, but for simple, numeric algorithm implementations it works fine. Sure - we can and do use purpose-built models. They have their place. However, they tend to be black boxes that can't be easily modified. They also tend to be really, really, really expensive and more of a solution than you need. In other words, for some problems, they tend not be the most cost-effective means for computation. If transparency of the calculation method is most important and not millisecond execution speed, then I agree with a previous poster who argued that Excel and VBA tend to be "open source" in the context of "how the calculation was done".

Spreadsheet Advancements in Recent Years?

[sheared]

What truly functional advancement has Microsoft made to Excel in recent years? It seems that they make a few bug fixes, slap the newest Office look on it, and push it out the door. The advancements in spreadsheet functionality were made by other companies, which MS copied into Excel (pivot tables is an example). The one real exception to this is the VBA macro system. I find it useful for data reduction, and it remains the one reason I will not switch to other spreadsheets.

I just wish some other company would make inroads in spreadsheet design and either push Excel to improve or topple it from its current perch.

Re:Spreadsheets are the worst sort of hack program

[MrWa]

Some minor points:

- Cryptic names for fields
How is this "stamped out in programming languages" other than convention and training? Is is forced on you?
- No comments
Excel can "comment" to some extent.
- No obvious flow of control
Some would argue that the freeform nature of a spreadsheet is what makes it so appealing.
- No modularisation
- No capability to test spreadsheet sub-components in isolation
- No capability to do a diff to see what's changed between versions
Excel can keep track of every change made - didn't we just have an article making fun of Microsoft for this feature in Word?!

Not saying the spreadsheets, or Excel specifically, is the answer to everything.

The tyrrany of spreadsheets

[drgonzo59]

I for one, heard that it's probably spreadsheets that are responsible for the proliferation of computers. Initially companies got interested in them because they wanted to use VisiCalc in their accounting. Then other departments wanted to have one too, they saw how the spreadsheet instantly responds and updates itself when one of the cells change, that made it easy to play the "what-if" game. Imagine having hundreds of formulas all inter-related that calculate the cost of a new plant. Then someone asks the question "what if we use a different building material?". Re-doing all the calculation by hand might take a couple of days, a spreadsheet, in turn,will give the answer in seconds.

Now fast forward to present. I would agree that people want to use Excel for everything: database, graphics, plotting, forms, as a programming environment (oh the humanity!) etc, etc. Most excel users probably don't even know how to use a formula in Excel. The other extreme is when the calculations are so complex that it would be better to switch to Mathematica or Matlab. But Excel is the only tool they know and they want to use it for everything. I can hear my boss's voice in my head "Let's use Excel for this" with the intonation that would make one believe it's the greatest idea ever. Oh, well, have to get back to work where I am forced to use Excel for most of those tasks mentioned above, yes, I am one of the guilty.

OO calc & marketing

[Bill, Shooter of Bul]

I think its funny that here at slashdot the center of advocasy for open software, that 95% of the discussion here is using Excel to mean spreadsheet. Talk about subtle bias! Apparently OO isn't good enough, or it isn't popular enough even amoungst slashdotters. Perhaps, its a mistake to give such generic names to the components of OO. Now if it was something like firecalc or pheonixview then I think it would be discussed more. Instead, Now when you talk about an individual component you have to use the suite's name( IE OpenOffice Calc). No one says Micorsoft Office System Excel 2004. They just call it excell, a techno sounding name that doesn't provide any clue as to its use.

RTFA: *nothing* to do with using spreadsheet as DB

[blorg]

The first poster obviously didn't read the article. It has *nothing* to do with using spreadsheets as databases. It covers two things. Firstly, it covers the inability of spreadsheets to deal with probability in projections, and our tendency to optimistically adjust some figures up and others down until we get the result we want. The problem is that a spreadsheet can only show a single 'snapshot' state:

It's not too hard to appreciate the difference between products that incorporate uncertainty and those that don't: On the one hand, you've got, "We predict a $1 million profit in the first year"; on the other, "The expected Year 1 profit is $1 million, but there's a 30 percent chance of losses for the first two years." These different statements will lead to quite different discussions.

The second point made is simply how what-if tools such as spreadsheets lead users to think they are making better forecasts while in fact their forecasts do not improve.

What if it was Gnumeric?

[FreeLinux]

That's quite incredible!

The funny thing is that while everyone is going to look at this and say that it is ridiculous, and it is, think what people would say if it had been done with GNumeric. The Slashdot headline would read something like "Cool Hack Let's You Play Pacman in GNumeric" and there would be 300 comments saying how cool it is. Another 50 comments would say that the guy has too much time on his hands. People would talk about the awesome power of GNumeric but, no one would complain that it was an absurd abuse of Gnumeric as they are here about Excel.

Just some perspective.

Obligatory blame Microsoft

[HarveyBirdman]

But it is quite true in this case. They haven't done anything with Excel beyond adding pointless features. Other spreadsheets have been tried, some with neat innovations, but they don't make it because, well, in most cases users like me don't get to choose which software we use at work.

How about the simple idea of breaking away from the rectangular grid? Or free form cells placed on a diagram or schematic or blueprint?

change control for spreadsheets?

[Freedom Bug]

What I don't understand is: they make us software developers use change control (for good reason), but upper management builds their business on this fragile house of cards spreadsheet system.

Is there a good change control system for spreadsheets? Sure, we could treat the xls files as opaque binary files, but that's losing most of the power of the change control system. I'm sure it's out there. Pointers anyone?

Bryan

Excel in place of HTML

[skarth]

There's a special place in hell reserved for the people who use Excel spreadsheets as mockups for HTML pages, considering that there are numerous HTML WYSIWYG editors available.

These are the same people who complain when the final HTML page does not look EXACTLY like the Excel version.

Explaining to them the impedance mismatch between Excel and HTML is a pointless endeavor. Banging my head over and over against a doorknob is more enjoyable.

The Problem with Spreadsheets

[tjic]

An acquaintance criticized spreadsheets and praised pencil and paper forms because mathematical errors can crop up in either one, but with paper there is a double-entry system, running totals, and review by brains and eyeballs.

My argument is that paper is a big step backwards:

1. it's not FTP-able; can not make arbitrary backups
2. it's not mailable
3. one can insert arbitrary figures with out validation
   • line 1 (paying customers): 10
   • 
     line 2 (non-paying customers): 10
     line 3 (all customers; add 1+2): 400

...and yet, I understand my acquaintance's points. However, I think he has identified a defective coding style (yes, I'm arguing that filling in a spreadsheet is equivalent to writing a program), and that defective spreadsheet coding styles is encouraged by the fact that spreadsheets are a "language" that don't give the right mix of features.

I use a decently large spreadsheet to run Technical Video Rental, and I've certainly found bugs in it, but I've noted that the bugs are denser, and harder to find in those areas where the computation appears with more intermediate values hidden.

I think that a more confident spreadsheet programmer tends to hide more variables in complex cell formula; as I am not a confident spreadsheet programmer, I've - in many places - spread formula across multiple cells...and this has helped me figure out bugs.

This points out running totals as one example of good practice. Nothing could be simpler in a spreadsheet, yet we almost never see it.

So: why do spreadsheet programmers not do these things?

One reason that occurs to me is that spreadsheets conflate calculation with presentation. Intermediate values use up screen real estate, and look ugly.

Yes, there are tools that *allow* one to separate calculation from presentation: one could have two separate tabs, for example.

Yet these tools allow for disambiguation of calculation and presentation in the same way that assembly programming allows for object oriented design.

Or, to rephrase it: "Hidden steps considered harmful".

I don't even like C/C++ code that puts too much computation on a single line: I want intermediate values that I can step through with a debugger.

Perhaps what's needed are much higher level tools with in the spreadsheet that let one select cells of interest on one tab, then create a presentation tab based on these? I've got visions of cool Mac-Aqua-like greying out of 90% of cells, while one drags and drops the still-crisp cells around... Another/alternate idea: it might be nice if instead of the heavyweight tabs that most spreadsheets support, one could open zoom in on a single presentation cell and investigate little "pocket tabs" which might have ~10 x ~10 cells in them. The equivalent in C/C++ would be a complex expression on one line that decomposed itself into multiple lines with intermediate values only when you walk it with a debugger.

Now, don't get me wrong: I'm not arguing for fancy presentation layers, or dancing pie-charts; I'm arguing for the ability to take a huge page of calculations and tie the some of the inputs, intermediate steps, and output to a much smaller summary page, or, conversely, I'm arguing for the ability to take spreadsheets as they are currently written, and expand them into a debuggable format.

This, I argue, would make spreadsheets more useful, and decrease the number of bugs that crop up in them.

Spreadsheet: Worst Invention of 20th Century

[LaCosaNostradamus]

I catch flack each and every time I say that, but I still think it's true.

The ss has some serious advantages. In an environment of increasing number density and decreasing personal involvement, the need to have a comprehesive tool for data analysis could only have given birth to the spreadsheet. We could talk all day about how handy the ss is for many of the tasks in this environment.

But the space between the substance is what concerns me. Ss have allowed us to max/min too many things without much regard for the things that are undefined and necessarily intangible, but are still entangled in the matter itself. No corporate ss takes into account the costs of pollution, unemployment and general social degradation due to uncontrolled greed.

Like handguns, ss have brought us significant personal power at the cost of a good many social problems. Hence, they seem to require more careful handling and regulation. One aspect to this is training, and in general ethics training is a good place to start. (The BBB in my area is attempting to emphasize this, but they are meeting stiff resistance from the business community.)

Ss should be used with care, and their results are suspect anyway. That's the least message I've tried to convey for years.

Excel != Database Software

[cyranoVR]

The worst misuse of Excel is as a database. And yet Administration / HR / Marketing staffers always end up using excel to store extremely important data. Sales records, accounts receivable, timesheets, inventory, contact lists - you name it.

And they always organize the list with subtle font-weight and cell-shading. Woe unto the intern that accidentally Selects Edit->Clear->Formats. Woe unto the manager that needs to sort the list by "bold" or "light-green."

Unfortunately, MS encouraged this perversity by including the menu option Data->Forms

What were they thinking?!

In the end, I have to come along with MS Access and clean up the mess. Oh well, it's a living :-\

statistics and graphics

[phiala]

I haven't seen anyone else bring this up (and actually saw some posts claiming they use Excel for statistics and its "nice graphics").

Excel charts are generally horrible- the default values tend to include extraneous "chartjunk" (to borrow a word from Tufte). It is tedious to get a nice-looking chart from your data, and seems to be very difficult to produce any even mildly-complicated graphics. I use R for charts, and I'm familiar with several linux charting apps of varying degrees of complexity, but I'm not sure whether there are any good OSS apps for Windows.

A much bigger problem, though, is Excel's lack of statistical quality! This website provides a quick overview, with links to some more detailed references. Excel is occasionally accurate for simple analyses, but why on earth would you use an unreliable program for _anything_? The only way to be sure that Excel did your ANOVA or whatever correctly is to redo it in better stats software, and at that point I don't see the advantage.

This is an issue that comes up regularly on scientific mailing lists. Lots of people seem to take the path of least resistance and use Excel for both their analysis and presentation. Ick!

Data resposibility

[kcdoodle]

I often get non-standardized spreadsheets and tables of data with this exact same problem. King-sized can be expressed many different ways.

I always take the bull by the horns and normalize the data myself (UPDATE T_PROD SET SIZE='KING' WHERE (SIZE='K' OR SIZE='74x80' OR SIZE='KNG')).

My simple rule is "never make the data worse". If you substitution fixes 100 bad entries and creates only 1 questionable result, it is a good substitution.

Remember, when faced with non-standardized data, the data ALWAYS has lots of errors in it. When you are through normalizing the data it WILL be better than when you started. The client can then go clean up the rest (they will have to even if you don't normalize).