By Emma Nelson

I don't have a favorite when it comes to using Microsoft Excel or Microsoft Access, but sometimes the best program to manage your data is the one you least expect.

Making the right choice is critical if you want to access and update your information with maximum performance and accuracy. In this column, let's talk about how to pick the program that will work best for your data.

In both Access and Excel, you can:

• Run powerful queries to sort and filter your data.
• Run sophisticated calculations to derive the information you want.
• Use Microsoft PivotTable® and Microsoft PivotChart® views to work with your data interactively.
• Generate reports on your data and view them in multiple formats.
• Use forms to add, change, delete, and navigate your data easily.
• Create a Microsoft Word mail merge — for example, to mass-produce address labels.
• Connect to external data and view, query, and edit it without having to import.
• Create Web pages to display your data as read-only or to access it in an updatable format.
• Import data from external databases (Access, Microsoft SQL Server™) and other file types (.txt or .htm).

Both programs organize data in columns, also called fields, that store a particular kind of information, or data type. At the top of each column, the first cell is used to label the column. One difference in terminology is that what is called a row in Excel is called a record in Access.

As an example, you could create a personnel list. Your list might have five columns to organize employees' ID numbers, first names, last names, telephone numbers at work, and dates of hire. The cell row at the very top of each column would contain text labels to describe the data.

Why the comparison?

This is a fair question. After all, Excel is not a database management system. It is spreadsheet software, and it stores units of information in rows and columns of cells, called worksheets. The most common task performed in Excel is the management of lists such as telephone numbers and personnel data. In comparison, Access stores data in tables that look much the same as worksheets but are designed for complex querying in relation to data stored in other tables and locations, and even in fields in other tables.

So, while both programs work well for managing data, each one has clear advantages depending on the type of data you are managing and what you want to do with it.

The key to your decision: Is the data relational or not?

If you can store your data logically in a single table or worksheet, then do it. By logically, I mean that the data in each column is directly related and need only reside in a single, flat table in either Access or Excel. It should reside in and be updated in the same view. Data of this kind, contained in a single page or sheet (not multiple) is called flat or non-relational data. The personnel example above is a case in point. You would not store an employee's last name in a table other than the one that stores the employee's first name.

However, if your data needs to be stored in more than one table, then you need a relational database. Each table is basically a description of a type of data (such as orders for a customer). If you require a relational database, you've identified a one-to-many relationship in your data. For example, if you have a customer order database, one table will contain customer names and another will contain their orders. And a single customer can have many orders. Additionally, you might want to have another table for order details since each order can have multiple line items. Relational data is best stored in Access.

Do you notice this direct correlation between the size of your data and the challenge you have in organizing it effectively? The more data you have, the more likely you are to store it in multiple tables in Access. To help manage your data and keep it accurate, Access and Excel provide you with unique identifiers. In Access, a primary key (an icon in the shape of a key visible in Design view of your table) uniquely identifies each record. In Excel, each row is numbered and each column is denoted by a letter, so each cell or range of cells has a reference such as B5. In life, you have a social security number. It's the best unique identifier you have.

Ever noticed how, when at the doctor's office or calling an insurance company, you're asked for your social security number as well as your name? Last name and even full name are often not unique. Unique identifiers are almost always numeric. They preserve the integrity of your data and allow no duplicate records or data in cells that you don't recognize (called Null values). Numeric identifiers also provide the quickest way to retrieve data when searching or sorting.

When to use Access

Use Access when you:

• Require a relational database (multiple tables) to store your data.
   
• May need to add more tables in the future to an originally flat or non-relational data set.

  For example, if you want to keep track of customer information such as first and last names, addresses, and telephone numbers, but that information may grow to include actions by customers such as orders, then consider starting your data project in Access.

• Have a very large amount of data (thousands of entries).

  For example, if you work in a large company and are required to store personnel information, then use Access.

• Have data that is mostly of the long text string type (not numbers or defined as numbers).
   
• Rely on multiple external databases to derive and analyze the data you need.

  For example, if you need to import or export data regularly from Access databases, it may make the most sense to work in Access to maximize compatibility.

• Need to maintain constant connectivity to a large external database such as one built with Microsoft SQL Server.
   
• Want to run complex queries.

  For example, if you work in a large company that takes customer orders, you might have to look up customer names stored in a SQL Server database while taking new customer orders in Access. You can maintain a connection to the SQL Server database from within the Table view in Access. When you add or look up a customer name, you are working against the SQL Server database, but the new order details you just took are stored locally in Access tables.

• Have many people working in the database and want robust options to expose that data for updating.

  For example, Access offers data access pages for the more technical user and forms if you want to be more user friendly.

When to use Excel

Use Excel when you:

• Require a flat or non-relational view of your data (you do not need a relational database with multiple tables).

  This is especially true if that data is mostly numeric—for example, if you want to maintain a financial budget for a given year.

• Want to run primarily calculations and statistical comparisons on your data — for example, if you want to show a cost/benefit analysis in your company's budget.
   
• Know your dataset is manageable in size (no more than 15,000 rows).

Protecting your data in Access and Excel

No matter which program you choose, it's important that you know how to help protect your data. Here are a few tips:

• Create a backup copy of your file each time you update your data.
• Avoid blank cells in your rows and columns that contain data.
• Use Show/Hide options to remove critical data from view where possible.
• Take measures to control user access to your data to help protect it. Security measures include:
  • Encryption
  • Password requirements
  • User-level permissions
  • Digital signatures
  • Read-only privileges

For more information on protecting your data, you can search for Help in either of these programs.