Many students come to us wanting to learn how to create macros in Excel to automate their spreadsheets. However, they often don’t realize that formulas can be used to create most of the automation they want.
Although we can use macros to automate steps, there are limits to macros (especially the simplistic ones that are created by recording steps), and we still need to remember to run the macro. Thus, recorded macros are not full automation.
Full automation would mean not needing to click a button, using a shortcut, or adding a quick step to the Quick Access Toolbar. Fully automated macros can be created but require the use of Visual Basic for Applications (VBA). VBA is a smaller version of Visual Basic, added to each Microsoft application, and is limited to the application in which it is being used to create the modules or macros. This type of automation is very powerful (and is offered in our 2-day VBA course), but you are now talking about learning code to create these macros. Also, one character out of place in the code can make a macro inoperable, making it not the most efficient way to automate, in some cases.
Using macros is not the only way to automate Excel. You can often use functions and formulas to fully automate an Excel workbook. Functions and formulas do not require any shortcuts or buttons to make things happen; they only require data. As soon as data is entered, the functions and formulas go to work.
A Different Approach to Automation
Functions and formulas can be built into a template. This means that the areas where data is to be entered is empty and waiting for input. Then the functions and formulas create the magic after the data is entered. Since all the formatting, reports, and pivot tables can be built into the template as well, you can create a template for a finished workbook that will display robust visuals and calculations after a few pieces of data are entered. This can turn hours of work into mere minutes.
Microsoft has spent many years making Excel more robust, and has added a ton of functionality that can make full automation a reality. However, if you have not read about all the functionality available or had someone show you, then there is a whole world of possibilities you didn’t even know existed.
