Integrating Excel and Access – Responding to Clients’ Needs for Data Analysis

Demystifying Valuation, Economic Damages + Forensic Accounting

At Henry & Horne we often deal with requests that involve a heavy data analysis component.  Most of these requests can be handled with Microsoft Excel.  The multifaceted spreadsheets, in addition to the formulas and macros, allow for complete data articulation.  However, Excel does have its limitations.  In one recent project we had for a client it required to tabulate data on 600+ fields of information for an estimated 500 different entities.  Not only were there size issues but the data had to also be meaningful and relatable.  One of the more important fields was region.  The client wanted to track the entities by region and, in addition to the large amount of data for each entity; this would create many problems within Excel.  This required us to use the powerful functions of Microsoft Access.  While Excel is limited to 256 columns of data, Access has an almost limitless amount of data that it can store while still being meaningful to the user.

For this particular project we created three different tables within Access to handle all of the line items and linked them together as relational databases.   We linked all three tables together with a common ID tag that is seen in each table.  This allows us to ensure the data with the same ID tag will still be related to each other while stored in different databases.  Linking also helps to show the data in a meaningful way that is easily interpreted and reference specific columns to place the data.

Since the client was more familiar with Excel, we created a template that they could use in Excel to input the data.  Access has a feature that allows the importation of data from many programs, including Excel.  This allows a user with limited Access experience to enter data into Excel that can easily be put into Access.  This is also useful if not all of the clients data entry personnel have Access installed on their computer.  By creating this template it also helped us create the database because the data import feature will import column headings and formatting.

Being able to track the data was very important for the client.  Using Excel by itself would require having to link numerous excel worksheets together to track data and create sophisticated functions to sort it.  This creates a time consuming process to link each spreadsheet together and could cause Excel to crash or run very slowly if the spreadsheet has too many links to other sheets.  It helps the client keep track of which data points are comparable to create averages with great ease of use.  In addition, the reports and queries that Access has to offer produce a wonderful presentation of the data.

This is an important lesson of flexibility and being responsive to client needs.  In the ever growing age of technology, it is important to be able to adapt to any project put in your path.

By Henry & Horne


  1. admin says:

    Our experience with regards to your inquiry is that this type of analysis generally requires significant manual data input of transactions before any data sorting and analysis can take place. There is not typically an efficient method of automating the process before the data is input into spreadsheets, unless the transactions have been previously recorded in some type of accounting software, such as QuickBooks. However, there are scanning software packages available which enable bank statement data to be organized and imported into excel or QuickBooks. It may be beneficial for you to engage a forensic accountant experienced in this type of work.

  2. Mike Duggan says:

    Am working on a large project involved with a divorce where the husband had another woman that he was supporting and entertaining for about 4 years prior to DOS. He is now living on “his” IRA, but am still trying to discover how the money got into it – suspect mostly from W’s inheritance.

    BUT – I have many checking and card accounts and HELOC and IRAs and so forth and I have been looking for cash flow between accounts. Is there a way to automate that? I am not an accountant, but love this stuff – nailing it to the guy is proving very satisfying – but I want the complete story!

    In general do you just enter everything from every account and then automate the cash flow to see if money goes out of something but not into something known? And, into something where you can’t find where it came from? I am doing this visually and putting things into spreadsheets and matching dates from various card expenses,e tc. Is there a typical way that these discoveries can be presented legally so they are most easily followed by someone not an accountant? The network of cash flow is quite complex.

    Thanks for any advice you can give me!