Access vs. Excel - Which should I use for my database?

As a Microsoft Office software instructor for fifteen years, I am sometimes asked whether to use Access or Excel to keep track of database information and calculate summaries, subtotals or complex formulas and functions with the data. The fact is that Excel, which is probably best known for its number crunching capabilities, has robust database functionality. Access, which is indeed a database program, can perform many of the complex math formulas and functions that Excel is known for. So which should you use?

Like many people who use the Microsoft Office Suite, you probably have found Excel to not only be a good tool for spreadsheet features and "number crunching", but also for keeping track of lists of data. It is quite often one of the first Microsoft Office Suite applications users learn. Many people use and understand it to varying degrees. This makes it advantageous for sharing your data, since others commonly use Excel.

Perhaps you have an employee list, record of invoices, or names and addresses of customers, friends and family in one or more Excel workbooks. Entering your records into an Excel worksheet is fairly straightforward, as is adding column or field headings. You might have found the powerful sorting and filtering functionality, or even the extraordinary pivot table feature. In my experience, these have made Excel an excellent choice for many of the typical database requirements most users have.

Excel's advantage is its universal usage, comparative ease of use and quick solutions for simple databases. However, there are some limitations. The number of records in your database is limited to the number of rows in a worksheet. In the versions prior to Excel 2007, 65,536 rows was the limit on each sheet. Excel 2007 now allows 1,048,576 rows per worksheet. The other major limitation is that by its nature, data stored in a worksheet is a "flat" file which means all of the data is in one list. The result is that each record in your worksheet could have redundant data, such as customer name, address etc. repeated many times on a invoice list.

If you have more complex requirements for your database, Access is a great choice. Although Access is not as easy for some people to learn on their own, it was designed to be both powerful and user friendly. This means that with some research and/or training, you can be on your way to creating a very powerful database. Access's main advantage over Excel is that it is a relational database. Meaning it has the capability to store data in more than one table and relate or link the tables on common fields. This eliminates the redundancy of a "flat" file and makes entering information easy, with data entry forms using features which eliminate or reduce typing and ensure valid data is entered. Calculations and subtotals are available in the queries that access the data, in data entry forms and printed reports.

Once you are comfortable with both applications, then the choice will be easier to make. If you are most comfortable with Excel, and it meets your needs, remember that all of the data you enter can be imported into Access later, should the need arise. Conversely, Access has a very simple feature to export its data to Excel. One last thing to keep in mind - there is no rule preventing you from using both applications, if you are at ease doing so. As an Access database designer, I have created databases that use the efficient data storage of a Access's relational database capability, and added programmed automation to export portions of the data to Excel. The allowed others to use Excel's powerful spreadsheet analysis capabilities on the data originally entered into Access.