skip to Main Content
Access Vs Excel

Access vs Excel

Home Improvement Tools

If you’ve ever had the experience of working on a home improvement project without the proper tools, you’ll appreciate this story. A couple years ago, I got the bright idea that I would replace our well-worn shower with a bathtub and tile surround. Having never done a project like this before, I spent hours doing research about the various things I would need to do in order to complete the project. My wife and I selected the bathtub and tile (we ended up with travertine marble), along with an elegant but understated pattern, and I went to work.

Ripping out the old tile and shower was fun. Since the original tile installers hadn’t waterproofed the shower correctly 30 years ago, the tile came out quite easily. The fiberglass shower pan succumbed just as quickly to my pry bar and Sawzall.

Now came the real work.

photoTo make room for the tub’s drain, I had to cut through the foundation with a friend’s jackhammer. About a half hour into the process, I made my first of many mistakes. That 90 lb jackhammer I was using makes quick work of anything in its way – regardless of whether that ‘thing’ is high-strength foundation concrete, or not-so-high-strength water piping. It doesn’t take much of a hole to produce a good deal of water.

A frantic run to shut off the house’s water supply, and probably a few choice words yelled out in frustration alerted my 5 year old daughter that something was amiss. She came in to investigate. Surveying the mess, she said, “Daddy. You found water!” “Yes, Paige, I sure did,” I responded.

The problem with this particular mess was that I didn’t have the tools I needed to fix the issue. I now needed to remove the concrete around the water pipe so that I could replace the broken section with new pipe. I had the jackhammer, which was much to large for this delicate task, and I had my hammer and chisel. The hammer and chisel were just not powerful enough to chip away the high-strength concrete used in the foundation with any speed. I needed a rotary hammer. Without that tool, I would have spent hours on a task that should only take 30 minutes. Luckily, I found a friend that had one I could borrow. So, the next day, I spent part of the day fixing the leak, and then continued on with the rest of the project.

Application to Corporate Life

In the corporate world, so many people end up using the wrong tool for the task they’re trying to complete. Microsoft Excel is probably the worst offender. In its proper place, Excel is incredibly powerful, but when users start trying to analyze data that hasn’t been prepped properly, or if they try to turn Excel into an end-user application by adding a bunch of macros or VB code to it, the limitations of Excel become quite apparent.

It happens innocently enough. When you’re small, it is a simple task to create quotes or invoices in Excel. As you grow, price books get added, and then various configuration options creep in. Pretty soon, the simple price book turns into an application with 10,000 lines of vb code, 30 hidden worksheets used to store and process 1000’s of configuration options, and the process of producing a quote slows to a crawl. I should know. Years ago, I inherited a quote tool just like this, and was responsible for maintaining and upgrading it in my role in IT.

Excel vs Access

So what is Excel good at? Excel is simply masterful at doing on-the-fly analysis of data that has been standardized in a way that allows Excel to display it properly. If you’ve never used pivot-charts and pivot-tables to look at your company’s data, you’re missing out. It is also really good at managing simple business processes – creating a standard invoice or quote form than is distributed to a few sales reps, for example.

Excel is horrible as an application development environment, though. If you need to build a sales quote tool, project management system, or any other business workflow application, look elsewhere.

The obvious alternative to Excel is Microsoft Access. Access is almost the polar opposite of Excel. It is not very good at on-the-fly data analysis, and its ability to display data using pretty charts and graphs is lacking. (Developers have to jump through a number of hoops to get Access to do things that Excel does with ease.) However, if you need to design an application for internal users (or a handful of external users), Access can handle just about anything you throw at it.

What follows is a chart that shows the pros and cons of both Access and Excel

 

Access

Excels at:

  • Application development (for internal, or a few external users)
  • Multi-user environments where the data changes frequently (Access can handle hundreds of users when paired with a database server)
  • Handling large amounts of data (up to several million records, depending on the type of data, or more if connected to an external database server)
  • Performing pre-configured changes to large volumes of data
  • Combining data from multiple sources and manipulating it so that it can be examined in Excel later
  • Maintaining data integrity by limiting fields to certain data types, and other more advanced data validation

Square peg in a round hole when used to:

  • analyze data using complex, multi-step functions, especially when the analysis steps change frequently
  • Display data in pretty charts and graphs
  • Create simple business forms like invoices or quotes
  • Create web-based applications (as much as Microsoft would like to position Access as an easy-to-use, full-featured web-application development environment, it just isn’t there yet.)

Excel

Excels at

  • Analyzing data using complex, multi-step functions, especially when the analysis steps change frequently
  • Displaying data in pretty charts and graphs
  • Creating simple business forms like invoices or quotes
  • Performing on-the-fly analysis of multi-variable data in Pivot Charts and Pivot Tables (assuming that the data has been prepped prior to viewing it in Excel)

Square peg in round hole when used to:

  • Develop an application, especially a complex application that will be distributed to a number of users
  • Create a complex quotation system, especially one that has different price sheets for particular customers
  • Provide non-technical users with worksheets that perform various business processes

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top