When I was unfamiliar with Excel VBA (Visual Basic for Applications), I was working weekends in order to compile, clean, validate and finally analyze the data I was given. Fifteen years later, I automate the repetitive processes and devote most of my time to asking questions about the data and looking for the answers. I have seen countless colleagues struggle to analyze data because it never seems to arrive in a desirable format, so a lot of time is spent preparing data for analysis and much less time is spent performing analysis. My best experiences have been 5% data preparation and 95% analyzing the data to uncover the best questions and insights.
Investing a little time to learn how to automate a repetitive process will save you much more time in the long run. If you find yourself repeating the same tasks over and over, it can be automated. Here are three common time consuming problems in Excel data analysis and solutions that use Excel VBA.
“Unpivoting” Data
It amazes me how much data arrives in the following format:
This data is really a report which displays sales for each store (rows) and each week (columns). The format can be described as cross-tabulated or pivoted. I never recommend doing data analysis using this format because it is more difficult to manipulate than the exact same dataset in the following format:
If the dataset is small, then it won’t take too long to rearrange the cells manually, but how about hundreds of rows and columns? Automation is even more important if this needs to be done repeatedly on a weekly basis.
The first tool I reach for is Excel VBA which can easily automate the transformation of the data. The code would loop through each row and each column of the pivoted data, noting the column’s date, the row’s store number and the sales value and would then add it to the next blank row in a new “unpivoted” data table. A weekend’s work could be reduced to 30 minutes of coding and a few seconds to run the procedure.
Working with Many Files
A former colleague of mine once told me how she had to spend her weekend compiling data by repeating the following steps: Open a client data file, copy and paste several ranges of cells into a Master Workbook then close the client data file. Each client data file represented a store and this client had 300 stores! Not only did the process take a long time, but there were also a few errors made and one store was overlooked altogether. When a manual process takes a long time and errors are inevitable from repetitive fatigue, Excel VBA is a preferable alternative because it will execute quickly and will not contain human error. With Excel VBA, it is easy to loop through all of the Excel files in a folder and automatically extract and append the desirable data ranges to a Master Workbook. A weekend’s work could be reduced to 30-60 minutes of coding and a few minutes to run the procedure.
Creating Charts
Visualization is one of the key elements of communicating the patterns hidden in a dataset. If the data is already in Excel, simple charts can be generated quickly but complex charts can be time consuming. Another former colleague of mine used to create PowerPoint presentations as a full-time job. One of the reasons why he had no time for anything else was because all of his charts were built manually. Simple line or column charts should be built manually. However, as charts become more complex, there can be a lot of clicking and selecting cell ranges. Furthermore, if the chart needs to be recreated monthly for datasets that change, it makes sense to automate the process. A weekend’s work could be reduced to 30-60 minutes of coding and a few seconds to run the procedure.
Here are two examples of charts that would take a long time to create manually, but using code they can be created and recreated in seconds.
Stop the repetitive stress and start automating common Excel tasks with VBA in order to reclaim your weekend.
Scott Cuthbertson BBA
Scott is an Analytics Professional who has worked with over 100 clients in the retail and financial services industries. He has helped organizations improve their labour scheduling, inventory replenishment, performance management, organization design and pricing. He has been a practitioner of analytics since childhood, using data models to win Hockey Pools. His expertise lies in developing data models, reporting, algorithms and solutions for any organization that wants to use their data to improve decision making. He also teaches at the CMA Professional Development Institute.
If you are interested in analytical solutions or training:
Email: scottjcuthbertson@gmail.com
LinkedIn: http://ca.linkedin.com/in/scottcuthbertson
Blog: http://theanalyticspro.blogspot.ca/
© Scott Cuthbertson 2013 Blog: Words From The Analytics Pro
