How to Decide if You Should Use MS Access or MS Excel

As a massive geek who loves creating databases, I know that sometimes it’s easy to decide to use Access when Excel is more appropriate…and I also know that some people aren’t as comfortable in Access, and so they might use Excel, even when Access would help them a lot more. So, how do you decide which is the appropriate program to use to store, access, and manipulate your data?

Here’s the big decision to start with – is the data you have relational or not? Not sure? Relational data is data that has a relationship. For example, in Access, you can make a relational database. You can create two tables; one will contain the names and addresses of customers. Another will contain orders that the customers make. This database has a relationship in the data – the customers will appear in the orders, and to be more specific, you have a one-to-many relationship, assuming that one customer can place multiple orders.

In Excel, you cannot create relationships. You have a static table (also called a “flat file”). If one customer makes multiple orders, you may need to have multiple lines for that same customer, each one repeating the information. This gives you a greater chance of having inconsistent data. You can easily change a customer’s address in one line but not in another, making your data unreliable.

If your data isn’t going to change very often, or not at all, then you may be looking at Excel as a good option. If you’re going to need to constantly pull new information from other sources, do a lot of updates, or maintain a connection with another database, then Access is probably going to be better for your needs. While both of them allow you to easily change and access your data, if you’re going to constantly need to make changes (and potentially also need to keep increasing the size or the file or add more tables), then Access is better. Excel’s main purpose is to make and maintain lists. If that’s not what you want to do, then it might not work for you.

Are you tracking sales figures and need to do a lot of math? Complex formulas? Excel is your buddy! Sure, you can do a lot of the same work in Access, but if your data doesn’t require those relational tables, then why go through the additional work of creating a full database?

You can create queries and run reports from both Access and Excel; however, you have more options in Access because of the relational nature of the database. You can create more complex queries and reports than you can with Excel. But if your reports are going to all be financial or statistical, then Excel might be best. Excel is really meant to deal with numeric data above all else; it is capable of dealing with other data, but it loves its numbers.

Excel can be shared, but not as easily as Access can be shared. Access lets you build numerous forms, reports, and queries, all of which can be locked down or open, allowing you the control over what data can be seen, shared, and exported. While Excel may also be locked down and its data can also be controlled, it is not as simple to create forms and queries with it, and there is more danger in letting others into your data. Simply put, Access can be made “friendly” to users who are not necessarily comfortable with spreadsheets or other non-WYSIWYG programs.

Excel is not meant to handle much, about 15,000 rows of data. Sure, that might sound like a lot, but what if you’re working at a university and you want to track all the students who’ve ever applied or attended? You might quickly hit that limit.

Access, on the other hand, can connect to Microsoft Servers and handle a much larger quantity of data. It is not limited, and the use of the relational tables means that you can handle and combine much more data than a simple flat file.

So, overall, while Access is great anytime you want to build a relationship between your data, Excel is there for you when you want to manage money, numbers, or statistics. The choice is yours!