Updating Excel reports is a tedious, mind-numbing activity that many people would love to do without. Looking for a solution to the problem, you will discover that there are a quite a few options available to you. They are all listed below, but something you should consider first…
The Analytics Edge Add-in for Microsoft Excel is designed to simplify the automation of Excel reports for people that don’t want to become programmers and don’t have big budgets. It works inside Microsoft Excel, leaving you with all the flexibility you have become accustomed to, while removing the tedious step-by-step process of refreshing data. Combined with a growing selection of connectors to web services, the entire process to download new data and update the analysis can be reduced to a single click. Analytics Edge provides simple Excel report automation at a price that is affordable by owner-run businesses and individuals.
Get the Analytics Edge, and stay focused on your work instead of the tool. Get started now – free for 30 days!
Formulas and Features
The first level of automation in Excel is to use simple formulas and features of Excel to calculate key metrics or transform your source data into more meaningful representations. Most people can handle simple math and SUM formulas, and features like charting and conditional formatting. To perform real analysis, though, you’ll need to learn the VLOOKUP function (or the INDEX/MATCH combination) and basic pivot table operation.
The internet is full of helpful web sites and video tutorials for just about any challenge, but you can start with Microsoft’s help for free as well. One excellent and highly-recommended third-party resource is the Chandoo.org website. The Microsoft Excel product has been under development for 25 years, and it has a phenomenal depth and feature set, so simply accept that you will only learn a small fraction of what it could do. All that is important is that you learn enough to do your work.
Excel Macros
Excel can record the steps you take and play them back as a macro, but you should learn about the option to “Use Relative References” before you start playing. The biggest problem with macros is that if you want to make a little change, you either start all over and record a new one, or you become a Visual Basic (VBA) programmer since that is the language used in macros. Since many people are not ready to become VBA programmers, this option has a limited audience.
Visual Basic (VBA) Programming
If you are ready to take the plunge, though, there are almost no limits to what a good Visual Basic programmer can do. Most organizations find a trusted techie and have their reports automated for them. A word of caution: the internet is littered with stories of not-so-good programmers and the mess they left behind. Remember that things change, and you need to think that way when you take the leap into automation – don’t throw away that contact information and treat your programmer nicely; you will need an update someday, and it will happen when you are in a time crunch.
If you are the programmer, you can get help from a number of sources, including reddit’s /r/excel list, LinkedIn’s Excel BlackBelts group, and StackOverflow. If you need one, you can contract the work out to a Microsoft partner, or enter the amazing worlds of freelancer.com, odesk.com or elance.com.
Microsoft Excel Add-ins
A lot of the mundane or difficult operations in Excel can be simplified with the assistance of a free or inexpensive add-in program. Microsoft actually has a few of its own installed with Excel (like Analysis Toolpak), and they released a series of new ones for Excel 2013 (like PowerPivot). While not necessarily automating your finished report, they can take a lot of the work out of the effort. Analytics Edge has a unique add-in that can not only automate the download of data from common services, but it can also automate the analysis and refresh of your reports. Click here to learn more.
Reporting Applications
To be fair, I have to talk about the ”other” option – not using Excel at all. There are a number of business intelligence and reporting tools available, from visualization tools like Tableau, enterprise platforms like MicroStrategy, and newer cloud-based reporting services. These tools are not for everyone (up to 74% of businesses under $100 million still use spreadsheets for finance functions) and can be expensive.