Data Decisions: Choosing the Right Tool
A common dilemma of Microsoft Office users is whether to store and manipulate data in an Excel spreadsheet or an Access database. Even though the manner and flexibility in which data can be handled varies greatly between Excel and Access, they have similar capabilities. Both applications:
- Can handle large amounts of data.
- Can be shared among several users.
- Allow filtering, sorting, and querying the data.
- Have data entry form options.
The Deciding Factor
One of the most important factors when deciding between Excel and Access is the complexity of the data. Is the data simple enough that you can store it in a single set of rows and columns with no other supporting data needed? Or is the data related in such a way that you will need to link data from one set of values to another?
When to Choose Excel
Excel is a good choice for “flat” files, that is, data that is easily stored in a single spreadsheet with no duplication of data. For example, a simple list of library patrons could be stored in rows and columns. Categories such as FirstName, LastName, Email, or Phone can be specified in the columns, while each patron would be entered on a distinct row. This list could be quickly sorted, filtered, and printed out from Excel (see Fig. 1). If this type of file is too limiting, Access is probably a better choice.
Figure 1 Excel spreadsheet
When to Choose Access
Access stores data in tables, an arrangement of rows and columns referred to as records and fields. A single table looks very similar to an Excel spreadsheet. However, the power of Access comes from its ability to bring separate pieces of data together by establishing a relationship between tables.
Returning to our list of patron data , if you also needed to track the items charged by a patron, then Access would be more appropriate. You would store the patron information in one table (Patrons) and the charged item information in another (ChargedItems). Then you would define a relationship between the tables using a key field, a field that contains a value that identifies each record in a table as unique.
In this example, each patron is given their own PatronID. Now each patron appears once in the Patrons table, with the PatronID and personal information stored in the fields established for that table (see Fig. 2).
Figure 2 In the Patron table, the Patron ID serves as the key field that identifies each record as unique.
For every item any patron checks out, a new record is added to the ChargedItems table using the appropriate PatronID accompanied by the data describing the charged item transaction (see Fig. 3).
Figure 3 The ChargedItems table is related to the Patron table through the Patron ID.
Since the tables are related using the PatronID, Access can always track what items any patron currently has charged as well as identify the patron that has charged any given item (see Fig. 4).
Figure 4 This form pulls data from both tables to display Patron data and Charged Items information.
To store this expanded information in the Excel “flat” file mentioned above, you would have two options. One, you could add a row for each new item that any patron charges. However, for repeat customers the patron information would be duplicated again and again. This would result in confusing and inconsistent data, not to mention much unnecessary data entry (see Fig 5).
Figure 5 Excel spreadsheet storing Patron and Charged Items data. The red brackets indicate duplicate entries, and the red circles point out inconsistent data.
Or you could choose to extend a patron’s row by adding Charge 2, Charge 3, Charge 4, etc., in additional columns. This, however, would spread out the data, making it difficult or impossible to query, sort, calculate, or report. Eventually the number of items charged by a single patron would be limited because Excel has a finite number of columns (see Fig. 6).
Figure 6 Excel spreadsheet with additional columns for each item charged.
Excel is an excellent choice if you do not require a relational table. This is especially true if the data is heavy with numeric content, such as statistical or budget data. However, if you find that you are repeating data again and again in multiple records (as in Figure 5) or if you are adding columns for multiple items (as in Figure 6), then these are some clear signs that data should be moved from Excel and into Access.
There are many resources online for help. If you'd like to read more about this topic, I'd recommend the article Using Access or Excel to manage your data from Microsoft Office Online.
TDS is not responsible for the content of external internet sites.