By CPE Link Instructor David Ringstrom, CPA
A number of years ago I coined the phrase “Either you work Excel, or it works you.” I can’t count the number of knowing nods I’ve gotten when spreadsheet users relate to the latter. Even proficient Excel users can sometimes get snarled into a quagmire of repetitive work in Excel. However, for those up to the challenge, Excel does offer a way to lighten your workload.
Depending upon your desktop version of Excel, there’s a Record Macro button tucked away on the View tab or the Tools menu. Think of this is a built-in video camera in Excel, except that instead of simply recording a video of what you’re doing in Excel, it actually transcribes your actions into programming code that you can play back on demand. Even if you’ve never even seen a line of programming code, you can use the Macro Recorder to take your first step into a new realm of possibilities in Excel.
In simple terms, a macro is one or more lines of programming code that can be played back. A macro can be as simple as centering text across a range of columns, or an elaborate arrangement of programming code that takes minutes or even hours to complete. Many users shy away from macros in the same way that they eschew pivot tables in Excel—the terms are seemingly incomprehensible and unapproachable. Fortunately the macro recorder offers an easy on-ramp for anyone ready to get his or her feet wet. As for Pivot Tables, Excel 2013 has your back there as well. Click anywhere within a list of data, and then choose Recommended Pivot Tables from the Insert tab of the ribbon to receive point-and-click assistance.
Back to macros—you’re only moments away from creating your first one:
- Excel 2007 and later: Choose View, Macros, and then Record Macro.
- Excel 2003 or Excel for Mac 2011: Choose Tools, Macro, Record New Macro.
Carry out these actions in the Record Macro dialog box:
- Enter MyFirstMacro in the name field.
- Choose This Workbook from the Store Macro In list.
- Click OK to start recording.
Next, type the words “Hello, World!” in any blank worksheet cell, press Enter, and then turn off the Macro Recorder:
- Excel 2007 and later: Choose View, Macros, and Stop Recording.
- Excel 2003 and Excel for Mac 2011: Choose Tools, Macro, Stop Recording.
To test your work, carry out these steps in a blank worksheet:
- Excel 2007 and later: Click the Macros button on the View tab.
- Excel 2003 or Excel for Mac 2011: Choose Tools, Macro, Macros.
Next, double-click on MyFirstMacro in the Macro dialog box to run the macro. The words “Hello, World!” should appear in the active cell within your worksheet.
To save a macro for later playback, activate the workbook within which you recorded the macro, choose File, Save As, and then:
- Excel 2007 and later: Choose either Excel Macro-Enabled Workbook or Excel 97-2003 Workbook from the File Type list, and then save your workbook.
- Excel 2003 and Excel for Mac 2011: Simply save the workbook in the usual fashion.
Your macro can now be played back anytime the workbook is open in Excel, and even better, the macro can be run in any other workbook that is open at the same time as the macro workbook. You’re now among the initiated, so try using the Macro Recorder to automate repetitive tasks that you encounter, such as removing extraneous rows from an accounting report that you’ve exported, or adding your contact information to the bottom of a spreadsheet that you’re about to send out of the building. You’ll quickly find that there are limits to the Macro Recorder, but if you find creating macros intriguing, there’s a whole new world in Excel available for you to explore. If nothing else, being aware of Excel’s automation capabilities means you can seek help in the future when repetitive spreadsheet tasks become part of your daily grind.
Do note that in Excel 2007, 2010, or 2013 that if you save your workbook as an Excel Workbook—with the .xlsx extension—that your macros will be discarded. If you catch yourself doing this accidentally, immediately resave your workbook as a Macro-Enabled Workbook (.xlsm extension) or Excel 97-2003 Workbook (.xls extension) to preserve your work.
For more Macros tips, view David’s online CPE tutorials Introduction to Excel Macros or join him in his Live Webcasts.
About the author:
David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at firstname.lastname@example.org or follow him on Twitter. David speaks at conferences about Microsoft Excel, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB.