Access vs Excel—Which should we use for what?

Microsoft Access, Microsoft Excel, Microsoft Office, PowerPivot

Judging Access vs Excel can take thought. Most people find Microsoft Excel fairly easy to learn—it has a fairly gentle learning curve, the fundamentals only take a short time to pick up, and the program is actually fairly versatile—it’s not just a ledger book on steroids. Microsoft Access, on the other hand, can be kind of intimidating. Some folks hear the word “database” and, to quote the Joker, “they start losing their minds!” The whole idea of a database program is scary because there’s more to learn, more to set up before you can use it, and more to manage even when it’s working fine.

So let’s just ask the question: When should we use each one?

To answer this, we need to understand what each one can do. Excel is a spreadsheet program. Even though information on one sheet can talk to info on another, it’s primarily meant to do math-related things. So if all you need is the equivalent of a sheet of graph paper to visually organize, say, a personal or house inventory, Excel does pretty well. You could use Word for this same purpose, and have about the same amount of work, if you don’t want to use Excel. Formatting and other aesthetic things are only about as hard as in Word, so it’s almost as if you built a giant table in Word and jotted stuff in it. Or if you want to budget monthly, quarterly, and annually, and have all of those update each other, great.

But if you need to track, say, company employee info, you may run into trouble with Excel. You may need to have three or four kinds of information that all relate to the employees. But they’re not directly related to each other. Personal info (home address, birthday, family), office info (building, floor, office number, phone number, business email), health plan (HMO, policy type, policy number), and maybe transportation (car make and model, parking spot, bus route, commute time).

Yet all of these need to tie together, as it were. You might have each of these connect to the others through, say, the employee ID number or Social Security number. Make that ID part of each of the four categories so they have a common element. This is where the term “relational database” comes from. The different categories of information relate to each other, like a baseball team. Each player has a separate job, but all cooperate to play and win.

If this idea makes sense, then you understand one of the main concepts of the relational database. Like Access.

So the basic answer to the question is, If you have just one kind of information (or several that are unrelated), you can store each chunk on a separate worksheet in Excel and probably have no trouble. But if the different subsets (or as Access would call them, data tables) are related after all, using Access to store the information and work with it would actually be worth the extra time and effort. A pickup truck can haul some serious cargo. A bicycle is good for getting around by yourself. So Access vs Excel. (Sort of.  🙂  )

Postscript:

I’m not bringing up PowerPivot here—which lets us do some database-ish things in Excel. It’s a different tool again from Access, though useful. And not everyone knows about it, or how it works. We do teach it, but again, it’s a somewhat specialized tool. Access is a more general database application and has more multi-user features, making it more suitable for business database work.

To learn more, take a look at our Access classes and Excel training.