The Most Underutilized Automations Tool for Microsoft Office

May 24

When time is money, automation is your best friend. That’s something we’ve scaled a lot in the last few years here at FGS. We do a lot of variable data work, so figuring out how to quickly and accurately process data for mailers has been a consistent goal. We’ve been able to automate processes for a daily mailer we do using macros with Excel (you can also use macros in Word, Outlook, and more).

We’ve cut our processing time from over 5 hours to a mere 45 minutes, an 85.7% time reduction. While the initial setup took a greater deal of time than processing manually (primarily because I learned it all with no prior knowledge), our overall time saved will continue to grow. Last year alone I estimate around $19,000 saved through our automation solutions.

While the complexity of what we needed to do required me to learn to code for Visual Basic, there is a handy tool that can help any newcomer get their automation started right away. The “Record Macro” function (available in both Excel and Word) lets you record a series of actions that you can then automate.

To find this, you’ll need to add the Developer tab to your ribbon. You can do this by going to File > Options > Customize Ribbon > Main Tabs > Check the box by Developer

To record your macro, you’ll click the record macro button and give your macro a name. You can even give it a keyboard shortcut for easy access. You can also choose where you wish to store the macro – This Workbook, New Workbook, or Personal Macro Workbook. If you want the macro to be available when you open Excel, a hidden personal workbook is the way to go (more on that here). You’ll then perform the tasks you want your macro to perform and click the same button (which now says stop recording). This creates the code for what you want to do.

There are definitely some limitations to this feature – for example, all of your data references must be the same for each use. There is an option to “Use Relative References” which changes the code slightly:

Even using this option, though, there are many things you can’t accomplish without delving into the code itself. However, for someone starting out, this provides a great starting point and, with a little research, you can probably tweak the recorded code to suit your needs.