[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).
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.
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).
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 )
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).
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.
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.
If you need a solution to one of the above problems… but don’t have the required VBA programming skills in-house… feel free to contact me for a quick email consultation. I work closely with a friendly team of dedicated Excel and VBA developers who can help you create professional-grade solutions for your company. You can read more about what we offer on this page …
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)
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.
Victor expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.
Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.