Using Excel to Manage Simple Databases

Here is a video from freetutorials (youtube) on using Excel to manage simple databases.

Send post as PDF to

Microsoft Office Assistance: Using Access or Excel to manage your data

When to use Acess (database) vs. when to use Excel (spreadsheet).

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).

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.

“The most common task in Excel is the management of lists. If you can store your data logically in a single worksheet or table, then do it.”

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.

“In Access, you have a primary key. In Excel, you have a cell reference. In life, you have a social security number. It’s the best unique identifier you have.”

Source : Microsoft Office Assistance: Using Access or Excel to manage your data

Send post as PDF to