When to Move on From Excel??

If you’ve worked at any job that requires a computer, chances are you’ve been asked to use Microsoft Excel.  But are you really using Excel because it’s the right application for the job, or because it’s seems easier and you’ve already been using it forever?  The truth of the matter is that, in many cases, Microsoft Access is the better data management solution.  But how many people actually take the time to learn what Access has to offer?  Most users are lured into Excel with its tabular format and force fit the application for their use.  They are not always wrong, but quite often is the case when I see people using Excel because they don’t know how to use Access.

So how do you know if you are one of these offenders?  How do you know if you’re using the right Microsoft application for your data?

Well, the primary difference is that Excel is a spreadsheet for analysis of numerical data.  Access is a relational database management system (RDMS) which means it has multiple related tables at its foundation.  These tables may be linked by parent and child fields, and displayed in pretty much any configuration the user wants.   Let’s look at five functions offered by Access, that makes it superior to Excel for certain applications.

1.)  Management of fields at the table level instead of the cell level

There’s a huge difference between Excel and Access in the way that users enter data.  While the Excel table looks inviting and easy to use, this quickly changes when you start to add a good amount of data to your table.  Unless you actually need to manage the properties of EACH individual cell in your spreadsheet, this probably becomes a burden for the average user.  I know what you’re thinking… I can just highlight the cells or rows or columns I want to format, and change the properties all at once.  You can do that until JUST ONE cell entry exceeds the width of your column.   Then you’ll either widen the column and distort your table to accommodate the entry, or merge the cell with the adjacent cell.  Once you start to merge cells, you lose filter, sorting, and other functions because your columns are no longer aligned.

Microsoft Access is a Relational Database Management System (RDMS) and tables are at its core.   Not Excel spreadsheets, but actual tables with columns and rows.  And every cell in a column follows the rules of that column.  There are no exceptions.  This is incredibly important when records are being added to your database and the user wishes to preserve the format of these entries throughout the table.  The field formats are controlled by input masks, formats, and validation rules.

Let’s use an expected delivery date field as an example. With Access, a user could set the “Delivery_Date” column to require:

• display format of “mmmm dd yyyy” or “September 19, 2019”

• entry format of “mm/dd/yy”, prompting __/__/__ to the user.

• requiring the entered to be later than the current date

While these same controls exist in Excel,

2.) Ability to establish master data records.

And then use this in ANY field you want

3.) Action tracking and event management

4.)  Easy handling of numerical AND text data

There’s no doubt about it, Excel is tops when it comes to analyzing numerical data.  That’s where it ends.

5.) Formatted, filtered,  and shareable reports

Okay great, so now I know I should be using Access, but I haven’t a clue what to do with it!