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.

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! 🙂

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.

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.

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.