What Is the Difference between Access and Excel?

There's probably a frustrated IT or database person telling someone that they shouldn't be using Excel, that the data should be in Access or a database. The Excel user on the receiving end is probably wondering what Access is.

They seem similar. They both store data as rows and columns, but it's the differences that make a difference.

Excel lets non-programmers manage data in flexible ways.

That's why people like Excel. You put your lists in there, and it's easy to add different kinds of categorization. You can use highlights, colors, boldface, italics, and different font sizes. Some people use elaborate indentation. It's pretty awesome... for human beings.

For a computer, that's all "mess". While people can tell each other "the red background means so-and-so owes money, so make them pay up first," getting a computer to deal with that is tougher.

Someone who knows how to program the spreadsheet - to do math, or comparisons, or use filters, or make crosstabs - they'll tell you to add a column, and put a 1 or 0 in there to indicate that. They can then use a filter to produce a list of people who owe money. (At this point, this person might suggest using Access. That's usually ignored.)

Incidentally, the spreadsheet was invented in the 70s to do the math, not to store data. People just started using them to store data, and when the Mac and Windows versions came out in the 80s with fonts and styling, people started using the styling to organize the data, too. That's how people are. We do things the wrong way, and if it looks nice, we think it's cool. Yeah, we're stupid, or something.

Access lets programmers or semi-programmers manage data in flexible ways.

That clever person who could do the filters took the spreadsheet one step towards being used like a database. Access is a database system.

The main problem with using Excel to store data is that it's difficult to store large amounts of data, and manipulate it. The bigger the list becomes, the tougher the task becomes. Managing 100 rows is easy. Managing 1,000 rows is tougher. Some people end up making complex macros to perform the manipulations.

That's where Access shines - when you have a lot of data. Like in the example about the red highlight above, you can't resort to styling tricks; you have to make new columns and put values in them.

Instead of filters or macros and direct manipulation of the data, you perform "queries". A query is a database's way to extract a subset of rows from the database.

Access has a query designer.

Here's an example of a query:

SELECT name, address FROM customers WHERE debt=1

That selects a subset of customers where their "debt" value is 1, which means "true" in our system. That's not too hard, it is?

Access separates data entry from data reporting.

In a spreadsheet, the data you enter is the data you see, sort, and filter.

In a database, the data entry is separated out. Database systems typically have "forms" for data entry, and "reports" or "report writers" to print or export data.

We're all familiar with databases because we use the web. Some websites have fill-in forms. You submit them, and, generally, you get some information back, like an order number or an email. That's analagous to forms, databases, and reports: the fill-in form saved data into the database, the page with the order number or email you receive is a report. The report is mostly a big template, and your data is this tiny thing, but it's still a report.

Access comes with a form designer, and a report designer.

It's a system

With Access, you have queries, forms, and reports. You also have a programming tool similar to macros. Each of these things is saved within a database file.

What's nice about having these things saved in a database file is organizational. Each of the things you want to do has a place to be filed, and a name. It's not all stuck in your head, like when you use Excel.

That "red highlight" example above could be saved out as a query, maybe named "qry People Who Owe Money". So someone who knows Access can read the query and run it.

In fact, you can still have a red highlight. You can do it in the reports. You can create a report shows all the people, and puts a red highlight behind all the rows of people who owe money. The report can use a query that calculates the debt as the data source.

This description of what Access can do only scratches the surface.

The problem

The main reason why people don't switch to Access is because it takes away a lot of things, like fonts, boldface, colors, and all the tools we use. What it offers is a more spartan environment, with only grids and text. So, people start using Access and think, "this sucks."

But it doesn't suck; you just haven't gotten to the good part yet. It gets good when the amount of data increases.

Excel is fine for a few hundred rows of data. Access is considered small for a database system: it's good for up to tens of thousands of rows. Database servers are typically used to store millions or even billions of rows, and hundreds of columns, across thousands of tables. It's vast.