It was a hard truth to learn, and it took years for me to learn it, but facts is facts:
There are times when Excel just isn’t the right tool for the job.
Now, Excel and I get along swimmingly. I’ve devoted hundreds of hours to learning its “ins and outs,” and I regret none of that. To this day, I still get a little giddy when I discover a new function that’ll be useful to me somehow.
But darn it, Excel just isn’t the right tool for managing data … especially if it’s relational data. (“Relational data” meaning that data in this table over here has a specific relationship to data in that table over there … and maybe even more tables of other data beyond that.)
Excel Was My Best Friend.
Access Was a Weird Guy Across the Street. (Nobody Talks to That Guy.)
I can’t remember when I was first introduced to Excel — probably the mid- to late-1990s — but I was a fan from Moment One. I also can’t remember when I was first introduced to Access (the database program Microsoft bundles with its Office suite), but it was probably ten years later, if not more. It took me a long while to figure out why anyone would bother with Access. Excel was SO much easier to learn and implement for whatever tasks I had at hand. In fact, at my Day Job, while Excel licenses were utilized by just about every employee in the place, it wasn’t until a few years ago that Access licenses were made available — and even then it was by request only.
So without knowing it, early in my career, I was trying to shoehorn Excel into doing data-management jobs for which it wasn’t really built. One particular spreadsheet I created, maintained, and added data to over 15+ years, across multiple worksheets … only to find out last year why it should’ve been exported to an Access (or similar) database long, long ago.
(When you need to search for, say, multiple part numbers across about 20 separate worksheets, you figure out pretty fast just why a good relational database is as valuable as it is. And why Excel isn’t good for that AT ALL.)
So now I have this Excel spreadsheet with 15 years of work-related data crammed into it. It’s not unusable by any stretch, but depending on the searching that needs to be done, it can be, uh, ungainly. At best.
For THAT data-management job, I should’ve used Access. I’d be much better off now.
Excel vs. Access: Which Tool for the Job?
So, having lived and learned, here are MY thought processes for deciding whether to use Excel or Access for any given job:
- If it’s a numbers task, and you want pretty charts and complex calculations run on the data, then Excel.
- If it’s a flat-file, one-table sort of database, then either Excel or Access might do the trick.
- If it’s a multi-table database with no relationships between tables, then Access is probably a better choice. But Excel could be workable. (Especially if complex searching is not required.)
- If it’s a data-driven task, which will rely on text, numbers, text and numbers, or other file types being accumulated, modified, and analyzed over time, then Access.
- If you need to implement strict controls on non-numeric data which users enter and/or modify, then Access.
- If your data needs to have relationships (e.g., this table shows part numbers, and that table shows work operations, and the two tables are somehow “related” in use and/or search-ability), then Access.
- If the end result of your data requires complex queries, searches, and filters, then Access.
Note that these aren’t hard/fast rules. They’re just how I approach the problem now. You see, I’m all the time watching folks try to make Excel handle jobs which would be much better suited for a database program of some sort. But suggesting such a thing (“What? But I already know Excel!”) is almost always dismissed out-of-hand. At some point, I’m afraid, they’ll be sorry.
Hey — just because you CAN whack a nail into a two-by-four with a heavy Swingline stapler doesn’t mean that’s the way you OUGHT to do it.
Here, by the way, are Microsoft’s thoughts on the issue:
Office.com: Using Access or Excel to Manage Your Data
Much wordier than mine, but very thorough, and worth a read-through.