Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I work in a company where there are Excel spreadsheets everywhere. As my colleagues are non-programmers, I'm sure they haven't contemplated that there might be a better/easier/more productive way of managing their data. Naturally, I advocate an application of sorts that can leverage the features you find in relational databases and interactive front ends, tailored to the needs of the various workflows currently distributed over disparate spreadsheets.

The problem I have is that I have tried to explain the merits of such systems to various parties, but short of actually writing them from scratch I'm having a hard time convincing them, or even just exposing them to the greener grass. People in general understand Excel (even if they aren't power users) but perhaps go all fuzzy when the word 'database' pops up or you start talking about 'code'.

Can anyone suggest a method, with evidence of how a workflow was improved by switching from spreadsheets to an actual application?

UPDATE

Thanks to everyone for their replies. It seems that there are no textbook methods as such, more a range of philosophical approaches to the problem i.e. the problem isn't that Excel is profligate, the problem is in how to communicate the fact that there are sometimes better tools for the job.

share|improve this question
26  
Don't rule out the possibility that the disadvantages of your proposed plan outweigh the advantages. Excel is a wonderful tool for non-programmers. –  Avner Shahar-Kashtan Jul 25 '13 at 11:14
1  
It's not profitable to impose your worldview on experts in their domain. What you can do, though, is prototype an application and showcase it. Start small, make interface with existing Excel spreadsheets easy, and always test your ideas before showing them off. –  Deer Hunter Jul 25 '13 at 11:26
    
@avner - I agree that in some situtations you are 100% right as deer points out. Although, one of the problems I see all the time is that my colleagues aren't necessarily experts...! –  boatingcow Jul 25 '13 at 12:27
1  
Maybe not experts at databases or even Excel but compared to most they may be expert in their business domain and as that (should) drive the decisions that's ok. –  Michael Durrant Jul 25 '13 at 16:21
1  
Database + application could very well be database + Excel. My colleague uses it to present numbers from database to internal Excel user. You just tell excel to read from database (dsn). For external users we have app to generate html from the same database. –  imel96 Jul 25 '13 at 22:39

5 Answers 5

up vote 12 down vote accepted

When people manage data, there are three fundamentally different ways they can add value:

  1. Computing
  2. Storage and Retrieval
  3. Forwarding and Sharing.

For computing at the level of simple arithmetic, you can't beat Excel. Even if you are an experienced programmer, you can build a spreadsheet in a fraction of the time you'll take to write and debug a computer program. You can even group data into tables and use lookup functions to gain some of the advantages you get with queries. The pivot table and the graphing features make summarizing data simple and easy.

For storage and retrieval at the file/document level Excel is about as useful as MS Word or MS Access. For retrieval at the level of SQL queries, MS Access works a lot better than Excel, although Access is severely limited as you will know if you've ever compared Access with an industrial strength DBMS.

For data sharing, Excel is extremely primitive. Most shops that adopt Excel by contagion run into trouble at this point. Your question suggests that this is the case at your shop.

There are several problems in introducing databases in a place that is not ready for databases. One is political. The people who jockey their spreadsheets may recognize the system wide problem, but they are extremely pleased with the control they have over their own little piece of the data. You can expect resistance if you try to substitute central management and control for the decentralized solution you have now. Some of that resistance is justified.

Another is the cost of databases. As has already been stated in another response, the disadvantages of database may outweigh the advantages.

Another is the credibility of databases. People who put data into databases usually do so because it's their job or maybe to gain something for themselves, like placing an order on a website. They don't do it out of community spirit. At least, not much. Without good inputs, the database never gains credibility.

A solution I am working on now at a site where spreadsheets are just catching on is building a prototype database in MS Access. There is a distinct downside to this: people may learn the wrong thing about databases, something they will have to unlearn at a later stage of evolution. I also think that the reporting features of Access are sorely lacking. and if you are thinking of concurrent users, you have to think beyond Access although Access is making progress inthat regard.

I like three things about Access: it's simple, quick, and cheap to produce primitive prototypes. There is almost no learning curve, especially if you already understand data analysis and table composition.

Second, it's less intimidating than a big centralized DBMS. When users are shown how to open an Access table in table view, by just clicking on it, they lose their fear of tables. And the fact that a single file stores the data, the data definitions, and the application keeps things simple for people who don't think in terms of systems.

Third, it plays well with Excel and Word. copying a table or query into an Excel spreadsheet is as simple as clicking on a button.

Before I could get to this point, I had to overcome a somewhat snobby attitude I had about Access. It's not SQL Server or Oracle. But it does have it's uses, as long as you understand the limitations

Having said all this, you will still be faced with a monumental task when it comes time to move your shop from a lot of little Access databases into a single large scale integrated database, with professional management and shared resources.

share|improve this answer
    
Thanks for your considered reply. Access is a dirty word in most places I've been, but I wonder if it wouldn't just be useful enough as a 'prototyping tool' for this company. In addition, I appreciate the power of Excel (like with the graphing functions) so will always have a connector available to plug Excel into any solution I create. –  boatingcow Jul 25 '13 at 13:43
    
+1, Access is a great tool in the right circumstances –  Wyatt Barnett Jul 25 '13 at 19:47
    
I understand why Access is a dirty word among many experts. I would have downvoted it myself before I got rid of my snobby attitude. The thing is, as Wyatt said, there is a class of problems for which Access is an entirely adequate tool, and where you don't need the power of a full blown DBMS. The trouble usually comes when somebody who never learned the principles of sound database design (including but not limited to normalization) tries to put together an Access database just using naïve intuition. The result is usually terrible. –  Walter Mitty Jul 25 '13 at 20:35
1  
Many of the shortcomings of Access can be mitigated by using a SQL Server Express database for the data store instead of an .MDB or .ACCDB. You get better performance, way better stability, and all of the RAD advantages that Access provides. –  Robert Harvey Jul 25 '13 at 22:19
    
@RobertHarvey - good plan, even if you only keep the UI in one access project and the data store in another that can go a long way. –  James Snell Jul 26 '13 at 13:23

When you are trying to persuade people about the advantages of using a different system you should avoid trying to persuade them.

I know that sounds very strange.

But what I mean is this:

People like using the current system. It works for them. It's easy to use and quick to make changes. To persuade people to use another system, treat them like adults:

Lay out the pro's and con's of excel and of a database approach. Explain the short and long term consequences of each. If you can mention all the benefits of BOTH and still persuade folks to change then you have made a convincing enough argument. The key is to let THEM make the decision that a database approach is better based on the facts and concepts that you've presented.

share|improve this answer

I'd like to add some ideas/thoughts to @WalterMitty's nice answer.

He's right in that it really depends on the purpose of the Excel files. I would add however that if they contain complex rules AND data, you could think about leveraging the power of rules engines. Rules engines such as Drools, OpenRules or OpenL Tablets allow you to use Excel files for storing the business rules, and (at least for Drools that I know), to update these rules on-the-fly.

It's an excellent way for business-minded people to keep control of their ... business, and to properly separate the business from the datan which is your job, or at least the job of the IT departement. I think business people would agree that the storage, maintenance, and backup should be taken off their shoulders. You should frighten them a bit with the backup part in fact:

  • What would happen if the guy's hard drive suddenly dies ?
  • What would happen if the file gets corrupted, deleted, inadvertently reset ?
  • If we migrate to Excel 2013 from Excel 2007, will everything be alright ?

Now, if you go that way, also think about the fact that if you add databases, it will be your job to maintain their health and integrity. That is, you should conceive a way, or/and prepare your IT infrastructure for keeping data backups. Although not as sophisticated as Oracle's, or IBM's databases, Microsoft Access can produce backups (though I'm not sure this can be automated).

Last but not least, if they accept, don't forget that you would need someone to analyze the business data, conceive the database, prepare a migration plan, allow business people to submit evolution plans, etc. Depending on the amount of data, it may be an IT project of a certain size, and you will most likely be responsible for it. Brace yourself! :-)

share|improve this answer

The problem we had was that of different excel sheets with different purposes but with same contacts. Then when a contact changed their adress it would be hard to synchronize all excel and while their adress was changed in excel sheet 1, he would still get mail because excel sheet 2 wasn't updated.

We solved this with a central database of clients.

share|improve this answer

I worked for an organization that had spreadsheets all over the place, largely because the IT resources were hopelessly overstretched. They would have moved this to an application if it was possible - it simply wasn't possible.

One of the things I did after I left that organization was created a tool for generating C#/SQL Server table maintenance programs from Excel spreadsheets. It assumes the column header is the column name for purposes of table construction and field labeling. However, this is a crude instrument. It ended up getting used for things having nothing to do with Excel.

The issues we had ran into several categories:

  • Validation: When the validation rules are complicated, people have to keep the procedures in their head. It's possible to design the spreadsheet with 'if' statements all over the place, as long as people understand how these work.

  • Sharing: as pointed out elsewhere, if one person has a network share spreadsheet open for read/write other users can only read. We were not at the point where we could open a spreadsheet in a collaboration mode. That was another thing the IT people couldn't handle at the time.

  • Schema consistency: In the situation I was in we had a spreadsheet for each state: the format was common but the state by state data traveled in separate files. If someone made a creative use of a column then everyone had to agree on this use, which was not always assured.

The spreadsheets were always on a network share and the server was backed up every night, so none of this was an issue. There was also the distinct possibility of one of them being accidentally emailed to a destination where it didn't belong. While the likelihood of real harm was slight in this case, the organization was one where 'leading by example' was critical to it's credibility. That kind of mess up would have been embarrassing.

I saw an ad on Craigslist where someone needed an "Excel Guru". I passed this to a friend that was between jobs, and he found that the company had a construction contracting estimation system operating at sites all over the state. Someone here had arbitrary doctored up the system to 'make it work more in they're style'. Whatever that style was, it had caused a lot of damage. He spent several days fixing it. Hardly had he turned in his work and gotten paid they called him back to fix another one in another city. Same story: someone arbitrarily 'fixed' it to do what they wanted. This one was even worse.

Complicated systems like this need to be 'out of reach' of ad-hoc customization.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.