Reclaim Your Weekend With Excel VBA - Three Common Repetitive Tasks To Automate

Whenever I mention “VBA”, business professionals tremble at the thought of programming code. However, I believe that a little coding ability is the next frontier for those who need to transform data into information to generate insights and make decisions. Coding is not exclusively the domain of developers and technology professionals. Business professionals can achieve 80% of the benefits by knowing 20% of the code. Since Microsoft Excel is the world’s most popular analytics tool, it makes a lot of sense to introduce coding using this comfortable environment.

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:

The data to the left is “unpivoted” and is ideal for sorting, filtering and aggregating. I figure that business professionals naturally have a preference for this format because I am often asked to “unpivot” their data table.

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.

After learning Excel VBA to extend your Excel capabilities, you will discover that VBA can also be used to automate many more tasks such as the distribution of Emails to a large group of recipients or the creation of multiple PowerPoint presentations.

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

Risk Management For Excel Analytics - Three Tips For Reducing Risk

A great deal of analytical activity is performed using Excel spreadsheets. Their simplicity and power make them an ideal analytics environment, albeit one fraught with risk. Organizations are supporting critical business objectives with Excel, so Risk Management is imperative in ensuring that these workbooks perform as intended. Eliminating risk completely is impractical, but simple risk reduction ideas can improve the quality of spreadsheets. I have been using spreadsheets for almost 30 years and have some simple suggestions for mitigating the following three risks in Excel 2010: Data Security Risk, Calculation Risk and Data Entry Risk.

Data Security Risk

Depending on the level of sensitivity of your data, there are different approaches to protecting it from spreadsheet users. A simple way to protect an Excel worksheet is to hide it by right-clicking on the worksheet and selecting Hide. Unfortunately, many Excel users know how to unhide a worksheet by right-clicking on any worksheet and selecting Unhide.

The next level of security is to “Very Hide” a worksheet. This can be achieved through Excel’s Visual Basic for Applications (VBA) environment. Hold the Alt key and press F11 (Alt-F11) to toggle between the spreadsheet and the VBA environment. Once in VBA, make sure the Project Explorer (Ctrl-R to show) and Properties window (F4 to show) are visible. Click on the worksheet that you want to hide in the Project Explorer and its properties will appear in the Property Window. The property of interest is called Visible, use its drop-down menu to select ‘2 – xlSheetVeryHidden’. The worksheet is now hidden and cannot be Unhidden from the spreadsheet screen (can only be Unhidden from VBA). This is a great way to hide a worksheet – just don’t forget that you’ve hidden it!

Another way to protect data is to use a database such as Microsoft Access. You can have a secured database which holds the data in tables and another database which grants permission to selective views of that data for use in Access or Excel. Databases offer greater levels of security than spreadsheets.

Calculation Risk

Nobody’s perfect. Studies have shown that a high percentage of spreadsheets contain errors. When thousands of cells are involved in a model, it’s not uncommon to create a formula that returns an incorrect result, especially when the formula is lengthy and difficult to read. A formula can be easier to write and read when cell references are replaced with named ranges.

Take a look at this formula:

And this one:

They are the same formula but the shorter one is using named ranges instead of cell references for the calculation. Formulae that are easier to read are less likely to contain errors. To replace cell references with named ranges, click on the Name Manager button on the Formulas tab. Click the New button to open the New Name dialog box. Enter a name in the Name: field, click on the corresponding cell in the Refers To: field then click OK on the New Name dialog box and click Close on the Name Manager. When you write a formula and click on the newly named cell, the name is used in place of the cell reference. Named ranges make sense for cells that are referred to a lot, such as a period ending date or a region growth factor.

Data Entry Risk

Garbage in, garbage out. In order to make good decisions, model insights must be based on good data. The easiest way to validate data entry in Excel 2010 is to use the Data Validation feature on the Data tab.

Choose a cell to apply constraints to then click the Data Validation button to open the Data Validation dialog box. Click on the Settings tab and in the Allow: field, select Decimal. Set the Data: field to ‘between’, Minimum: to 0 and Maximum: to 1. Click OK to close the dialog box and then test the cell. Only numbers between 0 and 1 can be entered in this cell, which is useful for percentages.

Another popular use of Data Validation is to create a drop-down list like in the example pictured below. Click on cell E3 then click the Data Validation button. From the Allow: field, select List to display a field called Source: and choose the range of cells that contains the values for the drop-down list (=$A$1:$A$4).

Cell E3 is now a drop-down list that will only accept the specified values.

Access Database Forms are another great way to improve data entry. Date fields can use Calendar Controls to alleviate any confusion in calendar date entry. Some regions of the world read 7/2/2013 as July 2, 2013 but others see February 7, 2013. The Calendar Control ensures that the format will not affect the date entered.

If you like these tips and you’re interested in learning more about how Excel VBA can improve your Excel experience or how Access Databases can help you manage and manipulate data, I encourage you to take a look at these courses offered at the CMA Professional Development Institute.

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 analytic 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