- It is a common desire amongst people to learn how to write Macros
- Working professionals, however, don’t really have the time required to learn macros
- People can benefit more by learning the new ways of working with Excel like dynamic arrays, new Excel 365 functions, writing own functions using lambda. These are easier to learn and more relevant for data analysis compared to macros
Many people working on Excel have a strong desire to learn macros. It sounds very impactful if you can say that you know how to write Macros. Of course, they will also perform complex tasks for you. Many have asked me if I can help them with learning macros. There are three questions that I ask them first.
Many people working on Excel have a strong desire to learn macros
The first question - Do you really need them?
The first question that I ask them is, “How many times have you used a macro or have seen anyone use a macro?” Most often, the answer is never. Many understand that they may have a desire to learn macros, but not necessarily a need.
The second question - Do you know how to code?
For the persistent few, I ask them the second question, “Do you know how to code?” If you want to write macros, then you must know how to code. If you don’t know how to code, you must have the patience to learn how to code.
Coding is fairly easy if you understand algorithms, data structure, conditions, loops, functions, objects and a few more things. If you don’t understand these well, then you will need to put in a bit more effort. And not to forget, you also need to learn the language in which you will code.
With effort, all this is possible, but requires a lot of time to understand the basics. It takes some more time to use it for simple operations, and a lot more to master it for running complex programs. Most working professionals cannot take out the time required to learn macros.
Even if the answer to both the questions is yes, the most important is the third question. I ask this question irrespective of the answers I receive for the first two questions.
Most working professionals cannot take out the time required to learn macros.
The third question - Are you making the most of what you already know?
The question is, “How well do you know the simple functions or formulas in Excel?” The answer to this question varies for every individual. And in this answer lies the solution, too.
Most everyday users of Excel do not know coding. While macros are extremely powerful, they are not a workable solution. Many organizations also restrict the use of macros because of security concerns. Even if you write a useful macro, you may not get to use it at your workplace.
It is my understanding that Microsoft also recognizes that. That’s why they have introduced so many new functions as part of the Excel 365 offering that the need to use macros is reducing significantly.
There are so many functions like MAXIFS, LARGE, TOCOL, CHOOSROWS, EXPAND, TAKE, DROP to name a few which have made working with Excel ridiculously simple. Earlier you had to write macros or rather complex formulas to get results which these function effortlessly get for you.
Many of them are based on dynamic arrays, which is the biggest open secret of Microsoft Excel. In the last several years, I have taught thousands of people either in-person or virtually and none of them knew about dynamic arrays. And after learning what they are, not one of them writes formulas without dynamic arrays. It is such a game-changer.
Excel has also introduced the capability to write your own functions. This is known as lambda. You can even write recursive lambdas. These are functions that can call themselves till a condition is met. You can also navigate row wise or column wise using the helper functions like BYROW and BYCOL.
And the time investment required to learn all this is just a few hours. These are simple Excel functions that everyday users and working professionals with no knowledge of coding can learn. Some of them are as simple as the SUM function, while the others can be slightly more challenging, like learning VLOOKUP for the first time.
The truth is most people need to learn simple Excel functions and need not really learn macros to do an excellent job of data analysis. I have been working with Excel since 1997, and it’s only one macro that I have used in those years. That too, for saving my analysis into a PDF file.
It pains me when I see people running after learning macros rather than learning how to use basic Excel functions. It is much simpler, quicker and far more relevant to learn these functions rather than learning something which you would rarely use.
What’s the best solution?
All those who understand the merit of my argument after my three questions often ask me another question after that. What’s the best way to learn the most important formulas, the new Excel 365 formulas, dynamic arrays, or create your own functions using Lambda? I tell them that there are three ways to do that.
- You can learn all these by asking around, or watching YouTube videos. But, you will have to invest the effort to track the new Excel releases and then identify what is relevant and then find people or videos that can teach you.
- You could request your organization to conduct training sessions for you. Many organizations plan for such sessions on employee request. These can be useful, however you must be able to align your availability with such sessions. When I conduct such immersive sessions on data analytics, usually only 80-90% identified people are able to attend due to conflicting priorities.
- You could enrol in a self-paced online course like our Excel Masterclass. In this course, we teach you everything you need to know about Excel and how you can use it as a great tool for data analysis. The focus is on solving real-world problems rather than just learning a few formulas. We keep updating the course so that you don’t have to look for the updates yourself and then find resources to learn them. In January 2024, we released the third major update to our course, and as always, the updates were for no additional cost to those who had already enrolled.
The new formulas in Excel 365 have significantly reduced the need to learn macros
Excel Masterclass is already one of the most comprehensive courses available, and we keep making it more wholesome with updates like these. If you are someone who also likes personal guidance, then you can select that option while purchasing the course, or even request for it at a later stage.
So next time, if you meet someone who says that they would like to learn macros, please ask them the three questions, or redirect them to this page. These questions will help them get clarity on what they are really looking for.
Click here to learn more about Excel Masterclass.