I started my career as a spreadsheet warrior in 2014. Like many Excel pros, my first job wasn’t actually designing or developing spreadsheets and data analysis tools. I was doing daily number crunching in models built by the pros in my organization. These models relied heavily on VBA macros for automation and scenario calculation.

VBA seemed like the holy grail of Excel skills. I studied in the evenings and tried to find opportunities to use it during work hours. It was the first real programming language I ever encountered besides the native Excel formula language. It is a wonderfully powerful and complex language that can do pretty awesome things in a spreadsheet. My favorite project was developing a set of macros to help my friend automate some tedious web browser tasks that were very routine and time consuming, thus freeing him up to goof off in my office most of the day (in productive ways of course). Pretty cool, right?

Despite the cool browser automation macros, I never really considered myself all that advanced at VBA, and I’m actually ok with. In fact, I would answer the title of this post by saying NO. Analysts in 2021 do not need VBA to make a major impact on their organizations and finance teams.

A major sign that I’m not alone in this opinion is that Microsoft is really not improving or supporting VBA much further. VBA still works, and it probably always will because of legacy workbooks deployed by organizations all over the world, but it doesn’t seem like it will change or improve much from where it is now.

My main reason for this opinion though has to do with the advent of Power Query and Power Pivot for Excel. It baffles me that many Excel users and finance managers are still unaware of these tools, but they are already built directly into Excel. They are the reason that I could never go back to Google Sheets, LibreOffice, or even VBA. It’s basically Excel 2.0 for the 21st century.

If you don’t know anything about these tools, let me attempt to explain.

Imagine that it’s 1987 and your firm has adopted Microsoft Excel. Your competitors are using paper spreadsheets or complex programming languages on a command line interface to estimate business scenarios. You have a tremendous advantage because you can instantly recalculate estimates and reports in a visual and user friendly way. You can also forecast profitability by tweaking revenue and expense assumptions. The possibilities seemed endless.

Fast forward to today and this cute little example seems unrealistic, right? Business data has become so voluminous and unwieldy that Excel seems to have lost that magic due to the sheer complexity of the data being spit out of your various systems.

The answer to this problem has historically been to hire a team of IT consultants, implement software with expensive licensing, and create the ultimate data warehouse with all of your business’s data in one place. Spoiler alert: you don’t have that much money and the data warehouse will never be complete. Data warehouse development can’t keep up with the pace of business changes and it will probably never have all the data that the finance team and executives need.

This is where Power Query and Power Pivot come in. Imagine if Excel could formidably wrangle with huge amounts of complex data, and Joe from the finance department can do it himself instead of trying to explain to the expensive IT consultants why the data in his important spreadsheet is worthy of inclusion in their perfect database.

So back to VBA. Can’t it do pretty much anything? What’s so special about these new tools that VBA can’t do? Well, Power Query is actually kind of like a macro recorder, but it lets you connect to just about anywhere that data lives and transform it into a table or set of tables. The “macro recording” aspect of it is far more flexible than VBA recording so there is a much gentler learning curve. With Power Query, you can automate the processing of data from anywhere! I worked with a team that was spending 45-60 minutes per day generating a report. I created some queries that could process the data in 5 seconds. Could VBA have done that? Perhaps, but Power Query is specifically designed for this purpose.

When we get to Power Pivot, this is where VBA is no match whatsoever. Power Query loads data into Power Pivot which creates a dimensional data model that supports very advanced calculations. What does this mean? Imagine a super pivot table that isn’t constrained by what you can cram into a single source table. It is supremely flexible and can slice metrics by numerous related fields. There is no need for VLOOKUP and your workbook won’t be slowed down by too many rows, too many look ups, or that pesky INDIRECT function you’ve had no choice but to rely on.

How many times have you spent hours manually compiling a report, only for the boss or CFO to say “well…can you also have it broken out by client?” (or office location, product, quarter, etc). In that moment, you give a polite reassurance that this is no problem, then you swear and punch the air on the way back to your desk. I promise, Power Query and Power Pivot will improve your quality of life.

In conclusion, VBA is awesome and powerful, but I would propose that any interesting data worth analyzing can be represented by a table or set of tables. Power Query and Power Pivot are specifically designed for this purpose and have incredible power that VBA cannot match. For any analyst crunching through data spread across different systems, these data analysis tools built into Excel can be your salvation.

Where to start? These books are really fantastic for just that.

Collect, Combine, and Transform Data Using Power Query in Excel and Power BI by Gil Raviv

Analyzing Data with Power BI and Power Pivot for Excel by Alberto Ferrari and Marco Russo

The Definitive Guide to DAX: Business Intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel Second Edition by Alberto Ferrari and Marco Russo