Microsoft Excel is great at a lot of tasks, but it has limits as a database manager. If your Excel data worksheets are beginning to get out of hand, it may be time to call in a little help from Access.

Like this article? We recommend

Like this article? We recommend

I'll get my bias out of the way right off. I think Excel is the greatest
application ever. If this were a perfect world, I'd do everything in
Excel: budget, write, balance my checkbook, answer my email. Unfortunately, as
nearly perfect as Excel is, it isn't the best tool for every task.

Basic Excel Databases

Excel does a fine job of maintaining simple databases. It's fast and
easy to learn. As long as your data fits this description, it's officially
a "database:"

a single list

headings for each column in the first row

each row a unique value

no blank rows

This kind of worksheet table is called a flat-file database. If your
needs are that simple, there's no reason to change. But at some point
you'll probably bump up against the limits of Excel as a database manager
and wish for the sort of flexibility that Microsoft Access provides.

Let's use an equipment inventory as an example (see Figure 1). This
database started out as a simple list in Excel, but now we might want to add
more complex information, such as who uses the equipment or its maintenance
history. If one piece of equipment is assigned to different people over time or
has multiple service incidents, it would be awkward (to put it mildly) to try to
maintain that kind of detail in a single list. Better to create multiple tables,
linked by unique elements such as an asset tag number.

Before getting too carried away with planning your new Access database, you
need to decide what to do with your existing data in Excel. Is it possible to
move completely to Access, or do you still need to maintain the database in
Excel for backward compatibility? (For example, maybe the person who maintains
the data doesn't have Access or doesn't know how to use it.)

Excel data can be imported into a new or existing Access table, or it can be
linked into a table to make the data editable by either application. This
article covers importing and linking, how to create a basic data-entry form to
view the data one record at a time (rather than in table format), and using
filtering and sorting.