Automate Excel Spreadsheet Reports and Charts
Recently we finished a project for a customer where we automated their Excel Spreadsheet Reports and Charts, which they used to produce customer data and provide commentary on the results. The projects aim was to take a 2 day manual process and reduce it to a task that could be run nearly instantly and allow more time to be devoted to the analysis of the data rather than it’s preparation. While not wanting to get into the specifics of the customer I’ve put the stages of the automation below with methodology and explanations where relevant.
Reasons to Automate Excel Spreadsheet Reports and Charts
- Save time on data import and manipulation (in most cases reduce to single click)
- Reduce or remove the opportunity for manual error (miss a tab, accidentally delete a row)
- Create standardised professional looking output
- Allow users to spend time analysing and not preparing.
The system data comes in the form of a ‘data dump’ from another 3rd party software program. Ordinarily we would try and access the raw data underneath the system (either in the SQL or similar database) however in this case the reports performed calculations that could not be recreated within the extract, also the table naming conventions within the 3rd party application made for very confusing and non-standard information and without table level documentation it was deemed best to work with the data dump.
In the end we decided to take the raw feed and write a VBA (Visual Basic for Applications) script that imported the data into a clean spreadsheet formatted and sorted for the subsequent steps. VBA is the language of ‘Macros’ and also lends itself to other very clever excel (and indeed word and access) extension.
The clients system featured 25 tabs of charts each tab with 7 charts leading to a colossal 175 charts that needed to be updated on a fortnightly basis (see where they got the 2 days from!). Worse still if you know excel was the fact that the chart data source would change regularly and the selection criteria also had a habit of changing meaning that the charts needed to be dynamic. To automate this we used the tables functionality (excel 2007 onwards) to create a dynamic list of the criteria and then used dynamic named ranges to ensure that as the data changed the user only had to choose from a drop down list of criteria and the entire chart changed.
The last thing we did was to create an automatic export function that sent the completed reports (and only those with data for the report period) to PDF. Thus removing any printing issues and formatting concerns that befell the previous manual system.
So what has the net result been?
- Data import now takes 2 minutes
- Chart Generation takes 3 minutes (select criteria for each page)
- Analysis takes as long as necessary but the 2 day time line mentioned earlier excluded the analysis.
- Report Generation takes 20 seconds.
- Customer is very happy because of reasons 1-4
- We’re very happy because of reason 5
I realise the above is very vague in terms of specific scripts or how-to’s but if you post a comment below or tweet us with a question on anything I’ll be happy to oblige with an answer (if I can)