Where should I put my data, Excel or Access?

  • Article

When you have a lot of data and it needs to be organized, do you use Excel or Access?

I get this question a lot when talking to people and I wish I could give you a short answer.

Well, I guess I can: It depends.

I understand why people are confused. In Access, you can specify the types of information (fields) you want to store about items, customers, or whatever you’re tracking (records). But you can do the same thing in Excel. Just substitute columns for fields and rows for records.

Access and Excel are alike in many ways. They can store millions of records. They can pull in information from outside sources. Both also can run queries, a fancy word for an advanced search that allows you to see which records meet certain conditions.

They even look similar. In Access, you can view your data in a giant table, which looks a little like an Excel worksheet.

To decide which program is right for you, you need to understand their differences.

Numbers vs. text

Is your data primarily numbers? Is your goal to do a financial analysis or crunch numbers in some way? If you said, “yes,” Excel is probably your best bet.

While Access can handle some pretty complex math, it’s no match for Excel, which has hundreds of built-in formulas and financial analysis functions. Excel is the program to use if you need to do a cost-benefit analysis, calculate a return on investment, make charts, or do any sort of statistical analysis.

On the other hand, if you plan to store mainly large chunks of text, you should probably put it into an Access database.

Complex queries

Both programs are great at searching through your data to answer any question you might have, but if you plan to pose especially complex or highly varied queries, you may want to use Access.

Access can easily run complex queries that search through several databases. It can almost instantly generate a list of all your customers that have more than 500 employees or find clients with cumulative purchases that exceed $2 million who haven’t placed any orders within the past six months. Does this sound like the type of information you need? If so, build a database in Access.

Pictures

Do you want to store pictures with your records? This can be useful if you’re putting together an inventory database or a directory of employees. If you do, then you should definitely use Access. Access can store pictures, web links, and most other types of information as easily as someone’s name.

Size of the job

While Excel has a lot of database features, you may want to leave the really big jobs for Access. If any of these statements are true, Access is probably your best bet:

  • Many people need to use or add information at the same time.
  • You need to work closely with data in a SQL Server database.
  • You have more than 20,000 rows or records.
  • You need several databases to work with each other.

For smaller projects, especially where all the data can neatly fit in a single worksheet, Excel may be just fine.

Don’t stress too much about your choice, though. Both programs are part of the Microsoft Office system, so it’s easy to transfer your data back and forth. You may want to have your master data stored and maintained in an Access database and then pull out appropriate cross-sections to analyze in Excel.

Suzanne