Getting the Most out of Excel
Formulae and Visual Basic for Applications
It seemed silly to me while attending the New Jersey Institute of Technology that companies ranging from Pharmaceuticals to Financial Institutions to Information Technology would all use Microsoft Excel as their go to spreadsheet program. Here we have a seemingly simple tool, almost universally installed on Windows machines, 30+ years old and still dominating the field overwhelmingly. So naturally I wanted to take educate myself on how to get the most out of this tool that will be used in nearly every job I ever have.
I have always been of the “work smarter, not harder” mindset. Automation always captivated me and I saw how I could use Excel to automate tasks that most people would do manually over and over. It may feel like a daunting task but looking at the Excel function list is a good place to start educating yourself on getting more out of this program. Even just the top 10 most popular functions, from a simple Summation to the incredibly flexible VLOOKUP to a function that counts the number of days between two dates, shows what people want to do most: make their jobs easier.
Customers often ask me for Excel help while trying to sort, match or manage large groups of data and I always come back to VLOOKUP. VLOOKUP takes in just 4 parameters: lookup value (what you want to compare), table array (where you think the data exists), column index (the column that holds the data you need), and an optional range lookup for exact or approximate matches (I always go exact). With these 4 parameters I can compare two extremely large groups of data and automatically find needed data. For example:
A customer has two data sets: one with employee ID and email address, and another with employee ID and machine name. To combine the two would take either a lot of time manually, or less than five minutes with a simple VLOOKUP. My VLOOKUP would look something like:
To put it simply
=VLOOKUP(the cell where my first employee name starts, the WorkBook that has the Machinenames, the column with the machine names, [0 stands for an exact match, rather than approximate])
So in 40 characters and one formula I saved the customer from manually looking up thousands of machine names. Although this seems trivial, these types of scenarios are happening at nearly every company I’ve experienced. Someone somewhere right now is manually comparing two giant groups of data instead of typing a quick VLOOKUP, MATCH, etc. Knowing when to use the right formula is half the battle, and the only way to learn the ins and outs of each is to experiment.
While at NJIT I was always intrigued by computer programming; being able to tell a computer what to do and waiting for the output was just fascinating to me. So my deep dive into Excel started with a look into Visual Basic for Applications, which is the official programming language for the entire Microsoft Office Suite. (There are newer versions such as vb.net, however VBA is the official supported version).
Formulae just scratch the surface of the power of Excel, but VBA is where it’s truly unleashed. In the past I have written VBA Macros that automatically update a workbooks worth of numbers then automatically open Outlook and email those numbers to individual customers. I’ve used VBA to compare 5 different books to each other, opening and closing them one by one. I’ve even used VBA to generate pie charts and top 5 problem areas based on ticketing information.
VBA is a fairly easy language to learn, especially if you have any knowledge of an Object oriented language such as Java, but again it takes time to learn all of the different Function names, which frustratingly can be entirely different than their Microsoft Excel counterpart. For instance, StrComp compares two strings in VBA but in Excel you can simply use the “=” compare character. And although VBA is too high level for some of my customers to use, I would recommend it to anyone that does any manual data manipulation between multiple workbooks, or anyone that has repetitious daily tasks in excel. Putting in the extra time to learn VBA will be a net positive when these time saving methods are put into effect.
One of the best parts of Excel and VBA is the “record Macro” function, which I use because I simply forget some VBA function names! If I can’t remember exactly how to call a copy function, I hit record macro, copy a cell, and the VBA code writes itself. Truly a timesaving, headache saving feature.
Educating your way to a better Excel experience
Often I look at spreadsheets my customers use and I think “why are they doing it this way?” Usually it’s comparing mismatched data, or trying to add a chart that just doesn’t make sense, or WAY too many colored cells and texts, but the solution to this is education. There are free Excel tutorial videos, there are a large number of books, YouTube videos, etc. Educate yourself, educate your customers and help them educate themselves. Excel, backed by VBA, is a universal tool that is easy to get started and fun to try to master. Many customers will exclaim “you won’t believe how long that would have taken me!” or “wow you did that fast!”.