How Developers Can Use Excel to Accelerate Their Career

Whether you love it or hate it, you can't deny it - Microsoft Excel is everywhere.

And maybe because of this very fact, Developers, Data Scientists, and Business Intelligence professionals often view Excel as a “toy” application - perfectly adequate to help tech-challenged office workers accomplish basic tasks, but without much value for serious programmers.

Just because Excel can make life easier for folks lacking programming skills, it doesn’t mean it can’t also be a super helpful tool for developers.

I'd even argue that Excel is actually one of the most powerful applications ever built, with a massive and widely underappreciated feature set. It definitely has the horsepower - and flexibility - to play a useful role in any developer’s toolbox.

Excel as an Application Development Platform

Excel turns out to be a great platform for learning programming skills and application development principles because it demystifies those tools, by putting them in the hands of anybody with Microsoft Excel installed on their computer. Which is pretty much... every single person working in an office, especially at any major company and most startups.

Now if this sounds a little far-fetched, please hear me out. If you squint just a little bit, Microsoft Excel has all the essential components of a full-stack application development platform. And for rapid development of data-driven applications, you’d be hard-pressed to find a better solution.

Traditionally, application architectures are commonly understood to have three tiers or layers: a data tier, a business logic tier, and a presentation tier. Excel has a role to play in all three.

Excel as a Simple Data Store

The data tier, which is responsible for storing and managing the raw information used by an application, is commonly served by a database, such as a SQL Server or MongoDB.

While Excel wouldn't be considered a database by the strictest definition, it can be a perfectly adequate data storage mechanism for many purposes. The Excel grid is a basic example of this and Excel Tables make the analogy between storing data and a database a much closer fit.

And with the game-changing abilities of the Excel Data Model, Excel actually has a relational database-like mechanism that can efficiently store up to hundreds of millions of records in a single Excel workbook.

We can take things one step further with Power Query which gives Excel users an industrial-strength ETL (Extract, Transform, and Load) tool to fetch and transform large data sets from a wide variety of sources into Excel.

Applying Business Logic With Excel

Excel also brings powerful tools the middle tier (aka business logic application tier). This layer of an application is responsible for applying business rules and logic to the information stored in the data tier, so that the result can ultimately be fed to the application's user interface.

The most obvious option for applying business logic to data stored in our spreadsheets is formulas. Combined with Excel’s seemingly endless collection of functions (ranging from simple text and date manipulation to highly specific finance and engineering functions), Excel formulas can take your analysis a surprisingly long way.

And that doesn't even take Excel’s turbocharged formula language DAX into account. Especially designed for creating complex chunks of business logic called measures in the Excel Data Model, DAX is fast, flexible, and powerful. And unlike with traditional Excel formulas, DAX even allows you to incorporate some basic programming structures, like variables!

Excel as a User Interface / Presentation Layer

But where Excel arguably shines most of all is in the presentation tier, where an application’s data is displayed and made available for end users to interact with.

With an extensive collection of visualizations, from traditional line and column charts to more modern evolutions like Treemaps and Sparklines, Excel can “tell the story” of even the most complex data in an intuitive and visually appealing way.

Aside from the more obvious visualization tools like charts and graphs, Excel also has Pivot Tables, which are incredibly powerful tools for summarizing large amounts of data in a compact and readable way. And, unlike so many Enterprise reporting tools that require expensive licenses and extensive configuration, Pivot Tables allow users to drill down into row-level data behind the numbers they see - by default.

And with the more recent innovation of “Power” Pivot Tables, it's even possible to analyze and present data from multiple different related data sources - like tables from a relational database, for example - in a single place.

VBA - Excel’s Built-In Programming Language

On top of all of this is Excel’s built in programming language: Visual Basic for Applications, or VBA for short. VBA allows you to apply traditional programming structures like loops and IF statements to the Excel Object Model. That is, the hierarchy of objects - from a single cell on a worksheet all the way up to the application itself - in Microsoft Excel.

Some may argue with the concept of Excel formulas or even the DAX formula language being a “real” programming language, but there's no disputing that status for VBA.

While VBA may be a bit rougher around the edges than popular programming languages like Python or JavaScript, it is still Turing Complete and gives you all the standard features you'd expect from any programming language.

But VBA's real power is its access to, and control of, Excel's extensive Object Model. With Excel VBA for programmers, you can apply programmatic logic to most any Excel object - from ranges of cells, to charts and Pivot Tables, or even entire workbooks - empowering you to automate the vast majority of Excel’s extensive functionality.

Excel as an Application Development Platform: Pros and Cons

What do you think, are you starting to see how Excel can be a useful tool in your toolbox?

Admittedly there are points where the “Excel as application development platform” analogy breaks down a bit. For example, when it comes to applications that are focused on collecting and storing data from users, Excel is typically not going to be the best fit. While a combination of VBA and clever spreadsheet design can certainly make something like this work, you would likely be better off with a more purpose-built tool.

But when it comes to use cases that involve importing, processing, analyzing, and presenting data, in quantities large or small, Excel is a tough option to beat. Especially if you’re pressed for time to deliver a solution NOW, as we so often are in the real world.

There are individual applications that can perform any one of these tasks better (dedicated ETL tools for importing data, SQL for data processing, Python or R for advanced data analysis, Tableau/Power BI for visualizations, etc.), but good luck finding one that does ALL of them as well. Much less one that is so widely available, and with such a convenient user interface and shallow learning curve.

Which goes to another sneakily underrated benefit that Excel has for programmers, especially aspiring ones.

Start Building Solutions with Excel – Today

Excel is, in my opinion, the single most practical platform for gaining a foothold in the world of programming and application development.

What, you say? Not a language like Python, which is practically the lingua franca of introductory programming classes?

Well, if you're in college and have access to all the tools, textbooks, and support you've paid dearly for then by all means learn to program in Python or JavaScript. But if you're in the working world, there's a decent chance you may not have the tools to program in those languages installed on your computer; and even if you do, building such applications might be out of scope for your current role.

But there's a very good chance you do have Microsoft Excel, and an equally good chance that there is some monotonous, inefficient process around your office that could be somehow streamlined or automated with a pretty simple application of Excel’s tools.

With Excel, you don't have to wait until you've secured a Developer role role to build something useful. You can literally start today - I mean, right now! (At least, as soon as you're done reading this article).

Let Excel Teach You to Program

While Excel's programming tools may not be the shiniest, the Excel macro recorder provides novice programmers with one of the most powerful tools in existence for “grokking” programming code.

By recording macros and then inspecting the VBA code the macro recorder generates, you can gain that all-important intuition for how code works - and build something useful in the process.

I've personally observed numerous examples of entry-level analysts without a programming background (including myself!) get their start in programming by recording simple macros and then gradually working up to modifying the macro-generated VBA code.

Once you get comfortable with this, you can begin layering in programming logic: if statements, then loops, then organizing your code in a modular fashion, and so on. And all of this can be done organically, in the context of building real solutions at the office, without needing to create a bunch of contrived toy applications.

By doing this, you can contribute to your resume in two ways simultaneously: by learning programming AND stacking up real accomplishments that deliver tangible business value.

It's not just the macro recorder either. Excel’s suite of Business Intelligence tools - Power Query, Power Pivot, and The Excel Data Model - let you build powerful BI solutions using intuitive, graphical tools. And these tools double as a superb way to develop your intuition in important database concepts like normalization, relationships, data integrity, and more.

While you won't learn the syntax of a database programming language like SQL just from Excel, you will learn many of the fundamental concepts of relational databases just from working in the Data Model. This will give you a massive leg up on other developers when it comes to working with databases.

Finally, don't under-rate tried-and-true Excel formulas and functions for learning some basic programming principles.

Even if VBA seems a bit obscure and intimidating, Excel formulas and functions are within the reach of even the most beginner Excel users. And they introduce a whole slew of concepts - from conditional logic, to manipulating different data types such as numbers, text, and dates, to the mere concept of functions themselves. All of this provides a solid jumping off point for a deeper exploration into the world of programming.

The Bottom Line

Whether you're a programming novice or an experienced developer, there's an excellent chance Excel has something for you.

So the next time you're tempted to scoff at a colleague using Excel, consider that it just might be the fastest, best tool for the job they're doing... and it might just be for the job you're doing too.

I'm a self-taught programmer, and my Excel Bootcamp is a great place to start your Excel training. I'll teach you how to use Excel and help you accelerate your career whether you're a programmer or non-programmer.

See you inside!