This is the second part in my two part Access Vs. Excel series, where I’m basically trying to differentiate when you would want to use one of these tools over the other for a project. This question comes about because of the many similarities that these two tools share. You can see the first article here: Access: Vs. Excel: When to Use Access.

Here are three example cases where I think you would want to use Excel over Access:

• You need to create a one-time, formatted report - If you’re creating a one-time report based on a set of data, then Excel is really the way to go. On the average, setting up a new report model in Access takes longer than creating a new report model in Excel. This is because in Access, you have to setup the database and other backend structures, while in Excel you can pretty much start plugging away from the get-go. If it’s only a one or two time report, you don’t need to take the time to setup a whole database for it. Plug the data into Excel, throw a format at it, and you should be good to go.
  
  
• The relationships/fields won’t change - This basically means that once you put the report together, the structure of your data won’t change, and neither will the relationships. For example, say you are creating a spreadsheet that shows all of your customers and key parts of their company info. This is pulled from a hidden spreadsheet that contains all the info from each company. This is fine, however, if you want to add new fields to the company info, this could cause cascading cell reference problems. Also, if you have some complex relationships between data fields, it’s much harder to check to make sure that they are still intact and correct. As long as your backend is pretty set (relationships and fields), Excel can be a good tool because of the shorter setup time involved.
  
  
• The report format won’t change - Don’t get me wrong, I think that Excel does have some flexibility when it comes to changing an existing spreadsheet. However, once you get past a certain point of complexity, it gets very difficult to make changes that don’t break other parts of the spreadsheet. For example, if you have a bunch of different formulas with cascading dependencies, changing just one requires you to check in multiple different places to make sure that nothing broke. I prefer Access for these kinds of reports. However, if you don’t have to change your format too drastically, I recommend Excel.

It’s critical to understand that both Access and Excel have their uses. Sometimes these overlap, most of the time you can clearly see which one would be better to use. It may not seem like it now, but as you get more familiar with both tools, you’ll see which one is better to use in certain situations. Also, there are always exceptions to the rules. Maybe your client doesn’t have Access, maybe they don’t like to use Excel. You might be forced to use one, all you can do in that situation is roll with the punches.