How to do 10 Hours work in 10 Seconds – using this Powerful Excel skill
[Note: Approx. 14 mins reading time]
“Excel is probably one of the most powerful programs that’s readily available for working with data. Even if you’ve been working with Excel for quite a while, it always seems like there’s so much to learn about its endless possibilities and features.”
That’s how the previous blog post started (if you missed it here’s a link… Top 29 Excel Shortcuts)
In this post I’m going to dive into talking about a rare and powerful Excel skill that will blow you away with the results you can get with it. Stick with me to the end … I promise you it will be worth it.
(And if you’re a fellow Excel fan reading this article I’d love to hear your views on this topic in the comments section – both for and against).
What? 10 hours work in 10 seconds!?!
A couple of months ago I created a spreadsheet that reduces 10 hours of work to 10 seconds. And it does this with 100% accuracy.
The situation was like this… a small company was using Excel to generate invoices. The invoice details were entered by hand and printed out for the accounting files.
This was fine because the number of invoices were small and the dollar amounts relatively large. However, the business added a new project and started billing smaller amounts and with a higher number of invoices.
The new billings were easily downloaded from the original online source. The accounts staff had the sales data, and next wanted to create sales invoice (for audit purposes). However, there wasn’t a way to automatically generate sales invoices in the company’s standard format online.
Here’s where the problem started…
Let’s say there are 400 invoices to be created each month, and let’s say the accounts staff take 1.5 minutes to key in each invoice using information from the download (date, customer, e-mail, product(s) and amount(s) billed).
That’s a total of 400 invoices x 1.5 min / invoice = 600 minutes or 10 hours
So… I helped by creating a spreadsheet that automates the process.
My simple solution takes the downloaded billing information and creates sales invoices using the company’s standard format. And it does this much faster than any trained person can do it.
I timed how fast the spreadsheet was.
With 400 invoices it took a total of nearly 10 seconds… that’s 0.03 seconds per invoice and about 1 million times faster than doing it by hand. Nice!
What’s more, there’s no room for data entry error because the spreadsheet takes the exact data from the billing download. A person (no matter how accurate and tireless a worker) might still make a couple of errors every hundred invoices.
Now that’s the kind of result I like…
I hope you like the idea of doing 10 hours work in 10 seconds.
Now… for a dose of reality.
I’m the kind of guy who likes to invest time to reap rewards. Sometimes I will happily spend months of my life to learn a new discipline or skill.
And it did take me several months to learn enough to achieve the results above.
As you can see, the payoff can be really big.
This is the kind of guy I’m like…
I’m the kind of guy who likes to take an active approach to solving troublesome problems.
I don’t like waiting around for someone else or another department to implement a solution if it can be done quicker and more effectively myself or by someone in my team.
And I like the challenge of learning new skills.
But … if you like the results but don’t want to invest the time to personally learn and master a new skill, then you’d better not read the rest of this article (just contact me instead… because I work with a team of like-minded people who can help you achieve the same kind of results in your own company – find out more on this page).
Where else can this skill be used?
Before I tell you more about what this skill is, and how you too can get started… Let me explain that this “rare and powerful skill” is not just limited to Excel or only applicable to financial transactions.
In fact once you know this skill… you can use it to link and automate multiple Microsoft applications like Access, Outlook and PowerPoint to create unified solutions. And you can even scrape data from the web.
One good example is that of a US healthcare services company who brought in consultants with this skill (if you want more details… see this Tenet Healthcare case study). They reduced the time taken for one daily data reporting job from hours to minutes – while increasing accuracy of the output.
This particular job took up a substantial amount of effort and was prone to error because of the multiple human-data interactions taking place between different Microsoft programs. The company data analyst would manually pull out data from several different sources, transform it, format for presentation and share the results.
After the consultants built the automated solution, the analyst was no longer so stressed every day. The solution did almost all of the work (especially data transfer between different systems).
It was scheduled to run at 2am daily… so by the time the analyst came into the office the data was all ready for review. The analyst had more time to do other value-adding work (and also take holidays!)
Another example is one you can personally play with right away.
Did you know that it’s possible to export Google Maps and Street View images from the Google Maps service and show them in an Excel workbook?
Getting Google Maps Images into Excel opens up the possibility of creating simple applications that combine the different types of maps onto one screen. I created a 5 minute video that gives a short demo showing an Excel spreadsheet that generates a 360º Street View panorama by combining 4 images of the chosen location… (if you’re curious, check out this article )
OK… just what is this skill?
So how DID I manage to do something in 10 seconds that would otherwise take 10 hours?
I did it by programming Excel with the built-in coding language called VBA (which is short for Visual Basic for Applications). It comes FREE with almost every installation of Microsoft Office.
Before you can create VBA solutions, you first need to learn how to write VBA… which can be a little challenging. But the rewards can also be BIG. Both in terms of time saved when you can automate jobs, as well as satisfaction gained from mastering a new skill.
Of course the results you get will depend on many factors… including what kind of work you need to automate… and how good you get at coding with VBA.
Having said all that, it’s far easier today to learn VBA programming than it was 10 years ago… because of access to really good online resources such as courses, forums and Q&A sites (including the widely used Stack Overflow).
What would you do with a super-power?
Now… if you think about it for a while, let’s say you could automate several jobs around your office or company. And after they’re automated you’d several hours more every single week.
It’s like having a super-power in the office.
What do you do now?
If you wanted to, you could surf the web, watch Netflix shows or spend some time with Facebook. And what would this get you? Some short term satisfaction that wears off until the next fix…
On the other hand, if you’re anything like me… you could use the spare time to figure out where else you can make an impact.
Either by getting rid of some annoying process that doesn’t work and gives everyone headaches, and replacing it with a better process. Or by investing in yourself – with some more skills or education.
That way, you’ll be making a difference in the lives of people around you as well as your own life.
Four useful things you can do with this…
OK if you’re still with me (and not gone off to watch a Netflix show) let’s take a look at four very useful things you can do with VBA, both in Excel and outside Excel.
1. Bridge the Gap: between project implementations
- One of the key strengths of VBA is that it allows you to fill the gaps between other IT implementations. This can lead to significant jumps in cost and time savings.
- For instance if new business software is installed, no matter how well the planning stage, there is an ever-present chance that existing software doesn’t communicate well with the new software, or an extra module is needed to fulfill new requirements.
- Enterprise software often needs months of training, meetings and testing to complete implementation… and then some new OS upgrade or technology update happens and a new implementation is needed
- VBA comes in to fill the gaps between such implementations and keep things being done with high accuracy and efficiency.
- Excel and VBA are flexible enough to provide bridging solutions where a company doesn’t have the immediate resources (either local and offshore IT teams) to implement dedicated new packages across all employees, departments, business units and countries.
- VBA can also be useful in acquisitions, where the parent company creates business dashboards using custom VBA applications. Newly acquired companies may use different financial IT systems from the parent, and it is difficult to integrate them in a reasonably short time frame.
2. Create a unified solution: with office suite and database tools
- Another key strength of VBA is that it works across Microsoft Office productivity tools which are used worldwide. This allows for convenient and rapid in-house implementation.
- Without VBA, Microsoft Excel is less powerful (though still very capable). With VBA, Excel can be customized, automated and integrated with PowerPoint, Outlook, Access and more.
- Most office users still copy and paste from one program to another, change formulas, insert or remove rows and columns, then open Outlook, attach files, add email lists, and so on. While it’s possible to use Microsoft applications like this, there’s much more that can be done by unifying different applications.
- You can benefit from ease of use with a simple customized interface (built using the Ribbon or with form controls). You can also create scheduled or one-click programs that run without any user intervention. And of course you get the repeatability of a computer program… thus reducing human error (imagine getting a phone call from your boss just when you’re halfway through processing your data into a dashboard).
3. Quickly create custom applications
- I talked about one example of rapid application prototyping in one of my earlier articles and I’ll summarize it here…
- I was in the Business Assurance team of a UK head-quartered technology company (ARM plc – acquired by Softbank for $31bn in 2016) … and my boss gave me an open-ended challenge. He wanted me to help him gather input on company-wide risks by department and division, to create a better and updated version of the corporate risk register.
- He left it up to me as to how I would achieve this, suggesting I maybe put something together in a spreadsheet and send it round.
- I got a bit creative with VBA and built a simple automated database inside Excel. The department and division managers got a “user-version” and I maintained the “master-version”.
- In total I spent a couple of hours designing a simple user interface (UI) and a few days coding it. In a couple of weeks I had coded the backend to handle database operations and version control. And I wrote some more VBA code to automate report creation by summarizing Excel data in new Word documents for easier reading and printing.
- My boss was impressed. We used the VBA solution to gather risk register information and quickly turn around the summary data in Word documents to share with senior managers.
- Without the VBA solution it would have taken days instead of hours to turn around the reports. With the VBA solution we could issue reports in a very timely manner, which made a big difference in enabling the ongoing flow of discussions between countries and divisions.
4. Automate and speed up repetitive tasks
- I already talked about doing 10 hours of work in 10 seconds… and there are many more cases just like mine.
- For example it’s quite common to automate monthly reports by splitting a single set of data into multiple workbooks (say between 30 and 60). A task like that can take 3 days for one competent person to do, yet with VBA you can reduce it to mere minutes. And with higher reliability.
- Not only does this reduce the mind-numbing stuff thats better done by a machine than a person, it also enables greater flexibility for knowledge workers to do what humans do better than machines … namely pattern recognition and design of responses to changing scenarios. (note: the rise of smart AI is nowhere near human level abilities in these cases… yet!)
- As well as report generation, you can automate chart creation, data mining, web scraping, communication between Excel and databases, business dashboards, financial modelling and create customized applications.
VBA is pretty powerful in the right hands!
Yes, VBA is powerful in the right context…
It’s a very document-centered language. VBA programs run from inside Excel spreadsheets, or Word documents, or Access databases or PowerPoint presentations.
Although Excel VBA may not be widely used in certain industries, it is definitely very popular in finance (investment banking, financial modelling, reporting, data analysis).
In my opinion, one of best things about VBA is that it’s a built-in part of Microsoft Office. So, if you want to implement a small project in a large organization, you can do it without having to get extra project funding.
While VBA was released a while back (in 1993) it’s still in use today.
And Microsoft is continuing to support it. In fact I just learned that Microsoft has finally brought the Windows-style VBA Editor to Mac Excel 2016.
Before the latest Mac Excel update, it seemed to be pretty difficult to use the Mac to write VBA … but no more! From this update of Excel 2016 for Mac, both Windows and Mac users will have a similar interface.
And I’m sure you can now expect to see more and more VBA programs being written on both platforms. (I personally use VBA on Windows far more than on Mac, you can read more about the Mac update from my fellow Excel VBA expert here)
OK – How do I get some?
If this article lit a fire inside you, and you’re keen to learn more about VBA… make sure you sign up to my email list to get free updates. I’ll soon be writing more articles about how to use and learn VBA, and exactly where to get started with Excel VBA.
And… I’d like to hear from you if you want to share any case studies where VBA helped in your company… or any experiences where you found that VBA was the wrong tool for the job… (yes, VBA can be great in some situations but it’s just the wrong tool in other situations).
So please do share your views below in the comments.