MS Excel and Access Technical Prowess

Microsoft Excel and Access Technical Prowess

It was years ago and I was at my desk when a group of visitors to the bank happened by on a tour.  I didn’t know them, but they apparently knew me by reputation. One gent asked me, “How do I find technical talent like you? ” I was flattered, thought for a moment and said look for the guy with the Excel Bible on his desk.  Some years later I heard there was a discussion on the need to cultivate technical people. My colleague and I were being used as examples. The definition of technical at that point seemed to include going beyond excel and the ability to query large databases (a.k.a. big data).

As an ex auditor I can attest Microsoft Excel and Access prowess can yield some amazing results.  I can also tell you that too many auditors do not have the baseline skills that are needed today.  All to often shortcuts are taken and tests are not performed because the auditor lacked “automation instincts”.   Oh I’m not advocating that the auditor should be able to query every source system, rather I suggest that they should have a personal toolset that includes Microsoft Access. Why? Because Access is good at teaching about tables, fields, indexes and SQL.

Developing in different platforms adds complexity
Excel is limited to 1,048,576 rows but that doesnt mean you should ever have that much data in a spreadsheet.
Sometimes Excel simply isnt the solution

Microsoft Access database: Maximum size of 2 gigabytes (includes all objects minus the space needed for system objects all database objects and data). You can work around this size limitation by linking to tables in other Access databases or link to tables in multiple database files (each of which can be as large as 2GB). 
I’ve successfully used Access tables with well over a million rows.

George and limited skills (Excel vs Access)
George is auditing in a new shop and the audit data analytics guys don’t have system access.  To move things along George asks for a download sample of three days activity.  He soon learns each day has between 1.2  and 1.5 million transactions. The developer provided thirty four key columns.  As we all know the last few versions of Excel have 1,048,576 rows.

With some help George creates three Excel workbooks with the data split into 2 sheets.  I can tell you while Excel may have a million row, things go very wrong when you overload Excel.  For one thing, Excel has filtering limits.  I’ve been amazed to see people use this approach.  It simply isnt the way to go.  

George is stuck and thinks what do I do now? There are any number of tools that George could use to get out of this mess.  Why not use the one that came with MS Office installed on his laptop.  Yes good old MS Access.  

In another version of this hypothetical George dumps the files into access, adds a primary key,  some indexes on key fields and not only executes his tests he comes up with some additional testing that reveal some interesting findings.

Building MS Excel and Access Technical Prowess

All this begs an interesting question: Can you cultivate technology talent and build that MS Excel and Access Technical Prowess without spending a fortune on training?  I believe the answer is yes and here is how.


♦  Create a technology friendly environment.
♦  100% testing using automation will likely take longer, provide for ample time.
♦  Have technology be a portion of the annual review process.
♦  Reward the use of technology that yielded results.
♦  Showcase examples of effective uses of technology.

There is one way to absolutely not get that innovative enviroment you are looking for and that is to manadate it.  Better to apply a friendly positive nudge.  Perhaps suggest they read my blog post: 

Excel vba got me to London and Hong Kong

Raymond Mills MBA, MS

Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle,  Squel Server and Sybase to give up their secrets.

Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” 
If you have a challenge with Excel, Access or Word and would like to speak with Ray,   You can get his contact details by clicking here: Contact Me

Leave a Reply