To date, our community has answered nearly 2 million questions in the Smarterer Excel test. Excel’s just one of those skills that almost everyone finds themselves needing at some point, and this series is here to help you improve and simplify your life with what can be a confusing program if you don’t know the tricks of the trade. In previous weeks we helped you out with shortcuts and formulas — Macros are another basic technique that will make your life a thousand times easier. Read on to find out how!
The Power of Automation
Are you tired of tasks that are repetitive and time-consuming? Such tasks are almost a given in entry level roles across many professions today. Thanks to the power of Excel, many of these tasks can be automated easily, and the entire process can be in your control. For part 4 of our Excel series, we will show you the magic of Keystroke Macros, so you can turn “mundane” into “fast lane”!
Suppose you’re a financial analyst using Excel to track expenses. Every week, your manager wants the overdue amounts formatted so that they’re very easy to see (e.g. bold, red, gray cell borders). To format one cell, you have to go to “Format Cells”, then click the “Font” tab and select “Color”. For the borders, click the “Borders” tab, select “Outline” and your desired colors on the lower left. That took nearly 10 clicks.
As you can see, if you have thousands of entries in random order, formatting each cell individually will take you several hours to complete the task (along with extreme boredom). Thankfully, you can record all these actions into a Macro, and apply them to any other cells with a click of a button.
Enabling the Magic
1. To use Macros, you must save your workbook into a new file. Choose “Save as”, and select “Excel Macro-Enabled Workbook (*.xlsm)” for the file type.
2. Click the Microsoft Office Button, and then click “Access Options”.
3. Click “Trust Center”, then click “Trust Center Settings” on the right, and then click “Macro Settings”.
4. Select “Enable all macros”.
Once Macros are enabled, you should see a “Developer” tab on the top right of the ribbon.
Steps to Heaven
1. In the workbook, click one of the cells you’re going to format.
2. Click the “Developer” tab, and then click “Record Macro”.
3. In the Record Macro dialog box, type a name for the macro in the Macro name box. Macro names must start with a letter and can include letters, numbers, and underscore characters, but can’t include spaces.
4. Specify a keyboard shortcut (aka keystroke) so you can invoke the Macro easily later on. Be sure to avoid using a keystroke that is already used by common Excel operations, such as Ctrl+c etc. We recommend Ctrl+j, Ctrl+k and Ctrl+m, since their default actions are not commonly used. You can cross reference your shortcuts with this list.
5. When you click OK, you’re ready to record. Until you stop the recording, every Excel command and keystroke will be recorded in the macro, in the order in which they are entered.
6. Now format the cell the way your manager instructed (e.g. bold, red, gray cell borders).
7. Once you’re done with all your desired actions, click “Stop Recording”.
Now you can go down the list and simply hit “Ctrl+j” to apply the format!
We hope that these tips save you a few hours and give you time to do something more productive, like learning another new skill! For more, check out Bill Jelen, the renowned “Mr. Excel” and Microsoft MVP, who offers videos showing many tricks on recording Macros.
Follow us @Smarterer to stay tuned for Part 5, Programming Basic VBA Macros — and let us know what other Excel skills you’d like to see here!